Full Version: Combo box query
UtterAccess Forums > Microsoft® Access > Access Forms
Dogsbody
I have an Access 2003 table called Products, this has fields: ProductID (= primary key), description, basic price, three levels of discount % (for quoting sales discounts).
I have a form called Enquiry which has a Combo box linked to the Products table on ProductID. In the form I need to be able to select a product from the combo box and display all the fields for that ProductID on one line of the form. Then I need to be able to overtype in any of the fields (say to slightly modify the product description because of a slight product variation or change a discount %). I also need to select which discount % is to be applied. I then need to be able to save that whole line to another table (say Quotes) so that we have a record of what was specified but I do not want to change the underlying basic data in the original table Products. I’d be grateful for any suggestions.
Jack Cowley
Welcome to Utter Access Forums! -o!

Add all the fields to your combo box, but they do not need to be displayed... In the After Update event of the combo box:

Me.NameOfDiscountControl = Me.NameOfComboBox.Column(n)

The 'n' in Column(n) is the number of the column minus 1 so if Discount is in physical column 3 then n = 2. Combo boxes are zero based... Do this for the rest of the control on your form...

hth,
Jack
Dogsbody
Thanks Jack - that allows me to overtype without affecting the underlying data nor does it save it anywhere. How can I save those changes in a different table so that we have a record of those changes?
Jack Cowley
Generally speaking you do not save existing data in two places. If you have a product then you want to save the ProductID in the Quote table and then you would add type in the discount for this customer as well as the quantity and cost then maybe add a comment about a modification or whatever. You would not want to save any other data that is in the Products table.
Yally speaking you do not save existing data in two places. If you have a product then you want to save the ProductID in the Quote table and then you would add type in the discount for this customer as well as the quantity and cost then maybe add a comment about a modification or whatever. You would not want to save any other data that is in the Products table.
Your quote table might look like this:
tblQuotes
QuoteID (PK and auto)
CustomerID
Quantity
PartID
Cost
Discount
Comment
That should be enough information, but if you need more you can add it, but you do not want to save all the possible discounts, etc.
I hope I have understood what it is you want to do and that this answers your question. If not, just let us know!
Jack
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.