Invoice Form with Multiple Price Variables

[fountain27]

I am creating an invoicing form for advertising insertions. We have 3 different variables to determine the price per insertion.

1. Program (different tiers based upon qty purchased)
2. Payment Method (full price or discount)
3. Size of the Ad (full page, half-page etc)

I would like a form to populate as user selects the options and show both prices for Payment Method. I can do it via query, but I would like the user to see the options when inputting information for a proposal. I have a table with all pricing options.

I hoping someone can point me in the right directions.

Thank you for your time and any help.

[pbaldy]

Is this what you're looking for?

http://www.baldyweb.com/CascadingCombo.htm

[fountain27]

Thank you for your reply. I was hoping to design similar to the one below. The User Selects the Program for the Account Once. The User then selects the Months and Ad sizes per Month. The Full Price and Discount Price Auto Populate. Can this be done with a Cascading Combo Box?

Program:
Month	Ad Size	Full Price	Discount Price
January	Half-Page	$0,000	$0,000
February	Full Page	$0,000	$0,000

[pbaldy]

Maybe the ad size would cascade off program, though I don't really understand your structure. This may help too:

http://www.baldyweb.com/Autofill.htm

Can you attach the db here?

[fountain27]

tbl_AdPrice.accdb

I cannot post the full DB since some of the data is confidential, but here is the pricing table showing all the various options. Again, thank you for your help.

[pbaldy]

Well, If I chose program 2 you could use the cascading combo method to restrict the ad sizes selections to 1 and 3 (though it appears you would also have to specify year). I assume those are half and full, though I see 6 too. Choosing one of those would get me 3195 or 4295. I'm not sure how you get full and discount pricing, as there only appears to be one price per program/ad size.

[fountain27]

I able to get the cascading combo box to work, except how can I have the price auto-populate - (e.g. 3195 or 4295) in two unique text boxes?

I cannot say thank you enough for all your help!

[pbaldy]

Did you try the link in post 4 on the ad size combo? I still don't understand how you get full and discount prices, I only see what look like prices for ad sizes.

[fountain27]

Thank you for your reply. The client decides which version they want Full Price or Discount Price (prepayment). I just want to display both options using 2 text boxes.

[pbaldy]

Yes, but what are those prices? Where do we pull them from? For 2020, program 2 has ad size 1 for 3195 and ad size 3 for 4295. I assume 1 and 3 represent full and half page. If they choose full page for 4295, is that the full price or the discount price? How is the other determined?

[fountain27]

Ok, I understand you question. Sorry for not being clear. Below is screen shot for one Program.

Program: Bronze
Ad Size: half-page
Contract Year: 2020
Full Rate is $3,195
Discount Rate is $2,695

The User selects all the variables (Program, Ad Size, and Contract Year)

[pbaldy]

Ah, the program I randomly chose only had one payment option. A couple of ways come to mind.

You can use 2 DLookup()'s, each with the 3 main criteria plus either full or prepayment.

You can open a recordset with the 3 criteria and then loop it, putting the 2 found values into the appropriate textboxes.

Either of those would happen after then chose the last of the 3 main criteria (ad size in your picture). Let me know if you need help with either.

[fountain27]

Again, thank you so much! I am familiar with DLookup, but not with multiple criteria. Do you mind showing the syntax?

[pbaldy]

This has multiple criteria at the bottom. Just delete the ' or # for numeric criteria.

http://www.theaccessweb.com/general/gen0018.htm