Pass Form Values to Update Query without updating blank fields in table

Question

I'm trying to use a form with an update query to update fields in a table, but only if the field in the form has information in it and not to update the fields in the table with nothing from the form.

Your question isn't clear. Are you trying to click a button to run an update query based upon the value of a field? Is the form bound or unbound? — Josh Miller, Apr 03 '15 at 15:29
I'm clicking a button to run the update query. the form is unbound. — Kenneth, Apr 03 '15 at 22:25
I need to know how to update the table but not update it with blank fields from the form where it is getting it's information. — Kenneth, Apr 03 '15 at 22:30

score 0 · Accepted Answer · edited May 23 '17 at 12:06

You can build the Insert statement dynamically based upon the fields and values. Here is some Pseudo code. If you add more to your question (i.e. click Edit on your question), I can give you more specific help.

Conditionally build up a string (i.e. add values only if there is a value in your control) called something like strFields in the format of:

"TableFieldName1, TableFieldname2, TableFieldname3"

Build up a similar string for the Values in the format of:

"Value1, Value2, Value3"

Keep in mind, if you are inserting into text fields, you will need to include "" in your string around the values.

Create the SQL Statement

"INSERT INTO Table 1 (" & strFieldNames & ")"
"VALUES (" & strValues & ")"

Execute the SQL.

Here is a similar thread on how to dynamically work with SQL

Building SQL Strings in Access

Pass Form Values to Update Query without updating blank fields in table

1 Answers1