Automation

[NightPort]

First let me give a little background on what I am trying to do. I have a form where I enter in data on a publication that my company releases for my department. On that from I have a subform that holds all of the record management data. Now I have a table that holds all the things important to us when it comes to record management. This table holds the company (Auto, Fire), the series name, the series number, retention years for that series, so on and so forth. Now I have two other tables which will hold the data on the publication and then all the relevant data on the publication when dealing with record management. Here is my problem.

On my subform I have a text box(Retention) that is bound to the table where data is being saved in tblRecordMan so I can look up information on the publication that coinsides with it. What I want to do is that when I select a Record Series name from a combo box(this is looking up data from tblRecordMgmt...this table holds the data prviously stated above) I want the Retention to automatically fill in with the coinsiding years to that Series. I know that DLookup won't work because it won't save it to the other table. I think a macro or module should do it, but I am unsure to how I would have to set it up. Any help you provide will be most appreciated.

 

[NightPort]

For lack of response I have to wonder. Is this even possible? or does it have severe logic problems to it?

 

[NightPort]

I thank you Pat. I thought this as well, but my mentor project supervisor or whatever is pretty set in her ways. Hopefully I can convince her to see it the way I did.