RecordsetClone - Type mismatch

[rothjm]

I am trying to do a search in a subform.
I am trying to use the Recordset clone method.
I have a text box on my subform (txtSearch) and the ProcNumber is the field on the subform that I want to match.
I get a type mismatch on the line where I try to set rs=Me.RecordsetClone.
Any ideas why?
Here's my code:

Private Sub cmdSearch_Click()
Dim rs As Recordset
Dim strSearch As String
Set rs = Me.RecordsetClone
strSearch = Str(Me!txtSearch)
rs.Find "[ProcNumber] = " & strSearch
Me.Bookmark = rs.Bookmark
rs.Close
End Sub

Thanks

 

[Pat Hartman]

I seem to remember reading somewhere that the Recordset of a form is always a DAO recordset. So if that is the case, then you need to use:

Dim rs As DAO.Recordset

and make sure that you have a reference to the DAO library set.

 

[Pat Hartman]

Amazing how the same problem just pops up in clumps. Luckily Travis has a better memory than I. He posted a link to the article I read. I was right about the form's recordset being DAO. This article goes into a longer explaination.

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=60879

 

[rothjm]

I tried using DAO and I get a compile error: User-defined type not defined.

Here's the code:

Private Sub cmdSearch_Click()
Dim rs As DAO.Recordset
Dim strSearch As String
Set rs = Me.RecordsetClone
strSearch = txtSearch
rs.Find strSearch
Me.Bookmark = rs.Bookmark
rs.Close
End Sub

Thanks for any advice.

 

[dcx693]

Try this link: [FAQ] Access 97 To Access 2000 - User-defined type not defined (DAO & ADO)

 

[Lynn_AccessUser]

Try

Set rs = Me.Recordset.Clone

 

[rothjm]

I get a type mismatch error (same line) when trying the .clone.

 

[Lynn_AccessUser]

Dim RS As ADODB.Recordset

 

[dcx693]

Lynn_AccessUser, the user is specifically needs to work with a DAO recordset since he's trying to clone the form recordset.

rothjm, did you look at the link I posted? Did you set a reference to DAO in the VBA editor?

 

[rothjm]

Yes, I did try the ADO & ADODB.

And then also made sure the reference was there to Microsoft DAO 3.x Object Library was there and unchecked the Microsoft ActiveX Data Objects 2.x Library, as the artclie explained, when trying to use the DAO.

 

[Mile-O]

Is this code in the subform or the main form?

 

[rothjm]

I was trying this from the subform.

 

[Mile-O]

Is ProcNumber actually a Text field? If so, this should fix it.

Private Sub cmdSearch_Click()
    Dim rs As DAO.Recordset
    Dim strSearch As String
    Set rs = Me.RecordsetClone
    strSearch = CStr(Me.txtSearch) 
    With rs
        .Find "[ProcNumber] = """ & strSearch & """"
        Me.Bookmark = .Bookmark
        .Close
    End With
    Set rs = Nothing
End Sub

Otherwise:

Private Sub cmdSearch_Click()
    Dim rs As DAO.Recordset
    Dim lngSearch As Long
    Set rs = Me.RecordsetClone
    lngSearch = CLng(Me.txtSearch) 
    With rs
        .Find "[ProcNumber] = " & lngSearch 
        Me.Bookmark = .Bookmark
        .Close
    End With
    Set rs = Nothing
End Sub

 

[rothjm]

OK, the code runs without error but it doesn't move to that record.
Yes, the ProcNumber is a text field.
I had to import from a spreadsheet they were using and they used 2k2 to designate year 2002...just great.
I have the reference DAO 3.6 selected.

Here's my code so you can look for typo's etc:

Option Compare Database
Option Explicit

Private Sub cmdSearchProc_Click()
Dim rs As DAO.Recordset
Dim strSearch As String
'DoCmd.GoToControl "frmSubProcData"
'ProcNumber.SetFocus
Set rs = Me.RecordsetClone
strSearch = CStr(Me.txtSearch)
With rs
.FindFirst "[ProcNumber] =""" & strSearch & """"
Me.Bookmark = .Bookmark
.Close
End With
Set rs = Nothing
End Sub

Thanks a bunch!

 

[rothjm]

Also, I put a MsgBox .Bookmark after the find, and it returned a "?".

Notice, I had to use FindFirst I got an error trying to use Find. It said Method or Data Member not found.

 

[Mile-O]

Maybe something like this...

Option Compare Database
Option Explicit

Private Sub cmdSearchProc_Click()
    Dim rs As DAO.Recordset
    Dim strSearch As String
    Set rs = Me.RecordsetClone
    strSearch = CStr(Me.txtSearch)
    With rs
        If .BOF And .EOF Then
            MsgBox "There are no records."
        Else
            .MoveFirst
            Do While Not .EOF
                If .Fields("[ProcNumber]")  = strSearch Then   
                    Me.Bookmark = .Bookmark
                    Exit Do
                End If
                .MoveNext
            Loop
         End If
        .Close
    End With
    Set rs = Nothing
End Sub

 

[Lynn_AccessUser]

Oops . . . sorry about the ADO references. I have been working on a similar bit of code using recordset.clone for the last day or so and read the DAO references and ADO.

 

[rothjm]

My database is setup with a qryPatient as the main form and then tblData as the subform. Each Patient has several tests which are stored in the tblData. Your code is only searching on the current patient and data....it doesn't search through the rest of the patients. The link between form and subform is PatID.

I appreciate your time and assistance...I think you are really close to resolving the problem. I put in several msgBox's to test your code and it does go through and searches all the Procedures on the current patient that is in the main form but does not go through all the patients.

I'm attaching a test version. The text box and "Go" button is what I am testing with.
FYI - I had to import from an existing spreadsheet so all the fields are not as I would have designed them from scratch.

 

[Mile-O]

Only because I thought we were searching the subform and not moving up to the parent, moving to the next record and then searching the child again.

I'll be back on in a couple of hours.

 

[rothjm]

I appologize for not clarifying that.
Sorry, about that.
Thanks again.