Navigating using a combo box in a form - Access 2007

[jdashm]

Using Access 2007.
I am trying to use a combo box (based on a query) to navigate to the first occurrence of a record in a form.
The query is using a date selected in another control on the form to filter the list of items in the combo box.
That seems to be working.


However when I select an item in the list I get a syntax error. What am I missing here?

Here is a snippet of the code:

Sub cboMRN_AfterUpdate()
Dim rst as recordset
Set rst=me.RecordsetClone

rst.FindFirst "strMRN = " & cboMRN.Column(2)

Set rst= Nothing

End Sub

Thanks in advance for your help

[Naphta]

hello,

try this

rst.FindFirst "strMRN = ' " & cboMRN.Column(2) & "'"

bye

[jdashm]

Thanks for the reply.
I tried your solution and it still won't navigate

I did try a different twist using a unique identifier in the record to navigate to... Because this is a number field I did not add the single quotes.
It looks like this now.

rst.FindFirst "lngDeliveryID = " & cboMRN.column(0) -- lngDeliveryID is in the first column in the underlying query.

I am not getting any error messages but when I select an item from the combobox it just doesn't navigate.
Thanks in advance for your help.

[davegri]

Sub cboMRN_AfterUpdate()
    Dim rst as DAO.recordset
    Set rst=me.RecordsetClone
    debug.print cboMRN.Column(2)    'show what we are looking for
    rst.FindFirst "strMRN = " & cboMRN.Column(2)
    Set rst= Nothing
End Sub

What is strMRN? If it's a textbox on the form use Me.strMRN
If it's not a textbox but is a field in the form's recordsource, use [strMRN]

[jdashm]

Thanks for the response...
I tried the debug.print option... did not learn anything from that.

The code currently looks like this...

Private Sub cboFindPatient_AfterUpdate()

Dim rst as DAO.Recordset
Set rst= Me.RecordSetClone

rst.FindNext [lngDeliveryID] = cboFindPatient].Column(0)

Set rst=nothing

End Sub

lngDeliveryID is a bound control
cboFindPatient is a combo box set to display a list that (in its underlying query) filters by a date set in an unbound textbox. The combo box seems to be filtering out the unneeded values based on the date selected but will not navigate.

Thanks for looking at this.

[davegri]

OK, instead of debug.print
Msgbox cboFindPatient.column(0)
This will show what your search criteria is.
Also

rst.FindNext [lngDeliveryID] = [cboFindPatient].Column(0)

is missing the square bracket

[jdashm]

Thanks for this reply.
I tried it with message box and it is capturing the correct lngDeliveryID but not navigating to that record. I also put in the other square bracket
Am I missing some other statements here?

[davegri]

OHO...
it should be rst.findFIRST, not findNEXT!

[jdashm]

Thanks for the suggestion. However, that did not change the outcome.
It just won't navigate to the associated record.

See previous post above from me with code example and other info
Any help would be appreciated.

rst.FindFirst [lngDeliveryID] = [cboFindPatient].Column(0)

[davegri]

rst.FindFirst [lngDeliveryID] = 5

What happens if you hard code a known good value for lngDeliveryID?
Also what's the SQL for the form's recordsource?