populate table with form combo box (lookup)

[z_bryant]

I am trying to modify a database that stores leak data from air leak surveys.

Currently it has basic data such as date found, date repaired, type of leak, etc.

We use a simple form to make new entries to the database.

Air leaks are quantified by a dB reading on the collector, but need to be represented as a CFM or $ amount to mean anything. So:

• I have added colums for dB reading "dB" of the leak, and the estimated CFM "CFM" of the leak.
• I created a new table consisting of "dB Value"(field 1), which has the values 1 through 120, and "CFM"(field 2), which has the estimated leak rates for the values in field 1.
• I added a combo box to the form to lookup the values, based on the dB. It populates to the "dB' field in the "Air Leak" database table just fine. But how do I also get the corresponding value to populate the "CFM" field in the "Air Leak" database table?

I can set it up to display both columns in the combo box, but only one carries over. I feel like this should be simple enough, and that maybe I'm just overlooking something.

Also, as the dB_Value (in the lookup table) will always equal the id, do I need the dB_Value column? or is it better to use it rather than the ID? What problems might I encounter using the id column instead of the dB_Value column?

 

[RainLover]

Is the Combo bound to the Record Source.

An AutoNumber cannot be guaranteed to be Sequential. But it makes a Great Primary Key.

 

[z_bryant]

I've tried it numerous ways, but here's one of the last:

• I created a combo box (which started the wizard). The label tells the operator to select the dB.
• Selected it to lookup the data from Table/Query.
• Selected my lookup table.
• Selected both columns (ID and EstCFM). (Using ID for the dB reading).
• No sort order selected.
• Unchecked the "Hide Key Column" option.
• Stored that value as "CFM" in the database (which should make it "Bound").

In this way, the combo box shows both columns, I can pick by the dB, and the CFM get's entered correctly. However, the dBValue won't be recorded anywhere.

Maybe that's all they built into it, but it'd be nice to make both columns come over at the same time, from one selection.

 

[RainLover]

Unchecked the "Hide Key Column" option

That was not correct. A user should not see a Primary Key, especially when you use Autonumber.

You can't save Two Fields into One. You should be saving the Autonumber so the Field should be of Data Type Number.

 

[z_bryant]

I'm not trying to save two fields into one. I wanted to save both fields into two different fields. I wanted to select the dB value, and save that value in one field as well as the associated CFM value in another field.

I can understand the "user should not see a Primary Key". But in this case, the user will only see the generated reports. They don't have access to the database.

This table is only a list of converted values. The data should never change, unless we decide to use different conversion factors. Plus I changed Autonumber to number after the table was completed.

 

[RainLover]

Plus I changed Autonumber to number after the table was completed.

AutoNumber makes a better Primary Key.

I'm not trying to save two fields into one. I wanted to save both fields into two different fields.

If you save just the Primary Key then you can always find the other field in any Query Via a simple Join.

I can understand the "user should not see a Primary Key". But in this case, the user will only see the generated reports.

This is still not a reason to show the Primary Key. So don't break your own rules.

Hope this helps. If you are at all confused with the saving of just the Primary I can create a very simple Database to demonstrate.

 

[spikepl]

There is no need for introducing any artificial primary key, since OP already has the natural dB and can use it for that.

You have two tables. One with your AirLeaks and one with the dB amd CFM, $ or whatever. You should only store dB in the main table. All the remaining data hanging onto the dB in the otehr table can be displayed at will, when using a query that gets data from both joined tables, as Rain said.

BTW: Abstain from using spaces or non-alpha characters in field/table/query/form/object names. Or else live with the subsequent pain.

 

[z_bryant]

Ok, I think I heard the nickel drop. I think I was misled by the label of "lookup column", even though I mostly had it working.

I looked at the join option after seeing Rain's response, tried it, and I do like it better in my application.

I put a field in my main table dBValue and also in my conversion table, linked the two with a join type 1, and was able to get it working. It'll simplify the controls for me, that's for sure. I was able to do it with a text box, instead of a combo box. So that got rid of the other problems, too.

That was all good advice, thanks to the both of you.