none
My use of a recordset within a form fails RRS feed

  • Question

  • I am trying to use a recordset within a form to look up information, but I get an 'Object variable or With block variable not set' error.

    In the variables declaration at the top of the form I declare the recordset variable:

       Public rsAttTypes As DAO.Recordset

    In the 'form open' event I define the recordset and set the index:

       Set rsAttTypes = CurrentDb.OpenRecordset("AttendanceType", dbOpenTable)
       rsAttTypes.Index = "desc"

    I have created a function to use the recordset to look up the id associated with the attendee's description:

    Public Function GetAttnType(ByVal descp As String) As Long
    rsAttTypes.Seek "=", descp
    If rsAttTypes.NoMatch = False Then
       GetAttnType = rsAttTypes!id
    Else
       'type "unknown"
       GetAttnType = 11
    End If

    End Function

    BUT when I try to use the function GetAttnType to look up the id associated with a description:

    attn_type_id = GetAttnType(Me.txtAttendeeType)

    This errors out within the function GetAttnType: 'Object variable or With block variable not set' occurs when I reference rsAttTypes within the function GetAttnType.

    What happened to my recordset rsAttTypes that it is not available within the function GetAttnType?

    Thanks in advance for any help.  --Fred

    Tuesday, August 11, 2015 8:54 PM

Answers

  • Thanks for all the replies.

    I tried what you and another suggest: create db as a persistent copy of CurrentDb and used that to define the recordset, but still get the object variable error.

    So I solved it this way: I have a 'lookup' class object that is instantiated in the parent form, so I moved the lookup function there. In the 'initialize' event I set rsAttTypes, then added the function 'Public Function GetAttnType' as a public function in my lookup object, then request the lookup this way:

    attn_type_id = Form_fMain.dl.GetAttnType(Me.txtAttendeeType)

    Here dl is my 'data lookup' class object instantiated in the form fMain.

    This way works.

    Thanks again for all the replies. This forum has been invaluable to me in offering suggestions to solve problems in Access, thanks.

    • Marked as answer by Fredrated451 Wednesday, August 12, 2015 1:50 PM
    Wednesday, August 12, 2015 1:50 PM

All replies

  • usually that error means you have to explicitly set the database.

    Declare a variable for the database:

    Dim db As DAO.Database
    Set db = CurrentDB

    Then go on from there.


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Tuesday, August 11, 2015 8:57 PM
  • This errors out within the function GetAttnType: 'Object variable or With block variable not set' occurs when I reference rsAttTypes within the function GetAttnType.

    What happened to my recordset rsAttTypes that it is not available within the function GetAttnType?

    Hi Fred,

    The Seek and Index methods do not work on a link to the table in BE.

    You must declare a variable that refers to the BE-database directly (e.g. Data_db), and use that variable:

         Set rsAttTypes = Data_db.OpenRecordset("AttendanceType", dbOpenTable)

    An other way of working could be to not to use .Seek and .Index, but simply something like:

        Set rsAttTypes = CurrentDB.OpenRecordser("SELECT * FROM AttendanceType WHERE Attendee = " & descp)

    You can test for the existence of that record with   rsAttTypes.EOF:  If (rsAttTypes.EOF) then no record was found.

    Imb.

    Tuesday, August 11, 2015 9:28 PM
  • Is your function GettAttnType defined in the form's module?  If it's in some other module, it won't know about the module-level recordset object you defined in the form's module. 

    What happens if you set breakpoints on various lines of the code and step through it?  What lines are executed successfully, and what exact line raises the error?


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Tuesday, August 11, 2015 11:13 PM
  • Thanks for all the replies.

    I tried what you and another suggest: create db as a persistent copy of CurrentDb and used that to define the recordset, but still get the object variable error.

    So I solved it this way: I have a 'lookup' class object that is instantiated in the parent form, so I moved the lookup function there. In the 'initialize' event I set rsAttTypes, then added the function 'Public Function GetAttnType' as a public function in my lookup object, then request the lookup this way:

    attn_type_id = Form_fMain.dl.GetAttnType(Me.txtAttendeeType)

    Here dl is my 'data lookup' class object instantiated in the form fMain.

    This way works.

    Thanks again for all the replies. This forum has been invaluable to me in offering suggestions to solve problems in Access, thanks.

    • Marked as answer by Fredrated451 Wednesday, August 12, 2015 1:50 PM
    Wednesday, August 12, 2015 1:50 PM