Setting a column value based on another column

[Sarita2710]

OK what I would like to do is in my customer form which has the normal customerId, etc I would like to add a discount column where the value is set according to the category of customer e.g. if I select Retail the discount is set automatically to 10% Can any one give me an idea of how to mange it. I am using a form to fill the database table.
Thanks
Sarait2710

 

[isladogs]

Welcome to the forum Sarita.

Use a combo box to assign the customer category with values populated from a table or value list
In the after update event use select case or if..else..end if to set the discount textbox.

Alternatively, use two columns in your combo : Category and discount.
The category column would be bound. The discount column can be hidden.
This time the after update event just selects the value in the second column -this is column(1) as numbering starts at 0

 

[arnelgp]

You dont really want to add the category or the duscount to your customer table. You will add those fields to your sales transaction table. On initial sale for a customer you will be required to fill these fields in the sales trans table. Each sale fir same customer you will lookup the last trans of this cistomer to determine its cat. There naybe time in the future that the category will change.

You will be needing another tabke for sales.

 

[Sarita2710]

thanks Colin - not sure how to do that but will give it a go.

 

[Sarita2710]

Arnlgp - I need the customers discount to show on their records - depending on their category they get a certain discount which I don't want changed Thanks

 

[isladogs]

thanks Colin - not sure how to do that but will give it a go.

My recommendation is to create a table for your combo source as it's easier to edit at any future time.
See my previous post or that by arnelgp as we both suggested the same thing
Also I'd go with the 2 column combo as it's less code.

If you get stuck, ask again.

 

[Sarita2710]

Thanks everyone managed it.

 

[isladogs]

Excellent.
For the benefit of others reading this post, could you describe your solution

 

[Sarita2710]

Yes sure - I followed both of you and created a table for the category/discount and used a combobox for the user to select the category which when selection was made the After Update event filled the discount textbox with the appropriate discount value. Again many thanks for the help