Results 1 to 7 of 7
  1. #1
    Join Date
    May 2019
    Posts
    65

    Missing Recorset from options in VBA

    I am using this Subroutine in a test database and everything works fine.

    Sub ShowCount()


    Dim myR As Recordset
    Set myR = CurrentDb.OpenRecordset("Products")
    MsgBox "Number of products is: " & myR.RecordCount & "."
    myR.Close
    Set myR = Nothing

    End Sub

    But, if I use it in my active database if get a compile error "User-defined type not defined". When I re-wrtie Dim myR As Recordset and get to the R in Recordset there is no such type (Recordset) listed the drop-down list.i

    I designed the Db with relationships, queries, macros, etc. but this is the first time I tried to incorporate a subroutine.

    Jeff

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Try disambiguating to:

    Dim myR As DAO.Recordset

    and for an accurate count:

    http://www.baldyweb.com/RecordCounts.htm

    For what you're doing this would be simpler:

    MsgBox "Number of products is: " & DCount("*", "Products") & "."
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Join Date
    May 2019
    Posts
    65
    Hi,
    Thanks,
    Somehow my immediate reply to your suggestion didn't get posted. That didn't work. I'm still faced with the fact that Recordset isn't in the list of types.
    It's weird that this can work with other database files and not mine.

    Jeff

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    What references are checked in the VBA editor, Tools/References? Verify you're using 2016?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    First, make sure the top 2 lines in EVERY module are
    Code:
    Option Compare Database
    Option Explicit
    Then open the IDE and go to TOOLS/References.
    Select (add check mark) to Microsoft Office 16.0 Object Library

    Debug/Compile, then execute the code.

    If that doesn't work, try selecting Microsoft Office 16.0 Access database engine Object Library.

    In my work version, A2016, I've had to try both to get the code from a dB designed in A2010 to execute.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'll get out of the way.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    Join Date
    May 2019
    Posts
    65
    In my database the following references are checked,
    Visual Basic for Applications
    Microsoft Access 16.0 Object Library
    Ole Automation

    In the exercise database
    Microsoft Office 16 Access database engine Object

    When I added the latter in my database everything worked.

    Thanks Steve,
    Resolved.

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

Similar Threads

  1. Import Data Options Missing from Ribbon
    By wcrimi in forum Import/Export Data
    Replies: 6
    Last Post: 07-09-2017, 04:06 PM
  2. Missing Options
    By rezprez in forum Access
    Replies: 4
    Last Post: 06-27-2016, 04:39 PM
  3. yes or no options
    By madhu in forum Access
    Replies: 3
    Last Post: 04-18-2013, 07:31 AM
  4. missing commands and options dialog box
    By marlowj30 in forum Access
    Replies: 1
    Last Post: 06-15-2011, 01:38 PM
  5. Add new Recordset using Recorset by Query
    By gailoni in forum Programming
    Replies: 1
    Last Post: 10-22-2008, 11:52 PM

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