UtterAccess.com
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    
 
   
phisaw
post May 27 2020, 04:06 PM
Post#1



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.
CODE
  
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

CheckJobNumber:
  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"
  Else
'************
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
rs.Close

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,
Pam
Go to the top of the page
 
DanielPineault
post May 27 2020, 04:21 PM
Post#2


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



Not sure if I fully follow, but what about simply doing
CODE
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"
    Else
        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
 
phisaw
post May 27 2020, 05:07 PM
Post#3



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!!
Pam
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    9th July 2020 - 05:26 AM