Update Statement with Null Value

[mcaliebe]

I have a query which builds a temp table to populating my form with. Not all the values in the table are completed as I may not have all the information. I have set an update statement, fired by a command button, to pass these values back to the proper table when the user makes changes.

If my text fields are populated, the update statement works fine, however If I have no data in the text box, the update statement shows the field as NULL and fails. How do I get around this?

[mcaliebe]

Here is the simple code I am using. All the fields in the tables are set to text, no default, and no requirements.
Dim stSQL As String

stSQL = "UPDATE tblItem " & _
"SET tblItem.ItemNbr =" & Me.txtItemNbr & ", tblItem.BrennanItemNbr =" & Me.txtBrennanItemNbr & " " & _
"WHERE tblItem.ID =" & Me.txtID

DoCmd.RunSQL stSQL

[Micron]

If those fields permit empty strings ("") you could try the NZ function to coerce Nulls to empty strings. I think that would work, but I'm getting rusty...
Nz(Me.txtItemNbr,"")

Note : As long as the nulls don't involve foreign keys. If you sql is really that simple, you could also try Is Null Or ...
The error message would help if you're getting one.

[ssanfu]

Why don't you just bind the form to "tblItem"?

Here is the simple code I am using. All the fields in the tables are set to text, <snip>

Really? You are sure that the data types of ALL of the fields are TEXT? Even the PK field "ID"?

Since text fields must be delimited, I wouldn't expect the SQL to execute at all.
If the fields are TEXT datatypes, I would expect the SQL to look like (note the single quotes)

Dim stSQL As String

stSQL = "UPDATE tblItem"
stSQL = stSQL & " SET tblItem.ItemNbr = '" & Me.txtItemNbr & "',"
stSQL = stSQL & " tblItem.BrennanItemNbr = '" & Me.txtBrennanItemNbr & "'" 
stSQL = stSQL & " WHERE tblItem.ID = '" & Me.txtID & "';"

'  DoCmd.RunSQL stSQL
Currentdb.Execute stSQL, dbFailOnError    '<<-- I would use this

Add Debug statement to see what the SQL statement looks like and post the result from the immediate window:

Dim stSQL As String

stSQL = "UPDATE tblItem"
stSQL = stSQL & " SET tblItem.ItemNbr = '" & Me.txtItemNbr & "',"
stSQL = stSQL & " tblItem.BrennanItemNbr = '" & Me.txtBrennanItemNbr & "'" 
stSQL = stSQL & " WHERE tblItem.ID = '" & Me.txtID & "';"

Debug.Print stSQL

Currentdb.Execute stSQL, dbFailOnError

[mcaliebe]

Hi Steve-

You hit the nail on the head. I wasn't developing my code to pass a string. When it failed, and worked and failed again, was due to my use of a number as opposed to mixed text. Formatting with the correct parenthesis corrected my issue.

As for the use of
Currentdb.Execute stSQL, dbFailOnError

I assume I would need an error trap in my module to make use of this statement. What do you recommend?

[pbaldy]

A primer on error trapping:

http://www.baldyweb.com/ErrorTrap.htm

[ssanfu]

Yes, error trapping is good. I use code like the link Paul provided.


Here is a little more info on DoCmd.RunSQL vs CurrentDB.Execute (plus more)
http://www.utteraccess.com/forum/lof.../t1953226.html