Adding combo boxes to frmBudget

[Teddy]

I am trying to create a form called frmBudget but I am having a hard time getting the form to work and do what I want it to do.

These are the fields in frmBudget
ExpenseDate
ExpenseCategory
ExpenseItem
Projected Cost
Actual Cost
Comments

I want to create a combo box for the ExpenseCategory and ExpenseItem fields in frmBudget so I can enter the values that I want to appear in these combo boxes thru another table in datasheet view (let’s call the table TblHidden).

As a result I would like to be able to select a value from the combo box in the ExpenseCategory and ExpenseItem fields of frmBudget and have the selection go into the ExpenseCategory and ExpenseItem fields in TblExpenseCategory and TblExpenseItem, not the table where I am selecting the values from (TblHidden). Is this possible? If so can you tell me how?

Just in case it helps to understand my question, these are my tables and relationships

TblExpenseCategory
ExpenseCategoryID (linked to ExpenseCategoryID in TblExpenseItem)
ExpenseCategory

TblExpenseItem
ExpenseItemID
ExpenseCategoryID (linked to ExpenseID in TblExpense)
ExpenseItem

TblExpense
ExpenseID (linked to ExpenseID in TblExpenseDetail)
ExpenseDate
Comments

TblExpenseDetail
ExpenseDetailID
ExpenseID
ExpenseItemID
Projected Cost
Actual Cost

[ConneXionLost]

Hi Teddy,

Which table/query is frmBudget bound to? Reason I ask is that it's usually a good idea to let your forms deal with one table, or two tables & one relationship at a time. It appears your trying to do everything in just one form. The result will be more or less what you're experiencing with frmBudget now.

Take a second look at your table relationships, then consider building at least two forms.

Cheers,

[Teddy]

Hi ConneXionLost thanks for the input. All the tables are bound to frmBudget, looks like you might have identified the problem. However I tried creating frmBudget with only two tables (see below) however I ran into the same problem I described above. Any thoughts?

tblExpenses
ExpenseID
ExpenseCID link to tblExpenseCategories.ecaExCID
ExpenseDate
ExpenseProjectedCost
ExpenseActualCost
ExpenseComments

tblExpenseCategories
ExpenseCID autonumber primary key
ExpenseTitle
ExpenseItem

[ConneXionLost]

Hi Teddy,

Not knowing anything about your expenses I could be way off mark with this explanation.

Your first arrangement was better with a couple small changes:

tblExpense
ExpenseID (PK)
ExpenseDate
Comments

tblExpenseDetail
DetailID (PK)
ExpenseID (FK from tblExpense)
ItemID (FK from tblItem)
ProjectedCost
ActualCost

tblItem
ItemID (PK)
Category (lookup from tlkpCategory)
ItemDescription

tlkpCategory
Category

In my prior post, I didn't mean that you should use fewer tables in your database, only that you should generally aim for a "one table = one form" arrangement for the sake of making the forms manageable.

For these tables, you should have three forms (lookup tables don't need a form or a Primary Key). First, you should have a form "frmItem" to be able to add items as required, and identify which Category they belong. Second, you should have a form "frmExpense" to identify your record of expenses. Third, you should have a subform "fsubDetail" (that will be embedded into "frmExpense") to be able to itemize the expenses. Each form is bound to it's respective table via a select query built for that purpose. Note that each form ONLY feeds information to it's respective table.

Getting back to your original question. If you want to get fancy with the combo boxes, it would be possible to set up two combo boxes on the subform. The first would be unbound, and list the categories. The second would be bound to the ItemID in tblExpenseDetail. These combo boxes would have to be linked via queries and some VBA to get the record sources to work. Microsoft describes the process here. Ensure you're really clear on the differences between "Control Source" and "Record Source". I'll caution you here that setting this up in a subform is tricky, so unless you really want the challenge, it may just be easier to live with a longer list in one combobox.

Good Luck!

[Teddy]

My goodness ConneXionLost thank you very much! You have helped me tremendously. I am so grateful for your feedback. Everything makes more sense now. Thank you!