How to insert Data from a NULL textbox

[khanhly246 via AccessMonster.com]

hi all
I use MS Access2003 and VBA language
I have a form with many textboxex and 1 button
when I fill all text box and click button, everything's OK
but If I don't fill all textbox I get a error : "run-time error '94' :
Invalid use of Null"
I set require properties in all field is "No" already but I still get the
error message
this is my insert statement :
dbs.Execute " INSERT INTO tablename(field1,field2)
VALUES ('"&Me.textbox1.value&"','"&Me.textbox2.value&"')
I tried to use Nz function already but it's not OK

what should I do now? please help?
best regard

 

[Allen Browne]

Insert the word Null into the SQL statement, without delimiters:

strSql = "INSERT INTO tablename (field1, field2) VALUES (" & _
IIf(IsNull(Me.textbox1), "Null", """" & Me.Textbox1 & """") & ", " & _
IIf(IsNull(Me.textbox2), "Null", """" & Me.Textbox2 & """") & ");"
Debug.Print strSql
dbs.Execute strSql, dbFailOnError

If the execute fails, open the Immediate Window (Ctrl+G) to see what's wrong
with the SQL statement.