Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2006
    Posts
    30

    Exclamation Unanswered: Help! Type Mismatch Error When Trying To Clone Recordset!

    Hi everyone,

    I've got a really irratating error (irratating for me!). I used a function to display the total number of records & the current selected record in a label control. It works by using the forms recordsource.
    I used it on one MS Access 2000 DB and it worked fine.
    I now have used it in another MS Access 2000 DB but the label brings up a "Type Mismatch" msg within it rather than the record numbers.

    I have no idea why it is doing this. The function is listed below. The point where the error occurs is highlighted in red.
    To call the function i use the following line of code within the form's OnCurrent event : Me.lblRecCount = RecordNumber("Rec", Me).

    The form has got a valid recordsource selected (a table) and has valid data appearing on it.

    If anyone can shed any light on this, then please contact me and don't let me suffer in the dark wondering about what the erro might be!

    Thank you in advance.

    Shuja.

    Code:
    Function RecordNumber(pstrPreFix As String, pfrm As Form) As String
        On Error GoTo RecordNumber_Err
        Dim rst As Recordset
        Dim lngNumRecords As Long
        Dim lngCurrentRecord As Long
        Dim strTmp As String
        
        Set rst = pfrm.RecordsetClone
        rst.MoveLast
        rst.Bookmark = pfrm.Bookmark
        lngNumRecords = rst.RecordCount
        lngCurrentRecord = rst.AbsolutePosition + 1
        strTmp = pstrPreFix & " " & lngCurrentRecord & " of " & lngNumRecords
    RecordNumber_Exit:
        On Error Resume Next
        RecordNumber = strTmp
        rst.Close
        Set rst = Nothing
        Exit Function
    RecordNumber_Err:
        Select Case Err
            Case 3021
                strTmp = "New Record"
                Resume RecordNumber_Exit
            Case Else
                strTmp = "#" & Error
                Resume RecordNumber_Exit
        End Select
    End Function

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Not sure what the problem is, but I discovered a while back (while doodling around trying to solve someone else's problem) that RecordSetClones work a little different when the Record Source is a Table rather than a Query. Here's a rather simpler approach to your problem:

    Code:
    Private Sub Form_Current()
    	If Not Me.NewRecord Then
    		Me.Caption = "Record  " & CurrentRecord & "  Of  " & RecordsetClone.RecordCount & "  Records"
    	Else
    		Me.Caption = "Record  " & CurrentRecord & "  Of  " & (RecordsetClone.RecordCount + 1) & "  Records"
    	End If
    End Sub
    The above is all you need for a form with a Query as a Record Source. If you use a Table, instead, you'll need to add the following:

    Code:
    Private Sub Form_Load()
    	DoCmd.GoToRecord , , acNext
    	DoCmd.GoToRecord , , acFirst
    	Me.Caption = "Record  " & CurrentRecord & "  Of  " & RecordsetClone.RecordCount & "  Records"
    End Sub
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    I suspect you do not have a referance to the DAO Library (or it is below the ADO reference).

    RecordSetClone seem to be an DAO object.

    If you convert a DB from 97 to 2k that used DAO then a refence is automatically set, but if you start a new 2k (and later!?) then you need to set a reference to it as ADO is assumed.

    It is best to fully qualify recordset decarations, ie DAO.Recordset or ADODB.Recordset. This will throw up error during compilation (if not before).

    HTH


    MTB

  4. #4
    Join Date
    Sep 2006
    Posts
    30
    Thanks for your help missingLinq. Your info was useful & worked well.
    But MikeTheBike has hit the nail on the head. My recordsetclone declaration did not specify the ADO or DAO prefix and the DAO reference was listed lower than the ADO reference. I moved it above the ADO reference & BINGO it works!
    Thanks MikeTheBike, u da man!

    Thanks to all who responded with help.

    Much appreciated.

    Shuja.

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    Shuja,

    do yourself a future favour.
    don't mess with the order of the references, but declare explicitly:
    dim rst as DAO.recordset

    messing with the reference order will eventually bite you in the bum

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Sep 2006
    Posts
    30
    What should the order of references be?
    are there any other references that i should be vary of using & ordering?

    Shuja,

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    don't bother with the order - it is a false route.
    FORGET the order of the references.

    declare explicitly:
    it is faster
    it is reference-order independent
    it sometimes returns slightly less stupid error messages if you are missing a reference

    DAO.Recordset
    DAO.QueryDef
    ...already covers 80% of the potential confusion.

    izy

    LATER
    ...other refs
    set the references you need (IGNORE the order)
    if you are playing with an object that you are not familar with, use the object browser to search all libraries for the name of the object:
    ...if you get hits in more than one library: always declare explicitly Dim myThing As thisLib.Thing
    ...maybe you can also speed up .MDB by declaring everything explicitly (in .MDE there is ??obviously no difference since all the early-bind stuff is resolved during the 'compile')
    Last edited by izyrider; 10-19-06 at 12:10.
    currently using SS 2008R2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •