Find First on Other Form

  • Thread starter Thread starter CJ
  • Start date Start date
C

CJ

Happy Spring Groupies!!

I am trying to have access open a second form (and find the first occurrence
of a job number) based on what I select on my first form. Unfortunately, I
am getting a syntax error in my code:

Dim rs As DAO.Recordset
Dim strFind As String
Dim JobNumber As String
Set rs = Me.RecordsetClone
strFind = Me.cmbJob
docmd.openform "frmPopEditMLT",acnormal,rs.FindFirst "[JobNumber]=""" &
strFind & """" <syntax error>

I am not very good with code. Can somebody please help me fix this?
 
The code says that JobNumber is a text field. Is that correct?
First, Apostrophe out the Docmd line and put in
MsgBox strFind
See if it says what you think it ought to
If that gives you syntax error then you are possibly referring to the wrong
column in the combo and may need to use something like Me.cmbJob.Column(1)
(Column 1 is the *second* column in the Column's Row Source query when seen
in design view)


If your combo has the correct info, you could try this differently using
OpenArgs.
In the form you want to open, put

Private Sub Form_Open(Cancel As Integer)
If Len(Me.OpenArgs) > 0 Then
'Checks that openargs have been used to open the form
Me.RecordsetClone.FindFirst "[JobNumber]=""" & OpenArgs & """"
Me.Bookmark = Me.RecordsetClone.Bookmark
End If
End Sub

In your current form have
Dim strFind as String
strFind = Me.cmbJob
DoCmd.OpenForm "frmPopEditMLT",acNormal, , , , , strFind
'note the number of commas after acNormal
'uses OpenArgs to pass the value to your form
End If

Evi
 
CJ said:
Happy Spring Groupies!!

I am trying to have access open a second form (and find the first
occurrence of a job number) based on what I select on my first form.
Unfortunately, I am getting a syntax error in my code:

Dim rs As DAO.Recordset
Dim strFind As String
Dim JobNumber As String
Set rs = Me.RecordsetClone
strFind = Me.cmbJob
docmd.openform "frmPopEditMLT",acnormal,rs.FindFirst "[JobNumber]=""" &
strFind & """" <syntax error>

I am not very good with code. Can somebody please help me fix this?
Click to expand...

Well, thanks to both of you. I think you both got my synapse working.

It was not as complex as we were making it out to be:

DoCmd.OpenForm "frmPopEditMLT", acNormal, , "JobNumber =
forms.frmPopLookupMLT.cmbjob" (frmPopLookupMLT is my first form)

Works just fine!
 
Back
Top