Query Question Need Microsoft Access MVP! Help!

[Guest]

Hi, I am new to forum posts, but have reached last resort, - have flat spot
in my head from beating on brick wall! LOL.

Need to build a query ( I think) for a database and cant work it out!

Scenerio.

Have product table, with usual fields, ie product name, description, unit
price, special price etc.

Have a form for customer orders with a subform in it for product name,
description qty, unit price and line total.

Two problems, first is:

Would like to see when I select the product i want in the product column
from the combo box, the product description to be automatically filled in
that corresponds with that product. ie - product name, AUSMUG, then product
description to show Australian Mug.

Second Issue:

With my database I have three types of price in the Product List Table, the
Unit Price the Bult Price and a Special Price. Would like to create a combo
box that supplies me the prices in my products table, that relate the AUSMUG.
Also would love if the combo box had headers. ie. would like to hit the
drop down box in the Unit price column and see three columns, each labled
Unit$, Bulk$, Special$ respectively, then the price under each heading.

Just another thing, while I am here. with the Line Total

Thats it, sounds simple, but I must be barking up the wrong tree.

 

[Guest]

Not an MVP, but I'll try to give a head start
=====================================
Let start with the first Issue, and then move to the second one.

If the Combo RowSource that list the product includes also the description
Select Prod_Id , Prod_Desc From ProductTable

Then, next to the ComboBox create a text box to display the description, in
that text box ControlSource write
=[ComboName].Column(1)

Note: the column number of the combo start with 0, and this is why I wrote
1, been the second column
That should display the description every time you select the product, no
need for extra code
======================================
Second Issue, Include in the RowSource of the combo also the Prices.

Select Prod_Id , Prod_Desc, [Unit$], [Bulk$], [Special$] From ProductTable

On the AfterUpdate event of the combo write the code

Me.[TextBoxUnitname] = Me.[ComboName].Column(2)
Me.[TextBoxBulkname] = Me.[ComboName].Column(2)
Me.[TextBoxSpecialname] = Me.[ComboName].Column(2)

So after a value selected in the combo, the prices will be spread in the
appropriate text boxes in the form

====================================
*********** Important *************
Change the Combo ColumnCount to include all the columns (5)

You can either display the prices when the combo opens or not, by setting
the ColumnWidth of the combo (0 - wont display the field)

If you want the heading of the fields to apear, change the ShowHeading
property of the combo to Yes

=======================
I hope I'm in the right track, but it's something for you to start with

 

[Guest]

Dear BS"D,

Thank you so much for your help - would have gotten back earlier, but got flu!

Ok, I did all that you said, and you are so on the money that I was
screaming will joy!

With the first part - ie, put prod desc in automaticall when selecting
product id. (Combo12 is the name) that works absolutely fab! Thanks Thanks
Thanks.

I must be doing something wrong with the price issue though. I have almost
got it but no quite right! I have managed to make a combo and it shows
UnitPrice, Break $ and Special Price with the headings, on the combo box drop
down, but it is not specific to the product I have chosen. It displays ALL
the product prices from my products list.

Ok so what I have in my properties for the price combo box.(called Combo16)

Name: Combo16

Row Source Type: Table/Query

Row Source: SELECT Products.ProductID, Products.UnitPrice, Products.[Break
$], Products.[Special Price] FROM Products;

Column Count: 5

Column Heads: Yes

Bound Column: 1

List Rows: 8

Limit to List: yes

Auto Expand: yes

IDE Hold No

IME Mode : No control

IME Sentence Mode: None

After Update: Me.[UnitPrice] = Me.[Combo12].Column(2), Me.[Break $] =
Me.[Combo12].Column(2), Me.[Special Price] = Me.[Combo12].Column(2)

I think that might be all the info you need.

Seriously I cannot thank you enough for you help. It is the first database
I have done, and must say I have managed well, up to this point, but without
you, would have just gone insane!

Thanks again.
Meredith