SQL Update and Nulls (1 Viewer)

[jerrywolf]

Hi all-

not sure if this is the correct forum to post this, but here it goes. I have a form called order entry for well entering orders. A user types part number into a textbox, and my code finds all related parts (for the assembly), and inserts them into a temporary table, linked to a listbox on my form. The user can then go through each line item and decide if we need to manufacture the part, or if it is in inventory, give it an order number if it needs to be made, add notes, etc. to each line item. When the user clicks on the line item in the listbox, it populates the info for that line item in textboxes on the same form, and when the user clicks save, it saves it back to the temp table and refreshes the listbox. After all line items are correct, a user clicks a button to append them to the orders table. Here's my problem. If a user leaves a field blank (pertaining to line item), and clicks save, they get invalid use of null 94. Ideally, a field would be allowed to be left blank and the sql statement would insert null into that field. I know there has to be a way to do this! Here is my code for when the save button is clicked. Any help would be awesome! Thanks

Dim intTMPID As Integer
    Dim intCUSTID As Integer
    Dim strSHOP As String
    Dim strREV As String
    Dim intQTY As Integer
    Dim strNOTES As String
    
    intTMPID = Me.lst_Orders.Value
    intCUSTID = Me.cbo_Customer.Value
    strSHOP = Me.txt_ShopOrder.Value
    strREV = Me.txt_Rev.Value
    intQTY = Me.txt_Quantity.Value
    strNOTES = Me.txt_Notes.Value
    
    Dim strSQL As String
    strSQL = "UPDATE tbl_TempOrders" _
    & " SET ShopOrder = '" & strSHOP & "'" & ", Customer = " & intCUSTID & ", Rev = '" & strREV & "'" & ", Quantity = " & intQTY & ", Notes = '" & strNOTES & "'" _
    & " WHERE TmpOrderId=" & intTMPID & ";"
    
    CurrentDb.Execute strSQL
    Me.lst_Orders.Requery

 

[vbaInet]

For you the easiest thing to do is to run an UPDATE query. Set the Update To part to the textboxes/fields on the form.

 

[jdraw]

What exactly does " If a user leaves a field blank (pertaining to line item)" mean? They forgot to add something; there was/is nothing to enter????

You can review the text fields to see if there populated bfore saving.

Also, when using the .execute method it is a good practice to use the dbFailOnError option.

 

[vbaInet]

You can review the text fields to see if there populated bfore saving.

You can validate the fields/controls but you don't have to if the textboxes are referenced directly in a query. In fact you don't even have to if you do this:

    strSQL = "UPDATE tbl_TempOrders" & _
             " SET ShopOrder = Forms![COLOR="Blue"]FormName[/COLOR]![COLOR="blue"]txtBoxSHO[/COLOR], Customer = Forms![COLOR="blue"]FormName[/COLOR]![COLOR="blue"]txtBoxCUSTID[/COLOR], " [COLOR="Blue"]...etc[/COLOR]
             " WHERE TmpOrderId = Forms![COLOR="blue"]FormName[/COLOR]![COLOR="blue"]txtBoxTMPID[/COLOR];"

... just remember to add the parameters.

 

[jerrywolf]

What exactly does " If a user leaves a field blank (pertaining to line item)" mean? They forgot to add something; there was/is nothing to enter????

Also, when using the .execute method it is a good practice to use the dbFailOnError option.

Not all fields are required, for example, an order may not have any specific notes listed.

As for dbFailOnError, yes good call I left that out , but the app isn't finished yet .

Hmm, I like the idea of referencing the fields directly in my SQL syntax. I will try this. Hopefully it won't mind a blank field.

 

[jerrywolf]

You can validate the fields/controls but you don't have to if the textboxes are referenced directly in a query. In fact you don't even have to if you do this:

    strSQL = "UPDATE tbl_TempOrders" & _
             " SET ShopOrder = Forms![COLOR="Blue"]FormName[/COLOR]![COLOR="blue"]txtBoxSHO[/COLOR], Customer = Forms![COLOR="blue"]FormName[/COLOR]![COLOR="blue"]txtBoxCUSTID[/COLOR], " [COLOR="Blue"]...etc[/COLOR]
             " WHERE TmpOrderId = Forms![COLOR="blue"]FormName[/COLOR]![COLOR="blue"]txtBoxTMPID[/COLOR];"

... just remember to add the parameters.

I can't get it to work for me this way, for some reason I keep getting errors.

 

[tehNellie]

Use a query rather than build SQL in code and pass the relevant parameters to it. You can test the query in isolation to your code and your ability to extract data from your form in isolation to your query. Plus if you're passing a string to the parameter that only accepts a Long you'll get a meaningful (as much as any Access Error message is meaningful) error message.

i.e.

for the query

PARAMETERS [@custId] Long, [@TMPID] Long;
UPDATE tbl_TempOrders
SET Customer = [@custID] 
WHERE TmpOrderId = [@TMPID];

For the code

Dim qdf as dao.querydef

set qdf = currentdb.querydefs("TheQuery")
qdf.parameters("[@CustID]").value = Forms!FormName!txtBoxCUSTID
qdf.parameters("[@TMPID]").value = Forms!FormName!txtBoxTMPID

qdf.execute