I am a novice with Access and was assigned a project to create a database for work. My issue is that my coworkers want to be able to see a certain identification number across the board but only enter it once. In this case, this identification number is called "batch number." I have made it the primary key of the first table and a foreign key in following tables, however, I can't figure out how to make it automatically transfer from one table to another without them having to do enter it or choose it from a list.

Any suggestions?

A common setup is to use a form/subform, and let the master/child links handle it (if you use the wizard, it will ask about linking fields).

I was hoping for maybe a vba code so I wouldn't have to redo all of my forms. My coworkers informed me that they wanted this After I created everything.

Hard to say without knowing your setup. Generally you can copy a value from one form to another with:

Forms.TargetFormName.TextboxName = Forms!SourceFormName.TextboxName

Is the data going to be entered in these multiple Forms at different times? If so, your statement "I have made it the primary key of the first table and a foreign key in following tables..." really reinforces what Paul has said; you're talking about a one-to-many relationship and using a Main Form/Subform is the standard way of implementing this.

When your users need to enter data into any Table for a given 'batch number,' they go to that batch number's Record on the Main Form and Access automatically pulls up the corresponding Records on the Subforms for that batch number, either for review or foe entering new Records.

Unless you do it this way, I'm afraid your users are really going to have either enter it manually, which is not really wise, for this type of data, or select it from a Combobox, which is a far better idea, as it will at least prevent the entering of a non-existent number. Stop and think about it; short of a neuro-link interface, ala Clint Eastwood in "Firefox," how else will Access know what batch number the user is thinking about?

As for your concern about redoing all your Forms, it shouldn't really be all that big a deal. You could add a Tabbed Control to your Main Form and place each Subform on its own page, using the Forms you already have as the basis for the Subforms.

If all of the Records for a given batch number are going to be entered at one time, i.e. enter the batch number data in the first Form/Table, move to the second Form/Table and enter data for that batch number, and so on, never having to enter data for a given number again, then you can do this by opening each subsequent Form carrying the batch number forward, using OpenArgs to do do. If this is the case, let us know and we'll walk you thru that scenario.

Linq ;0)>

They will only be entering the data on the first form, so the open argument idea would be wonderful!

How do I go about this?