Choosing a record source for a subform from a combo box

[wwwredback]

Dear All,

I am yet again in need of help. Basically I have the attached database that I
require some guidance on please.

If Trade is selected from combo box AccountType in the Orders Form then it
displays the prices from the Trade Price in the products table.

If Account is selected from combo box AccountType in the Orders Form then it
displays the prices from the Unit Price in the products table.

If DIY is selected from combo box AccountType in the Orders Form then it
displays the prices from the DIY Price in the products table.

Also on the above it will input the TRADE / ACCOUNT / DIY into the
AccountType field in the Orders Table

Now to add to all of this I need the ability to then add the Customer Name
and Address and record this entry in the Orders table only if Trade or DIY is
selected. The Account has already given their name and address so this
doesn’t need to be inputted.

I have tried the following code and doesn't seem to work:

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

I can attach the database if I am allowed to do so if it helps.

Hope someone can help me and thanks in advance,

Thanks a million.

James.

 

[Arvin Meyer MVP]

I've used the same code many times and it works fine:

Me.subformcontrolname.Form.RecordSource = "QueryName"

I suggest that something in your code, or logic is afoul. Perhaps your
combo's bound column isn't pointing to the correct values. Are you getting a
specific error message?

 

[wwwredback]

Hi Thers,

Thanks for your reply.

There are no errors, basically it is retrieving the UnitPrice field and not
the others depending on what is selected on the combo box. Basically whatever
is selected in the combo box it is still obtaining the info from the
UnitPrice field.

Its giving me a real headache

Thanks again.

James.

I've used the same code many times and it works fine:

Me.subformcontrolname.Form.RecordSource = "QueryName"

I suggest that something in your code, or logic is afoul. Perhaps your
combo's bound column isn't pointing to the correct values. Are you getting a
specific error message?

Dear All,

[quoted text clipped - 38 lines]

James.

 

[Chegu Tom]

Is Me!OrdersSubform form open when you do this? If not you must open the
form first. I don't think it will change the recordsource in a closed form.

 

[wwwredback via AccessMonster.com]

Thanks again for your help, but again no joy.

I have got the event procedure running onchange from the combo box and here
is the code it is running:

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 is the code used for one of the queries i.e. Query2

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

I really can' see why it wouldn't work.

Thanks again for all your help and for anyone else in advance for their help.

Cheers

James.

Is Me!OrdersSubform form open when you do this? If not you must open the
form first. I don't think it will change the recordsource in a closed form.

I've used the same code many times and it works fine:

[quoted text clipped - 47 lines]

 

[BruceM]

I am not clear about what you are trying to accomplish. In your first post
you stated "If Trade is selected from combo box AccountType in the Orders
Form then it
displays the prices from the Trade Price in the products table". I assume
"it" is the subform, but the guess may be incorrect.

I would use the After Update event rather than the Change event, but I don't
think that is the problem.

I see that your SQL includes TradePrice, then TradePrice again as the alias
UnitPrice. Is a control on the subform bound to TradePrice? Are UnitPrice
and ExtendedPrice aliases in all of the queries? If the questions are
unclear, post the SQL for another query.

Thanks again for your help, but again no joy.

I have got the event procedure running onchange from the combo box and
here
is the code it is running:

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 is the code used for one of the queries i.e. Query2

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

I really can' see why it wouldn't work.

Thanks again for all your help and for anyone else in advance for their
help.

Cheers

James.

Is Me!OrdersSubform form open when you do this? If not you must open
the
form first. I don't think it will change the recordsource in a closed
form.

I've used the same code many times and it works fine:

[quoted text clipped - 47 lines]

James.

 

[wwwredback via AccessMonster.com]

Thanks for the reply, very confused so here is another query (Query3)

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

Thanks again for your help!

Cheers

James

I am not clear about what you are trying to accomplish. In your first post
you stated "If Trade is selected from combo box AccountType in the Orders
Form then it
displays the prices from the Trade Price in the products table". I assume
"it" is the subform, but the guess may be incorrect.

I would use the After Update event rather than the Change event, but I don't
think that is the problem.

I see that your SQL includes TradePrice, then TradePrice again as the alias
UnitPrice. Is a control on the subform bound to TradePrice? Are UnitPrice
and ExtendedPrice aliases in all of the queries? If the questions are
unclear, post the SQL for another query.

Thanks again for your help, but again no joy.

[quoted text clipped - 44 lines]