combo box to add certain fields into a subform

[wwwredback]

Hi All,

I have asked this on a few occasions but I am now thinking it is not possible.

I am trying to only use certain fields in a subform depending on what is selected on a mainform combo.

Can anyone please point me in the right direction.

Thanks

James.

 

[John Big Booty]

Are you saying that you only want certain fields on your sub form available for data input, dependant on the selection made in a combo located on the main form?

If this is the case then it is simply a matter of doing a logical test on the combo value in the combo's On Change event, and then either hiding or showing the required fields based on the result. Check this link for the correct syntax for referencing objects and control on your sub form. You would also need the same test on the combo value in the main form's On Current event.

 

[wwwredback]

Hi john,

THanks for the reply.

I have tried the following code but it doesn't seem to be working:

Private Sub AccountType_AfterUpdate()

Me.OrdersSubform.Form.UnitPrice.Visible = False
Me.OrdersSubform.Form.TradePrice.Visible = False
Me.OrdersSubform.Form.DIYPrice.Visible = False

Select Case Me.AccountType
Case "Account"
Me.OrdersSubform.Form.UnitPrice.Visible = True
Case "Trade"
Me.OrdersSubform.Form.TradePrice.Visible = True
Case "DIY"
Me.OrdersSubform.Form.DIYPrice.Visible = True
End Select

End Sub

Any more advice.

Cheers

James.

 

[DCrake]

What is not working?

 

[John Big Booty]

Try;

Private Sub AccountType_AfterUpdate()

Me!OrdersSubform.Form!UnitPrice.Visible = False
Me!OrdersSubform.Form!TradePrice.Visible = False
Me!OrdersSubform.Form!DIYPrice.Visible = False

Select Case Me.AccountType
Case "Account"
Me!OrdersSubform.Form!UnitPrice.Visible = True
Case "Trade"
Me!OrdersSubform.Form!TradePrice.Visible = True
Case "DIY"
Me!OrdersSubform.Form!DIYPrice.Visible = True
End Select

End Sub

 

[wwwredback]

Hi there,

Thanks for the info.

It is still displaying all of the 3 fields in question in the subform and also taking the UnitPrice and not the relevant fields i.e. TradePrice or DIY Price.

I have attached a screen shot to show you what I mean.

Thanks again.

 

[DCrake]

Your field Running total is on the main form, correct? how are you populating the value in this control? Is subform dependant?

David

 

[John Big Booty]

You failed to mention that your sub form is in datasheet view not form view

 

[wwwredback]

Well I am having a slight issue on this as it is just running from a query which gathers its data from another query. This was going to be a later question as I need to know how to refresh the 2 queries on formload and when the form and subform data is inputted.

Sorry I'm just not very good at explaining stuff.

Thanks

James.

 

[wwwredback]

Ah sorry John Big Booty,

Does this make a big difference then?

Apologies again ,

James.

 

[John Big Booty]

You'll have to set the column width of the columns you don't want to see to 0cm.

At this time of night you'll need to do your own google search to solve that little problem. I have no doubt it can be done though

 

[John Big Booty]

Here's a link that should get you started.

 

[wwwredback]

You're a star!

A will look into this now.

Thanks a million.

James.

 

[wwwredback]

Hi Team,

Thank you for all your help.

Just to let you know I have now got the solution:

In the After Update Event I have placed the following code:

Private Sub AccountType_AfterUpdate()
Me.OrdersSubform.Form.UnitPrice.ColumnHidden = True
Me.OrdersSubform.Form.TradePrice.ColumnHidden = True
Me.OrdersSubform.Form.DIYPrice.ColumnHidden = True
Select Case Me.AccountType
Case "Account"
Me.OrdersSubform.Form.UnitPrice.ColumnHidden = False
Case "Trade"
Me.OrdersSubform.Form.TradePrice.ColumnHidden = False
Case "DIY"
Me.OrdersSubform.Form.DIYPrice.ColumnHidden = False
End Select

End Sub

IT WORKS!!!!!

The only issue I have now is working out my line total in my subform but this will be my next question as the code I am using now in a query is this:

SELECT DISTINCTROW [Order Details].OrderID, [Order Details].ProductID, Products.ProductName, Products.UnitPrice, Products.TradePrice, Products.DIYPrice, [Order Details].Quantity, [Order Details].Discount, CCur(Products.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;

As you can see it is picking up the UnitPrice, but of course this changes depending on what fields are displayed from the combo box.

Any takers?

Cheers

James.