Lookup help for pull-downs

[Guest]

I'm working on a Q&A Database, where each QuesID has a different set of
possible Answers. I have not been able to figure out how to get my answer
field's pull-down to change based on the QuesID of the current record. The
code I tried is:
SELECT tblPossibleAnswers.Choice FROM tblPossibleAnswers WHERE
tblFinalAnswer.QuesID=tblPossibleAnswers.QuesID but when I'm in the answer
table or form, it prompts me to enter the QuesID instead of using the data
for the current record QuesID and then it uses that same value for the answer
in all remaining records.

 

[Guest]

You can do it in the form, but not in raw datasheet view of the table. But
as data should only ever be entered via forms that's not relevant. The
RowSource of the Answers combo box should reference the Questions combo box,
e.g.

SELECT Choice
FROM tblPossibleAnswers
WHERE QuesID = Forms!YourForm!cboQuestions;

where YourForm is the name of the form and cboQuestions is a combo box of
questions bound to the QuesID column of the form's underlying table. In the
AfterUpdate event procedure of cboQuestions *and* in the form's Current event
procedure requery the second combo box:

Me.cboAnswers.Requery

Note that this only works in single form view. If you do it in continuous
form view then when you select a new question in another record the answers
combo box for all other records where the answer is not among the possible
answers of the current record will go blank. Their values will still be the
same, you simply won't see them. It can be done in continuous form view, but
you have to use a hybrid control made up of a combo box with a text box
superimposed on it so it looks like a single control to the user.

You can download a demo of using correlated combo boxes, albeit in a
different context (it uses my local administrative areas) at:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23626&webtag=ws-msdevapps


Ken Sheridan
Stafford, England

 

[Ron2006]

The other way for it to work is if you break one of the normalization
rules and store the question itself instead of the questionID and
obviously use the data and not the ids in the further combos.

Ron

 

[Guest]

Ron:

I don't follow that I'm afraid. Can you elaborate?

Ken Sheridan
Stafford, England

 

[Ron2006]

Ron:

I don't follow that I'm afraid. Can you elaborate?

Ken Sheridan
Stafford, England






- Show quoted text -

Ken,

I was just commenting on the cascading / correlated combo boxes. As
you brought out, if you use IDs for the bound field for cascading
comboboxes and display the description that corresponds to the id in
the successive combo boxes, there is a problem in datasheet view at
the least. If you do store the IDs then to show the values you have to
play around with overlaying txtboxes etc to have everything look ok in
the datasheet/multi form view.

However if you break the normalization rule and actually store the
description/value instead of the IDs in the bound field, then the
problem of NOT seening the proper description in datasheet view is
eliminated. Everything shows and all of the successive combo boxes
show their proper description for all of the records in the datasheet
view. And you don't have to play around with overlaying txtboxes.

Ron

 

[Guest]

Ron:

Yes, that will work, but its not a normalization issue, its using a
'natural' key rather than a 'surrogate' key. Take the example of US States,
each of which has a unique name so you can have a column State of text data
type as the primary key of a table States with values AK, AL, AR etc. In a
Cities table you can then have a State foreign key table State of text data
type with exactly the same values. These are 'natural' keys rather than
'surrogate' keys as would be the case with arbitrary numeric StateID values.
Using the natural keys does not put the table in any less normal form than
using the surrogate keys and is perfectly valid. In situations like this I
favour them myself.

The use of natural keys does depend on the values being unique of course, so
there is no way that City could be the primary key of a Cities table as city
names are duplicated. Consequently you cannot have a City foreign key column
referencing the key of Cities, so your solution would not be possible in a
form bound to a table referencing cities; there would be no way of knowing
whether a row in the table is referencing Newark, California or Newark, New
Jersey for example ( I think there's one in Delaware too, and possibly
others). The only way you could apply your solution in that context would be
if a composite key were used, which might in fact be an appropriate solution
in the OP's case.

Ken Sheridan
Stafford, England

 

[Ron2006]

Thanks for the insight and clarification, Ken.

I think that we sometimes become so focused on the ID concept we (or
maybe it is just me) forget to look at what we are really trying to
keep track of and miss the boat and add complexity where none is
really needed.

Theoretically we in the States could use ZipCode for the Cities,
although I believe there are some areas where it doesn't quite work.
(Which means it really doesn't work.)

Have a great day, although, I think it is the middle of the night for
you right now.

Ron

 

[Guest]

Ron:

Postal codes work pretty well here for storing addresses as they represent a
very specific area. Mine for instance represents one side of my street, just
36 houses. Many databases consequently hold just the post code and house
number or building name as an address. UK web sites often require just those
to be entered and the rest of the address is automatically filled in as, for
example, the delivery address for goods.

They don't relate to larger areas all that well though. For instance the
city of Lichfield (actually quite a small town, but a city by virtue of an
ancient charter), which is, like here, in the county of Staffordshire has WA
as the first part of its post codes, which actually stands for Walsall, which
is another city to the south of it, but not even in Staffordshire. This is
because any post sent to Lichfield goes to Walsall for sorting. So there is
no correlation between the larger postal area and the administrative areas
normally used for other purposes. To identify a town from its post code
you'd need to use several; Stafford where I live for instance has codes
beginning ST16 and ST17, the ST standing not for Stafford, but for
Stoke-on-Trent, the only city of any size in the present county.

Ken Sheridan
Stafford, England