Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2003
    Posts
    10

    Unanswered: Access 97 - Run-time error '91'

    I am receiving the following error:
    ------------------------------------
    Run-time error '91'
    Object variable or With block variable not set
    ------------------------------------

    This is my code:
    ------------------------------------
    Dim LastDist As Recordset
    Dim varDist_Name As String
    Dim varDist_Start As String
    Dim varDist_End As String
    Dim varDist_Country As String
    Dim varDist_Region As String

    LastDist.OpenRecordset ("SELECT * FROM Distribution_Details WHERE ID = 7")

    varDist_Name = LastDist.Fields("Dist_Name")
    varDist_Start = LastDist.Fields("Dist_Period_Start")
    varDist_End = LastDist.Fields("Dist_Period_End")
    varDist_Country = LastDist.Fields("Dist_Country")
    varDist_Region = LastDist.Fields("Dist_Region")
    ------------------------------------

    When I click debug, it highlights the line 'LastDist.OpenRecordset.....'

    Does anyone have an idea what this problem could be?

    Thanks in advance

    Wes

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    You cannot make automatic variables of Recordsets ... You must instantiate it as it is a com object.

    Dim MyRec As RecordSet

    Set MyRec = new RecordSet

    Set MyRec = Nothing

  3. #3
    Join Date
    Oct 2003
    Posts
    10
    Thanks for the help, but i'm getting a new error that points to:

    Set LastDist = New Recordset

    The error says:
    --------------------------------
    Compile error:
    Invalid us of New keyword
    --------------------------------

    My code now looks like this:
    --------------------------------
    Dim NumBranches As Long
    Dim BranchList As Recordset
    Dim LastDist As Recordset
    Dim varDist_Name As String
    Dim varDist_Start As String
    Dim varDist_End As String
    Dim varDist_Country As String
    Dim varDist_Region As String

    Set LastDist = New Recordset
    Set LastDist = Nothing

    LastDist.OpenRecordset ("SELECT * FROM Distribution_Details WHERE ID = 7")

    varDist_Name = LastDist.Fields("Dist_Name")
    varDist_Start = LastDist.Fields("Dist_Period_Start")
    varDist_End = LastDist.Fields("Dist_Period_End")
    varDist_Country = LastDist.Fields("Dist_Country")
    varDist_Region = LastDist.Fields("Dist_Region")
    --------------------------------

  4. #4
    Join Date
    Nov 2002
    Location
    Belgium
    Posts
    243
    Originally posted by westleyk
    Thanks for the help, but i'm getting a new error that points to:

    Set LastDist = New Recordset

    The error says:
    --------------------------------
    Compile error:
    Invalid us of New keyword
    --------------------------------

    My code now looks like this:
    --------------------------------
    Dim NumBranches As Long
    Dim BranchList As Recordset
    Dim LastDist As Recordset
    Dim varDist_Name As String
    Dim varDist_Start As String
    Dim varDist_End As String
    Dim varDist_Country As String
    Dim varDist_Region As String

    Set LastDist = New Recordset
    Set LastDist = Nothing

    LastDist.OpenRecordset ("SELECT * FROM Distribution_Details WHERE ID = 7")

    varDist_Name = LastDist.Fields("Dist_Name")
    varDist_Start = LastDist.Fields("Dist_Period_Start")
    varDist_End = LastDist.Fields("Dist_Period_End")
    varDist_Country = LastDist.Fields("Dist_Country")
    varDist_Region = LastDist.Fields("Dist_Region")
    --------------------------------

    I'm not sure if it's the right way, but I usually write

    Dim BranchList As New Recordset

    and it works fine for me.
    Last edited by artemide; 10-10-03 at 07:49.

  5. #5
    Join Date
    Oct 2003
    Posts
    10
    I am now getting this error:

    Compile error:
    Invalid us of New keyword

    Which points to this line:

    Dim DistList As New Recordset


    this is the same error i started with? Anyone know whats going on? I fell like i'm going round in circles.......

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Doh! I hate when I do that ...

    You cannot make automatic variables of Recordsets ... You must instantiate it as it is a com object.

    Dim MyRec As RecordSet

    Set MyRec = new RecordSet

    Set MyRec = Nothing
    Try:

    Dim MyRecSet As ADODB.Recordset

    Set MyRecSet = New ADODB.Recordset

  7. #7
    Join Date
    Oct 2003
    Posts
    10
    OK, getting somewhere!!!!!

    Have now got this error(!):
    ------------------------------------------
    Compile error:
    User-defined type not defined
    ------------------------------------------

    And it highlights:
    ------------------------------------------
    Dim MyRecSet As ADODB.Recordset
    ------------------------------------------

    Any ideas? I really appreciate the help, my head is nearing implosion.....

    )

  8. #8
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198

    Re: Access 97 - Run-time error '91'

    Try this:

    Dim DB as Database
    Dim LastDist As Recordset
    Dim SQL as String
    Dim varDist_Name As String
    Dim varDist_Start As String
    Dim varDist_End As String
    Dim varDist_Country As String
    Dim varDist_Region As String

    SQL = "SELECT * FROM Distribution_Details WHERE ID = 7"

    Set DB= Currentdb()
    Set LastDist = db.OpenRecordset (SQL)

    varDist_Name = LastDist!Dist_Name
    varDist_Start = LastDist!Dist_Period_Start
    varDist_End = LastDist!Dist_Period_End
    varDist_Country = LastDist!Dist_Country
    varDist_Region = LastDist!Dist_Region
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

  9. #9
    Join Date
    Oct 2003
    Posts
    10

    Talking

    Fantastic!!!!!

    It's actually working!!!!

    Now i've got an error on a createobject line....... but i'll put this as a new post!!

    Thanks for your help everyone!!!!

  10. #10
    Join Date
    Oct 2003
    Posts
    10
    If anyone knows about creating word 97 documents from access 97, could you take a look at this as well?

    http://www.dbforums.com/showthread.php?threadid=927756

    Thanks for your help!

Posting Permissions

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