UPDATE only where form value is different from table value

[Thumbs]

Hello,



I am not sure if this is possible without many lines of relatively redundant if/else statements, but in my quest to be an efficient developer, I ask the masses.

I have a subform that I have set to open (via button) and view the currently selected record based on a separate subform. This form will be used for editing the record, so I can lock down the previous form to prevent accidental changes to data. On the subform for editing the data, I have a button to save the record, which I am going to code to run an UPDATE to the table.

Is there an easy way to compare the value on the form fields with the value of its corresponding column in the table, and return either a boolean or integer, then use that value to UPDATE only the fields where the data has changed? I want keep this as streamlined and dynamic as possible, using as few lines of code as I can.

There are about 20 fields that could possibly be changed, so you can probably see my hesitation to write an individual statement for each one.

I am currently exploring the use of some creative FOR statements in VBA to pass values through variables to the SQL statement, but I figured it wouldnt hurt to ask here whilst I grind away, in case anyone had any ideas off the top of their head that I had not come up with.

I am not a very experienced developer, so pardon me if I have missed something that should be common knowledge.

Thank you in advance for your help.

[June7]

Why do you need to run UPDATE? Are the forms bound? Data entry/edit to bound forms passes directly to the associated table.

[Thumbs]

I know I could use bound forms, but i need to preserve data integrity as best as possible. This database, when implemented, will have quite a few people viewing it and updating it. I want to make sure that these users press a button to confirm the changes they have made before they are rolled in to the table. It's not yet a requirement for the project, but I can easily see it becoming one (this will be used on a government program, thus the fickleness).

[June7]

I manage a db that has up to 10 users and all forms are bound and the db is split. Data integrity is tightly controlled with code. Never been an issue.

Doesn't matter if the data has or hasn't been changed, just save them all anyway and one UPDATE statement will serve. An alternative to UPDATE sql is to bind the form to RecordSource but don't bind the controls. This will make the table directly available to form code for populating fields. Open form to existing record or to new record. Use form Close event or a button click to populate fields with code like:

Me!fieldname = Me.textboxname

Check into the OnDirty event and the Dirty property of forms if you want to make the update conditional on changes.

[Thumbs]

I guess I was sort of trying to challenge myself to learn something new as well in trying to differentiate between fields with changes vs. fields without. I'll have other sub forms using a similar setup to the one you described with unbound controls. I'll see if that might serve my purposes instead. I'll probably still work on the selective UPDATE in my free time, just to learn something new and further develop my understanding of VBA and SQL.

I'm a network-engineer-turned-reluctant-coder (well, I was only reluctant until I started being able to code most of the things I needed off the top of my head or with minimal googling), so I'm having to develop a completely new skillset, starting with VBA and SQL and migrating to other languages and platforms.

Thanks for your help and suggestiopns.

[June7]

If you go with the bound form/unbound controls, conditional code for each data:

If Me!fieldname <> Me.textboxname Then Me!fieldname = Me.textboxname

However, probably as much processing time as just saving the value.

[Thumbs]

Got it, I only wanted to do the selective UPDATE if I could do it efficiently with some sort of loop. I'll post back if I figure out how to do it and keep it clean, in case anyone who viewed this might find some benefit to the code. Thanks again.

[June7]

It doesn't matter if it's in a loop or sequential, each control would have to be checked to see if value changed. Looping can be done but tricky. Still just as much computer processing, but with fewer lines of code.