Multiple If Then statements

[uroron via AccessMonster.com]

I am using Access 2007
I have a Main form and a subform.
The main form has 10 text boxes where allergies can be entered. all1, all2
to all10.
The subform has 9 text boxes where medicines can be entered. med1, med2,
to med9
I want a msgbox to alert one if one enters a medicine that a person is
allergic to.

I have tried without success codes similar to:
If Forms![Main].[all1] or [all2] or [all3-9] = Me.[med1] or [med2] or [med3-9]
Then
Msgbox = "Caution"
End If

Select Case doesn't seem to help.
Thank you in advance
Ron

 

[wphx]

Could you be more clear as to how the records are structured, and their
relationship to the subform?

off the top of my head, I would think about restructuring the problem. There
are too many variable factors when you have to explicitly match 10 items
against 10 others.

Maybe using a relational approach for for the mecations and the allergies
(i.e. 1 person record - n allergy records (1 allergy /record) and n
meication records (1 medication/record).

You may then be able to write some queries which match the allergy records
against the medication records and highlight the cautons depending on how
the records are connected?

 

[John W. Vinson]

I am using Access 2007
I have a Main form and a subform.
The main form has 10 text boxes where allergies can be entered. all1, all2
to all10.
The subform has 9 text boxes where medicines can be entered. med1, med2,
to med9
I want a msgbox to alert one if one enters a medicine that a person is
allergic to.

I have tried without success codes similar to:
If Forms![Main].[all1] or [all2] or [all3-9] = Me.[med1] or [med2] or [med3-9]
Then
Msgbox = "Caution"
End If

Select Case doesn't seem to help.
Thank you in advance
Ron

It sounds like you're making a very common beginner's mistake: going to Forms
design too early.

Date is NOT stored in forms. It's stored in Tables. A Form is *just a window*,
a tool to manage data stored in tables. Where in your tables is this
information stored?

My guess is that you modeled your tables after your forms (which were probably
in turn modeled after paper forms). This pretty much guarantees a flawed,
non-normalized design!

If you have ten Allergies fields... your table IS WRONG.
If a patient is limited to nine medicines because you have nine Meds fields...
Your table is WRONG.

You need two many to many relationships here, using *five* tables, not one:

Patients
PatientID
LastName
FirstName
<other appropriate biographical data>

Note: *nothing* in this table about either meds or allergies!!!

Allergies
AllergyID <perhaps a standard medical industry code>
Allergen <e.g. "cephalosporins">

PatientAllergies <a table linking Patients to their Allergies>
PatientID <link to Patients, who is allergic...>
AllergyID <link to Allergies, what they're allergic to>
<other fields about this patient's allergy to this agent, e.g. severity,
date of onset, etc.>

Medications
MedID <Merck Index or insurance company unique ID>
MedicationName <other general info about this medication>

PatientMeds
PatientID <who's taking it>
MedID <what they're taking>

You will surely need other tables as well... but I'll be surprised if any of
them have more than eight or ten fields.

Do note that any database containing identifiable patient information is
subject to the very stringent HIPAA patient-privacy regulations. You can get
in *real* trouble if you're not careful with these. Some would argue that
ACCESS is incapable of providing the security required under the law.

John W. Vinson [MVP]

 

[uroron via AccessMonster.com]

Thank you very much for your well thought and cogent response.
I apologize for the senario that I presented. Actually, the database that I
have constructed has many tables and relationships and is very similiar
layout to that which you describe.
I presented the simplistic (and ridiculous - in a design sense ) senario more
as a programming question than a design question.
In the tables which you describe, the question would be:
How do you have a message box Caution pop up when an entry in the 'MedID"
table for a particular patient is the same as one listed in his "AllergyID"
table?

Thank you very much
Ron

I am using Access 2007
I have a Main form and a subform.

[quoted text clipped - 14 lines]

Thank you in advance
Ron

It sounds like you're making a very common beginner's mistake: going to Forms
design too early.

Date is NOT stored in forms. It's stored in Tables. A Form is *just a window*,
a tool to manage data stored in tables. Where in your tables is this
information stored?

My guess is that you modeled your tables after your forms (which were probably
in turn modeled after paper forms). This pretty much guarantees a flawed,
non-normalized design!

If you have ten Allergies fields... your table IS WRONG.
If a patient is limited to nine medicines because you have nine Meds fields...
Your table is WRONG.

You need two many to many relationships here, using *five* tables, not one:

Patients
PatientID
LastName
FirstName
<other appropriate biographical data>

Note: *nothing* in this table about either meds or allergies!!!

Allergies
AllergyID <perhaps a standard medical industry code>
Allergen <e.g. "cephalosporins">

PatientAllergies <a table linking Patients to their Allergies>
PatientID <link to Patients, who is allergic...>
AllergyID <link to Allergies, what they're allergic to>
<other fields about this patient's allergy to this agent, e.g. severity,
date of onset, etc.>

Medications
MedID <Merck Index or insurance company unique ID>
MedicationName <other general info about this medication>

PatientMeds
PatientID <who's taking it>
MedID <what they're taking>

You will surely need other tables as well... but I'll be surprised if any of
them have more than eight or ten fields.

Do note that any database containing identifiable patient information is
subject to the very stringent HIPAA patient-privacy regulations. You can get
in *real* trouble if you're not careful with these. Some would argue that
ACCESS is incapable of providing the security required under the law.

John W. Vinson [MVP]

 

[wphx]

I'd try and do an sql statement, which matches he medication against the
allergy. if it returns more than one record you know you've got a 'hit'

That way you don't have to hassle over all the permutations of the data to
match & test

Thank you very much for your well thought and cogent response.
I apologize for the senario that I presented. Actually, the database that
I
have constructed has many tables and relationships and is very similiar
layout to that which you describe.
I presented the simplistic (and ridiculous - in a design sense ) senario
more
as a programming question than a design question.
In the tables which you describe, the question would be:
How do you have a message box Caution pop up when an entry in the 'MedID"
table for a particular patient is the same as one listed in his
"AllergyID"
table?

Thank you very much
Ron

I am using Access 2007
I have a Main form and a subform.

[quoted text clipped - 14 lines]

Thank you in advance
Ron

It sounds like you're making a very common beginner's mistake: going to
Forms
design too early.

Date is NOT stored in forms. It's stored in Tables. A Form is *just a
window*,
a tool to manage data stored in tables. Where in your tables is this
information stored?

My guess is that you modeled your tables after your forms (which were
probably
in turn modeled after paper forms). This pretty much guarantees a flawed,
non-normalized design!

If you have ten Allergies fields... your table IS WRONG.
If a patient is limited to nine medicines because you have nine Meds
fields...
Your table is WRONG.

You need two many to many relationships here, using *five* tables, not
one:

Patients
PatientID
LastName
FirstName
<other appropriate biographical data>

Note: *nothing* in this table about either meds or allergies!!!

Allergies
AllergyID <perhaps a standard medical industry code>
Allergen <e.g. "cephalosporins">

PatientAllergies <a table linking Patients to their Allergies>
PatientID <link to Patients, who is allergic...>
AllergyID <link to Allergies, what they're allergic to>
<other fields about this patient's allergy to this agent, e.g.
severity,
date of onset, etc.>

Medications
MedID <Merck Index or insurance company unique ID>
MedicationName <other general info about this medication>

PatientMeds
PatientID <who's taking it>
MedID <what they're taking>

You will surely need other tables as well... but I'll be surprised if any
of
them have more than eight or ten fields.

Do note that any database containing identifiable patient information is
subject to the very stringent HIPAA patient-privacy regulations. You can
get
in *real* trouble if you're not careful with these. Some would argue that
ACCESS is incapable of providing the security required under the law.

John W. Vinson [MVP]

 

[John W. Vinson]

How do you have a message box Caution pop up when an entry in the 'MedID"
table for a particular patient is the same as one listed in his "AllergyID"
table?

Use the BeforeUpdate (if you want to be able to cancel the med) or AfterUpdate
event of the control (presumably a combo box) bound to the MedID field; you
can use DLookUp or open a recordset based on the allergy table. Since your
posted table structure is (I hope!!!) not the actual normalized structure I
hesitate to post exact sample code, but it would resemble

Private Sub cboMeds_BeforeUpdate(Cancel as Integer)
Dim iAns As Integer
If Not IsNull(DLookUp("MedID", "[PatientAllergies]", _
"[MedID] = " & Me!cboMeds & " AND [PatientID] = " & Me!PatientID) Then
iAns=MsgBox "This patient may be allergic to this medication.", vbOKCancel
Cancel = (iAns = vbCancel)
End If
End Sub

John W. Vinson [MVP]