Record Navigation by ComboBox

N

Nick Shinkins

I am trying to use a combobox populated by a field to navigate between
records in a form.

I have seen this done before but I seem to keep generating errors when I try.

The code I am using is:

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

Set rs = Me.Recordset
SearchName = Me!ClientNameComboBox
rs.FindFirst "Client Name = " & SearchName
Me.Bookmark = rs.Bookmark

End Sub

Client Name is the field I am looking through and trying to match but I keep
getting a syntax error.

Any suggestions? I can provide more detail if needed,

Thanks in advance,

Nick Shinkins
 
J

JackP

try this...

Private Sub ClientNameComboBox_AfterUpdate()
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[ClientName] = " & Me![ClientNameComboBox]
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

....works for me, anyhow
 
D

Douglas J Steele

Assuming that Client Name is a text field, the value you pass to it must be
enclosed in quotes. As well, since your field name has an embedded blank
(seldom a good idea!), you need to enclose the field name in square
brackets:

rs.FindFirst "[Client Name] = " & Chr$(34) & SearchName & Chr$(34)

Chr$(34) is the representation for "

You could also have tried

rs.FindFirst "[Client Name] = '" & SearchName & "'"

where, exagerated for clarity, that's

rs.FindFirst "[Client Name] = ' " & SearchName & " ' "

except that would fail for names that have an apostrophe in them, such as
O'Connor.
 
N

Nick Shinkins

Thanks to both Jack and Douglas for your help,

It was indeed the lack of quotation marks that was causing the code to fail.
Not a problem that I anticipated.

I think the previous example I was looking at was searching through a
numerical ID field.

Nick

Douglas J Steele said:
Assuming that Client Name is a text field, the value you pass to it must be
enclosed in quotes. As well, since your field name has an embedded blank
(seldom a good idea!), you need to enclose the field name in square
brackets:

rs.FindFirst "[Client Name] = " & Chr$(34) & SearchName & Chr$(34)

Chr$(34) is the representation for "

You could also have tried

rs.FindFirst "[Client Name] = '" & SearchName & "'"

where, exagerated for clarity, that's

rs.FindFirst "[Client Name] = ' " & SearchName & " ' "

except that would fail for names that have an apostrophe in them, such as
O'Connor.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Nick Shinkins said:
I am trying to use a combobox populated by a field to navigate between
records in a form.

I have seen this done before but I seem to keep generating errors when I try.

The code I am using is:

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

Set rs = Me.Recordset
SearchName = Me!ClientNameComboBox
rs.FindFirst "Client Name = " & SearchName
Me.Bookmark = rs.Bookmark

End Sub

Client Name is the field I am looking through and trying to match but I keep
getting a syntax error.

Any suggestions? I can provide more detail if needed,

Thanks in advance,

Nick Shinkins
Click to expand...
Click to expand...
 
Top