calling other forms within a form

[macleodjb]

Hi guys,
I'm looking to have a form where i can enter a job number, on submit it will open editjob.form and populate the form elements with the database values. I'm new to vba with access so i don't know how to do it. How can i pass my variable from jobnumber.form to editjob.form, perform my database query and then populate the elements with the values? Thanks in advance.

 

[MajP]

If you have the control wizard turned on (majic wand icon), and you pick a command button in design view it will step you through everything you need. The wizard will prompt you which form to open, and prompt you to show all records or a specific record. It will then create the code for you.

 

[MajP]

Also when using the wizard choose "form operations" and then "open form". Your final code will look something like this with the name of your field and control.

Private Sub Command8_Click()
On Error GoTo Err_Command8_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "yourEditJobFormName"

stLinkCriteria = "[yourEditJobFieldName]=" & "'" & Me![yourControlNameWithID] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command8_Click:
Exit Sub

Err_Command8_Click:
MsgBox Err.Description
Resume Exit_Command8_Click

End Sub

 

[macleodjb]

Why do i get an error for this code snippet
"Error you can't reference a property or method for a control unless the control has focus"

<code>
On Error GoTo Err_SubmitJobNum_Click

Dim stDocName As String
Dim QueryJobNum As Variant
QueryJobNum = Text0.Text
stDocName = "Edit job"
DoCmd.OpenForm stDocName, acNormal, acEdit, "[job number] = " & QueryJobNum

Exit_SubmitJobNum_Click:
Exit Sub

Err_SubmitJobNum_Click:
MsgBox Err.Description
Resume Exit_SubmitJobNum_Click
</code>

 

[PHV]

Replace this:
QueryJobNum = Text0.Text
with this:
QueryJobNum = Text0.Value

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[macleodjb]

My form doesn't open to the specified record? Why doesn't this code work? It just opens to the first record in the table insteand of the queryjobnum.

<code>
Private Sub SubmitJobNum_Click()
On Error GoTo Err_SubmitJobNum_Click

Dim stDocName As String
Dim Where As String
stDocName = "Edit job"
QueryJobNum = Text0.Value
Where = "[job number] = " & QueryJobNum
DoCmd.OpenForm stDocName, acNormal, acEdit, Where, , , QueryJobNum

DoCmd.Close
Exit_SubmitJobNum_Click:
Exit Sub

Err_SubmitJobNum_Click:
MsgBox Err.Description
Resume Exit_SubmitJobNum_Click

End Sub
</code>