Conditional Data Entry Requirement

[JeffGeorge]

I have a form whose datasource is a 3 field table. There is one field that is required and is restricted to 3 specific values. This is set up to be the 1st field the user enters data in. I would like the second field to be required ONLY when there is a particular value in field 1. How would I do this in the form?

[pbaldy]

I would use the before update event of the form:

http://www.baldyweb.com/BeforeUpdate.htm

but amending the test to fit your needs.

[JeffGeorge]

Thanks for that reference but the stuff on that page is meaningless to me. I don't know VB so I have no idea what I would change in that to make it work on my form.

Basically, I'm trying to enact

If Field1Entry="XYZ" then Field2Entry is required.

[JeffGeorge]

Is there anyone that give me an idea of how to do this when you don't know VB?

[pbaldy]

The test would look like:

If Me.Field1Entry="XYZ" And Len(Me.Field2Entry & vbNullString) = 0 Then

[JeffGeorge]

Ok. Trying it now. I need to take some introductory VB for Applications or something. I hate not understanding what I'm doing.

[JeffGeorge]

This is the code as it actually appears in my database.

Private Sub BoxNumber_BeforeUpdate(Cancel As Integer)
If Me.Location = "WAREHOUSE" And Len(Me.Field2Entry & vbNullString) = 0 Then
MsgBox "You need to fill out Box Number."
Cancel = True
Me.BoxNumber.SetFocus
End If

However, even if I put WAREHOUSE in the Location field I can pass right through the BoxNumber field without anything stopping me. What did I do incorrectly?

[John_G]

The before update event for a control does not happen if you just pass through it using Tab.

As Paul suggested, you should put your checks (i.e. validation) in the Before Update event of the form, where you can cancel the update (Cancel = True) if any of the validations fail. Cancelling the update does not delete any of the data you have entered into the current record - it just prevents the data from being saved until it is valid.

[JeffGeorge]

So does that mean I need to change the first line of that code? Should it be just

Private Sub BeforeUpdate(Cancel As Integer)

[June7]

Private Sub Form_BeforeUpdate(Cancel As Integer)

[JeffGeorge]

Whoo hoo! It seems to be working! Thanks, all!

[pbaldy]

Glad you got it sorted out.

[JeffGeorge]

Does the "Me" in the code refer to the local object, such as Form in this case?

[June7]

Yes, it is shorthand reference to the object the code is behind.