FindFirst sticking with first record?

[BonnieW via AccessMonster.com]

I have a combo box that queries from a table of people to get a list of names,
ordered lastname, firstname, peopleID. There are several people in this list
that have the same last name, and no matter which person I choose with that
last name, Access chooses the first person with that last name.

(So effectively, if I have Smith Jon 1, Smith Jane 8, Smith Harold 9074395,
it'll always stick with Smith Harold 9074395.)

The VBA behind this is:
Private Sub Combo26_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[LastName] = '" & Me![Combo26] & "'"
If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
End If
Rem MsgBox "Check back later this is being improved!"
DoCmd.GoToControl "Command28"
End Sub


Advice please?

 

[ruralguy via AccessMonster.com]

You need to FindFirst by an ID number rather than the last name.

I have a combo box that queries from a table of people to get a list of names,
ordered lastname, firstname, peopleID. There are several people in this list
that have the same last name, and no matter which person I choose with that
last name, Access chooses the first person with that last name.

(So effectively, if I have Smith Jon 1, Smith Jane 8, Smith Harold 9074395,
it'll always stick with Smith Harold 9074395.)

The VBA behind this is:
Private Sub Combo26_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[LastName] = '" & Me![Combo26] & "'"
If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
End If
Rem MsgBox "Check back later this is being improved!"
DoCmd.GoToControl "Command28"
End Sub

Advice please?

 

[BonnieW via AccessMonster.com]

Wonderful! Thanks! I changed it up so that it's no longer referencing
LastName, but PeopleID.

Private Sub Combo26_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[PeopleID] = '" & Me![Combo26] & "'"
If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
End If
Rem MsgBox "Check back later this is being improved!"
DoCmd.GoToControl "Command28"

Now it's giving me error 3464: Data type mismatch in criteria expression. I
assume that this means now that it's a number field, not a text field, I need
to get rid of some of the quotation marks- but I don't know which ones.

Sorry about my noobishness; this is not my code even in the slightest, nor
have I ever really had to deal with VBA til about a week ago.

You need to FindFirst by an ID number rather than the last name.

I have a combo box that queries from a table of people to get a list of names,
ordered lastname, firstname, peopleID. There are several people in this list

[quoted text clipped - 19 lines]

Advice please?

 

[ruralguy via AccessMonster.com]

Which column in the ComboBox has the PeopleID? If it is the bound column
then you can just change this like to remove the quotes:

rs.FindFirst "[PeopleID] = " & Me.Combo26

If it is other than the bound column then you can use the column property:

rs.FindFirst "[PeopleID] = " & Me.Combo26.Column(2)

...for the 3rd column since the column index is zero based.

Wonderful! Thanks! I changed it up so that it's no longer referencing
LastName, but PeopleID.

Private Sub Combo26_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[PeopleID] = '" & Me![Combo26] & "'"
If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
End If
Rem MsgBox "Check back later this is being improved!"
DoCmd.GoToControl "Command28"

Now it's giving me error 3464: Data type mismatch in criteria expression. I
assume that this means now that it's a number field, not a text field, I need
to get rid of some of the quotation marks- but I don't know which ones.

Sorry about my noobishness; this is not my code even in the slightest, nor
have I ever really had to deal with VBA til about a week ago.

You need to FindFirst by an ID number rather than the last name.

[quoted text clipped - 3 lines]

 

[BonnieW via AccessMonster.com]

It's the third column that holds the PeopleID. The second code bit you
provided worked perfectly! Thank you very much!

Which column in the ComboBox has the PeopleID? If it is the bound column
then you can just change this like to remove the quotes:

rs.FindFirst "[PeopleID] = " & Me.Combo26

If it is other than the bound column then you can use the column property:

rs.FindFirst "[PeopleID] = " & Me.Combo26.Column(2)

...for the 3rd column since the column index is zero based.

Wonderful! Thanks! I changed it up so that it's no longer referencing
LastName, but PeopleID.

[quoted text clipped - 22 lines]

 

[ruralguy via AccessMonster.com]

That's great Bonnie. Glad I could help.

It's the third column that holds the PeopleID. The second code bit you
provided worked perfectly! Thank you very much!

Which column in the ComboBox has the PeopleID? If it is the bound column
then you can just change this like to remove the quotes:

[quoted text clipped - 12 lines]

 

[Susie Johnson]

findFirst is a DAO method; and DAO has been depecrated

move to ADO and use a real FILTER method

 

[ruralguy via AccessMonster.com]

I'm sorry but DAO has *not* been depreciated. Where do you get this
information?

findFirst is a DAO method; and DAO has been depecrated

move to ADO and use a real FILTER method

I have a combo box that queries from a table of people to get a list of names,
ordered lastname, firstname, peopleID. There are several people in this list

[quoted text clipped - 19 lines]

Advice please?