Margin Calculator showing nothing

[mindbender]

Hi there,

I am working on a margin calculator. Currently it has 4 fields, Product, Term, Quantity and Base Price.

I am trying to get the Product combo box to show the unique values in the Product Field, the Term Combo Box to show the unique terms for the product, then the Base Price is automatically populated based off of the chosen values in the combo boxes, then the user enters a quantity and the Base Price field is then populated with the Base Price X the quantity entered. If they enter more than one row the Grand Total at the top would be the sum of all the Base Price calculations..

I can't seem to get any values to appear in either drop down. Not sure what I am doing wrong.

I have attached a sample of my database in case you want a first hand look. If you feel I am going about this the wrong way I am always open to suggestions.



Thanks

[mindbender]

attached db

[June7]

Nothing shows in product combobox because SQL pulls only one field and combobox is set for 2 columns with widths 0;1". Change to 1 column and no widths.

Comboboxes should NOT be bound to tbl_main fields.

TotalWeight textbox should be Locked and TabStop No.

If you want unique terms for selected product then that is a cascading combobox. Common topic.

SELECT tbl_main.ID, tbl_main.Term, tbl_main.Base_Price FROM tbl_main WHERE ((([Product])=[ProductID]));

Bind this combobox to Prod_ID field. Change ColumnCount and ColumnWidths properties.

Now need code in some event(s) to Requery combobox. Should be aware that cascading combobox with alias does not work nice on continuous or datasheet form.

UNBOUND product combobox will show same value for every record so suggest it be moved to form header.

Have Locked TabStop No textbox bound to Product field.

[mindbender]

Nothing shows in product combobox because SQL pulls only one field and combobox is set for 2 columns with widths 0;1". Change to 1 column and no widths.

Comboboxes should NOT be bound to tbl_main fields.

TotalWeight textbox should be Locked and TabStop No.

If you want unique terms for selected product then that is a cascading combobox. Common topic.

SELECT tbl_main.ID, tbl_main.Term, tbl_main.Base_Price FROM tbl_main WHERE ((([Product])=[ProductID]));

Bind this combobox to Prod_ID field. Change ColumnCount and ColumnWidths properties.

Now need code in some event(s) to Requery combobox. Should be aware that cascading combobox with alias does not work nice on continuous or datasheet form.

UNBOUND product combobox will show same value for every record so suggest it be moved to form header.

Have Locked TabStop No textbox bound to Product field.

I think I may be going about it the wrong way then. I attached a sample of what I am seeing the form look like. Basically the users can enter up to three different products and for each one they choose a term and enter a qty. The Base Price is then calculated as the value from the table where the product and term are equal X qty. Then at the top all of the Base Processes are summed.

After I get this step to work it is then on to other steps to get Profit Margin.

You can see my table structure from the last attached database.

Am I going about it all wrong?

[mindbender]

screenshot of form idea

[June7]

With that route, TEMP_TABLE is not needed. Form is UNBOUND, controls are UNBOUND, no data is saved.

Could still use cascading comboboxes.

SELECT Term, Base_Price FROM tbl_main WHERE ((([Product])=[cbxProd1]));

Use each Term combobox GotFocus event to requery itself.

Base_Price textboxes can have expression that looks at combobox field for price. =[cbxTerm1].Column(2)

Then Total Base would add: =Nz(tbxBase1,0) + Nz(tbxBase2,0) + Nz(tbxBase3,0)

Then you might want code that clears each control so user can perform a different calc without have to close form or manually clear each control.

[mindbender]

With that route, TEMP_TABLE is not needed. Form is UNBOUND, controls are UNBOUND, no data is saved.

Could still use cascading comboboxes.

SELECT Term, Base_Price FROM tbl_main WHERE ((([Product])=[cbxProd1]));

Use each Term combobox GotFocus event to requery itself.

Base_Price textboxes can have expression that looks at combobox field for price. =[cbxTerm1].Column(2)

Then Total Base would add: =Nz(tbxBase1,0) + Nz(tbxBase2,0) + Nz(tbxBase3,0)

Then you might want code that clears each control so user can perform a different calc without have to close form or manually clear each control.

Thank you for taking the time to help me out I appreciate it. I cannot seem to get a unique value to return in the combo box. The way my table is set up (and again it may not be the ideal way) it has an id field but there is a row for each product/term/base price combination, so I end up with 4 of each value in the combo boxes. If I remove the ID field from the underlying row source the combo box is empty. Should I have a Product Table, a Term Table and a Base Price table and somehow tie them all together to get the fields to populate properly?

[mindbender]

Thank you for taking the time to help me out I appreciate it. I cannot seem to get a unique value to return in the combo box. The way my table is set up (and again it may not be the ideal way) it has an id field but there is a row for each product/term/base price combination, so I end up with 4 of each value in the combo boxes. If I remove the ID field from the underlying row source the combo box is empty. Should I have a Product Table, a Term Table and a Base Price table and somehow tie them all together to get the fields to populate properly?

Thank you for your help on this June7..I now have the first step complete. Now onto step 2.