I am designing an sales expense tracking system and I would like some opinions on a design decision.
In my expense lines table, I need to have an expense type code. E.g. 1 = Meals, 2 = auto expense etc. I can create a lookup of expense types but I also need to store an general ledger account number. E.g.
ExpenseID Type GLCode
1 Meals 5050
When the user is entering the data for the lines, they need to select the expense type from a drop down. This is all pretty straight forward so far. However, the GLCode could be changed in the future. So if I was to look back at the expenses and the associated GL account it was charged to, I might be looking at inaccurate information.
In VB 6, what I would normally do is store the GL account number in the Expense lines table so it will be accurate throughout the life time of the record. The way I would normally do this is setup a combo box with hidden fields to contain the GL account number as well as the Expense Type ID and the description. When the user selects an entry from the combo box, I would simply use some code to pickup the hidden value out of the combo box thus preventing additional reads from the database. In VB2008, you can only have 2 columns of data in a combo box. So how could I achieve the same functionality.
Thanks
In my expense lines table, I need to have an expense type code. E.g. 1 = Meals, 2 = auto expense etc. I can create a lookup of expense types but I also need to store an general ledger account number. E.g.
ExpenseID Type GLCode
1 Meals 5050
When the user is entering the data for the lines, they need to select the expense type from a drop down. This is all pretty straight forward so far. However, the GLCode could be changed in the future. So if I was to look back at the expenses and the associated GL account it was charged to, I might be looking at inaccurate information.
In VB 6, what I would normally do is store the GL account number in the Expense lines table so it will be accurate throughout the life time of the record. The way I would normally do this is setup a combo box with hidden fields to contain the GL account number as well as the Expense Type ID and the description. When the user selects an entry from the combo box, I would simply use some code to pickup the hidden value out of the combo box thus preventing additional reads from the database. In VB2008, you can only have 2 columns of data in a combo box. So how could I achieve the same functionality.
Thanks
