Synching Find Record Combo Box 1

[shelby55]

Hello

I am using Access 2003 for a patient falls database. I have a form that is being used to add each fall incident to a table. I have a combo box that I want the user to be able to select an account number (which is a unique identifier) and have the form go to that record.

So the source type is table/query of tblFalls and the after update code is:

Private Sub cmbFind_AfterUpdate()
    ' Find the record that matches the control.
    Me.RecordsetClone.FindFirst [AcctNo] = "'" & Me![cmbFind]
    Me.Bookmark = Me.RecordsetClone.Bookmark
    [ChartNo].SetFocus

Why is this not working?

Also, some patients could have multiple hospital visits with falls so I'd like to have another combo box that you could select the chart number and then in the find record combo box would only show the account numbers associated with the chart number selected in the first combo box.

However, I'd like users to be able to use either method i.e. chart first and then select from applicable account numbers or just select account number. Thanks.

 

[MajP]

You have a stand alone single quote, wrong in any case

if the acctNo is text
Me.Recordset.FindFirst "AcctNo = '" & Me![cmbFind] & "'"
if numeric
Me.Recordset.FindFirst "AcctNo = " & Me![cmbFind]

If you use the recordset instead of the recordsetclone the above line is all that is needed. No bookmarks.

 

[shelby55]

Thanks so much, MajP....that did the trick!

 

[shelby55]

Sorry but part 2 of my question was about being able to select on chart number and synch the account number box to that.

Can I do both so that the user can either select by account number OR select by chart number which then limits the account number combo box to only include account numbers related to that chart number?

Thanks.