If "None" then no others, If others then no "None"

If "None" then no others, If others then no "None"

Post by jlut » Thu, 02 Oct 2008 21:21:25


Two tables:
tblProfiles
PK is ID

tblProfilesAllergens
PK is ID & Allergens

These are linked in a one-to-many. Data is entered into the table via
forms. sfrmProfilesAllergens is opened in datasheet view with a combo
box named Allergens. Many allergens can be selected:
CS - Crustacean Shellfish
E - Egg
F - Fish
M - Milk
P - Peanuts
S - Soybeans
TN - Tree Nuts
W - Wheat

Another selection is "None" which is used when no allergens are
present. The challenge is to assure that if "None" is selected that no
other selections can be made AND if one or many of the above are
selected then "None" may not be selected.

Any ideas on how to do this?

Thanks in advance!
 
 
 

If "None" then no others, If others then no "None"

Post by Dirk Goldg » Thu, 02 Oct 2008 22:51:24


John, this could be done, in a somewhat convoluted manner, but why don't you
just let the absence of any allergen records represent the "None" condition?
If it's a matter of how things come out on a report, it's usually easy to
jigger the report to show "None" when there are no matching sub-records.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 
 
 

If "None" then no others, If others then no "None"

Post by jlut » Thu, 02 Oct 2008 23:18:30

Hi, Dirk!


That's been considered however an absent record could ultimately be
interpreted:
A. There's no allergens.
B. There is an allergen(s) but it's not been recorded.

Recording "None" clarifies both A & B and provides a warm, snuggly
feeling by removing all doubt.


I couldn't find "jigger" in the help file. Is this a technical term? :)
 
 
 

If "None" then no others, If others then no "None"

Post by Dirk Goldg » Fri, 03 Oct 2008 00:35:57


So the problem is a failure of trust in data entry. Well, if that's what
you're stuck with, let's see what you need to do.

Presumably, you have a table of Allergens. Can you add a record to this
table for the pseudo-allergen "None"? For now, I'll assume that you can.
Thus, your combo box can have its rowsource set to a query of this table.
What's the structure of this table? We need to know what the PK value is,
of the record that represents "None".

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
 
 
 

If "None" then no others, If others then no "None"

Post by jlut » Fri, 03 Oct 2008 01:07:50

> So the problem is a failure of trust in data entry.

Yup. As Billy Joel pointed out - it's a matter of trust.

Actually, it goes deeper than that. In the food industry any confusion
regarding an allergen is entirely unacceptable and could lead to the
death of a consumer. We want to remove all doubt. There's either an
allergen or there isn't. Claiming "None" removes any doubt.


All of the above are correct assumptions except for the combo box's
rowsource:
SELECT tblAllergenTypes.AllergenAbb, tblAllergenTypes.txtAllergenName
FROM tblAllergenTypes ORDER BY tblAllergenTypes.AllergenAbb;


tblProfilesAllergens has three fields:
ID |Allergens |Comments
12345 |F |Anchovy
12345 |S |
12345 |W |
12367 |None |
12378 |CS |
12488 |E |
12488 |M |
12488 |TN |Walnut

Hope that illustrates. ID and Allergens are in a multiple field index
linked to the parent tblProfiles.ID
 
 
 

If "None" then no others, If others then no "None"

Post by Dirk Goldg » Fri, 03 Oct 2008 02:29:02

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? What are the data types of these fields? Which field is the
primary key? What are the values of these fields for the record in this
table that represents "None"?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)





Yup. As Billy Joel pointed out - it's a matter of trust.

Actually, it goes deeper than that. In the food industry any confusion
regarding an allergen is entirely unacceptable and could lead to the
death of a consumer. We want to remove all doubt. There's either an
allergen or there isn't. Claiming "None" removes any doubt.


All of the above are correct assumptions except for the combo box's
rowsource:
SELECT tblAllergenTypes.AllergenAbb, tblAllergenTypes.txtAllergenName
FROM tblAllergenTypes ORDER BY tblAllergenTypes.AllergenAbb;


tblProfilesAllergens has three fields:
ID |Allergens |Comments
12345 |F |Anchovy
12345 |S |
12345 |W |
12367 |None |
12378 |CS |
12488 |E |
12488 |M |
12488 |TN |Walnut

