Wierd Results from a combobox and recordsource

[wwwredback]

Sorry but it is a long question:

I am using MS Access 2003.

In a Form called: Orders I have a drop down combo box as shown below:

cboAccountType
Account
Trade
DIY

If you select Account from cboAccountType I need to pull the following fields and add them into the subform: OrdersSubform

OrderID from tbl_OrderDetails
ProductID from tbl_OrderDetails
Quantity from tbl_OrderDetails
Discount from tbl_OrderDetails
ProductName from tbl_Products
UnitPrice from tbl_Products

If you select Trade from cboAccountType I need to pull the following fields and add them into the subform: OrdersSubform

OrderID from tbl_OrderDetails
ProductID from tbl_OrderDetails
Quantity from tbl_OrderDetails
Discount from tbl_OrderDetails
ProductName from tbl_Products
TradePrice from tbl_Products

If you select DIY from cboAccountType I need to pull the following fields and add them into the subform: OrdersSubform

OrderID from tbl_OrderDetails
ProductID from tbl_OrderDetails
Quantity from tbl_OrderDetails
Discount from tbl_OrderDetails
ProductName from tbl_Products
DIYPrice from tbl_Products

On the On Change property I have the following [Event Procedure]:

Private Sub AccountType_Change()

If Me.AccountType = "Account" Then
Me!OrdersSubform.Form.RecordSource = "Query1"
ElseIf Me.AccountType = "Trade" Then
Me!OrdersSubform.Form.RecordSource = "Query2"
Else
Me!OrdersSubform.Form.RecordSource = "Query3"
End If
End Sub

Here are the queries:

Query1

SELECT DISTINCTROW [Order Details].OrderID, [Order Details].ProductID, Products.ProductName, Products.UnitPrice, [Order Details].Quantity, [Order Details].Discount, CCur([Order Details].UnitPrice*[Quantity]*(1-[Discount])/100)*100 AS ExtendedPrice
FROM Products INNER JOIN [Order Details] ON Products.ProductID = [Order Details].ProductID
ORDER BY [Order Details].OrderID;

Query2

SELECT DISTINCTROW [Order Details].OrderID, [Order Details].ProductID, Products.ProductName, Products.TradePrice, [Order Details].Quantity, [Order Details].Discount, CCur(products.TradePrice*[Quantity]*(1-[Discount])/100)*100 AS ExtendedPrice, Products.TradePrice AS UnitPrice
FROM Products INNER JOIN [Order Details] ON Products.ProductID = [Order Details].ProductID
ORDER BY [Order Details].OrderID;

Query3

SELECT DISTINCTROW [Order Details].OrderID, [Order Details].ProductID, Products.ProductName, Products.DIYPrice, [Order Details].Quantity, [Order Details].Discount, CCur(Products.DIYPrice*[Quantity]*(1-[Discount])/100)*100 AS ExtendedPrice, Products.DIYPrice AS UnitPrice
FROM Products INNER JOIN [Order Details] ON Products.ProductID = [Order Details].ProductID
ORDER BY [Order Details].OrderID;

When I run this combo box it always picks up the UnitPrice whatever you choose from the combo box and then updates that price into whatever selection you have made.

i.e. if a rudgwick brick is

$0.15 for UnitPrice
$0.35 for TradePrice
$0.45 for DIYPrice

And I have selected Trade from the cboAccountType it will display the UnitPrice $0.15 instead of $0.35 and then when I look at the tbl_Products Table it has updated the TradePrice field for rudgwick bricks to $0.15

Why is this? What am I doing wrong?

Thank you so much in advance for any pointers.

I would also like to say a big thanks to john big booty for his help on this so far.


Cheers

James.

 

[MarkK]

- At the top of your post you say the combo is called cboAccountType. The code you posted handles the Change event of an object called AccountType. Are these the same object? If not, that could be a problem.
- Also, at the time the Change event of a control fires, the Value property--which is the default property--has not changed yet. If you use the Change event of a control, reference it's Text property instead of its Value property. The Change event in conjunction with the Text property is more commonly used to respond to each character a user types into a control, which you don't need to do here.
- To retrieve the outcome of a change a user made to a control it is more common to use the AfterUpdate event, or the Click event for a combo.

 

[wwwredback]

Thanks Lagbolt for your help.

Still no joy.

I am really struggling now so if anyone can help me sort this out then I would really appreciate it.

THanks again for anyone looking and helping me to try and sort this nightmare out.

Cheers

James.

 

[MarkK]

Still no joy? What does that mean though? Did you try the AfterUpdate or Click event?

 

[wwwredback]

Hi there,

I did try the AfterUpdate or Click event but nothing is working, it still picks up the UnitPrice and changes the Trade or DIY price to whatever the unit price is for the item.

Can't work out why at all.

Please help.

Cheers

James.

 

[MarkK]

Can you recap?
What it the name of the combo that fires the event?
What is the code your currently have that fails?

 

[wwwredback]

Hi there,

The name of the combo is:

AccountType

The code that doesn't seem to be working or at least creating wierd results are the 3 queries which are shown in this thread: Query1, Query2 and Query3

If you selected Query2 then in the Products table the TradePrice field will be overwritten by the UnitPrice field and the same with DIYPrice using Query3.

The displayed results are also only showing up the UnitPrice in the Subform rather than showing the relevant fields: TradePrice or DIYPrice.

Thanks again for looking at this.

Cheers

James.