(re-posting, as my original reply hasn't appeared)
Actually, I don't care whether tblAllergenTypes has a numeric ID field or
not. I'm going to proceed based on the idea that AllergenAbb, a text field,
is the primary key of this table.
What I think I would do is use the BeforeUpdate event of the Allergens combo
box on the subform to check in that form's recordset to see if it's okay for
the combo to take on the value that has just been selected. Something like
this:
'------ start of code ------
Private Sub Allergens_BeforeUpdate(Cancel As Integer)
' Are we changing the allergen to "None"?
If Me.Allergens = "None" Then
' Do we have any allergens listed for this profile besides
' the one we're changing?
With Me.RecordsetClone
.FindFirst "Allergens <> 'None' AND Allergens <> '" & _
Me.Allergens.OldValue & "'"
If Not .NoMatch Then
Cancel = True
MsgBox _
"There are existing allergens listed for this " & _
"profile. You must delete those allergens " & _
"before selecting 'None'.", _
vbExclamation, _
"Delete Allergens First"
Me.Allergens.Undo
End If
End With
Else
' We're selecting a real allergen.
' Do we have "None" listed for this profile?
With Me.RecordsetClone
.FindFirst "Allergens = 'None' AND Allergens <> '" & _
Me.Allergens.OldValue & "'"
If Not .NoMatch Then
Cancel = True
MsgBox _
"This profile's allergen list currently specifies " & _
"'None'. You must delete or change that entry " & _
"before selecting an allergen.", _
vbExclamation, _
"Delete 'None' Entry"
Me.Allergens.Undo
End If
End With
End If
End Sub
'------ end of code ------
That ought to work, with minor adjustments, to prevent improper entries.
Note that, because I'm using the subform's RecordsetClone to check for other
allergens, you must set the subform's AllowFilters property to No, so that
the search can't be misled by a filter that might have been applied to that
form.
It would be possible, and reasonable, to relieve the user of the need to
manually delete entries that are no longer wanted. For example, if they've
just selected "None" and there are other existing allergen entries, then one
could pop up a message that asks the user if they want to delete all the
other entries for that profile. If the answer is yes, then save this
record, execute a SQL statement to delete all the other records for that
profile,. and requery the form.
Similarly, if the user has just entered an allergen (not "None"), and there
is another record on the subform that says "None", one could ask the user if
that record should be deleted. If the answer is yes, then save this record,
execute a SQL statement to delete the "None" record, and requery the form.
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
I was asking about the structure of the table that stores the allergen
types, which I gather is called "tblAllergenTypes". From what you posted
below, I gather that it has fields [AllergenAbb] and [txtAllergenName].
Any
others?
Sorry! I misunderstood. No others.
What are the data types of these fields?
Text.
Which field is the
primary key?
AllergenAbb
What are the values of these fields for the record in this
table that represents "None"?
If I'm understanding:
AllergenAbb | txtAllergenName
None |NONE
I'm going to try and guess where you may be going with this, Dirk.
I've added a number field to this table and assigned a numeric value
to all of the allergen types with "0" given to "None":
AllergenID AllergenAbb txtAllergenName
0 None NONE
1 CS CRUSTACEAN SHELLFISH
2 E EGG
3 F FISH
4 M MILK
5 P PEANUTS
6 S SOYBEANS
7 TN TREE NUTS
8 W WHEAT
Perhaps it's better to store the numeric value and then lookup whether
or not a record is <> 0?
