Need help understanding VB syntax of .FindFirst command

[Guest]

At the end of this message I have listed a Sub routine that Access 2003
automatically generated in the AfterUpdate event of a combo box. This Sub
syncs the combo box to the Form when the combo box changes.
I want understand how this code works so I can make changes if needed. I
have work for days to understand it and think I have most of it down. Except,
I do not understand the programming syntax this line of code:

rs.FindFirst "[TP-Key] = " & Str(Nz(Me![Combo8], 0))

I how it works, it searches for the first record in the recordset that is
equal to the value in the combo box combo8. I understand “rs.FindFirst†and
also “(Nz(Me![Combo8], 0†parts. But the -- "[TP-Key] = " & Str -- syntax I
have not been able to understand …..why is there a -- &-- and the quotes
around the --"[TP-Key] =.
In general does any one know where I can get this information, I have tried
but was not successful

Thanks for your help
Brad


Private Sub Combo8_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
'this declares variable rs as type Object
Set rs = Me.Recordset.Clone
'This make a duplicate of original in variable rs
rs.FindFirst "[TP-Key] = " & Str(Nz(Me![Combo8], 0))
'This finds the first record recordset that is equal to the
combo8
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
‘This sets the current record of the recordset to the one found
End Sub

 

[Guest]

[TP-Key] is the name of a field in the recordset. The Findfirst will look
for the vaule returned from Str(Nz(Me![Combo8], 0))

The Str() is a function (See Access Help) the converts a number to a string.
The Nz() Function will return the value in Me![Combo8] if it is not Null and
not 0, otherwise, it will return 0.

The & is the VBA operator that concatenates strings

X ="ABC"
Y ="XYZ"
X & Y = "ABCXYZ"

There are quotes around the [TP-Key] = so that Jet will understand what to
do with it. The Findfirst is looking for a string, so that is how you
designate it is a string.

Now, there is, I think, a syntax error in this statement. If it is workin
as is, then I need to do some research. The Str converting it to a string
would make me think that [TP-Key] is a text field. It should, therefore,
also be in quotes. If it is a number, the syntax would be correct. For a
string, it should be:

rs.FindFirst "[TP-Key] = '" & Str(Nz(Me![Combo8], 0)) & "'"

To show how the quotes work, this could also be written as:

strKeyToFind = "[TP-Key] = '" & Str(Nz(Me!Combo8,0))" & "'"
rs.FindFirst strKeyToFind.

I hope this answers your questions.
As to your research, The answer to every question you asked can be found in
Access Help. I know it is hard to find info in Access Help. I haven't
cussed it in over an hour, but the more you use it, the easier it gets. For
example, Open Access Help and in the Search text box type in findfirst.

Also, use the Object Browser. It is more logically laid out and can help
you find info in Help. Just find the object you are interested in, highlight
it, and press F1.

Good Luck

At the end of this message I have listed a Sub routine that Access 2003
automatically generated in the AfterUpdate event of a combo box. This Sub
syncs the combo box to the Form when the combo box changes.
I want understand how this code works so I can make changes if needed. I
have work for days to understand it and think I have most of it down. Except,
I do not understand the programming syntax this line of code:

rs.FindFirst "[TP-Key] = " & Str(Nz(Me![Combo8], 0))

I how it works, it searches for the first record in the recordset that is
equal to the value in the combo box combo8. I understand “rs.FindFirst†and
also “(Nz(Me![Combo8], 0†parts. But the -- "[TP-Key] = " & Str -- syntax I
have not been able to understand …..why is there a -- &-- and the quotes
around the --"[TP-Key] =.
In general does any one know where I can get this information, I have tried
but was not successful

Thanks for your help
Brad


Private Sub Combo8_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
'this declares variable rs as type Object
Set rs = Me.Recordset.Clone
'This make a duplicate of original in variable rs
rs.FindFirst "[TP-Key] = " & Str(Nz(Me![Combo8], 0))
'This finds the first record recordset that is equal to the
combo8
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
‘This sets the current record of the recordset to the one found
End Sub

 

[Guest]

Klatuu,
Thank you, you solved my problem. By the way you were correct [TP-Key] is an
Integer so the code does work. Again, Thank you!!
Brad

[TP-Key] is the name of a field in the recordset. The Findfirst will look
for the vaule returned from Str(Nz(Me![Combo8], 0))

The Str() is a function (See Access Help) the converts a number to a string.
The Nz() Function will return the value in Me![Combo8] if it is not Null and
not 0, otherwise, it will return 0.

The & is the VBA operator that concatenates strings

X ="ABC"
Y ="XYZ"
X & Y = "ABCXYZ"

There are quotes around the [TP-Key] = so that Jet will understand what to
do with it. The Findfirst is looking for a string, so that is how you
designate it is a string.

Now, there is, I think, a syntax error in this statement. If it is workin
as is, then I need to do some research. The Str converting it to a string
would make me think that [TP-Key] is a text field. It should, therefore,
also be in quotes. If it is a number, the syntax would be correct. For a
string, it should be:

rs.FindFirst "[TP-Key] = '" & Str(Nz(Me![Combo8], 0)) & "'"

To show how the quotes work, this could also be written as:

strKeyToFind = "[TP-Key] = '" & Str(Nz(Me!Combo8,0))" & "'"
rs.FindFirst strKeyToFind.

I hope this answers your questions.
As to your research, The answer to every question you asked can be found in
Access Help. I know it is hard to find info in Access Help. I haven't
cussed it in over an hour, but the more you use it, the easier it gets. For
example, Open Access Help and in the Search text box type in findfirst.

Also, use the Object Browser. It is more logically laid out and can help
you find info in Help. Just find the object you are interested in, highlight
it, and press F1.

Good Luck

At the end of this message I have listed a Sub routine that Access 2003
automatically generated in the AfterUpdate event of a combo box. This Sub
syncs the combo box to the Form when the combo box changes.
I want understand how this code works so I can make changes if needed. I
have work for days to understand it and think I have most of it down. Except,
I do not understand the programming syntax this line of code:

rs.FindFirst "[TP-Key] = " & Str(Nz(Me![Combo8], 0))

I how it works, it searches for the first record in the recordset that is
equal to the value in the combo box combo8. I understand “rs.FindFirst†and
also “(Nz(Me![Combo8], 0†parts. But the -- "[TP-Key] = " & Str -- syntax I
have not been able to understand …..why is there a -- &-- and the quotes
around the --"[TP-Key] =.
In general does any one know where I can get this information, I have tried
but was not successful

Thanks for your help
Brad


Private Sub Combo8_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
'this declares variable rs as type Object
Set rs = Me.Recordset.Clone
'This make a duplicate of original in variable rs
rs.FindFirst "[TP-Key] = " & Str(Nz(Me![Combo8], 0))
'This finds the first record recordset that is equal to the
combo8
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
‘This sets the current record of the recordset to the one found
End Sub