Using a parameter from a lookup field

[Bentley]

Good day everyone,



I have a query created that lists the Inspector associated with each work order. the Inspector field in the work order table is populated from a drop down list which obviously only lets you select the people that are in the Inspectors table. The problem I am having is when I set the parameter to say "Which Inspector?" I have to enter a numeric value (ie. 1, 2, 3, etc) in order to return the name that is stored in that field. I am not sure what I set up incorrectly or where to change this to be able to enter the name when prompted by the query. Any suggestions?

Bentley

[Bulzie]

What fields are in the Inspectors table and what fields are you pulling in your combo box?

[Bentley]

Primary Key (autonumber), LName, FName

[MatthewGrace]

I will assume you're using the Query Design visual design view rather than coding SQL Directly. That said, the reason you have to enter 1,2,3, etc. is because you have chosen to place your [Which Inspector?] in the criteria field for a foreign key. Since your foreign key is just a number and not a name, this is not the field for you to be placing the aforementioned [] criteria. Move that criteria into a different field - the one you probably called "Inspector" or "InspectorName" or whatever you called the column that actually stores the persons name.

If you respond "but I have no field in my query that stores the Inspectors real name - so I tried using the foreign key field and that's why it prompts me for 1,2,3,etc.", then the solution is to (again, assuming you're still in Query Design view), double click the Show Table button (it will be under the "Design" contextual tab all the way on the right in the ribbon) and Add the "Inspectors" table. You should now have the ability to add the "InspectorName" real name field (or whatever you called it). Lastly, move your [Which inspector?] criteria to that field.

Hopfuelly that should work, but you may still run into trouble if you have not established a relationship between the two tables back when you originally created them. If that's the case - no worries. Assuming you're still in the Query Design view, and both a Work Orders table and an Inspectors table have been added via the Show Table button, simply click on your Inspector table ID (primary key) field and Drag-Drop it onto your Inspectors Foreign key field (the one you currently have your [Which inspector?] erroneously set up on and need to enter 1,2,3,etc.). You will then be prompted to setup a join and the default settings will probably work just fine.

If you have more trouble still, go ahead and upload the file and I'd like to try and fix it.

I'm not a great Access user or anything but I think this is your problem. Good luck!

[Bulzie]

If the issue just about your combo box, then I assume it is based on table that has those 3 fields for Inspectors? If so, then when you create the combo box, it usually hides the key(autonumber) field. If not and that key field is the "bound" field, then it makes you put in the number to select a record. If your data in the combo box is as listed above (ID, LName, FName) then look in properties on that combo box for these values:

In Format tab:
Column Count should be = 3
Column Widths should be 0;1;1 (the 0 will hide the ID autonumber field when you pull down the combo box list on the form but still use it as the bound value for that combo box. I am assuming that first value is not 0?)

In Data tab:
Bound Column = 1 (this is saying the value of whatever is the first value in your Data RowSource will be used for this field. If you have a 1 then that Key ID field will be stored in your table.)

On the form, when you click the combo box you should just see LName and FName but after you select a name, in your table you will see the ID autonumber field stored. Later in a report say to get the names, you just have to link that Inspector table by that ID field to get the names.

[Micron]

the Inspector field in the work order table is populated..
The root of the problem is the root of all database evil - lookup fields in a table, not to mention using tables as data entry points. While the value displayed in the table field shows Joe Smith, it is bound internally to a number you can't see, so I'm not sure what effect the suggestions will have (I will be honest - I glossed over them because of what I see as the real problem). The best course is to have a properly designed, normalized data set so you can avoid future problems like this with this and any other lookup fields you might have.

You might be able to get around this by using the .value property of the lookup field in your query. At the moment, that trick is a bit fuzzy for me. In the query design grid, try LName.Value as the field, or in the criteria row, [Lname].Value = "Smith" - I can't remember for sure which it is at the moment. If that doesn't work, or the other posted suggestions don't work but is your preferred solution over proper design, let me know and I will look it up again.