rs.FindFirst limits?

[BrettS]

Hi, I've been using rs.FindFirst to search by two criteria using the
following code:

Private Sub FilterSamp_AfterUpdate()
On Error GoTo err_Handler
Dim rs As DAO.Recordset

If Not IsNull(Me.FilterSamp) Then
If Me.Dirty Then
Me.Dirty = False
End If
Set rs = Me.RecordsetClone
rs.FindFirst "[RE Job #] = """ & Me![RE Job #] & """ and [Task Samp
#] = " & Me.FilterSamp
If rs.NoMatch Then
MsgBox "Not found: Try Another Record"
Else
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If

Me.FilterSamp = ""
Me.Refresh

exit_Here:
Exit Sub

err_Handler:
MsgBox Err.Number & ": " & Err.Description, vbCritical, "***ERROR***"
End Sub



This is working great, my question is whether or not I can use this to
search by three criteria. It seems the answer is no, because when I try to
add this to the line for another form:

rs.FindFirst "[RE Job #] = """ & Me.[RE Job #] & """ and [Task Samp #] = " &
Me.[Samp ID #] and [Meas ID #] = " & Me.[Meas ID #]

when I actually use the combobox it edits the line and puts a " at the end,
and also seems to put it into its own expression? I'm only marginally
functional in VB so I'm not sure, but the second and gets capitalized and
turns into the navy blue text, so I'm assuming that is what is happening. Is
there any way to get around this? Or is two fields the maximum you can use
for rs.FindFirst?

 

[Dirk Goldgar]

Hi, I've been using rs.FindFirst to search by two criteria using the
following code:

Private Sub FilterSamp_AfterUpdate()
On Error GoTo err_Handler
Dim rs As DAO.Recordset

If Not IsNull(Me.FilterSamp) Then
If Me.Dirty Then
Me.Dirty = False
End If
Set rs = Me.RecordsetClone
rs.FindFirst "[RE Job #] = """ & Me![RE Job #] & """ and [Task Samp
#] = " & Me.FilterSamp
If rs.NoMatch Then
MsgBox "Not found: Try Another Record"
Else
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If

Me.FilterSamp = ""
Me.Refresh

exit_Here:
Exit Sub

err_Handler:
MsgBox Err.Number & ": " & Err.Description, vbCritical, "***ERROR***"
End Sub



This is working great, my question is whether or not I can use this to
search by three criteria. It seems the answer is no, because when I try
to
add this to the line for another form:

rs.FindFirst "[RE Job #] = """ & Me.[RE Job #] & """ and [Task Samp #] = "
&
Me.[Samp ID #] and [Meas ID #] = " & Me.[Meas ID #]

when I actually use the combobox it edits the line and puts a " at the
end,
and also seems to put it into its own expression? I'm only marginally
functional in VB so I'm not sure, but the second and gets capitalized and
turns into the navy blue text, so I'm assuming that is what is happening.
Is
there any way to get around this? Or is two fields the maximum you can
use
for rs.FindFirst?

No, there's no maximm number of fields you can specify in the criterion.
It's just that you left out a quote. Try this:

rs.FindFirst _
"[RE Job #] = """ & Me.[RE Job #] & """ and [Task Samp #] = " _
& Me.[Samp ID #] & " and [Meas ID #] = " & Me.[Meas ID #]

 

[BrettS]

Hi, I've been using rs.FindFirst to search by two criteria using the
following code:

Private Sub FilterSamp_AfterUpdate()
On Error GoTo err_Handler
Dim rs As DAO.Recordset

If Not IsNull(Me.FilterSamp) Then
If Me.Dirty Then
Me.Dirty = False
End If
Set rs = Me.RecordsetClone
rs.FindFirst "[RE Job #] = """ & Me![RE Job #] & """ and [Task Samp
#] = " & Me.FilterSamp
If rs.NoMatch Then
MsgBox "Not found: Try Another Record"
Else
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If

Me.FilterSamp = ""
Me.Refresh

exit_Here:
Exit Sub

err_Handler:
MsgBox Err.Number & ": " & Err.Description, vbCritical, "***ERROR***"
End Sub



This is working great, my question is whether or not I can use this to
search by three criteria. It seems the answer is no, because when I try
to
add this to the line for another form:

rs.FindFirst "[RE Job #] = """ & Me.[RE Job #] & """ and [Task Samp #] = "
&
Me.[Samp ID #] and [Meas ID #] = " & Me.[Meas ID #]

when I actually use the combobox it edits the line and puts a " at the
end,
and also seems to put it into its own expression? I'm only marginally
functional in VB so I'm not sure, but the second and gets capitalized and
turns into the navy blue text, so I'm assuming that is what is happening.
Is
there any way to get around this? Or is two fields the maximum you can
use
for rs.FindFirst?

No, there's no maximm number of fields you can specify in the criterion.
It's just that you left out a quote. Try this:

rs.FindFirst _
"[RE Job #] = """ & Me.[RE Job #] & """ and [Task Samp #] = " _
& Me.[Samp ID #] & " and [Meas ID #] = " & Me.[Meas ID #]


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Thanks a lot, Dirk! It works great.

 

[BrettS]

I have another question regarding this code. I have multiple forms, each
with their own tables, that all have the same field in it ([RE Job #]). I
have these comboboxes on each form, that show the data in that table by RE
Job # using pretty much the same code as before, only this is only searching
by this one criterion:

Private Sub FilterTask_AfterUpdate()
On Error GoTo err_Handler
Dim rs As DAO.Recordset

If Not IsNull(Me.FilterTask) Then
If Me.Dirty Then
Me.Dirty = False
End If
Set rs = Me.RecordsetClone
rs.FindFirst "[RE Job #] = """ & Me.FilterTask & """"
If rs.NoMatch Then
MsgBox "Not found: Try Another Record"
Else
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
Me.FilterTask = ""
Me.Refresh

exit_Here:
Exit Sub

err_Handler:
MsgBox Err.Number & ": " & Err.Description, vbCritical, "***ERROR***"
End Sub

I would like to add to each AfterUpdate event some code that would force the
equivalent AfterUpdate for all the forms, so that when the user changes which
RE Job # they're viewing on one form, it forces the same changes on the other
forms. I'm guessing this would go somewhere after the "End If" in that block
of code. Thanks for any help.