Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How to stop IsNull combo box visual basic error

Posted on 2009-04-20
26
Medium Priority
?
434 Views
Last Modified: 2013-11-28
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

0
Comment
Question by:JamieBennett
  • 11
  • 8
  • 5
  • +1
26 Comments
 
LVL 8

Expert Comment

by:Joe Overman
ID: 24186782
What is the error?
0
 
LVL 38

Assisted Solution

by:puppydogbuddy
puppydogbuddy earned 600 total points
ID: 24186787
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
 
LVL 19

Accepted Solution

by:
Eric Sherman earned 1400 total points
ID: 24187006
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:JamieBennett
ID: 24187219
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
 
LVL 8

Expert Comment

by:Joe Overman
ID: 24187312
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
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24187317
Does Me.cmbCheckOut return text?  If so, try the nz text function as shown.

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

0
 

Author Comment

by:JamieBennett
ID: 24187395
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
 

Author Comment

by:JamieBennett
ID: 24187435
I have my SQL statement above, but the cmbCheckOut is text and the Me.eid field is a number field.
0
 

Author Comment

by:JamieBennett
ID: 24187469
Trying again to upload document image1
debug-error.doc
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24187529
try this:
  strSQL = "INSERT INTO tEquipment_Detail (ItemID,OutEID,TimeStampOut,TransID) " & _
        "VALUES ('" & nz(Me.cmbCheckOut,"") & "'", " & Me.EID & ", #" & Now() & "#, 1)"
0
 

Author Comment

by:JamieBennett
ID: 24187560
I get a compile error now on the comas, in your statement.
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24187655
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
 

Author Comment

by:JamieBennett
ID: 24188044
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
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24188328
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
 

Author Comment

by:JamieBennett
ID: 24188403
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
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24188807
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
 

Author Comment

by:JamieBennett
ID: 24188892
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
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24189025
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
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 24189781
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
 

Author Comment

by:JamieBennett
ID: 24193788
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
 

Author Comment

by:JamieBennett
ID: 24193802
Scenario, sorry it's early morning.
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 24194699
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
 

Author Closing Comment

by:JamieBennett
ID: 31572328
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
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 24195192
Glad I could help Jamie.

ET
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24195307
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
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 24196034
>>>>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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question