How do I enter a validation message...

S

Sue

If one particular field in a form is filled out, I want to mandate that a
second field be completed. I want a message to pop up if the user tries to
leave the second field blank. How do I do that?

I appreciate the help!
 
J

John W. Vinson

If one particular field in a form is filled out, I want to mandate that a
second field be completed. I want a message to pop up if the user tries to
leave the second field blank. How do I do that?

I appreciate the help!
Click to expand...

Use the Form's BeforeUpdate event with code like:

Private Sub Form_BeforeUpdate(Cancel as Integer)
Dim iAns As Integer
If Me!ParticularField & "" = "" Then
If Me!SecondField & "" = "" Then
iAns = MsgBox("SecondField must be filled if FirstField is entered" _
& vbCrLf & "Click OK to fill SecondField, Cancel to start over", _
vbOKCancel
Cancel = True ' don't save the record
If iAns = vbCancel Then ' erase the form if user clicked Cancel
Me.Undo
End If
End If
End If
End Sub
 
S

Sue

I got an error message when I tried to enter data. This is the part of the
VBA code that "lit up""

iAns = MsgBox("Patient status must be filled if you have entered a
protocol for the patient" _
& vbCrLf & "Click OK to fill PtStatusID, Cancel to start over", _
vbOKCancel

Here's the full code I entered:

Private Sub Form_BeforeUpdate(Cancel as Integer)
Dim iAns As Integer
If Me!PtID & "" = "" Then
If Me!PtStatusID & "" = "" Then
iAns = MsgBox("Patient status must be filled if you have entered a
protocol for the patient" _
& vbCrLf & "Click OK to fill PtStatusID, Cancel to start over", _
vbOKCancel
Cancel = True ' don't save the record
If iAns = vbCancel Then ' erase the form if user clicked Cancel
Me.Undo
End If
End If
End If
End Sub


What am I doing wrong? The fields are PtID and PtStatusID

Thanks!!!
 
J

John W. Vinson

What am I doing wrong?
Click to expand...

Just copying my erroneous code: left out a closing paren. Sorry! Try

Private Sub Form_BeforeUpdate(Cancel as Integer)
Dim iAns As Integer
If Me!PtID & "" = "" Then
If Me!PtStatusID & "" = "" Then
iAns = MsgBox("Patient status must be filled if you have entered a
protocol for the patient" _
& vbCrLf & "Click OK to fill PtStatusID, Cancel to start over", _
vbOKCancel)
Cancel = True ' don't save the record
If iAns = vbCancel Then ' erase the form if user clicked Cancel
Me.Undo
End If
End If
End If
End Sub


Watch for wordwrap - the newsgroup has the line with iAns = split into two
lines, it needs to be all one line in the code.
 
S

Sue

Nope. Still not working. I get the canned Access message requiring entry of
the 2nd field if the first isn't working if I try to leave the entire row of
information, nothing if I enter a protocol, then try to blow past the field
for the patient's status.

Perhaps a bit more information is needed:

There are 4 underlying tables:
tblPatients - PtID is the primary key
tblProtocol - ProtocolID is the primary key
tblPatientStatus - PtStatusID is the primary key.
tblPtProtocolActivity - PtProtocolActivityID is the primary key, and I've
used PtID, ProtocolID, & PtStatusID as foreign keys (since one protocol may
have many patients & 1 patient may have many protocols).

Relationships are established between tblPtProtocolActivity and the other 3
tables.

The query is based on the 4 tables above.
I look up the fields "LastName" & "FirstName" directly from tblPatients.
I look up the field "ProtocolName" directly from tblProtocol.
I pull PtStatusID down from tblPtProtocolActivity & use a combo box to look
up the value in the query.

If I need to use a combo box to look up the fields from tblPatients, note
that I'm drawing 3 separate fields from that table into the query - LastName,
FirstName, & MRN. I tried to figure out how to pull these into the query
using the foreign key & a combo box but couldn't get that figured out.

Your help and time are much appreciated.
 
J

John W. Vinson

Nope. Still not working. I get the canned Access message requiring entry of
the 2nd field if the first isn't working if I try to leave the entire row of
information, nothing if I enter a protocol, then try to blow past the field
for the patient's status.

Perhaps a bit more information is needed:

There are 4 underlying tables:
tblPatients - PtID is the primary key
tblProtocol - ProtocolID is the primary key
tblPatientStatus - PtStatusID is the primary key.
tblPtProtocolActivity - PtProtocolActivityID is the primary key, and I've
used PtID, ProtocolID, & PtStatusID as foreign keys (since one protocol may
have many patients & 1 patient may have many protocols).

Relationships are established between tblPtProtocolActivity and the other 3
tables.

The query is based on the 4 tables above.
I look up the fields "LastName" & "FirstName" directly from tblPatients.
I look up the field "ProtocolName" directly from tblProtocol.
I pull PtStatusID down from tblPtProtocolActivity & use a combo box to look
up the value in the query.

If I need to use a combo box to look up the fields from tblPatients, note
that I'm drawing 3 separate fields from that table into the query - LastName,
FirstName, & MRN. I tried to figure out how to pull these into the query
using the foreign key & a combo box but couldn't get that figured out.

Your help and time are much appreciated.
Click to expand...

Are you using Lookup Fields in a table or query datasheet? If so, DON'T: see
http://www.mvps.org/access/lookupfields.htm. This feature is very limited in
its capability, and can be very confusing. If you're using a Form, what is the
Form's Recordsource? If it's a query please post the SQL.
 
Top