find record using combo box

[Song Su]

I want to use combo box to locate a name. After update, the combo box shows
the name but the form does not move to the record of combobox. Here is my
code:

Private Sub cboLook_AfterUpdate()

Dim rs As DAO.Recordset

'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[name]= """ & Me.cboLook & """"
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
frmFlexSubform.SetFocus
Set rs = Nothing

End Sub

 

[Allen Browne]

You have a field named Name?
Access is very likely to get that confused.
Rename the field in your tables, queries, forms, reports, macros and code.

To test what's going on, try:
Dim rs As DAO.Recordset
Dim strWhere as String

strWhere = "[name]= """ & Me.cboLook & """"
Debug.Print strWhere

Set rs = Me.RecordsetClone
rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "Not Found"
Else
me.bookmark = rs.bookmark
End If
etc

 

[Song Su]

I got 'Not Found' message. Rename [name] would solve it?

You have a field named Name?
Access is very likely to get that confused.
Rename the field in your tables, queries, forms, reports, macros and code.

To test what's going on, try:
Dim rs As DAO.Recordset
Dim strWhere as String

strWhere = "[name]= """ & Me.cboLook & """"
Debug.Print strWhere

Set rs = Me.RecordsetClone
rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "Not Found"
Else
me.bookmark = rs.bookmark
End If
etc
--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I want to use combo box to locate a name. After update, the combo box
shows the name but the form does not move to the record of combobox. Here
is my code:

Private Sub cboLook_AfterUpdate()

Dim rs As DAO.Recordset

'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[name]= """ & Me.cboLook & """"
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
frmFlexSubform.SetFocus
Set rs = Nothing

End Sub

 

[Song Su]

I took your advice and change [name] to [full_name]. I also discovered that
I bounded to wrong column in combo box. Now it's fixed.

I want to set up 2nd combo box to search [EN]. Error message says 'data type
mismatch'.
My [EN] is numerical field. how do I construct rs.FindFirst line?

Thanks.

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[EN] = '" & Me![cboFind] & "'"
Me.Bookmark = rs.Bookmark
frmFlexSubform.SetFocus

You have a field named Name?
Access is very likely to get that confused.
Rename the field in your tables, queries, forms, reports, macros and code.

To test what's going on, try:
Dim rs As DAO.Recordset
Dim strWhere as String

strWhere = "[name]= """ & Me.cboLook & """"
Debug.Print strWhere

Set rs = Me.RecordsetClone
rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "Not Found"
Else
me.bookmark = rs.bookmark
End If
etc
--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I want to use combo box to locate a name. After update, the combo box
shows the name but the form does not move to the record of combobox. Here
is my code:

Private Sub cboLook_AfterUpdate()

Dim rs As DAO.Recordset

'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[name]= """ & Me.cboLook & """"
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
frmFlexSubform.SetFocus
Set rs = Nothing

End Sub

 

[Song Su]

I figured out.
rs.FindFirst "[EN] = " & Me![cboFind]

I took your advice and change [name] to [full_name]. I also discovered that
I bounded to wrong column in combo box. Now it's fixed.

I want to set up 2nd combo box to search [EN]. Error message says 'data
type mismatch'.
My [EN] is numerical field. how do I construct rs.FindFirst line?

Thanks.

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[EN] = '" & Me![cboFind] & "'"
Me.Bookmark = rs.Bookmark
frmFlexSubform.SetFocus

You have a field named Name?
Access is very likely to get that confused.
Rename the field in your tables, queries, forms, reports, macros and
code.

To test what's going on, try:
Dim rs As DAO.Recordset
Dim strWhere as String

strWhere = "[name]= """ & Me.cboLook & """"
Debug.Print strWhere

Set rs = Me.RecordsetClone
rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "Not Found"
Else
me.bookmark = rs.bookmark
End If
etc
--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I want to use combo box to locate a name. After update, the combo box
shows the name but the form does not move to the record of combobox. Here
is my code:

Private Sub cboLook_AfterUpdate()

Dim rs As DAO.Recordset

'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[name]= """ & Me.cboLook & """"
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
frmFlexSubform.SetFocus
Set rs = Nothing

End Sub