How to stop IsNull combo box visual basic error

I have a database that checks out and in equipment. On my form I have two combo boxes, one for "Check Out" items and one for "Check In" items. For some reason when we navigate away from one of the buttons and go to the other one to check something back in that is already back in we are just cleaning up the database to show it back in, it throws us a visual basic error message. Picture attached.
Stops on "DoCmd.RunSQL strSQL"

I have tried it with trying to insert the: If IsNull(Me.cmbCheckOut) Then
         Me.Refresh
    Else
and still get the error message.
I would like to be able to navigate away from that combo box and return to it later if necessary. Could some one assist?

Thank you,

Private Sub cmbCheckOut_AfterUpdate()
' Purpose:  To add a record to tEquipment_Detail to reflect a check-out.
'    Runs when the 'Check Out an Item:' combo box is updated.
    Dim strSQL As String
    
    ' Disable the cautionary messages.
    DoCmd.SetWarnings False
    
    ' This is the SQL statement that adds a record to tEquipment_Detail with
    '    the appropriate information.
    If IsNull(Me.cmbCheckOut) Then
         Me.Refresh
    Else
        strSQL = "INSERT INTO tEquipment_Detail (ItemID,OutEID,TimeStampOut,TransID) " & _
        "VALUES (" & Me.cmbCheckOut & ", " & Me.EID & ", #" & Now() & "#, 1)"
    End If
    ' Run the SQL command.
    DoCmd.RunSQL strSQL
    
    ' Requery refreshes the information shown on the form
    Me.Requery
    
    ' These two update the data in the drop-down views.
    Me.cmbCheckOut.Requery
    Me.cmbCheckIn.Requery
    
    ' Enable the cautionary messages.
    DoCmd.SetWarnings True
End Sub

Open in new window

JamieBennettAsked:
Who is Participating?
 
Eric ShermanAccountant/DeveloperCommented:
What is the Visual Basic Error and can you explain this procedure, I'm not really following what you are saying here ...

>>>>For some reason when we navigate away from one of the buttons and go to the other one to check something back in that is already back in we are just cleaning up the database to show it back in,<<<<<<

ET
0
 
Joe OvermanEngineerCommented:
What is the error?
0
 
puppydogbuddyCommented:
try changing this:
If IsNull(Me.cmbCheckOut) Then
         Me.Refresh
Else
        strSQL = "INSERT INTO tEquipment_Detail (ItemID,OutEID,TimeStampOut,TransID) " & _
        "VALUES (" & Me.cmbCheckOut & ", " & Me.EID & ", #" & Now() & "#, 1)"
End If

To this:
        strSQL = "INSERT INTO tEquipment_Detail (ItemID,OutEID,TimeStampOut,TransID) " & _
        "VALUES (" & nz(Me.cmbCheckOut,0) & ", " & Me.EID & ", #" & Now() & "#, 1)"
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
JamieBennettAuthor Commented:
For some reason my error message document is not coming through to you.
It tells me:
Run-time error '3129':
Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.
0
 
Joe OvermanEngineerCommented:
Are the 'me.cmbCheckout' and 'me.eid' fields numerical or text.  If they are text it will certainly cause an error in your SQL.
Can you post what the strSQL is when the error fails?
0
 
puppydogbuddyCommented:
Does Me.cmbCheckOut return text?  If so, try the nz text function as shown.

"VALUES (" & nz(Me.cmbCheckOut,"") & ",

0
 
JamieBennettAuthor Commented:
puppydogbuddy;
I have been playing around changing the script and still seem to get this now.
Run-Time error '3134';
Syntax error in INSERT INTO statement.
0
 
JamieBennettAuthor Commented:
I have my SQL statement above, but the cmbCheckOut is text and the Me.eid field is a number field.
0
 
JamieBennettAuthor Commented:
Trying again to upload document image1
debug-error.doc
0
 
puppydogbuddyCommented:
try this:
  strSQL = "INSERT INTO tEquipment_Detail (ItemID,OutEID,TimeStampOut,TransID) " & _
        "VALUES ('" & nz(Me.cmbCheckOut,"") & "'", " & Me.EID & ", #" & Now() & "#, 1)"
0
 
JamieBennettAuthor Commented:
I get a compile error now on the comas, in your statement.
0
 
puppydogbuddyCommented:
try this:
1.remove the nz function from the syntax I gave and run the sql

2. if the above does not work, substitute a hard value for  Me.cmbCheckOut and run rhat way for testing purposes.
0
 
JamieBennettAuthor Commented:
Please find a partial copy of the database. The equipment check out/In is the one I am working on. I used to have two separate screens, but it is easier for them to just use one screen when they are in a hurry.  
Hope this helps in showing what I am trying to accomplish.
Jamie

CheckBackend.zip
0
 
puppydogbuddyCommented:
Jamie,
I downloaded your file, but I could only view the back end.  I could not view the front end because I only have Access 2000 and the front end is running version 2003  or later.  I think another expert will be able to look at your front end and solve.  If not, we can still solve if you substitute an actual value fhat would be returned from me.cmbCheckout in place of the object, itself, and adjust the syntax accordingly.  IF you have it working with the hard value, we can then adjust the syntax for a reference to the combobox object, itself.
0
 
JamieBennettAuthor Commented:
OK, Thank you,  I am still trying to figure it out and working with it. I am not that good with the visual basic stuff.
0
 
puppydogbuddyCommented:
Jamie,
some tips to help you.........
1. need to use combobox column property if the value you are using for the insert is not coming from the bound column(0).  Example>>>> = Me.combo.Column(1).Value

