type mismatch problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello

I have a form linked to a subform via user ID. I am trying to find a record in the subform based on the user's ID and the date, but I'm getting a type mismatch error that doesn't make sense to me. Here's my code
Private Sub Date_AfterUpdate(
Dim searchDate As Dat
Dim searchUser As Strin
Dim frm As For
Dim rs As Recordse

searchDate = Me!Dat
searchUser = Me!Logo
Set frm = Me.DE_Subform.For
Set rs = frm.RecordsetClon

frm.RecordsetClone.FindFirst "Date = #" & searchDate
"# And [User ID] = '" & searchUse
If frm.RecordsetClone.NoMatch The
Me.Und
MsgBox "Record not found
Els
frm.Bookmark = recSet.Bookmar
End I

End Su

When I type in a date on the form, I get the following error message: "Run-time error '13' Type mismatch" and when I go to debug, it highlights the line "Set rs = frm.RecordsetClone

This makes no sense to me. Any ideas

Thanks
Carrie
 
Hi Carrie,

Typically the recordsetclone is a DAO recordset - if you do not disambiguate
your declaration of the recordset variable, you will get a recordset object
from the first reference library that has a recordset object, which in your
case I suspect is the Microsoft ActiveX Data Objects library.

So, how to fix your problem? Well first, you don't really need the recordset
object. You can work directly with the Recordsetclone and skip the extra
object all together (actually you already did since your findfirst is using
the recordsetclone).

If you really did need a recordset object, first you would need to make sure
that you have a reference set to the Microsoft Data Access Objects library.
You can keep the ADO reference if you need it, otherwise just uncheck it all
together. Regardless, it is a good idea to disambiguate your object
declarations. Instead of:

dim rs as recordset

you would use:

dim rs as DAO.Recordset

I also do not see where you have declared 'recSet' - I suspect you really
want to use the bookmark to the recordsetclone instead. The With..End With
construct is used below instead.

Here is revised code:

Private Sub Date_AfterUpdate() Dim searchDate As
Date Dim searchUser As String Dim frm As Form
' do not need this
' Dim rs As Recordset

searchDate = Me!Date
searchUser = Me!Logon
Set frm = Me.DE_Subform.Form
' Set rs = frm.RecordsetClone

with frm.RecordsetClone
.FindFirst "Date = #" & searchDate & "# And [User ID] = '" &
searchUser
If .NoMatch Then
Me.Undo
MsgBox "Record not found"
Else
frm.Bookmark = .Bookmark
End If
end with
End Sub
 
I noticed either a syntax error in the criteria
expression:
"Date = #" & searchDate & "# And [User ID] = '" &
searchUser

Missing the ' and " at the end

It should be:
"Date = #" & searchDate & "# And [User ID] = '" &
searchUser & "'"

-----Original Message-----
Hi Carrie,

Typically the recordsetclone is a DAO recordset - if you do not disambiguate
your declaration of the recordset variable, you will get a recordset object
from the first reference library that has a recordset object, which in your
case I suspect is the Microsoft ActiveX Data Objects library.

So, how to fix your problem? Well first, you don't really need the recordset
object. You can work directly with the Recordsetclone and skip the extra
object all together (actually you already did since your findfirst is using
the recordsetclone).

If you really did need a recordset object, first you would need to make sure
that you have a reference set to the Microsoft Data Access Objects library.
You can keep the ADO reference if you need it, otherwise just uncheck it all
together. Regardless, it is a good idea to disambiguate your object
declarations. Instead of:

dim rs as recordset

you would use:

dim rs as DAO.Recordset

I also do not see where you have declared 'recSet' - I suspect you really
want to use the bookmark to the recordsetclone instead. The With..End With
construct is used below instead.

Here is revised code:

Private Sub Date_AfterUpdate() Dim searchDate As
Date Dim searchUser As String Dim frm As Form
' do not need this
' Dim rs As Recordset

searchDate = Me!Date
searchUser = Me!Logon
Set frm = Me.DE_Subform.Form
' Set rs = frm.RecordsetClone

with frm.RecordsetClone
.FindFirst "Date = #" & searchDate & "# And [User ID] = '" &
searchUser
If .NoMatch Then
Me.Undo
MsgBox "Record not found"
Else
frm.Bookmark = .Bookmark
End If
end with
End Sub


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Hello,

I have a form linked to a subform via user ID. I am trying to find
a record in the subform based on the user's ID and the date, but I'm
getting a type mismatch error that doesn't make sense to me.
Here's my code: Private Sub Date_AfterUpdate() Dim searchDate As
Date Dim searchUser As String Dim frm As Form
Dim rs As Recordset

searchDate = Me!Date
searchUser = Me!Logon
Set frm = Me.DE_Subform.Form
Set rs = frm.RecordsetClone

frm.RecordsetClone.FindFirst "Date = #" & searchDate &
"# And [User ID] = '" & searchUser
If frm.RecordsetClone.NoMatch Then
Me.Undo
MsgBox "Record not found"
Else
frm.Bookmark = recSet.Bookmark
End If

End Sub

When I type in a date on the form, I get the following error message:
"Run-time error '13' Type mismatch" and when I go to debug, it
highlights the line "Set rs = frm.RecordsetClone"

This makes no sense to me. Any ideas?

Thanks,
Carrie
Click to expand...

.
Click to expand...
 
Back
Top