I'm not even quite sure where to ask this question, but here goes:

I am trying to set up a table and a form to track ordering of equipment via Purchase Orders.

I am using the tables for people and agency divisions as linked tables in this database, linked from another database. All the communication between the 2 databases works fine, as at this point I have only 1 form and 1 table for this information. Here's my problem: Both with people and with divisions, the data in the combo boxes, and thus the contents of the fields that is displayed may change: For example, A division may be renamed. This isn't a problem for new entries, but I don't want the Division Name changing on entries that are 2 or 3 (or more) years old. I'm not sure how to track this. For instance, a division called "Budget" may be renamed to "Fiscal Management" in a couple of years. I'd like any old stuff to display the information from what the Division name was when it was originally entered and processed.

Does anyone have any thoughts on how to approach this?

My biggest problem is that I'm trying to make the data entry for the individual who uses this as easy as possible by using a couple of combo boxes. By doing that I've created this little problem for myself.

-- Steve Olson

Seems to me the simple answer is to store that denormalised information along with the order. I assume your combo has multiple columns; one for an ID and one for a name, and I imagine you display the name column.

When the user selects an entry from the combo, you can use the following construct to extract the data you want to keep.



The first column is number zero (0); the second column is one (1), and so on. Just store the name column (or whatever you want) in an additional field in the table.

Does that make sense?

Your idea more or less echoes my thoughts on it. I can't think of a way of simplifying her data entry besides using the 2 combo boxes as look-ups. It doesn't provide a simple way of storing the information other than what you've suggested.

Looking at the problem a bit more, I think I'm going to have to check for the current date not being more than 180 days after the date the order was placed. I'd hate for the woman using this to inadvertantly change something by just bumping the combo to another value, then having the Afterupdate event of the combo change it.

Actually, a better idea might be if my holding field is empty, then go ahead and update it with the display value of the combo, otherwise make sure she wants to do it... That might do it.

What I tend to do isn cases like this is to disable the control if certain criteria are met. For example, in the form's Current event, check the date and if it's > 180 days after order placement, disable the control.