locked
Object variable or With block variable not set problem RRS feed

  • Question

  • Hi

    Can anybody help me get past this problem. I keep getting an " Object variable or With block variable not set run time error 91"

    here is the code. Any help most apreciated. All I want to be able to do is remember a record Id close the form and go to another form then return to the form and have the form display the remembered record.

    Thanks Steve

    Dim rs As Object
    Set rs = Me.Recordset.Clone
    QuoteNumberLng = 16 ' This is a public variant variable set to 16 for testing but could be any QuoID Number
    If TogQuoteCalc = 0 Then
            Forms!frmMain!frmQuote.SourceObject = "frmQuote"
            Forms!frmMain!frmQuote.SetFocus
            Forms!frmMain!frmQuote.Form!QuoID.SetFocus
            rs.FindFirst "[QuoID] = " & CStr(QuoteNumberLng)
            If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    End If
    Thursday, August 5, 2010 5:05 PM

Answers

  • Congradulations on solving the problem! No reason to feel stupid. Programming is always a learning experience.

    • Marked as answer by SteveWretham Friday, August 6, 2010 1:40 AM
    Thursday, August 5, 2010 9:42 PM

All replies

  • Why don't you declare rs as Recordset rather than as Object?

    Thursday, August 5, 2010 5:11 PM
  • Ok will try that and see if it changes anything
    Ok have changed it to Recordset and still the same result

    SteveW

    Thursday, August 5, 2010 5:25 PM
  • Set rs = Me.Recordset
    
    Try, please. 
    Thursday, August 5, 2010 5:29 PM
  • Have changed and same result

    highlights error on this line now but same error message

    rs.FindFirst "[QuoID] = " & CStr(QuoteNumberLng)

    SteveW

    Dim rs As Recordset
    Set rs = Me.Recordset
    QuoteNumberLng = 16 ' This is a public variant variable set to 16 for testing but could be any QuoID Number
    If TogQuoteCalc = 0 Then
            Forms!frmMain!frmQuote.SourceObject = "frmQuote"
            Forms!frmMain!frmQuote.SetFocus
            Forms!frmMain!frmQuote.Form!QuoID.SetFocus
            rs.FindFirst "[QuoID] = " & CStr(QuoteNumberLng)
            If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    End If

    Thursday, August 5, 2010 5:36 PM
  • You seem to be converting a number to a string to catenate it.

    In your recordset is QuoID a string you will need to add quotes?

    Thursday, August 5, 2010 5:54 PM
  • QuoID is not a string its a number but stored in a public valiable called QuoteNumberLng but I thought it had to be converted into a string for the Find First criteria

    SteveW

    Thursday, August 5, 2010 5:59 PM
  • changed this line rs.FindFirst "[QuoID] = " & CStr(QuoteNumberLng) to rs.FindFirst "[QuoID] = " & QuoteNumberLng

    I get same error

    any more suggestions all most helpful and appreciated

    Steve

     

    Dim rs As Recordset
    Set rs = Me.Recordset
    QuoteNumberLng = 16 ' This is a public variant variable set to 16 for testing but could be any QuoID Number
    If TogQuoteCalc = 0 Then
            Forms!frmMain!frmQuote.SourceObject = "frmQuote"
            Forms!frmMain!frmQuote.SetFocus
            Forms!frmMain!frmQuote.Form!QuoID.SetFocus
           rs.FindFirst "[QuoID] = " & QuoteNumberLng
            If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    End If

    Thursday, August 5, 2010 6:14 PM
  • I suggest you use the rs.NoMatch property. Any time you use rs.FindFirst you want to check for rs.NoMatch. Failing to do so can cause unexpected errors like this. I don't know if it will solve your particular problem, but its good practice and might narrow your search.
    Thursday, August 5, 2010 6:25 PM
  • could you give me an example of how to use rs.NoMatch within my code above please

    I have been looking up help files all night trying to resolve the problem but even without the rs.NoMatch there is a record QuoID of number 16 in the database so it should find it ok for testing.

    Thursday, August 5, 2010 6:36 PM
  • I am unclear what is happening here.

    you are setting rs to the current form's recordset.clone

    you are then attaching another form as a subform

    setting the focus to a control on that subform

    and then searching for a record in the main form's recordset based on a field name that is in the subform on a value that you have definded globaly

    What does the subform have to do with any of this?

    are you sure the main form has QuoID as a field in its recordset?

    Thursday, August 5, 2010 6:37 PM
  • I have a form with a table as its record source and it has a field called QuoID which is also the primary key of the table

    All I am trying to do is navigate through the table with the form and that works fine

    When I get to a record that requires some work done to it I want to remember the field QuoID which is present in a text box on the form.
    navigate away from the form or close the form and open a different form when I have finished the task on the diffrent form I want be able to return to the original form and have the form show the remembered QuoID

    So that I can update the record on the original form.

    Thursday, August 5, 2010 6:51 PM
  • changed this line rs.FindFirst "[QuoID] = " & CStr(QuoteNumberLng) to rs.FindFirst "[QuoID] = " & QuoteNumberLng

    I get same error

    any more suggestions all most helpful and appreciated

    Steve

     

    Dim rs As Recordset
    Set rs = Me.Recordset
    QuoteNumberLng = 16 ' This is a public variant variable set to 16 for testing but could be any QuoID Number
    If TogQuoteCalc = 0 Then
            Forms!frmMain!frmQuote.SourceObject = "frmQuote"
            Forms!frmMain!frmQuote.SetFocus
            Forms!frmMain!frmQuote.Form!QuoID.SetFocus
           rs.FindFirst "[QuoID] = " & QuoteNumberLng
            If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    End If

    This should work:

    Dim rs As DAO.Recordset
    Set rs = Me.RecordsetClone
    QuoteNumberLng = 16 ' This is a public variant variable set to 16 for testing but could be any QuoID Number
    If TogQuoteCalc = 0 Then
            Forms!frmMain!frmQuote.SourceObject = "frmQuote"
            Forms!frmMain!frmQuote.SetFocus
            Forms!frmMain!frmQuote.Form!QuoID.SetFocus
           rs.FindFirst "[QuoID] = " & QuoteNumberLng
            If rs.NoMatch Then 
                msgbox Record Not Found"
            Else
                Me.Bookmark = rs.Bookmark
            End If
    End If

    If it doesn't, I don't know what is going on.

    I wonder if you need the explicit DAO when referencing the Recordset.  RecordsetClone is a DAO object.


    -- Roger Carlson
    MS Access MVP 2006-2010
    www.rogersaccesslibrary.com
    Thursday, August 5, 2010 6:53 PM
  • You can find out all about rs.NoMatch here: http://msdn.microsoft.com/en-us/library/bb208495(office.12).aspx rs.NoMatch returns true if a FindFirst method fails.

    Here is an example of it in code... this is really basic, you can edit for your needs.

    rs.FindFirst "[QuoID] = " & QuoteNumberLng
    If rs.NoMatch Then
    MsgBox("No Match: I need to put code here to handle this.")
    Else
    msgBox("Found it! I need to put code here to handle this.")
    End If
    
    

    It looks to me your code is very involved. You are referencing forms and subforms. If I were you I would comment out everything in the If TogQuoteCalc = 0 block and then add each line at a time compiling it again and again after each step works like I wanted until I was sure the logic was sound.  I get Object variable or With Block variable errors all the time and it usually turns out to be something minute.

    Thursday, August 5, 2010 6:57 PM
  • This looks good thank you

    but I get another error now run error 7951 "You entered an expression that has invalid reference to the RecordsetClone property

    I copied your code and have checked it as correct

    I think Im getting somplace if I can resolve this

    SteveW

    Thursday, August 5, 2010 7:12 PM
  • Thanks I have included the rs.NoMatch part now

    but sadly did not fix the error

    SteveW

    Thursday, August 5, 2010 7:14 PM
  • Ok this is what it looks like now

    But now I get runtime error 7951 " You entered an expression that has invalid reference to the RecordsetClone property

    Private Sub TogQuoteCalc_Click()
    Dim rs As DAO.Recordset
    Set rs = Me.RecordsetClone
    QuoteNumberLng = 16 ' This is a public variant variable set to 16 for testing but could be any QuoID Number
    If TogQuoteCalc = 0 Then
            Forms!frmMain!frmQuote.SourceObject = "frmQuote"
            Forms!frmMain!frmQuote.SetFocus
            Forms!frmMain!frmQuote.Form!QuoID.SetFocus
           rs.FindFirst "[QuoID] = " & QuoteNumberLng
            If rs.NoMatch Then
                MsgBox "Record Not Found"
            Else
                Me.Bookmark = rs.Bookmark
            End If
    End If
    If TogQuoteCalc = -1 Then
        'remember quote ID  number before switching to a form calculator
        QuoteNumberLng = Forms!frmMain!frmQuote.Form!QuoID
       Forms!frmMain!frmQuote.SourceObject = "frmCalculator"
    End If

    End Sub
    Thursday, August 5, 2010 7:22 PM
  • Try:

    Set rs = Me.Recordset.Clone


    -- Roger Carlson
    MS Access MVP 2006-2010
    www.rogersaccesslibrary.com
    Thursday, August 5, 2010 7:41 PM
  • I tried to simulate your problem Steve, and I got the exact error (runtime error 7951 " You entered an expression that has invalid reference to the RecordsetClone property) you recieved when the form Me references didn't have its RecordSource property was set to null. In other words, I got that error when I forgot to give the form a recordsource.
    Thursday, August 5, 2010 7:45 PM
  • Thanks have done that now the code looks like this but now back to the old error " Object variable or With block variable not set run time error 91"

    Private Sub TogQuoteCalc_Click()
    Dim rs As DAO.Recordset
    Set rs = Me.Recordset.Clone
    QuoteNumberLng = 16 ' This is a public variant variable set to 16 for testing but could be any QuoID Number
    If TogQuoteCalc = 0 Then
            Forms!frmMain!frmQuote.SourceObject = "frmQuote"
            Forms!frmMain!frmQuote.SetFocus
            Forms!frmMain!frmQuote.Form!QuoID.SetFocus
           rs.FindFirst "[QuoID] = " & QuoteNumberLng
            If rs.NoMatch Then
                MsgBox "Record Not Found"
            Else
                Me.Bookmark = rs.Bookmark
            End If
    End If
    If TogQuoteCalc = -1 Then
        'remember quote ID  number before switching to a form calculator
        QuoteNumberLng = Forms!frmMain!frmQuote.Form!QuoID
       Forms!frmMain!frmQuote.SourceObject = "frmCalculator"
    End If

    End Sub
    Thursday, August 5, 2010 7:47 PM
  • Put a breakpoint in the code before the error and see if rs is nothing

    Thursday, August 5, 2010 7:50 PM
  • Im thinking its the reference me.Recordset.clone

    as the main form does not have any Record source and so could the me.recordset.clone be looking for a recordset that is not present

    the form   "frmQuote" is the one that has a recordset and is a subform on the main form thats called frmMain that has no record source.

    What do you recon and how to rectify it

    SteveW

    Thursday, August 5, 2010 7:52 PM
  • yes rs = nothing
    Thursday, August 5, 2010 8:05 PM
  • I may have missed this, but which form is your toggle button on?

    Thursday, August 5, 2010 8:18 PM
  • its on the main form that has no record source called frmMain the reason for this is this main form has tabs on it to do other things and so has no record source
    Thursday, August 5, 2010 8:21 PM
  • Ok, I see, what you might be looking for is this Set rs = Me!frmQuote.Form.RecordsetClone.

    This makes rs equal to the recordsource for the subform named "frmQuote".

    Thursday, August 5, 2010 8:25 PM
  • I have a form with a table as its record source and it has a field called QuoID which is also the primary key of the table

    All I am trying to do is navigate through the table with the form and that works fine

    When I get to a record that requires some work done to it I want to remember the field QuoID which is present in a text box on the form.
    navigate away from the form or close the form and open a different form when I have finished the task on the diffrent form I want be able to return to the original form and have the form show the remembered QuoID

    So that I can update the record on the original form.


    Without knowing how this fits into an overall workflow, I am guessing you just want to leave some bread crumbs for your users to be able to find their way back.

    One option is to simply open another form (usually filtered to a specific record) to perform some type of edit. When complete the form closes and the original form is still where it was.

    Another method would be to have a form that functions just as a navigation console (Access used to use the switchboard metaphore).

    You may want to look at other examples and see what makes sense for your users.

     

    Thursday, August 5, 2010 8:28 PM
  • I thought that was going to do it but Im still getting the error Object variable or With block variable not set run time error 91

    here is the code now

    Private Sub TogQuoteCalc_Click()
    Dim rs As DAO.Recordset
    Set rs = Me!frmQuote.Form.Recordset.Clone
    QuoteNumberLng = 16 ' This is a public variant variable set to 16 for testing but could be any QuoID Number
    If TogQuoteCalc = 0 Then
            Forms!frmMain!frmQuote.SourceObject = "frmQuote"
            Forms!frmMain!frmQuote.SetFocus
            Forms!frmMain!frmQuote.Form!QuoID.SetFocus
           rs.FindFirst "[QuoID] = " & QuoteNumberLng
            If rs.NoMatch Then
                MsgBox "Record Not Found"
            Else
                Me.Bookmark = rs.Bookmark
            End If
    End If
    If TogQuoteCalc = -1 Then
        'remember quote ID  number before switching to a form calculator
        QuoteNumberLng = Forms!frmMain!frmQuote.Form!QuoID
       Forms!frmMain!frmQuote.SourceObject = "frmCalculator"
    End If

    End Sub

    Thursday, August 5, 2010 8:37 PM
  • You are setting rs before the form is attached as a subform.

    Even if you set it after it is attached, since your main form has no recordset the Bookmark will just be the next thing to fail.

    Thursday, August 5, 2010 8:43 PM
  • Thank you all for the time spent on this I do very much appreciate your efforts and has given me lots of guidance

    I have just put a combo box on the subform frmQuote and with this code found any record I wanted to but the toggle button just dont want to work

    maybe a work around could be to update the combo box with the value on return to the form and have the combo box find the correct record for me

    Private Sub Combo16_AfterUpdate()
        'Find the record that matches the control.
        Dim rs As Object

        Set rs = Me.Recordset.Clone
        rs.FindFirst "[QuoID] = " & Str(Nz(Me![Combo16], 0))
        If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    End Sub

    Thursday, August 5, 2010 8:45 PM
  • Yes a rethink may be in order if I cant resolve this problem its a shame because the workflow and the interactivness of this Main form with its tabs and subforms is very neat.
    Thursday, August 5, 2010 8:51 PM
  • Private Sub TogQuoteCalc_Click()
    
    QuoteNumberLng = 16 ' This is a public variant variable set to 16 for testing but could be any QuoID Number
    If TogQuoteCalc = 0 Then
      Me.frmQuote.SourceObject = "frmQuote"
      Me.frmQuote.SetFocus
      Me.frmQuote.Form!QuoID.SetFocus
    
      Dim rs As DAO.Recordset
      Set rs = Me!frmQuote.Form.RecordsetClone
    
      rs.FindFirst "[QuoID] = " & QuoteNumberLng
      If rs.NoMatch Then
       MsgBox "Record Not Found"
      Else
       Me!frmQuote.Form.Bookmark = rs.Bookmark
      End If
    End If
    
    If TogQuoteCalc = -1 Then
     'remember quote ID number before switching to a form calculator
     QuoteNumberLng = Me.frmQuote.Form!QuoID
     Me.frmQuote.SourceObject = "frmCalculator"
    End If
    
    End Sub
    
    After looking at William's last post, I think this is the code you are looking for.
    Thursday, August 5, 2010 8:54 PM
  • While working on this with you I found this link that you might find to be a helpful resource if you are using a lot of subforms. http://www.mvps.org/access/forms/frm0031.htm
    Thursday, August 5, 2010 9:01 PM
  • YES THIS WORKS

    OH thank you very much all conserned

    WOO HOO

    I can sleep now its late and thank you all very very much you have made this work Im so pleased.

    SteveW

    FINAL WORKING CODE

    Private Sub TogQuoteCalc_Click()

    QuoteNumberLng = 16 ' This is a public variant variable set to 16 for testing but could be any QuoID Number
    If TogQuoteCalc = 0 Then
        Me.frmQuote.SourceObject = "frmQuote"
        Me.frmQuote.SetFocus
        Me.frmQuote.Form!QuoID.SetFocus

        Dim rs As DAO.Recordset
        Set rs = Me!frmQuote.Form.Recordset.Clone

        rs.FindFirst "[QuoID] = " & QuoteNumberLng
        If rs.NoMatch Then
          MsgBox "Record Not Found"
        Else
          Me!frmQuote.Form.Bookmark = rs.Bookmark
        End If
    End If

    If TogQuoteCalc = -1 Then
      'remember quote ID number before switching to a form calculator
      QuoteNumberLng = Me.frmQuote.Form!QuoID
      Me.frmQuote.SourceObject = "frmCalculator"
    End If

    End Sub
    • Proposed as answer by crushbrain Thursday, August 5, 2010 9:06 PM
    Thursday, August 5, 2010 9:05 PM
  • Thanks again I should give you a credit mention for your help with this as it has stumped me for days but now I see what you have done and feel a little stupid to say the least.

    Thanks again

    SteveW

     

    Thursday, August 5, 2010 9:09 PM
  • Congradulations on solving the problem! No reason to feel stupid. Programming is always a learning experience.

    • Marked as answer by SteveWretham Friday, August 6, 2010 1:40 AM
    Thursday, August 5, 2010 9:42 PM