Help ! Display values from tables!

[kedarj]

Hi guys,
I need urgent help on a simple question. I hope you will be able to help me.
I have a table CustomerMaster which stores customer number and name. (Customermaster)
I have another table which stores the product details for customer. (customerProduct)
I have another table which shall store order details. (CustomerOrders)
On the form, the user selects a customernumber, the system then displays the customer name.
The user then picks up the product ordered by customer (picked from combo box). On the combobox on recordsource I have given a select query which fetches the records matching the customer selected from table
Now I want the Product Price, tax and duty of the product to be displayed for the selected product. Somehow, I have to again make that as a combobox and then select the price (although there is only one record...
Can anyone tell me how to do this?
Regards
K

 

[KenHigg]

Hum...

The customer product table seems a bit of a mystery. I can see a master customer table, a master products table and a order detail table. I assume there is a master order table.

So.... You have an order which has one cutomer and many order detail records which each have a product detail association...

Make sense?

 

[kedarj]

Thanks for the reply.

The CustomerProduct table has the Customer and Product relationship
e.g.
customernumber Product Price
X345 E54 45.34
X345 E55 46.23
X134 E55 49.26
X349 E55 41.13

So the same product can be sold to different customers at different prices and one customer can sell more than one product

I hope this makes clear.

 

[KenHigg]

So every time a new product is added or if a price for a product changes, you have to go through all the customer product records and change each price for that product? That sounds like a nightmare...

Seems like it would be better to have something like a price level system where a good customer might be in a better pricing level...

 

[kedarj]

Hi Ken,
It is like that, as the prices are not dependent on price rules, but individual with customers...
regards
K

 

[KenHigg]

Is this form going to be where you enter customer orders?

Say:

- The phone rings and it's the customer or the order is being entered from a form someone manually filled out and gave to you...

- You pull up the main 'new order' form, where you select the customer...

- Then you have a sub form where you select an item and enter a qty...

Is this correct so far?

 

[kedarj]

It is on the same form:
Select Customer
Select Product
Actually at this stage the price should be automatically displayed...But I have to use a combo box with a select statement saying select product price from customerproduct where customername=xxxx and productname=wwww

Enter Quantity...

save the form

 

[KenHigg]

Hum...

Here's a delima I see happening; When you do a detail line in the order, if you don't store the current price in the order detail record, when you do change the price in the customer products table, your order totals will be screwed up...

 

[kedarj]

I store the price with order details...

 

[KenHigg]

Ok. Sorry to be so nit picky

In the combo box, for the row source, use all the cols you need to eventually have in the main form (Price, tax and duty). Make these like col 3, 4 & 5. Then hide them (Make their width 0").

So now a user selects a product code. In the after update event in the combo box, do a pc of code something like:

myform!price = me!mycomboBox.column(3)

etc,...

Make sense?

 

[kedarj]

Do you mean in the same rowsource for the product description?
e.g:
Select Distinct Productname, price, duty, tax from customerproduct where productname=xxxx and customername=ssss
I passed the statement as you mentioned in the Afterupdate property. I get an error there that the macro does not exist.
-----

-----
I tried with following piece of code:
-----
Private Sub Combo450_AfterUpdate()
myform![Product Price 3] = Me!mycomboBox.Column(1)
Me.[Add Promo Details].Combo519 = Me.ComboBox.Column(1)
Me.[Product Price 3] = Me.Combo0.Column(2)
End Sub
-----
another thing, if we get two columns in the combobox, how can we display/bound the other column?
e.g. Product description, price, now if product description is bound first then how can we bound/display price to the feild Product Price?
But I think we are getting closer...

Thanks..
K

 

[KenHigg]

The order of the columns in the rowsource are dictated by the sql statement in your case. So if the first col in the sql is price, that will actually be col '0', then if description is the second col in the sql statement, that will be col '1'. (You also need to set the 'column count' property to the appropriate number)

Then, any col can be the 'bound' col by setting this in the 'bound col' property of the combo box.

Finally, you can display any col, or combination of cols by doing something like the following in the col width property (In this case lets assume there are 4 cols and you only want to display the third one):

0";0";1";0"

So now in the after update event, to use the value in the first col, you would use:

me!myCboBox.column(0)

or to use the 3 col, you use:

me!myCboBox.column(2)

Hope this helps...

 

[kedarj]

Hi Ken,
Thanks for the reply. But still somehow, I am getting an error.

As In my last post I have given the SQL Statement.

Now I get an error as shown in the attachment.

I shall appreciete you help and reply...
K

 

[KenHigg]

This is a stab in the dark but, in the after update property of the combo box, you should have:

[Event Procedure]

I'm guessing you have:

me!myComboBox

???

 

[kedarj]

Ok Let me tell you again, now what I did:
Control source: Lines 3
Rowsource: SELECT DISTINCT [CustomerProduct].[productdescription], [CustomerProduct].[product Price] FROM [CustomerProduct] where ([CustomerProduct].[brands]=Text498) and [acc number]=[parent acc number];
bound Column: 1
Columncount: 2

In the Event tab:
AfterUpdate: =[me]![myCboBox].[column](1)

Now I want the Price to be displayed.

Instead when I select the product, I get an error:
The expression After Update you entered as the event property setting produced the following error: The object does not contain the automation object 'Me.'

what could be wrong?

 

[KenHigg]

This is part of what's wrong:

In the Event tab:
AfterUpdate: =[me]![myCboBox].[column](1)

You can't put a pc of code there like that! Delete what you have there. Then put the cursor back and click the small ellipses (...),button to do code.

Then when you finished writing the code and go back and look, you'll see:

[Event Procedure]

If you try to put anything else there, Access thinks you want to run a macro when the event fires off...

Hope ths makes a little sense...

 

[kedarj]

How Should I assign Product Price 3, that is the field in which the price should be displayed?

[me]![Product Price 3]=[me]![myCboBox].[column](1)

Is that correct?

 

[KenHigg]

How Should I assign Product Price 3, that is the field in which the price should be displayed?

[me]![Product Price 3]=[me]![myCboBox].[column](1)

Is that correct?

[me]![Product Price 3]=[me]![myCboBox].column(1)

Provided the price is the second column in the combobox (I assume you get the part where 0 is really the first one, 1 is really the second one, etc,...)

 

[kedarj]

Ken,
Still not getting through...

I went into Event Tab, selected .... button, then went into Code builder and in the After Update section.
Here is the code piece
----
Private Sub Combo454_AfterUpdate()
[me]![Product Price 3] = [me]![myCboBox].Column(1)
End Sub
----

Then went back to properties...
Now it shows [Event Procedure]

Ran the program and got an error...It is attached...

What am I doing wrong?

 

[KenHigg]

Ok...

I'm guessing you have some bogus code mucking things up now. Let's back up a step.

Let's disable the code you just wrote and run it to see if it is the problem or see if there is some code in another place that is causing the problem. In the code you posted:

Private Sub Combo454_AfterUpdate()
[me]![Product Price 3] = [me]![myCboBox].Column(1)
End Sub

Add a comma like this:

Private Sub Combo454_AfterUpdate()
'[me]![Product Price 3] = [me]![myCboBox].Column(1)
End Sub

This will tell Access to treat the line like a comment and to not try and execute it.

Then try what you did before and see if it errors....