Wierd Results from a combobox and recordsource

  • Thread starter wwwredback via AccessMonster.com
  • Start date
W

wwwredback via AccessMonster.com

Hi All,

Sorry but it is a long question:

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.

Cheers

James.
 
W

wwwredback via AccessMonster.com

Hi Again,

Any takers on this annoying code that I have?

Thanks

James.
Hi All,

Sorry but it is a long question:

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.

Cheers

James.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

HELP PLEASE -- combo box problems 6
Master Form/Subform problems 5
Errors when adding Combo Box 1
SQL Troubles. 1
Report totals off by a couple cents. 4
append query 0
Extended Price: 3
Dealing with Pecentage Calculations in Reports 0

Top