2. Different delimeters used for text, numberical, and dates
If you have the variable names inside quote marks, VB thinks that the variable NAME is part of the string. Note that you have to include apostrophe field delimiters around text field data, and (ONLY for Access databases, as access uses non standard SQL) the hash mark (#) around DATE field type data. Numeric field types must have NO delimiters.
 _________text delimiter ezample______________
 strText = "THIS IS THE TEXT YOU WISH TO ENTER..."
strSQL = "INSERT INTO tblTest (fldText) Values ('" & strText & "')"
0
 
JamieBennettAuthor Commented:
Puppydogbuddy,
I do have the insert to coming from the combo boxes, meaning cmbCheckOut is only showing what items can be checked out. CmbCheckIn only shows or allows them to select what is actually checked out and needs to be checked back in. This way if they try to check something out and it gives an error message not in the list, they will know that the prior person who had it did not properly check it back in. Then the supervisor gets involved. But the person needing to check it out has the ability to go ahead and check it back in so they can properly check it out. Hope this makes sense!
It worked fine until some new employees working in it didn't know how to use a computer.
I am studying your second comment to mine.
Thank you for your assistance and patience with me.
Jamie
0
 
puppydogbuddyCommented:
Jamie,
Keep in mind that most comboboxes have at least 2 columns, one is the key column which usually hidden, and the other is the description which is displayed to the user.  Access indexes the columns starting with 0 for the leftmost column. ...hence the reference to column(1) in the example.
0
 
Eric ShermanAccountant/DeveloperCommented:
Hi Jammie and PDB (what's up today puppydogbuddy) ... It's been a while!!!

I have your sample DB downloaded .... Can you tell me the steps you are taking to produce the error.  Which employee selected, etc.  I can't seem to make it generate that error.

Thanks,

ET
0
 
JamieBennettAuthor Commented:
Hello Estherman,
Sinero: Select two of the employees and check in all their items.  Now lets pretend all the employees should have checked their items back in as the equipment is on the shelf.
Now view the report on the main screen that shows what equipment is still checked out by employee- "View all equipment out by Officer". Write down two or three of the barcode items still not checked back in.
Then choose one of the people you checked in and in the check out combo box type in the barcode and you should get an error telling you it is not in the list. Back space or clear the field and go to the check in combo box to check it in and it will produce the error.
Jamie
0
 
JamieBennettAuthor Commented:
Scenario, sorry it's early morning.
0
 
Eric ShermanAccountant/DeveloperCommented:
Hi Jamie ....

This should resolve your error.  Make your cmbCheckOut_AfterUpdate look like the following.  Basically, you were running the SQL outside the IF statement when the cmbCheckOut combo box is null thus producing the following error messages.

Run-time Error 3129
Invalid SQL Statement Expected DELETE, INSERT, PROCEDURE SELECT OR UPDATE.  
In this case the strSQL variable would have been blank because the cmbCheckOut is Null therefore the strSQL would not have been constructed but you were still trying to execute the DoCmd.RunSQL outside the IF statement.

Run-Time Error 3124
Syntax error in Insert Into Statement.
In this case you were constructing the strSQL variable but with a Null or "" ItemID.  This would have produced an invalid Values List for the SQL that looks like ..... Values (, 113, #4/21/2009 8:31:47 AM#, 1).  Notice the ItemID value is missing which is what caused the error.

This should work ....


Private Sub cmbCheckOut_AfterUpdate()
' Purpose:  To add a record to tEquipment_Detail to reflect a check-out.
'    Runs when the 'Check Out an Item:' combo box is updated.
    Dim strSQL As String
   
    ' Disable the cautionary messages.
    DoCmd.SetWarnings False
   
    ' This is the SQL statement that adds a record to tEquipment_Detail with
    '    the appropriate information.
    If IsNull(Me.cmbCheckOut) Then
         Me.Refresh
    Else
        strSQL = "INSERT INTO tEquipment_Detail (ItemID,OutEID,TimeStampOut,TransID) " & _
        "VALUES (" & Me.cmbCheckOut & ", " & Me.EID & ", #" & Now() & "#, 1)"
   
    ' Run the SQL command.
    DoCmd.RunSQL strSQL
    DoCmd.RunMacro "macUpdate_tEquipment_Detail"
    ' Requery refreshes the information shown on the form
    Me.Requery
   
    End If
   
    ' These two update the data in the drop-down views.
    Me.cmbCheckOut.Requery
    Me.cmbCheckIn.Requery
   
    ' Enable the cautionary messages.
    DoCmd.SetWarnings True
End Sub



ET
0
 
JamieBennettAuthor Commented:
etsherman, that worked perfect. I just thought it had to be outside of the If statement. Thank you for your assistance and thank you puppydogbuddy for  your assistance also.

Much appreciated!
0
 
Eric ShermanAccountant/DeveloperCommented:
Glad I could help Jamie.

ET
0
 
puppydogbuddyCommented:
Jamie,
Glad ET was able to help you.

ET,
Good job ! It sure makes things easier if you can work with the application in front of you.
0
 
Eric ShermanAccountant/DeveloperCommented:
>>>>ET,
Good job ! It sure makes things easier if you can work with the application in front of you.<<<<<

Yes PDB ...  That was a pretty comprehensive application and without actually looking at the code behind the form and performing the exact steps it would have been virtually impossible to figure out that one.  That's the added convenience of being able to upload a sample db.  I've found that some users will start asking a question in a very general tone without enough detail even though they have the application up in front of them.  Those I've learned to bypass as you will be fishing for information needed to properly analyze the question.  The sample db in this cas made all the difference.  

Nice db to Jamie ...  it seems to be very organized which probably works great as a tool for your organization.

Later ...

ET
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.