Results 1 to 7 of 7
  1. #1
    Pervasive is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2022
    Posts
    3

    Macros using RecordCount used to work but don't work anymore...

    For many years, coding with RecordCount method worked without hitch across many related macros.
    This year, all instances stopped working with error message:
    "Compile Error
    Type Mismatch"
    highlighting on ".RecordCount" in the line "For tmpvar = 1 To rsttmp.RecordCount"

    Something has obviously changed with Access -I am stumped and really would appreciate a solution!



    An example of the multiple similarly misbehaving codes follows:

    code

    Dim rsttmp As ADODB.Recordset, tmpvar As Integer
    tmpvar = 1
    Set rsttmp = New ADODB.Recordset


    With rsttmp
    .CursorLocation = adUseClient
    .ActiveConnection = CurrentProject.Connection
    .Open "SR_NoDup", , adOpenKeyset, adLockPessimistic, adCmdTable
    If Not rsttmp.RecordCount = 0 Then
    For tmpvar = 1 To rsttmp.RecordCount
    .Delete adAffectCurrent
    .Update
    .MoveNext
    Next tmpvar
    End If
    End With

    /code

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    2,028
    This person here was having same error.
    https://www.access-programmers.co.uk...-error.320761/

    I do not know ADO, but why can't you just use eof? in this case?

    BTW code tags need to be enclosed with []
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?search_query=debug+access+vba



  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    51,010
    Yes, try

    Do While Not .EOF
    .Delete adAffectCurrent
    .Update
    .MoveNext
    Loop

    But why loop a recordset to delete a batch of records?

    CurrentDb.Execute "DELETE FROM SR_NoDup"

    But why delete records?
    Last edited by June7; 04-24-2022 at 02:57 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,055
    Pervasive,

    Many things have changed over the years.

    Macros have a new/revised meaning.
    TempVars have been introduced.
    DAO seems to have been pushed harder than ADO.

  5. #5
    Pervasive is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2022
    Posts
    3

    Cool RecordCount no longer working - SOLUTION!

    [QUOTE=Welshgasman;493755]This person here was having same error.
    https://www.access-programmers.co.uk...-error.320761/

    Thank you for the above link! It pointed me to the "References" - I replaced MS ActiveX Data Access Library v2.5 with v6.1 and VOILA, problem solved.

  6. #6
    Pervasive is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2022
    Posts
    3
    Quote Originally Posted by June7 View Post

    "But why loop a recordset to delete a batch of records?
    CurrentDb.Execute "DELETE FROM SR_NoDup"
    But why delete records?
    "

    Thank you for the suggestion, (updating ADOX library "fixed" it, BTW, so didn't need to write new code, YEAH!)
    As to your question-
    This is an interventions database containing a query to find records with specific medical diagnoses.
    Some patients with a specific diagnosis have had more than one intervention.
    The follow-up query winnows the initial results so that an individual patient would only be listed once, no matter how many times they were operated.
    Looping the recordset pushes counters for several fields, so the final report also contains additional information (sex, age range, number of surgeries as a percent of total database, etc).
    Just deleting the record without walking through it would lose some of those parameters.

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,043
    Post 6 was moderated, I'm posting to trigger email notifications.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 01-19-2018, 12:20 PM
  2. Replies: 10
    Last Post: 03-11-2016, 04:16 PM
  3. Why Doesn't Stuff Work Anymore?
    By HowardOfOcal in forum Programming
    Replies: 4
    Last Post: 02-03-2012, 05:57 PM
  4. Inherited database - macros don't work on new page
    By VictoriaAlbert in forum Access
    Replies: 3
    Last Post: 04-13-2011, 11:45 PM
  5. macros in subform dont work
    By tuyo in forum Access
    Replies: 2
    Last Post: 03-25-2011, 09:49 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums