Price list

James Tebb

New Member
Joined
Nov 12, 2009
Messages
26
Hi
I thought that this would be a pretty straight forward thing to do but I seem to be struggling. Please point out where I'm going wrong. (I'm not too good with vba so if we could keep things simple it might be better.

My database has a price list stored as a table called [PRICE LIST]. This table has the fields [CATEGORY], [PACKAGE] and [WEEKLY RATE]. I use this table as a reference for prices of each package.

I also have a table called [EQUIP TABLE] this is used to log the requirements of each job and has the following fields. [JOB NAME], [category], [package], [weekly rate].

When I create an equipment list for a job I use a continuous form called [EQUIP FORM]. This has the following.
[JOB NAME] - A text box where control source is Job Name on Equip Table
[CATEGORY] - A combo where control source is Category on Equip Table
[PACKAGE] - A combo where control source is Package on Equip Table
[WEEKLY RATE] - A text box where control source is Weekly Rate on Equip Table.

1. The Job Name text box has a default value that = the job name on a previous form allowing it to auto fill.
2. The Category Combo has a Row Source which refers to a Query called [CATEGORY QUERY] this queries the category field of the price list to allow me to pick one.
3. The Package Combo has a Row Source which refers to a Query called [PACKAGE QUERY] this queries the package field of the price list and allows me to pick a package from the category chosen in the category combo.

All of the above works fine.

When I choose a category and a package I want the form to refer to the price list and show the relevant weekly rate in the Weekly Rate text box.

I created a query called [WEEKLY RATE QUERY] this references the price list and returns the relevant weekly rate depending on the package chosen.
However I can not figure out how to get this to appear in the text box on my form and therefor be stored in the Equip Table.

Not sure where I'm going wrong - Am I missing the bigger picture?

thanks
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Code:
I created a query called [WEEKLY RATE QUERY] this references the price list and returns the relevant weekly rate depending on the package chosen.

What is the SQL for this query?
ξ
 
Upvote 0
Hi sorry for the slow reply. The SQL reads as follows

SELECT [PRICE LIST].[Weekly Rate]
FROM [PRICE LIST]
WHERE ((([PRICE LIST].Package)=[Forms]![EQUIP FORM]![Package]));

In the properties for my weekly rate text box on my Equip Form I have the following entered in Default Value

=[WEEKLY RATE QUERY]![Weekly Rate]

It returns Error

Hope you can help
 
Upvote 0
James

Do you just want this value to come from the price table and not be entered by the user?
 
Upvote 0

Forum statistics

Threads
1,224,551
Messages
6,179,472
Members
452,915
Latest member
hannnahheileen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top