Access Command Button 1

[srodolff]

I have a simple Access 2000 database.

I have a form with an unbound field called "Find VSI".
I have a button called "Find" that, when clicked, should find the record where the field "Vehicle Shop #" is equal to "Find VSI".

Will this code work?

Private Sub Find_Click()
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[Vehicle Shop #] = " & Me![Find VSI]
Me.Bookmark = Me.RecordsetClone.Bookmark

End Sub

I am having some problems with this. Any suggestted solutions will be appreciated.

Steve

 

[RoyVidar]

Hi!

You could perhaps be a litle more specific concerning what problems (error message, which line etc)

Some thoughts:

If the fields are datatype text, you'll need to enclose (the criteria) field with single quotes:

Me.RecordsetClone.FindFirst "[Vehicle Shop #] = '" & Me![Find VSI] & "'"

The "preferred" way, is often to declare a recordset (as is the result from using the combo wizard):

dim rs as object ' Access Wizard or
dim rs as dao.recordset

set rs=me.recordsetclone
rs.findfirst "[Vehicle Shop #] = " & Me![Find VSI]

Since this doesn't seem to be a combo (and therefore might contain zero records, it might be wise to check for that before trying to set bookmark:

if not rs.bof then
Me.Bookmark = Me.RecordsetClone.Bookmark
else
msgbox "couldn't find any record"
end if

HTH Roy-Vidar

 

[srodolff]

I have tried the following code:

Private Sub Find_Click()
' Find the record that matches the control.
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone

rs.FindFirst "[Vehicle Shop #] = " & Me![Find VSI]
Me.Bookmark = Me.RecordsetClone.Bookmark

End Sub

I am receiving a 3464 Data Type mismatch on the FindFirst line. I am testing with a known existing valid value.

Thank you for your quick reply.
Steve

 

[RoyVidar]

Hi again!

To do some more troubleshooting (probably along the same lines as you have, but...):
Are the two fields (the control and the field) of the same datatype? (and are they spelled correctly) The field Vehicle Shop # is a field in the forms recordsource?

If their'e both (supposed) to be numeric, it might be an idea to convert the control:
rs.FindFirst "[Vehicle Shop #] = " & clng(Me![Find VSI]) ' Converting to long

And as stated earlier, if they are text, you'll need to enclose with single quotes:
rs.FindFirst "[Vehicle Shop #] = '" & Me![Find VSI] & "'"

If none of this helps, I'm as stumped as you are, I'm afraid.

Roy-Vidar