Empty Lookup

[RuralGuy]

Have you set up the relationship of the Gage and Procedure tables yet?

[mikel]

Yes, I have a 1:M relationship between the Procedure table (one side) and the Gage table (many side). In the Gage table I have a field called Procedure which is the foreign key of a field called Procedure_ID in the Procedure table. Is that right?

Mike

[RuralGuy]

If you have a query of the Procedure table then add the Gage table, it will show the link and you can add any of the fields from the Gage table you want. It will be as if they are all in one table. If your form uses a ComboBox for the Procedure_ID field that has a ControlSource of the Procedure_ID field that looks up values in the Gage table, then whenever you change the ComboBox all of the controls that display the Gage table fields will change.

[mikel]

Ok, I see the link in the Query design view now. And I can select whatever fields I want from either table. But which fields do I select? All of them? Would you use this query for the entire form, or just the part where I need to see the details of the selected procedure?

I wasn't using a combo box for the list. I'll give that a try and see if I can get it to work. All of the other fields that I have get pushed back through the query to the table now so that's good.

When I change the combo box I only want the procedure name and details to change. Is that the way this will work?

[RuralGuy]

Yes you use the query for the entire form. Just select a couple of fields for now so you can see what is happening and add controls to display them on your form.

[mikel]

I was able to get it to work on my test file, but now as I am working on my good file, I can't duplicate it. It's driving me nuts. I only have 2 tables, the gage and manufacturer tables and 1 query that joins the 2 but when I run the query nothing is listed and it seems like the connection just isn't there. In my sample file when I have the query open it lists all the data that is in the 2 tables and I can click on the cells with the drop down boxes (procedures or manufacturer) and make changes. But on my good file I don't even get drop down boxes.

I guess I'm not that clear on why lookup fields in tables are bad and I'm not sure what method to use to get around them.

[RuralGuy]

Having Lookup Fields in tables hides what is really in the table from the developer as well as the user. As a developer, you want to know what is actually in your tables. You should *not* see any drop down lists when you are looking at a query in datasheet view. That would mean you still have Lookup Fields in the table. Your Lookup ComboBox for changing the field is on the form designed for that purpose.

[mikel]

When you say "hide what is really in the table" do you mean that all you see is the ID number and not the actual data? That makes sense.

I guess I don't have it working because I definetly have drop down boxes in the datasheet view of my query. I'll try again. I didn't use the Lookup Wizard for any of the fields so I'm not sure why there would still be lookup fields.

I'm amazed that the documentation that I've read and even one of the books I bought on Access 2002 all say how great lookup fields are. Could you recommend a good book for Access 2007 that wouldn't steer me wrong?

[mikel]

Thanks for sticking with me through this but either I still can't get it to work, or it's working and it's not doing what I think it should do.

Here's what I did.

New file, created a table called tbl_Gage with Gage_ID, Manufacturer, and Location fields. The Gage_ID data type is AutoNumber and the other 2 are Text.

Created a table called tbl_Manufacturer with Manufacturer_ID and ManufacturerDetails fields. Both fields are set as Text for their data type.

Opened relationship window and dragged Manufacturer_ID from tbl_Manufacturer to Manufacturer in the tbl_Gage. Window pops up and I selected "Enforce Referential Integrity", and click on OK. The line is created with the gage table on the many side and the manufacturer table on the one side.

Created a new simple query and added all of the fields from both tables. Is that right?

Highlighted the query and then picked on Form. This created a new form but also gave me an error message that said "There isn't enough free memory to update the display. Close uneeded programs and try again." I don't have any other programs open and the computer is a 64-bit computer with 8GB of RAM so I don't know how it can be saying this. Clicked ok and it displayed the form.

At this point it doesn't have any combo boxes so I viewed the form in design view and added a combo box. In the wizard for the combo box I selected "I want the the combo box to look up the values in a table or query." I selected the Query and then it asked for which fields. I selected Manufacturer. Should I select Manufacturer_ID instead? After getting through the rest of the wizard I viewed the form in Form View and there is a drop down list now but doesn't seem like it links back to the manufacturer table. If I enter a new manufacturer in the table, it doesn't show up in the drop down list.

What did I do wrong?

[RuralGuy]

How about just posting your sample db so I can adjust it and post it back? Then you can ask any questions you might have when you look at the adjusted db.

[mikel]

That would be awesome. It's not much but I attached it. Thank you very much for taking the time to look at this. I really appreciate it!

[RuralGuy]

Let me know what questions you have about this db.

[mikel]

I must be missing something. I went through your db and found some places where I messed up. So, I tried to duplicate your db and still cannot get it to work like yours.

I have the tables created and linked together, nothing special there from what I see. I created the Query and from what I can tell it looks just like yours.

I created the form and from what I can tell all of the controls just pull data from the fields in the query expcept the manufacturer control which looks to be a combo box based on a query, right? I created my query to match yours and the form doesn't seem to work right.

If I open my form there is no manufacturer information in the drop down box so I opened the manufacturer table and entered a few samples. Then I opened the form and the drop down box showed the new manufacturers but the MfgOther never updates. When I enter information for the other boxes besides the details, I get an error that says, "The Microsoft Offce Access database engine cannot find a record in the table 'tblManufacturer' with the key matching field(s) 'MfgID'."

What the heck am I missing?

[RuralGuy]

Does the form I supplied work like you want it to work?

[mikel]

Yes exactly. I need to duplicate it so I can have procedures work the same way, but I just can't figure out what I'm doing wrong.