Choosing a Currency Rate for 1 table based on field in another tab

[Abouttime]

Currency fields do not allow lookup values.

Each piece of equipment will have a rate attached to it.

The equipment used is chosen from "Equipment" Table as a lookup column.
The equipment used is stored in "Applications" Table.
The rate is stored in a "Pricing" Table.

Can I have the rate field in the Pricing Table choose a value based on the
input in the Appliations table?

Eg. I have a query setup that combines the information from both Tables
(Applications & Pricing). A truck is used in my "Applications" table. The
rate for a Truck is $100 (this information will be stored in another table
for a lookup column). When I am inputting data for Pricing, is there a way
that I can have it automatically select the rate for a truck based on the
value chosen in the Applications Table?

 

[KARL DEWEY]

I have a query setup that combines the information from both Tables
Applications & Pricing).
In the query use a left join from Applications to Pricing on equipment.

Why do you not have price in the Applications table or does the price change
or different for some customers?

 

[Beetle]

First of all, you should not be using Lookup fields (aka combo boxes) in
tables. See this link for a discussion as to why;

http://www.mvps.org/access/lookupfields.htm

Second, why is your pricing in a separate table? Is it because each piece of
equipment can have more than one price associated with it? If that is the
case, how do you determine which of the possible prices for a piece of
equipment should be used for a given record?

On the other hand, if each piece of equipment only has one "price" then
maybe it should just be an attribute of the equipment and not in a separate
table.

Third, you should be using a form for data entry. There are several ways you
could get the pricing info from the table like DLookup or the Column property
of a combo box, but it would be helpful to know a little more about the fields
in you tables and the relationships between tables.

 

[Abouttime]

The pricing is in a separate table because it is information that only
certain people will have permission to view.

I will be using a form for the data entry; however, all the data will be
stored in these separate tables. Are you recommending to have the form have
lookup fields but not the Table?

What I'm hoping is to have the price come up based on the equipment chosen,
not to actually look it up.

Please let me know if there is still something I should clarify.

 

[Beetle]

Are you recommending to have the form have lookup fields but not the Table?

Yes. Combo boxes are perfectly acceptable, and commonly used, in forms but
they will cause you nothing but headaches if you use them in tables.

There are a couple of ways you could accomplish what you want. Both of these
methods assume the following;

1) You have an existing relationship between the Equipment table and the
Price table based on, for example, EquipID.

2) The text box that will be used to display the price will be *unbound*.
Text box name (for this example) = txtDisplayPrice. This text box
should be locked so the users can't overwrite it.

3) You are using a combo box in your form to select the Equipment.
Combo box name (for this example) = cboEquipment

Option 1
******

Modify the Row Source of your combo box to include the price from the
Prices table like;

Select tblEquip.EquipID, tblEquip.EquipName, tblPrices.Price From
tblEquip Inner Join tblPrices On tblEquip.EquipID = tblPrices.EquipID
Order By tblEquip.EquipName

Then set the following properties in your combo box

Bound Column = 1
Column Count = 3
Column Widths = 0",2",0"

This way the first and third columns will be hidden so the users will only
see the Equipment Name in the combo box.

Next, set the Control Source of the unbound text box (txtDisplayPrice) to;

=[cboEquipment].Column(2)

the text box will then display the value (price) that is stored in the third
column of your combo box. It is a zero based index, so the first column is
Column(0), second is Column(1), etc.

Option 2
******

Use a combo box Row Source like;

Select EquipID,EquipName From tblEquip Order By tblEquip.EquipName

combo box properties like;

Bound Column = 1
Column Count = 2
Column Widths = 0",2"

Then set the control source of txtDisplayPrice to;

=DLookup("Price", "tblPrices", "EquipID=" & [cboEquipment])

This will "Lookup" the appropriate price based on what is selected in the
combo box.