input field

[mlbwhf]

Hi guys,
have two questions:

1. i'm trying to great a selection from a drop down, but this drop down have sub category for one of them, like i'm creating a selection whether or not your business or personal, if your business then you have to choose a sub-category (silver, gold, diamond)
2. one of the field i want to do calculation for price which should be number of weekends/weekdays multiply by the rate for each, now the rate is coming from the equipment table, while the number is coming from rent table, if this field i wanted to be in rent table, is that possible?

[rpeare]

1. Let's assume your two field names are called CATEGORY and SUBCATEGORY (look at the field name on your form to get what yours actually are)
Let's also assume you have a table for your categories (tblCategories) and a table for your subcategories (tblSubCategories).

Set the ROW SOURCE of your CATEGORY field to

SELECT * FROM tblCategories

make the BOUND COLUMN of the combo box 1 (or the field that has the category primary key)

Set the ROW SOURCE of your SUBCATEGORY field to

SELECT * FROM tblSubCategories WHERE (Cat_ID = [forms]![formname]![category])

make the BOUND COLUMN of the combo box 1 (or the field that has the subcategory primary key)

In your COLUMN WIDTHS of both combo boxes set the width to 0 of any field you do not want to show when your users are using your form.

Where CATEGORIES is the table that is storing your business category make sure your CATEGORIES and SUBCATEGORIES tables are set up something like

tblCategories
Cat_ID  Cat_Desc
1       Business
2       Personal

tblSubCategories
Sub_ID  Cat_ID  Sub_Desc
1       1       Silver
2       1       Gold
3       1       Diamond

In the AFTER UPDATE event of your CATEGORY field put in this code:

SUBCATEGORY.REQUERY

[mlbwhf]

thanks a lot, what about the 2nd question

[rpeare]

The 2nd can be done a ton of ways and it really depends on your data structure, I'll just caution you do NOT store calculated values in your table. It's a terrible practice do your calculations on forms or reports or queries.

[mlbwhf]

1. Let's assume your two field names are called CATEGORY and SUBCATEGORY (look at the field name on your form to get what yours actually are)
Let's also assume you have a table for your categories (tblCategories) and a table for your subcategories (tblSubCategories).

so now i'm creating two new tables?

Set the ROW SOURCE of your CATEGORY field to

SELECT * FROM tblCategories

make the BOUND COLUMN of the combo box 1 (or the field that has the category primary key)

Set the ROW SOURCE of your SUBCATEGORY field to

SELECT * FROM tblSubCategories WHERE (Cat_ID = [forms]![formname]![category])

make the BOUND COLUMN of the combo box 1 (or the field that has the subcategory primary key)

In your COLUMN WIDTHS of both combo boxes set the width to 0 of any field you do not want to show when your users are using your form.

Where CATEGORIES is the table that is storing your business category make sure your CATEGORIES and SUBCATEGORIES tables are set up something like

tblCategories
Cat_ID  Cat_Desc
1       Business
2       Personal

tblSubCategories
Sub_ID  Cat_ID  Sub_Desc
1       1       Silver
2       1       Gold
3       1       Diamond

In the AFTER UPDATE event of your CATEGORY field put in this code:

SUBCATEGORY.REQUERY

and what about my original field in Customer table, what will i use its value?

[rpeare]

the ROW SOURCE of a combo box controls where the combo box is getting it's information
the CONTROL SOURCE of a combo box is where the data is actually stored.

So in my example if you set the CONTROL SOURCE of your form to be the field you want the data to be stored and the ROW SOURCE will be a static list (to avoid misspellings, or any unintended data) that is maintained in one table.

In terms of your database if you store a 1 or 2 instead of 'Business' or 'Personal' you are cutting down on the amount of information you have to store by a factor of 7 - 8 for that field, it just makes your database more efficient than looking up all of the information from the same table you're entering the information or from a static list which would require you to modify your form every time a new value was needed.

[mlbwhf]

can you give me a way to do #2, because i have to calculate the value.
also i'm maintaining the stock in a table, so if i have the qty in equipment table, and all hire in rent table, what the best way to creat entity for current stock?

[mlbwhf]

now i have the equation but which field i should put it in?

[rpeare]

if you have an equation that shows you what you want put it in a QUERY, make a field and put in your equation, it will automatically give it a name like expr1: <formula>, you just have to change the EXPR1 to a name that means something to you.