Sub Form Usage

[bassmanfranc]

Perhaps I need a refresher on the use of subforms...

I would like to be able to use a command button to direct the user to a new
form to enter additional information to keep the main form (screen) as clean
as possible.

I have placed the button and am able to get it to open a new user form,
however, I don't know how to get that data to be placed into the database
like it does on the original form. And maybe there is another way to go about
this? (Multipage?..then how do I hide that multipage on the original form?)

ORIGINAL FORM CODE
Private Sub cmdAddPrj_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("ProjectData")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for a project number
If Trim(Me.txtPrjNo.Value) = "" Then
Me.txtPrjNo.SetFocus
MsgBox "Please enter a project number"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtPrjNo.Value
ws.Cells(iRow, 2).Value = Me.txtPrjNm.Value
ws.Cells(iRow, 9).Value = Me.txtPrjTyp.Value
ws.Cells(iRow, 10).Value = Me.txtCon.Value
ws.Cells(iRow, 11).Value = Me.txtEst.Value
ws.Cells(iRow, 12).Value = Me.txtPm.Value
ws.Cells(iRow, 21).Value = Me.txtJobcst.Value
ws.Cells(iRow, 22).Value = Me.txtHrdCst.Value
ws.Cells(iRow, 23).Value = Me.txtMrkup.Value
ws.Cells(iRow, 24).Value = Me.txtGm.Value
ws.Cells(iRow, 25).Value = Me.txtSfEa.Value
ws.Cells(iRow, 26).Value = Me.txtPrjCstSfEa.Value
ws.Cells(iRow, 27).Value = Me.txtHcSfEa.Value
ws.Cells(iRow, 28).Value = Me.txtMuSfEa.Value
ws.Cells(iRow, 4).Value = Me.txtPrjStrAddr.Value

'clear the data
Me.txtPrjNo.Value = ""
Me.txtPrjNm.Value = ""
Me.txtPrjTyp.Value = ""
Me.txtCon.Value = ""
Me.txtEst.Value = ""
Me.txtPm.Value = ""
Me.txtJobcst.Value = ""
Me.txtHrdCst.Value = ""
Me.txtMrkup.Value = ""
Me.txtGm.Value = ""
Me.txtSfEa.Value = ""
Me.txtPrjCstSfEa.Value = ""
Me.txtHcSfEa.Value = ""
Me.txtMuSfEa.Value = ""
Me.txtPrjStrAddr.Value = ""

End Sub

Private Sub CmdBtnClntAddrsFrm_Click()
frmClntAddress.Show
End Sub

Private Sub cmdClose_Click()
Unload Me
End Sub

Private Sub TextBox1_Change()

End Sub


Private Sub Label1_Click()

End Sub

Private Sub CommandButton1_Click()

End Sub

Private Sub TabProject_Change()

End Sub

Private Sub lblClntAddrs_Click()

End Sub

Private Sub MultiPage1_Change()

End Sub

Private Sub txtHrdCst_Change()

End Sub

Private Sub txtJobcst_Change()
If IsNumeric(Me.txtMrkup.Value) _
And IsNumeric(Me.txtJobcst.Value) Then
If CDbl(Me.txtJobcst.Value) <> 0 Then
Me.txtGm.Value = FormatPercent(CDbl(Me.txtMrkup.Value) _
/ CDbl(Me.txtJobcst.Value), 2)
End If
End If

End Sub

Private Sub txtMrkup_Change()
If IsNumeric(Me.txtMrkup.Value) _
And IsNumeric(Me.txtJobcst.Value) Then
If CDbl(Me.txtJobcst.Value) <> 0 Then
Me.txtGm.Value = FormatPercent(CDbl(Me.txtMrkup.Value) _
/ CDbl(Me.txtJobcst.Value), 2)
End If
End If

End Sub

Private Sub txtPrjCstSfEa_Change()

End Sub

Private Sub txtPrjStrAddr_Change()

End Sub

Private Sub txtSfEa_Change()
If Me.txtJobcst.Value <> "" Then
Me.txtPrjCstSfEa = FormatCurrency(Me.txtJobcst.Value / Me.txtSfEa.Value, 2)
End If

If Me.txtHrdCst.Value <> "" Then
Me.txtHcSfEa = FormatCurrency(Me.txtHrdCst.Value / Me.txtSfEa.Value, 2)
End If

If Me.txtMrkup.Value <> "" Then
Me.txtMuSfEa = FormatCurrency(Me.txtMrkup.Value / Me.txtSfEa.Value, 2)
End If

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "Please use the Close Form button!"
End If
End Sub

NEW FORM CODE: Not much there yet...just an address label and textbox
'copy the data to the database
ws.Cells(iRow, 4).Value = Me.txtClntStrtAddrs1.Value

End Sub

Private Sub Label1_Click()

End Sub

Private Sub txtClntStrtAddrs1_Change()

End Sub

 

[Susan]

rather than calling up secondary userforms, i use a multipage form.
that way you can have as many pages as you want...........
i keep only the first one either visible or enabled, with the basic
entry info & check boxes that indicate what the user needs, and then
based on that "next" click button, it enables and/or makes visible the
additional pages that need filling in. then when everything is filled
in, the final "finished" button enters everything into the database.
i wish i could paste a picture of one for you to see how it
works............
i could e-mail you a sample workbook..........
susan

 

[bassmanfranc]

If you think e-mailing me the workbook would help, you can send it to
(e-mail address removed)

Thanks again for all of your help and willingness to share.

 

[Susan]

sent workbook via aol....... let me know if it doesn't come thru.
susan

 

[bassmanfranc]

Go it and replied via regular e-mail!

Thanks again.