X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Error 91-object Variable Or With Block Variable Not Set', Access 2013    
post May 27 2020, 04:06 PM

Posts: 330
Joined: 3-April 08

Hello All,
I'm getting 'Error 91-Object Variable or With Block Variable Not Set' on a form using a textbox to search for a job number. If the job exists, then it will open another form so user can enter time worked, if the job does not exist, then it should open a custom message stating the selected job does not exist.

Using the code below and removing the section between the asterisks, it works to open the time form if the job number is correctly entered.

So I inserted the section between the asterisks (CheckJobNumber) to check if the job exists. That section of code works on another form to open the custom message that the job does not exist.
I thought it would be easy to include the CheckJobNumber code into this form, as well, but it keeps throwing the error 91 and highlights on rs.Close.
Private Sub CboJobNumberSelect_AfterUpdate()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSql As String
Dim strInput As String
Dim vCheck As Variant
Dim sWhere As String
'Dim sMsg As String
'Dim sMsgHdr As String

  vCheck = ""
  sWhere = "jobnumber = " & Me.CboJobNumberSelect & " OR PAnumber=" & Me.CboJobNumberSelect
  vCheck = DLookup("Trim(jobnumber & ' ' & PAnumber)", "trepair", sWhere)
  If IsNull(vCheck) Or vCheck = "" Then
    DoCmd.OpenForm "frepairjobselectwithmessage"
strSql = "SELECT tech " & _
"FROM tWorkLog " & _
"WHERE tworklog.tech ='" & Forms!ftimeswitchboard!CboTech & "'" & _
" and tworklog.StopTime Is Null"

Set db = CurrentDb
Debug.Print strSql
Set rs = db.OpenRecordset(strSql, dbOpenSnapshot)
If rs.EOF = True Then
    DoCmd.OpenForm "fTimeEntry", , , "jobnumber=" & Me.CboJobNumberSelect & " OR PAnumber=" & Me.CboJobNumberSelect
End If
End If

Set rs = Nothing
Set db = Nothing

End Sub

If anyone can tell me what is causing this error, I would greatly appreciate it.
Thanks in advance,
Go to the top of the page
post May 27 2020, 04:21 PM

UtterAccess VIP
Posts: 7,389
Joined: 30-June 11

Not sure if I fully follow, but what about simply doing
Private Sub CboJobNumberSelect_AfterUpdate()
    Dim strepairWhere         As String
    Dim stWorkLogWhere        As String
    Dim iCount                As Integer

    strepairWhere = "([jobnumber] = " & Me.CboJobNumberSelect & ") OR ([PAnumber] = " & Me.CboJobNumberSelect & ")"
    iCount = DCount("*", "trepair", strepairWhere)
    If iCount = 0 Then
        DoCmd.OpenForm "frepairjobselectwithmessage"
        stWorkLogWhere = "([tech] = '" & Forms!ftimeswitchboard!CboTech & "') AND ([StopTime] Is Null)"
        iCount = DCount("*", "tWorkLog", stWorkLogWhere)
        If iCount = 0 Then DoCmd.OpenForm "fTimeEntry", , , strepairWhere
    End If
End Sub

Don't forget proper error handling.

Daniel Pineault (2010-2020 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc

All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
post May 27 2020, 05:07 PM

Posts: 330
Joined: 3-April 08

Hi Daniel,

Once again, you've helped me with coding my database and I must say thank you, thank you, thank you.

It works and is so much easier to understand than what I had!!

Someone helped me years ago with the code I supplied and it has always seemed difficult to understand and work with.
I will try the code you supplied on my other forms and hopefully, it will work as well as on this one.
I'm reworking a very old database and am trying to use generic 'go-to' code, if that makes sense.

Again, thanks for your time and help!
Very much appreciated!!
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    16th July 2020 - 08:38 AM