rs.FindFirst limits? BrettS 12/17/2008 3:31:00 PM 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?

Re: rs.FindFirst limits? "Dirk Goldgar" <dg[ at ]NOdataSPAMgnostics.com.invalid> 12/17/2008 3:37:37 PM "BrettS" <BrettS[ at ]discussions.microsoft.com> wrote in message news:64CC8A3B-0E61-44C1-AF05-E39B8190B1AC[ at ]microsoft.com... [Quoted Text] > 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)

Re: rs.FindFirst limits? BrettS 12/17/2008 4:04:10 PM "Dirk Goldgar" wrote: [Quoted Text] > "BrettS" <BrettS[ at ]discussions.microsoft.com> wrote in message > news:64CC8A3B-0E61-44C1-AF05-E39B8190B1AC[ at ]microsoft.com... > > 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.

RE: rs.FindFirst limits? BrettS 12/17/2008 6:22:03 PM 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.