Before Update VBA code not working

Question

Before Update VBA code not working

Robert Walberg 5

I have a combo box (PublicationCombo) in a subform that requires an entry before the record can be saved. I have the "Required" property in the corresponding table set to "Yes". The following VBA code is in the subform's property:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(PublicationCombo) Then

    Cancel = True

    MsgBox "Select a value from the dropdown menu."

    Me.PublicationCombo.SetFocus

End If

End Sub

From everything I've read and viewed, this should work; but it doesn't. I'm stumped as to why it doesn't work. Any ideas?

BTW, without the above code Access displays an error message so the record can't be saved, but the message is not in plain language. That's why I want to insert the message box and keep the focus on the field in question.

Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Answer recommended by moderator

6 additional answers

Your answer

Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Answer 1

Ken & everyone else who contributed

Happy to say I have solved the problem. The key was me studying and understanding what Ken said about Access setting a default value of zero in the bound table. Once I deleted the Default Value of "0" for all combo box fields (there are 3) and saved the changes, I was able to use the Before Update event as intended in the first place without using the On Error event. Not only that, but I was able to use IF/ElseIF to handle all three combo boxes in the subform. Now the user cannot leave the record without filling in the required fields. Setting the focus and displaying the dropdown menu should make the data entry idiot proof.

BTW, deleting the Before Update event and inserting Ken's code in the On Error event didn't make a difference. Probably because the bound table default value = 0.

Thank you to all of you. It wasn't a straight line, but I got there and learned a few things in the process.

Answer 2

It looks like your VBA code for the BeforeUpdate event should work as intended, but there are a few things you might want to check:

Combo Box Name: Ensure that the name of the combo box in your form is exactly PublicationCombo. If there's a typo or if the name is different, the code won't recognize the control.
Event Trigger: Make sure that the BeforeUpdate event is properly linked to the form. Sometimes, if the event is not set correctly, the code won't execute.
Control Bound to a Field: Verify that the PublicationCombo is bound to a field in your table. If it's unbound, the IsNull check may not work as expected.
Required Property: While you have set the Required property to Yes, ensure that this property is set correctly in the table design and that it is not being overridden by any other settings.
Error Handling: If there are any other error handling routines in your form that might be interfering with the BeforeUpdate event, they could prevent your message box from displaying.
Debugging: You can add a simple Debug.Print statement before the If condition to see if the code is being executed when you attempt to save the record. This can help you determine if the issue lies within the event firing.

If you check these aspects and the code still does not work, consider testing in a new form to isolate the issue. Sometimes, forms can have hidden properties or settings that affect behavior.

Robert Walberg 5 Reputation points

2025-11-12T20:42:18.68+00:00

Combo box name is correct.

Event trigger. I'm not sure what this means but this is my process: (1) open the subform, (2) click on the form top left to open the Form Property Sheet, (3) select Event>Before Update, (3) open code builder, (4) write the code, (5) save, and (6) enter test data in a new record. I assume this is what is meant by event trigger.

Control bound to a field. I'm wondering if this could be the source of the problem. The PublicationT (table) has two fields: (1) PublicationID-an autonumber field, and (2) Publication-short text. The control source is the PublicationID field. That said, Publication field is not on the list of control sources, only PublicationID.

Required property. In the PublicationT (table) PublicationID does not offer the option of required because it is an autonumber field type. Publication field is set to Required = Yes. Changing it to "No" doesn't make a difference.

Error handling. There is an error handling command in the Save Record VBA code. The record is locked to prevent accidental changes and can be unlocked by clicking on the Edit button. I neutralized this command, but it didn't make a difference.

Debugging. I inserted "Debug.Print" before the If condition. The only difference was how the Access message is presented. With the debug.print command in place, I get "Run-Time Error 3201" followed by the same text as in the screen shot.

Answer 3

Duane Hookom 26,545 Volunteer Moderator

It might be easier to change the Form’s On Error message from the default to plain language. Sorry I’m not able to test this at the moment.

Robert Walberg 5 Reputation points

2025-11-12T20:57:05.0333333+00:00

Duane,

Ken agrees with you. See my comments to his post. I will ask you the same question: how do I access the Form's On Error message to change the language?

Answer 4

George Hepworth 22,215 Volunteer Moderator

What, exactly is that cryptic error? Show us a screenshot of the actual error message, please.

Robert Walberg 5 Reputation points

2025-11-12T19:53:26.8866667+00:00

George

This is the message:

It appears when the user tries to exit/save the record.

A user not familiar with the developent behind the database wouldn't know that the Publication field requires an answer. I want the message to be clear and simple: "Select an option from the Publication dropdown menu." Furthermore, to be clear which field is in question, I want the focus to return to the Publication field.

Answer 5

Karl Donaubauer 2,691 MVP

Hi,

Your code doesn't look wrong.

this should work; but it doesn't.

Can you explain that in more detail? What exactly are you doing to test this and what happens?

Servus
Karl

Access Forever News DevCon
Access-Entwickler-Konferenz AEK

Robert Walberg 5 Reputation points

2025-11-12T20:08:22.4366667+00:00

Karl,

The user enters data into the subform. If s/he leaves the Publication field blank, when he ties to exit/save the record, the following message appears:

If the user isn't familiar with the development of the database, this message doesn't help the user fix the problem and cannot continue until the Publication field is populated. Note that the Access message refers to the PublicationT (table) not the Publication field. The user wouldn't understand what PublicationT is. I want the message (this one or one I write) to read "Select an option from the Publication dropdown menu." "Publication" is the label and appears on the form next to the open field. Furthermore, to assist the user I want the focus to return to the Publication field.

Regarding testing, I save the code then try entering data leaving the Publication field blank. I get the same message. What I expect to happen is after leaving the Publication field or trying to save/exit the record my message should appear, but it doesn't.

Share via

Before Update VBA code not working

6 additional answers

Your answer