Using iif statement to update data

[tagteam]

I am using a form to update data in a table. I use a variable to get the row Id of the record I am going to update. Then I get variables for all the things I want to update.



In the update query I use the iif statement to update things that need to be updated and leave things that I dont want to change by leaving their fields blank. I have to use the iif statement so I dont update an existing value to a blank value.

Here is an example of the iif i use

IIf([Forms]![GUF_GUSDataEntry]![CustomerID] Is Null,[tmpGU_GUS].[CustomerID],[Forms]![GUF_GUSDataEntry]![CustomerID])


Basically it is iif the update field is null, update the value to its existing value, else update to the form value.

What i would like to know is if there is a way to have the first if condition basically say do nothing instead of updating it to its existing value.

[Welshgasman]

Update the value to it's existing value?

[Welshgasman]

Why are you not using a bound form?
Build the update sql depending on what has been updated, omitting what has not changed.

[tagteam]

I usually do this because the user is going to update several records. This allows me to make a temporary table so they can review the data and then process the update. But yes, you are right. If they are going to just update one line a time I can just use something like Me.AccountingPeriod = Me.NewAccountingPeriod and attach it to a button. I use the unbound fields because they will do multiple updates and that way I can retain the value they have entered.

[Welshgasman]

Well in that case I would build the sql string, from two strings.
One would hold the field names, and the other the new values, and the concatenate them.

[tagteam]

I tried the idea of just updating the values using an if statement. I have a form that has all the field values I might want to change on it. It also has an unbound field for each of the data fields prefixed with New. Then i tried to just use and if statement to set the current value to the new value.

I did this:
If Me.NewAccountingPeriod Is Not Null Then
Me.AccountingPeriod = Me.NewAccountingPeriod
End If

However, i get the following error: Run-time error 424: Object Required.

[Gicu]

In VBA you should use the Not IsNull(Me.NewAccountingPeriod) function to check for Nulls:
https://nolongerset.com/null-handling-in-ms-access/

https://www.techrepublic.com/article/learn-the-differences-between-is-null-and-isnull-in-access/#:~:text=Is%20Null%20and%20IsNull()%20both%20find% 20null%20values%2C%20but,used%20only%20in%20VBA%20 modules.


Cheers,

[tagteam]

Thanks Gicu, That worked great. I am not a programmer so Not isnull(lkdsfl) is not something I would have thought of.

[Gicu]