Update a database column when mandatory fields are completed

[thebionicredneck2003]

Hi guys,



I have a form with lots of combo boxes and text boxes. They are all linked to various MS Access tables and some of the fields are mandatory. I would like do an check to see if all the mandatory fields are not null i.e. are populated with a value and then run an update query for my master table in the database to flag the records as being complete if all the mandatory fields are populated.

I have only recently started using access and vba and I know that the general idea would be something like

If (IsEmpty(tab1.cmb_a)) Then do something

But I am just blank at the moment on how to proceed, especially on how I can do all the checks and then populate the database.

Any useful pointers, code or links will be appreciated.


Thank you

[RuralGuy]

Is your Form bound to fields in a RecordSet? (Each Control has a value in the ControlSource.)

[thebionicredneck2003]

Hi there, yes my form is bound to the fields in the recordset

[RuralGuy]

Access will save all of the fields that changed during your edit session. You can check to make sure every control has a value in the BeforeUpdate event of the form using VBA code and Cancel the event if you're not satisfied.

[thebionicredneck2003]

Hi,

I know access will save the fields. The specific thing I am looking for is an example, a link or some code snippet of how to do this programmatically and fire off an update query when it notices that all the mandatory fields have been completed.

[June7]

This 'completed flag' in the master table is a value that is calculated dependent on other data. Conventional wisdom is that saving dependent data is poor design. The value can be calculated when needed, as on a report.

However, if you must, then code could be in the form's BeforeUpdate event, something like:

If Not IsNull(Me!field1) And Not IsNull(Me!field2) And Not IsNull(Me!field3) And Not IsNull(Me!field4) Then
CurrentDb.Execute "UPDATE tablename SET fieldname=True WHERE ID = " & Me.ID
End If

[thebionicredneck2003]

Thanks June7 and I agree that it is wrong to save dependent data this way, but for the time being, it has to be this way.
Your code works brilliantly, but I have been getting this message "The record has been changed by another user since you started editing it. If you save this record, you will overwrite the changes the other user has made".
Any pointers on how to resolve this and still keep the flag set to true?
Many thanks

[June7]

Is the field you want to update actually included in the form's RecordSource? I had presumed from your narrative that the 'master' table was not part of this form. If the field is available to the form, then simply:

Me!fieldname = True