Help with Combo box query.

[binbin]

Hi there, I have attached a sample database that I made to help me explain my problem more clearly.

I have a combo box query in my "fsubOrderDetail" subform that lists all products including their discounts. However, I would like that "combo box list" filter it's result by matching the CustomerID with their corresponding discount amount on CustomerDiscounts table.

The matching part is not a problem, I can easily do that, the problem lies on when the combo box list matches a discount, it removes the other items in the list that do not have a discount, it should still list all the items even if it does not have an existing discount assigned to a customerID... been driving me crazy trying out the different joins, but can't get my intended result.

Thanks for your time.

 

[binbin]

Ok, I think I've been going at this in the wrong direaction. I think I just have to apply the discount from CustomerID to each OrderSellPrice... but I realized that I really have not done any database that includes discounts for each customer. The ones I did only have 1 price for every customer.

In short, I do not know how to apply the specific discounts if a a particular customer ordered something... How do I get that specific discount from the tblCustomerDiscount table if a matching customer starts a transaction... I've been looking at database models, at databaseanswers.org... but I can't seem to make it work in my attach table. Any suggestions?

Thanks!

 

[binbin]

Yes, I have indeed been going at it the wrong way :banghead:, I have just solved my own problem with some dizzying reads of other people's problems. It was hard finding such an easy solution since I'm not a good googler and really couldn't put in "search words" on what I was suppose to look for, so I started small, starting with the word "query."

Me.ControlName = DLookup("QueryField", "Query or Table", "CriteriaField1=" & Me.Parent!ControlName & " and CriteriaField2='" & Me.ControlName & "'")

This basically means that I want the value of a certain control to be looked up in a query I made, filtered by the criteria based on two control values from a mainform and on a subform.

It is still half baked, since I still need to refine it with the "if" statement.

Hope this helps some of you folks out there... thanks for viewing.

PS. Forgot to say that I put that statement in the afterupdate event of my combo box with the product lists.