Combo Selection to ascertain which fields to use

[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.

Hope someone can help me,

Thanks a million.

James.

 

[John Big Booty]

Try;

    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

In the OnChange event of the Combo, you will also need this in the OnCurrent and I think also in the OnLoad events of the main form.

I've made some changes to your DB to implement the change of recourd source.

You should also avoid using spaces and other special characters in object and control names as this causes no end of grief once you start writing VBA code.

Also I would suggest that before you go too much further that you implement a naming protocol; something like TBL_TableName, FRM_FormNamer, QRY_QueryName etc. as this makes identifying object a whole lot easier.

 

[wwwredback]

Hi John,

Thanks a million for your help.

Starting to implement now and looks good so far.

Will keep you posted.

Thanks again.

James.

 

[wwwredback]

Thanks again for the DB changes but it seems to show an error on the UnitPrice field.

Can't work out how to change the field, as when the form loads it uses certain fields including the UnitPrice but I would like this to change if the TRADE / ACCOUNT or DIY is selected.

When you select a product from the list on the OrdersSubform the error says:
You can't assign a value to this object

Any guidance?

Thanks again for all your help.

James.

 

[John Big Booty]

From memory I think that this is coming about because you have not fully populated the price table, I think some of them had null values, which is causing the error.

 

[wwwredback]

Thanks.

Although I have now populated the table and doesn't seem to help.

Mmmm I'm stuck.

 

[wwwredback]

Hi Again,

Should the queries be taking the information for the prices from the products table or the order details table?

They should be taken from the products table shouldn't they, although saying that I have tried both ways but seems to still come up with an error?

Like I said before I'm getting very confused so any more help would be very much appreciated.

Thanks

James.

 

[John Big Booty]

Sorry my bad, I had to change the reference in my two new queries. However this was largely caused by the fact that you seem to be storing what appears to be similar information in two locations.

You will also need to re-jig the formula that is calculating your grand total.

 

[wwwredback]

Hi John,

Thanks for your help on this matter.

For some reason though it is not picking up the different prices for each category. They are all just selecting the UnitPrice.

Is therea reason for this.

Thanks again.

James.

 

[John Big Booty]

James, you will need to look at the three queries I've made (Query1, Query2 &Query3) to act as RecordSource for the sub form. You can either adjust those, to meet your needs, or start from scratch and write new queries to collect the information you require in your sub form.

 

[John Big Booty]

Hi John,

Thanks for your help on this matter.

For some reason though it is not picking up the different prices for each category. They are all just selecting the UnitPrice.

Is therea reason for this.

Thanks again.

James.

I've just re-downloaded the DB i posted, and as far As I can see it seems to work fine, unless (and this is likely) I've missed something

 

[wwwredback]

Hi there,

thanks again for your reply.

If you change the prices in the product table i.e rudgwick bricks to 3 different price in the UnitPrice, TradePrice and DIYPrice, it doesn't seem to pick it up if you do 3 invoice examples using each of the accountTypes. It still only seems to pick up the UnitPrice for each.

Sorry to be a pain.

Cheers

James.

 

[wwwredback]

Hi All,

I am still confused to why this isn't working.

I have set up the 3 queries like John suggested but it still only seems to be pulling the informaton from the UnitPrice field.

Am I doing something wrong with the RecordSource.

Please, please, please help.

Cheers

James.

 

[John Big Booty]

James, you will need to look at the three queries I've made (Query1, Query2 &Query3) to act as RecordSource for the sub form. You can either adjust those, to meet your needs, or start from scratch and write new queries to collect the information you require in your sub form.

James

Having had another look at the DB, I'd probably go further and suggest you start from scratch again as the structure of the DB seems to be badly normalised. Once you have the normalisation sorted things should become a whole lot clearer.

 

[wwwredback]

Thanks a million, I will redo the DB and see how I get on.

Cheers

James.