Lookup query with table value as criteria (1 Viewer)

[dbaldacchino]

I set up a query to be used as the Row Source for a table lookup field. I'm attaching a simplified file to make it easier to understand (saved to 2003 but I'm using 2007).

I have the following tables:

t_Discipline with fields ID, Discipline, Network_Path (this field is irrelevant for this example)

t_Details with fields ID, Discipline, Detail_Type

t_Subfolders with fields ID, Discipline, Detail_Type, Subfolder (this field is irrelevant for this example)

The ID of t_Discipline is joined to the Discipline field of t_Details and t_Subfolders (one to many). Both Discipline fields in these tables use t_Discipline as the lookup table.

If you run the query called "Lookup Query", you are prompted to enter the parameter value "t_details.discipline", which is what I set as criteria. If you type 1 for example, you'll get 4 rows that correspond to that value (the Detail_Type will show Type 1 through Type 4). This seems to be working fine, however I would like this query to automatically list the appropriate records when used as a lookup query, based on the value of t_details.discipline for the current record. But this isn't happening and the dialog mentioned above pops up asking for the parameter value.

How can I get this to work so that the user is presented with the relevant data based on the record value of the field Discipline (in t_Details)? If for example the user clicks in the field "Detail_Type" in t.Details for the first record (Discipline set to Disc1), then the presented values should automatically be Type 1, Type 2, Type 3 and Type 4. If the user clicks in the second record where Discipline is set to Disc2, the values presented should be Type A, Type B and Type C. Any help is greatly appreciated and I hope I explained myself properly. Thanks in advance!

 

[Starman]

You really shouldn't be using lookups in a table. It sounds like you could get the results you are looking for by using a combo box on a form to choose the criteria which could then be displayed in a list box.

 

[boblarson]

Do NOT use lookups at TABLE level. See here for more about why:
http://www.mvps.org/access/lookupfields.htm

You should be using FORMS for data input and using combo boxes (or list boxes) on them for lookups. Users should not be entering data directly in tables or queries. With forms you have much more control over things than if you do not use them.

 

[dbaldacchino]

Thanks for the advice guys. Not sure I understand why it's better to not use lookups in tables since it is an existing feature, but I know you know a LOT more than me! I assume it's a best-practices issue.

I'll abandon the idea of inputting data straight into tables and start developing forms. Users would never interact with the forms, only I will. So I assume that if I use that same lookup query in a combobox/listbox control, it'll work correctly as I was expecting it to work in the table, based on the field "Detail_Type" of the current record?

 

[georgedwilkinson]

If you base a form off the table, it will inherit the columns control information, including the combo-box properties (data source, etc.).

I had wondered about not making changes directly to the table if the functionality was there for lookups at the table design level. My ultimate answer was what I said in the first paragraph. If you are basing many forms off of a table, those other objects will inherit what you set at the table level.

 

[boblarson]

Not sure I understand why it's better to not use lookups in tables since it is an existing feature

Did you read the article at the link I provided. There are plenty of reasons given there. Just because a feature is in a program doesn't mean that it is necessarily good. Developers have been fighting for years with Microsoft on that one because the lookups at table level tend to cause more problems than they solve.

 

[dbaldacchino]

Thanks all for your guidance. Yes Bob I did read it, but some of that is a bit over my head at this point But I get it...I took all the lookups in tables out and will start building forms. After some more thoughts, I can still get the flexibilities I need due to future changes and assemble the information together by using Queries. Please bear with me as I'm learning and thanks again!

 

[Pat Hartman]

While you're in the change mode, change your column names. I presume that the ID fields are autonumbers added by Access. You should rename those and use the PK names when you need an FK. It makes the relationships between tables more obvious. It looks from your example as though you are relating FK to data field rather than FK to PK and that will prevent you from enforcing RI and when joined will result in non-updateable queries since the relationship will be indeterminate.

t_Discipline with fields DisciplineID, Network_Path (this field is irrelevant for this example)

t_Details with fields DetailsID, DisciplineID, Detail_Type

t_Subfolders with fields SubFolderID, DetailsID, Subfolder

This implies a hierarchial relationship: Discipline --> Details --> Subfolders.
If the relationship is Discipline --> Details and Discipline --> Subfolders, then change the DetailsID in t_Subfolders to DisciplineID so that it points to the correct table.

 

[dbaldacchino]

Thanks, finished making those changes per your suggestion.

I started playing around with forms and I tried using Split Forms. This way one can choose to use the "form" or "datasheet" view for input.

I created a query "Q_Detail_Type" and assigned the criteria to be based on the value of ComboBox "Discipline" in form "f_Details". I used this query as the Row Source of ComboBox "Detail_Type". To make it update, it seems that you have to add VBA code to re-query when "Discipline" is changed. So I added "Me.Detail_Type.Requery" to the ComboBox's After Update and the form's On Current events. Everything seems to work fine, except that since the column in the datasheet for "Detail_Type" is being requeried, the data is disappearing from the datasheet rows having a different discipline than the current record's. Can this be solved? The problem seems to be only with the datasheet view on the form; the entered data is not changing, which is good.

I'm attaching a test file. Thanks again for your help!

 

[dbaldacchino]

I found the mistake (although I still don't know what was causing the disappearing data). I realized that the incorrect data was being stored in the table so I changed the Bound Column for "Detail_Type" from 1 to 2, which is the correct column (column 1 in the query is the ID). Now it's working like a charm. If you spot any incorrect stuff, please let me know. I'm attaching an updated file.

 

[homeschoolmom2009]

In researching why my query criteria based on a combobox lookup in my table wasn't working, I came across this forum. After reading the article posted above, I get that I shouldn't have set the combo box in the table and why. My question is this:

I have one table (Volunteers 2013) that has fields labeled "Volunteer Area of Interest 1" and "Volunteer Area of Interest 2" (volunteers get to pick their two choices of where to serve). These are the fields I had originally set as look up fields...which look to a second table "Volunteer Areas". So, if I want the data selected in the form (combo box list) to be saved in the Volunteers 2013 table in the fields mentioned above...what should I be setting those as for a datatype...just text with no lookup? Keep it as number and set the relationship to the Volunteer Areas table?

Please be specific...even though I can muddle my way through pretty well...I like to have explicit detailed steps.

Thanks so much!!!!

 

[jdraw]

I'm not sure I'm understanding your set up. As has been said in this thread you should avoid using lookups at the table field level.

It sounds that you may have some tables missing from your database, but that may not be true.
Can you tell us more about WHAT your database is about?
Here is a link to a procedure for designing a database. It works from facts/rules and creates tables and relationships.
I'm sure you will learn from working through the example.
http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip

 

[boblarson]

In researching why my query criteria based on a combobox lookup in my table wasn't working, I came across this forum. After reading the article posted above, I get that I shouldn't have set the combo box in the table and why. My question is this:

I have one table (Volunteers 2013) that has fields labeled "Volunteer Area of Interest 1" and "Volunteer Area of Interest 2" (volunteers get to pick their two choices of where to serve). These are the fields I had originally set as look up fields...which look to a second table "Volunteer Areas". So, if I want the data selected in the form (combo box list) to be saved in the Volunteers 2013 table in the fields mentioned above...what should I be setting those as for a datatype...just text with no lookup? Keep it as number and set the relationship to the Volunteer Areas table?

Please be specific...even though I can muddle my way through pretty well...I like to have explicit detailed steps.

Thanks so much!!!!

You would store the ID for the volunteer area and you use the lookup as a combo box on the data entry FORM and not directly at table level.