Check for required field in Before Update event

[Catalina]

I use this code in the Before Update event to check if all required fields have been filled out.

Dim msg As String, Style As Integer, Title As String
Dim nl As String, ctl As Control

nl = vbNewLine & vbNewLine

For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
If ctl.Tag = "*" And Trim(ctl & "") = "" Then
msg = "Data Required for '" & ctl.Name & "' field!" & nl & _
"You can't save this record until this data is provided!" & nl & _
"Enter the data and try again . . . "
Style = vbCritical + vbOKOnly
Title = "Required Data..."
MsgBox msg, Style, Title
ctl.SetFocus
Cancel = True
Exit For
End If
End If
Next

It works well when I try to navigate to another record.
However when I click a custom button to close the from (docmd.close) it closes without firing the code
in the Before Update event. In other words, the form closes even when a required field is empty.

Obviously I'm overlooking something.

Any suggestions will be appreciated.

Gerrit

 

[boblarson]

Are you using the FORM's before update event or the control's? It should be the FORM's event that you are using. That way if any other control has something either entered in it by someone or an edit is made, it dirties the form (provided it is a BOUND form). Then the Before Update event WILL fire whenever you either try to navigate to another record OR even closing the form.

 

[Catalina]

It is in the form's Before Update Event and it is a bound form.
I thought it should work, but somehow it doesn't.

Thanks Bob.

Catalina

 

[boblarson]

It is in the form's Before Update Event and it is a bound form.
I thought it should work, but somehow it doesn't.

Thanks Bob.

Catalina

If no other controls have something put in, then it would not fire. Put a breakpoint on inside this event code and see if it halts when you click the close button. If not then it may be corruption causing your problem and you might try importing everything into a new, blank database file (use the import function, do not drag/drop or copy and paste).

 

[Catalina]

I have tried several things. It is a new database so corruption is unlikely.

Hitting the close button brings up the message box, puts the cursor
in the empty field and then closes the form anyway.

The cancel = true does not work.
However it does work when navigating to another record.

I wonder if I can come up with a workaround.

Catalina

 

[boblarson]

Any chance of seeing the actual database (without sensitive data, of course)?

 

[missinglinq]

However when I click a custom button to close the from (docmd.close) it closes without firing the code

The problem is a long recognized bug in Access. When DoCmd.Close is used to close a form Access dumps the record, without warning, if required fields are not populated.

The solution is to force the save, which will trigger the Form_BeforeUpdate event and the Validation code, before closing the form. On your custom close button, replace

DoCmd.Close

with

If Me.Dirty Then Me.Dirty = False
DoCmd.Close

 

[boblarson]

Hey thanks Linq. I think I missed that somewhere, so chalk up another bit of stuff to remember. I've had issues before like this but I've resorted to doing other work arounds I think. I do appreciate the input.

 

[Catalina]

Great, I'll try that.

Thanks to both of you.

Catalina

 

[missinglinq]

I understand Microsoft finally addressed the problem in 2007, by adding the code I gave when the Button Wizard creates a "close form" button. Only problem is, it created another bug!

Now, in 2007, if you use the Wizard to create a "close form" button and the form is unbound, it pops an error, because an unbound form doesn't have the Dirty property!

 

[Catalina]

That works, thanks.

But now I have another problem.
The code does not work with combo boxes.

I tried:

Dim msg As String, Style As Integer, Title As String
Dim nl As String, ctl As Control

nl = vbNewLine & vbNewLine

For Each ctl In Me.Controls
If ctl.ControlType = acTextBox or acComboBox Then
If ctl.Tag = "*" And Trim(ctl & "") = "" Then
MsgBox "A required field is still empty! ", vbCritical, ""
ctl.SetFocus
ctl.BackColor = 14680063
Cancel = True
Exit For
End If
End If
Next

But it just allows empty comboboxes anyway.

Any idea?

Thanks
Catalina

 

[Catalina]

Never mind, here is the solution:

Dim msg As String, Style As Integer, Title As String
Dim nl As String, ctl As Control

nl = vbNewLine & vbNewLine

For Each ctl In Me.Controls
If ctl.ControlType = acComboBox Or ctl.ControlType = acTextBox Then
If ctl.Tag = "*" And Trim(ctl & "") = "" Then
MsgBox "A required field is still empty! ", vbCritical, ""
ctl.SetFocus
ctl.BackColor = 14680063
Cancel = True
Exit For
End If
End If
Next

 

[ions]

If we set validation rules at the table level for the fields would this take care of the above situation? Thanks

 

[boblarson]

If we set validation rules at the table level for the fields would this take care of the above situation? Thanks

It would keep the record from updating but it is a clunky and not so elegant solution.

 

[boblarson]

Never mind, here is the solution:

Dim
If ctl.ControlType = acComboBox Or ctl.ControlType = acTextBox Then

Always remember that you have to include the comparison part each time.

 

[ions]

Bob,

Just curious why it's not as elegant and why it's clunky? I have been reading about validation rules and was suprised to learn there are validation rules for the table not only just the fields.

I am thinking of really taking advantage of these validation rules for my next project.

Bob, I have one more question. If you set an index that doesn't allow duplicates on a field, can you customize the message that comes up when the user does try to enter a duplicate?

Thank you.

 

[krishnanhemanth]

i used the above code and iam getting this error and this line is high lighted

If ctl.Tag = "*" And Trim(ctl & "") = "" Then

error 13

type mismatch

 

[boblarson]

i used the above code and iam getting this error and this line is high lighted

If ctl.Tag = "*" And Trim(ctl & "") = "" Then

error 13

type mismatch

When using ctl you HAVE to include the .Value part. So change it to:

If ctl.Tag = "*" And Trim(ctl.Value & "") = "" Then

 

[ions]

Bob, could you answer my follow up questions.

I would appreaciate it. Thank you.

********************************************

Bob,

Just curious why it's not as elegant and why it's clunky? I have been reading about validation rules and was suprised to learn there are validation rules for the table not only just the fields.

I am thinking of really taking advantage of these validation rules for my next project.

Bob, I have one more question. If you set an index that doesn't allow duplicates on a field, can you customize the message that comes up when the user does try to enter a duplicate?

Thank you.

 

[boblarson]

Just curious why it's not as elegant and why it's clunky?

basically because while you can have a custom message to display, you have to wait until the record is attempted to save and then it doesn't let you. But, unlike using a BEFORE UPDATE event, which can let you stop the attempt at a save BEFORE it hits the table, it does not let you do things like go back to the offending control, cancel the update all together (for example using a message box to ask the user if they really want to continue or to undo the values). Using the table validation, it would be up to the user to figure out how to go up to EDIT > UNDO CURRENT FIELD/RECORD as they would not be able to get out of it and cancel the record unless they did so or actually completed the record.

Bob, I have one more question. If you set an index that doesn't allow duplicates on a field, can you customize the message that comes up when the user does try to enter a duplicate?

I don't know if you can or not and that again plays into the fact that using a form's Before Update event is MUCH, MUCH more ROBUST than just relying on a table level validation rule.
Thank you.[/QUOTE]