Update a database column when mandatory fields are completed

[thebionicredneck]

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

 

[Catalina]

Are you using an unbound form?

 

[thebionicredneck]

No, I am using a form with bound controls

 

[Richard1980]

Hello,
the best way is to manage this action with the Form_BeforeUpdate event. You can set the TAG proprerty of the textboxes you want to be populated as mandatory with some keywords, like "required". Then, with the "For each..." expression you check if all mandatory fields were populated.

Private sub Form_BeforeUpdate (Cancel as Integer)
Dim ctl as control

For each ctl in Me.controls
Select Case ctl.ControlType
Case acTextBox, acListBox, acComboBox
If ctl.Tag = "required" Then
If IsNull(ctl) or ctl="" Then
MsgBox "The field " & ctl.Name & " is mandatory."
Cancel = True
End if
End if
End select
Next
End sub

The above code looks at all form's controls and focus on textboxes, listboxes and comboboxes (but you can manage your own). For each control which has the word "required" in TAG property verifies wheter the it has not null or blank. If yes, a message box comes up with the control name to be populated.
Give a try, it might work, I have not tested it.
Bye.