Recordset / FindFirst question

[Liz Hansen]

Hello,

I have a form with a combo box and a couple of other text boxes. I want to
be able to select an employee in my drop down box and then populate the
other text boxes with data... But I have run into trouble using the
following code in the After Update code. I took this code from a silumar
form but in that case the FieldID was refering to a number field. In my new
form the FieldID happens to be a text field. Now, when running the code I
get a Data Type Mismatch error and I sort of understand why, but unfornuatly
I don't know how to correct it.

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[FieldID] = " & Str(Nz(Me![Combo16], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Anu suggestions are very much appriciated.

Many thanks!!

Liz

 

[Dan Artuso]

Hi,
The data type mismatch is probably coming from your rs.

Try:
Dim rs As DAO.Recordset

Just make sure you have a reference set to DAO. With any code module open
go to Tools->References and check DAO.xxx where xxx is the appropriate
version.

If your criteria is a string then:
rs.FindFirst "[FieldID] = ' " & Str(Nz(Me![Combo16], 0)) & " ' "

The spaces between the quotes are for clarity, do not put them in.

HTH
Dan Artuso, MVP

 

[Liz Hansen]

Hi,

Thanks for your reply.

I updated the code as you suggested but I'm still getting a Type Mismatch
error.

The code stops on this line:

rs.FindFirst "[FieldID] = ' " & Str(Nz(Me![Combo16], 0)) & " ' "

FieldID is a text field. Does the code have to change to reflect this?

Thanks,

Hi,
The data type mismatch is probably coming from your rs.

Try:
Dim rs As DAO.Recordset

Just make sure you have a reference set to DAO. With any code module open
go to Tools->References and check DAO.xxx where xxx is the appropriate
version.

If your criteria is a string then:
rs.FindFirst "[FieldID] = ' " & Str(Nz(Me![Combo16], 0)) & " ' "

The spaces between the quotes are for clarity, do not put them in.

HTH
Dan Artuso, MVP

Hello,

I have a form with a combo box and a couple of other text boxes. I want to
be able to select an employee in my drop down box and then populate the
other text boxes with data... But I have run into trouble using the
following code in the After Update code. I took this code from a silumar
form but in that case the FieldID was refering to a number field. In my new
form the FieldID happens to be a text field. Now, when running the

code

I
get a Data Type Mismatch error and I sort of understand why, but unfornuatly
I don't know how to correct it.

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[FieldID] = " & Str(Nz(Me![Combo16], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Anu suggestions are very much appriciated.

Many thanks!!

Liz

 

[Willy Wichtig]

Hi Liz,
your first post had a "." between Recordset and Clone. If you copied and
pasted your code, then that could be part of your problem. Try this:

Private Sub Combo16_AfterUpdate()
Dim rs as DAO.Recordset
Set rs = Me.RecordsetClone 'no period between Recordset and Clone
rs.FindFirst "[FieldID] = '" & Str(Nz(Me![Combo16], 0)) & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End If

HTH,
Willy