Problems with search code

[BlueJacket]

I made a basic form for a County Information table.

I'm trying to use some VBA code I found (I can't post the direct link, but it's from fontstuff), so that I can perform a simple search for each county outside of the Navigation Bar. This is the code that I'm using:

Private Sub cboCountySearch_AfterUpdate()

On Error Resume Next

Dim rst As Object
Set rst = Me.RecordsetClone
rst.FindFirst "CountyID=" & Me.cboCountySearch.Value
Me.Bookmark = rst.Bookmark

End Sub

Private Sub Form_Current()

Me.cboCountySearch.Value = Me.CountyID.Value

End Sub

No Control Source. In my Row Source, I have: SELECT [County Information].CountyID FROM [County Information];

What am I doing wrong? When I use the combo box to switch counties, nothing happens. The CountyID (PK) is the name of the county itself (no duplicates since we only work within one state), instead of a number. Could that be it? Since this works within another form linked to a table that has the PK as a number.

 

[sneuberg]

If CountyID is text then the combo box should be in single quotes like:

rst.FindFirst "CountyID= [COLOR="Red"]'[/COLOR]" & Me.cboCountySearch[COLOR="red"] & "'"[/COLOR]

but there maybe other problems as I think you should have been getting syntax errors.

 

[sneuberg]

Just for your information it is not necessary to include the Value property for text boxes and combo boxes as that is the default. This for example

Me.cboCountySearch.Value = Me.CountyID.Value

could be written as

Me.cboCountySearch = Me.CountyID

 

[BlueJacket]

If CountyID is text then the combo box should be in single quotes like:

rst.FindFirst "CountyID= [COLOR="Red"]'[/COLOR]" & Me.cboCountySearch[COLOR="red"] & "'"[/COLOR]

but there maybe other problems as I think you should have been getting syntax errors.

What does this do and why would it cause a syntax error?

 

[MarkK]

Quotes specify that the data contained in the quotes is a string. This matters because...

"12" + "12" = "1212"

...but...

12 + 12 = 24

...so if the field in your table is a string, which you say CountyID is, then you need quotes when doing a comparison to data in that field.

 

[sneuberg]

For text and dates the beginning and end need to be signaled somehow and for text it quotes are used. Usually single quotes are used but you can used double quotes but you need two to indicate a double quote within a quote so

rst.FindFirst "CountyID= """ & Me.cboCountySearch & """"

would work too. You can read up on how and when to use delimiters here. Note that the expression you put after rst.FindFirst acts like an SQL WHERE clause.

 

[BlueJacket]

Quotes specify that the data contained in the quotes is a string. This matters because...

"12" + "12" = "1212"

...but...

12 + 12 = 24

...so if the field in your table is a string, which you say CountyID is, then you need quotes when doing a comparison to data in that field.

I see what you're saying. Now I think my confusion is coming from trying to fully understand that line of code.

rst.FindFirst "CountyID= " & Me.cboCountySearch.Value

This is saying to find the first value in CountyID that matches the value in cboCountySearch, right? I see why "County ID =" needs to be quotes, but in the example above (rst.FindFirst "CountyID= '" & Me.cboCountySearch & "'"), we have the first single quote inside of the quotes of "CountyID= " and then second single quote coming outside of everything else, inside its own double quotes. I'm confused why the single quotes are placed where they are and why the second single quote

& "'"

is written the way it is.

 

[BlueJacket]

Also, the fix worked brilliantly and I appreciate you two helping me out.

Edit: Reading up on delimiters now. Thanks sneuberg.

 

[sneuberg]

I see what you're saying. Now I think my confusion is coming from trying to fully understand that line of code.

"CountyID= " & Me.cboCountySearch.Value

This is saying to find the first value in CountyID that matches the value in cboCountySearch, right? I see why "County ID =" needs to be quotes, but in the example above (rst.FindFirst "CountyID= '" & Me.cboCountySearch & "'"), we have the first single quote inside of the quotes of "CountyID= " and then second single quote coming outside of everything else, inside its own double quotes. I'm confused why the single quotes are placed where they are and why the second single quote

& "'"

is written the way it is.

Let's for example say the the value in the combo box is SC then the expression after rst.FindFirst needs to look like

CountyID = 'SC'

after it is evaluated. This evaluation is the concatenation of the strings involved. So the string parts

"County ID '"         [B]String Literal[/B]
Me.cboCountySearch    [B]String Variable[/B]
"'"                   [B]String Literal[/B]

are put together by the & operator to form the required result. Please read the links I provided. They should clear this up.

 

[BlueJacket]

That actually explained it quite nicely, having it broken down like that. Thank you.