Update Query

[LEXCERM]

Good afternoon,

I have a saved update query (created from the QBE editor) which takes the values from unbound text fields on a form and updates an underlying table.

However, if a text field is left blank, it overwrites any existing data in the table for that particular field with blank data - which I do not want to happen.

I tried to set some criteria within the query, but not getting anywhere.

Rather than go down the route of using VBA to check value of fields and writing manual sql, can this be achieved in the QBE itself?

Many thanks and regards.

 

[arnelgp]

Update yourTable Set yourFieldName = IIF(Trim(Form!yourformName!yourControlName & "") = "", yourFieldName, Form!yourFormName!yourControlName)

 

[LEXCERM]

Thanks for the reply arnelgp.

Looks like VBA is the route to go. I did figure out how to do it in the QBE, but there are complications when trying to validate many fields on a form.

Can I ask one more question please.

I'm trying to add a variable text field name into the code, but can't get it to work. For example:-

myTableField = "ACTIVE-INACTIVE"
myFormField = "CMBSTATUS"
myUpdateSql = "UPDATE tbl_ABCdata SET [" & myTableField & "] ='" & [Forms]![subfrm_EditSchedule]![" & myFormField & "] & "' " & _
                "WHERE ID_ABCdata = " & [Forms]![subfrm_EditSchedule]![ID_ABCdata] & ";"

The code is debugging at & myFormField &. My syntax is obviously incorrect.

Thanks in advance.

 

[arnelgp]

try evaluating the expression first, one of these should return result:

Stop 'break at this point , now press F8 to step through code.
On error resume next
txtCBMStatus = Forms!subfrm_EditSchedule.Controls(myFormField)
debug.print txtCBMStatus
txtCBMStatus = Forms!subfrm_EditSchedule.Form.Controls(myFormField)
debug.print txtCBMStatus
txtCBMStatus = Forms!yourMainform!subfrm_EditSchedule.Form.Controls(myFormField)
debug.print txtCBMStatus

if you find what is correct syntax, just substitute txtCBMStatus variable to your query.