Hope that illustrates. ID and Allergens are in a multiple field index
linked to the parent tblProfiles.ID
 
 
 

If "None" then no others, If others then no "None"

Post by jlut » Fri, 03 Oct 2008 06:21:36

> I was asking about the structure of the table that stores the allergen

Sorry! I misunderstood. No others.


Text.


AllergenAbb


If I'm understanding:
AllergenAbb | txtAllergenName
None |NONE
 
 
 

If "None" then no others, If others then no "None"

Post by jlut » Fri, 03 Oct 2008 21:53:24


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?
 
 
 

If "None" then no others, If others then no "None"

Post by Dirk Goldg » Sat, 04 Oct 2008 09:07:30

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)


< XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...
On Oct 1, 5:21 pm, XXXX@XXXXX.COM wrote:

I'm g
 
 
 

If "None" then no others, If others then no "None"

Post by jlut » Sun, 05 Oct 2008 01:45:15

n Oct 2, 8:07爌m, "Dirk Goldgar"
< XXXX@XXXXX.COM > wrote:

Hate when that happens!


Serves me right for presuming to know what might be rattling about
inside your high octane brain!


I was thinking along a BeforeUpdate event, too but had no idea how as
RecordsetClone was entirely foreign to me. Now I'm thinking of several
other places/ways I can use it!

Your code is flawless. I didn't have to tweak anything other than
control names (of course) and clarified the msgboxes:
Private Sub cbAllergen_BeforeUpdate(Cancel As Integer)
' Are we changing the allergen to "None"?

If Me.cbAllergen = "None" Then
' Do we have any allergens listed for this profile besides
' the one we're changing?
With Me.RecordsetClone
.FindFirst "ProfilesAllergens <> 'None' AND
ProfilesAllergens <> '" & _
Me.cbAllergen.OldValue & "'"
If Not .NoMatch Then
Cancel = True
MsgBox _
"An allergen(s) is currently listed! " & _
"All listed allergens MUST be deleted " & _
"before selecting 'None'.", _
vbExclamation, _
"Delete Listed Allergens First"
Me.cbAllergen.Undo
End If
End With

Else
' We're selecting a real allergen.
' Do we have "None" listed for this profile?
With Me.RecordsetClone
.FindFirst "ProfilesAllergens = 'None' AND
ProfilesAllergens <> '" & _
Me.cbAllergen.OldValue & "'"
If Not .NoMatch Then
Cancel = True
MsgBox _
"The current allergen list specifies " & _
"'None'. This MUST be deleted or changed " & _
"before selecting an allergen.", _
vbExclamation, _
"Delete 'None' Entry"
Me.cbAllergen.Undo
End If
End With

End If

End Sub


Definitely reasonable however I think I'll "KISS" it (Keep It Sweet &
Simple) for now! This is working very well as is.

As always, thanks for opening up yet another door!!!
 
 
 

If "None" then no others, If others then no "None"

Post by Dirk Goldg » Sun, 05 Oct 2008 02:57:40


On Oct 2, 8:07 pm, "Dirk Goldgar"



Just remember that you need to set the subform's AllowFilters property to
No, so that you can be sure you're working with *all* the records that are
related to the main form's record. It seems to me unlikely, in this
particular case, that the user would have filtered the subform so as not to
list all allergens, but we don't want to take any chances.

Note that you could have used a suitably constructed DLookup to check for
records, instead of the RecordsetClone, but I figure you've already got the
set of records loaded up right there -- why not use it?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
 
 
 

If "None" then no others, If others then no "None"

Post by jlut » Sun, 05 Oct 2008 06:47:42

> Just remember that you need to set the subform's AllowFilters property to

I was clear on that and immediately set it to No. I may go back and
set it to Yes just to see what happens. You're right about a user
filtering.


I use DLookup BUT it can be painfully slow and I would've resisted it
had anyone suggested it.

Thanks, again for the tip about the RecordsetClone!

BTW I held a database review today with some people who are going to
cover for me when I go on vacation this year and they were blown away
by my design and what Access can do. I'm hoping that other people will
begin using it as this will help push the ASP.net conversion project
that I'd like for you and I to team up on!