How to force data entry on form without receiving the following popup boxes

[aolough]

Hi, I want to force data entry on one of my combo/text boxes on a form. The following code works however it's messy because after it displays the message "Please Choose Item", the user is presented with two popup boxes to navigate, as shown in the attached images. Is there a way around this? Below is the code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.Combo70) Then
MsgBox "Please Choose Item"
Me.Combo70.SetFocus
Cancel = True
End If
End Sub

.

Thanks in advance

[Rod]

I doubt very much whether your problem is caused by the procedure you have published; I can see nothing wrong. Something else is triggering an attempt to save the record and (I can't enlarge the first picture) it looks like a JET DB error coming to the fore, maybe a missing mandatory value or a referential integrity problem.

[aolough]

Hi Rob,

Thanks for the reply. This is the error message.

"You can't save this record at this time. Microsoft Access may have encountered an error while trying to save a record. If
you close this object now the data changes you made will be lost . Do you want to close the database object anyway?"

Sorry I'm only at this a week or so - I'd be very grateful if you could suggest how I could identify and locate the problem?

Regards,
Áine

[Rod]

Don't apologise, we've all been there, we all started somewhere.

Typically this error occurs for any number of reasons when you close the form. As I said previously, your before update procedure is fine but it contains a Cancel = True assignation. Now when Access tries to close a bound form it checks to see if the form is 'dirty,' specifically if there is something to be stored or updated in the database, and if there is the before update event will fire. If nothing has changed and Combo70 is still null then you are telling Access not to store the changes. Access does not know what to do and asks via this error message.

OK, that I think is what is happening but does not answer why it's happening. Something later in the process is trying to close the form. What you need to do is issue a Me.Undo statement before the close (so there are no outstanding changes) but without seeing more of your project I can't help specifically.

[aolough]

Hi Rob,

I'm clicking the save & close button, so that's when the beforeupdate fires, right? That displays the correct message and then I presume the cancel=true tells access not the save the record. I don't get the Me.Undo part though? I tried adding it in the next line but I'm still getting the same error. I don't want access to do anything until the user addresses the issue and populates the empty field and reclicks save & close.

I'm attaching my file. I appreciate your time so far

Áine

Inventory Management5.zip

[Rod]

I'm clicking the save & close button, so that's when the beforeupdate fires, right?

Right!

I often include a 'Cancel' button for overcoming such situations. My code typically looks as follows, it may work for you.

Private Sub btnCancel_Click
Me.Undo
DoCmd.Close acForm, Me.Name
End Sub

Beware I wrote the foregoing off the top of my head.