Update statement

[newbie87]

Hi All,
i have a form which allows a user to enter their personal details it allows them to add new or update existing records.
if the user only fills in half the forms a submits it, this inserts the submitted data correctly. I used DLookup's to allow their user to enter their personal ID ot bring back their data to allow them to modify it.
the problem i am have is if a user only fills in half the form and wants to modify it another time, they cant.
the DLook up returns their data in the correct text fields, but if they click the update button and leave the unpopulated text fields blank again i get an error
run-time error 3464, data type mismatch in criteria.
i know it's because im trying to modify null values with null values, but is there away around this?

below is my update code

CurrentDb.Execute "UPDATE Person SET Name = '" & Forms!frmDetails!Name & "', _
& " Age = '" & Forms!frmDetails!Age & "' ", Address = '" & Forms!frmDetails!Address & "' " _
& "WHERE Name = " & Forms!frmDetails!Name & ";", dbFailOnError

I've read about iif statements but I'm not sure if this would be the way to go or how to use them.

Many thanks in advance.

 

[vbaInet]

Do you want to eventually set the values to Null?

You will need to use an IF...ELSE IF block to check which textboxes are empty, then execute the command accordingly.

E.g.:

Dim strText1 As Variant

If IsNull(Textbox1.Value) Then
    strText1 = Null
Else
    strText1 = "'" & Me.Textbox1.Value & "'"
End If

DoCmd.Execute "UPDATE table SET field = " & strText1 & " WHERE ..."