Need Auto-fill of fields in Table (to enhance data entry and use values as historical

[LtDan]

Hi all, thanks in advance for your time. This is for Access 2010. First post to a forum like this. I am well versed in Excel but only just discovered Access to improve my workflow and handling of data. I understand some of the basic terminology and have been building my DB design slowly, trying to think ahead and future proof. I’ve been doing a lot of searching and maybe I am missing something here or using the wrong language and search terms.

Here's my problem. It’s twofold really. First, I just want to be able to auto-fill fields based on other data lookups (I select an ID # and it fills in names and contact info etc) and second, for that data to remain part of the record in the table (to not change because of changes to the Parent table). So I can’t just have it in a form as a viewable reference with the different controls I’ve been reading about.

Lets say I have 4 tables.

Regions
Staff


Studios
Status Changes

I know how to make a Lookup field to quickly fill one item at a time based on Tables and Queries I’ve established. I really like this aspect of Access being a big user of VLookup in Excel. Anyways, I get the relational parts to this and the need to be normalized. But, there is one table that I don't want to be normalized entirely. Let me explain. I want the lookup when completing a new record, but then I want the data to stay parked in the record indefinitely then on unless I change it at the record level (I don't want the Parent changes to come down to the Child). I think I know how to do that part, but in the context of everything I am look for the best way.

Now, Status Changes (tbl) holds records of job activity of employees (promoted, demoted, transferred etc plus all the other details I need to know about who, where and so forth). There is relevant information that I need to lookup and enter from Studios, Regions and Staff tables to place in the Status Change record. Looking that up one at a time works of course. And I understand you don't want anything duplicated in other records (like adding the studio address and phone # etc to the records of Status Change when they are in Studio tbl). The analogy I read all the time is, "what if the studio phone number changes? You want it to change everywhere else". Well, I actually don’t. That is true for most aspects and this is certainly something I get and leverage as much as possible in other tables (this is one of the main reasons for me using Access), but this particular need is different.

If I am adding a new record to Status Change, I have the Staff name in there (from Staff tbl), some other fields like dates and info surrounding the action are entered, but after I lookup the Region ID (from Region tbl) and place that in its corresponding field, I also want the CURRENT Regional Manager name that belongs to the Region to automatically end up in another field. I say current because I want the present data when I lookup/add the data to the records, but later on I don’t want that fact to change because of operational changes elsewhere (changes to the Parent).

The real problem is that if Jane Doe is the Regional Manager of a territory today what happens if that changes? In 6 months, we will grow the business, restructure the Regions, and shuffle what studios belong to what Regions, and Regional Managers will move to new territories or be added. So I will add new records to the Region tbl to grow it but also shuffle Staff and Studios around. If I make changes in those Parent tables that would impact the Child tbl, Status Change records (I don’t want it to mess with things in the past). To me, these are historical events like sales transactions that hold import details and metrics year after year to compare or check up on. And I want to know who was involved then, not who is in that role now when I reflect on that older data. So I want current data to auto-fill now (for accuracy, speed etc), and later to be left in place even if the current dynamic changes for the lookup tables.

I would like to be able to understand how you auto-fill in a table. I have figured out how to get it so the data doesn’t change if the Parent values are modified, but ultimately I cannot figure out for the life of me how to auto-fill.

If I am totally backwards here, I apologize. I am sure there may be other ways or suggestions too of how I should set up the tables and data, so don’t hold back. I am OK with some functions and so forth and if you give me clear guidance I can get there. I am also fine to paste in some code for a Macro, but I am by no means ready to tackle VBA scripting or anything.

Have a great day and thanks again!!

[LtDan]

Ok so I found another thread from another site that is getting close to a solution and has the same need I do, but I don't understand how to get from point A to point B here... how do I create these After Update commands with the builders?

">These addresses,
>phone numbers, etc. do in fact change somewhat with time, but the
>records should not reflect these changes, but should reflect what the
>data was at the time the order was placed.

Ok... if that is in fact the case you have a reason to store the formally
"redundant" data (it's not redundant if the meaning of the field is "this
person's address as of January 8, 2010" rather than "this person's address").

You can "push" data from a combo box into other controls on the form in the
combo's AfterUpdate event. Base the combo on a query containing all of the
fields that you want to record (they can be of zero width in the combo's
ColumnWidths property if you don't want them cluttering the dropdown).

In the AfterUpdate event you could use code like

Private Sub cboPerson_AfterUpdate()
If Not IsNull(Me!cboPerson) Then ' if the user selected someone...
Me!txtLastName = Me!cboPerson.Column(1)
Me!txtFirstName = Me!cboPerson.Column(2)
...
Me!txtPhone = Me!cboPerson.Column(9)
End If
End Sub

to copy columns 2 through 10 into textboxes."

Please help.