Me.Recordset.Clone

[tmarsh]

I'm having a bit of trouble with the following code:

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[LNAME] = '" & Me![Combo66] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

It's in the after update in a combobox on my form. It works OK in that I can select a staff name (LNAME) either by typing the first few letters or scrolling to the name. However, if there are 10 Smiths it will only go to the first one.

How can I include the first name? I've tried using and but I can't get it to work.

Thanks.

 

[Bat17]

Have the name list combo include the persons ID and use that for the criteria. It is what you should be storing anyway!

HTH

Peter

 

[RuralGuy]

In the query to which the form is bound create another field:
FullName: [FNAME] & " " & [LNAME]
or
FullName: [LNAME] & ", " & [FNAME]
then use the same idea in Combo66 as well. Post back if you need additional assistance. By the way, it would be even easier if you had StaffID AutoNumbers.

 

[tmarsh]

Have the name list combo include the persons ID and use that for the criteria. It is what you should be storing anyway!

HTH

Peter

When I do that I can only search by the id. I need to search by surname and name.

 

[Bat17]

the combo box will show you first name and surname if you set it up that way and filter to just the one you select.
Or do you realy mean that you want to select Smith and get the records for all staff named Smith?

Peter

Peter

 

[tmarsh]

In the query to which the form is bound create another field:
FullName: [FNAME] & " " & [LNAME]
or
FullName: [LNAME] & ", " & [FNAME]
then use the same idea in Combo66 as well. Post back if you need additional assistance. By the way, it would be even easier if you had StaffID AutoNumbers.

Not sure if this is what you mean but each staff member has an autonumber id. I tried to use this by making the id a combobox but I could only get it to search by id number.

 

[tmarsh]

the combo box will show you first name and surname if you set it up that way and filter to just the one you select.
Or do you realy mean that you want to select Smith and get the records for all staff named Smith?

Peter

Peter

No. If I select say Zenda Smith it might go to Abe Smith or whoever is the first smith. Then I have to skip through a few records to get to the one I want. It gets me near the record I want but not exactly there!

 

[Bat17]

In the table that the form is based on you should be storing the ID not the name.
Then when Julie Smith gets married and becomes Julie Jones you can just change her details in the staff table and not have to find and correct every record with her name on.
The combo should be set up with the staff id, First name, Second Name (and may be dept' or something as a tie-breaker) bind the controll on ID but set its width to 0, this is the way the wizard would normaly do it for you any way. That way, in the form you will see "John Smith" but you will save 10035 or whatever the ID happens to be.

HTH

Peter

 

[Paulsburbon]

Ok, I have the same problem. I have two combo boxes one to do a search by ID and one by name. If I had two Recordsets with the same name on both only the first one will show up if I click the second one. My bound column is the ID in the search by name combo box.

 

[RuralGuy]

Hi Paulsburbon,
Post the code in the AfterUpdate event of the SearchByName ComboBox.

 

[Paulsburbon]

Thanks, Here is the code I have in the events tab.

Private Sub Combo36_AfterUpdate()
Me.RecordsetClone.FindFirst "[lngInsID] = " & Me![Combo36]
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

Private Sub Combo36_GotFocus()
Me!Combo34 = ""
Me!Combo99 = ""
Me!Combo108 = ""
Me.Refresh
End Sub

 

[RuralGuy]

Your code should look like this for now:

Private Sub Combo36_AfterUpdate()
Me.RecordsetClone.FindFirst "[lngInsID] = " & Me![Combo36]
If Not Me.RecordsetClone.NoMatch Then
    Me.Bookmark = Me.RecordsetClone.Bookmark
Else
    '-- Diagnostic MsgBox - comment out when it works
    MsgBox "Unable to locate [lngInsID] = <" & Me.![Combo36] & ">"
End If
End Sub

 

[RuralGuy]

After some thought, lets change it to:

Private Sub Combo36_AfterUpdate()
Me.RecordsetClone.FindFirst "[lngInsID] = " & [b]Me!Combo36.Column(0)[/b]
If Not Me.RecordsetClone.NoMatch Then
    Me.Bookmark = Me.RecordsetClone.Bookmark
Else
    '-- Diagnostic MsgBox - comment out when it works
    MsgBox "Unable to locate [lngInsID] = <" & [b]Me.!Combo36.Column(0)[/b] & ">"
End If
End Sub