IIF SELECT in Row Source

  • Thread starter Thread starter ridgerunner
  • Start date Start date
R

ridgerunner

I sent a question about this earlier but since it has not shown up, I am
sending a shorter version. I have a field in my database that is a combo box
and the SELECT statement in the row source looks up a value in another table.
Is it possible for the row source to contain a statement that will cause the
lookup to look in another table based on a certain value in another field?

Your help is greatly appreciated,
ridgerunner
 
I now have two tables of similar questions that have the same question ID. I
tried the below statement in the row source of the QstID field but the result
is you only see the Question ID number.

SELECT IIF(tblQuestions.StoreNo=50, tblQuestions.QstID,
tblQuestions.QstWithPtVal FROM tblQuestions ORDER BY tblQuestions.QstSortOrd,
tblCWQuestions.QstID, tblCWQuestions.QstWithPtVal FROM tblCWQuestions ORDER
BY tblCWQuestions.QstSortOrd);
 
If I understand your description correctly, you are using a "lookup field"
in your table. You'll find a lot of (negative) comments in this newsgroup
about using lookup fields. Not only do they cause considerable confusion
(?do you query on the underlying ID value or on the
"displayed-but-not-actually-stored" value?), they encourage you and your
users to work directly in the tables.

Access tables store data (they are "buckets o' data") but are not
particularly capable when it comes to display.

Access forms display data (from queries/from tables), and are exceptional at
helping you manage how users interact with the data.

You can create a combobox in a form that does the same kind of looking up
(for the form) without any (or much) of the confusion from trying to get the
table to do something it isn't designed to do.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Thanks Jeff. This morning I read a lot about the evils of lookups in a
database. I previously decided to use them after reading about them in John
L. Viescas book, "Access 2033 Inside and Out". They worked great up to this
point and I am too deep into my project to change now, but I have had a
painful lesson. I am working around the issue by creating forms and reports
that point to the second table of questions (a few of the questions vary
only by a small amount in the wording for only one store). I could try to
change the combo box in the database but I have many queries, forms and
reports and it isn't worth risking blowing everything up at this point. I
guess famous last words are "be sure everything is the same for all".
ridgerunner
 
I wish you (and whoever "gets" to maintain your database in the future) all
the best ... and if you happen to be the one to return to do some work on it
in 6 months or a year, I hope you remember that "what you see is NOT what
you get" when you work in those tables.

Although it might be a painful lesson, I suspect this falls into the "pay
now or pay later" category...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Back
Top