Form populating two records in the same table?

[jbaughman]

I may be going about this in completely the wrong way, but I'll try to explain what I'm trying to accomplish.

I have a table - RollTable




I am tracking footage of usage on a roll of paper for printing. I would like to have a form where the operator would only have to put in the beginning information of the new roll, and the form would copy that information as the ending information of the previous roll. So, basically, I want to use the information from the form to populate different fields in different records in the same table.

I think I can do it with a split form, however, I'm just not sure that I would be using the best method to accomplish what I am needing.

Any help would be great!!

Thanks in advance,
Jeff B

[rpeare]

you'll need 2 tables to do what you're after.

1 table will hold your 'main' data
1 table to hold your 'sub' data

It's not clear from your sample data what is your 'main' data (the stuff you don't want to change) and your 'sub' data (the stuff you want your operators to enter).

From there you will likely want to create a form to make it easier for your operators to enter data rather than giving them direct access to your table.

If you're not super confident with access a form/subform arrangement might work best for you.

[jbaughman]

Thank you rpeare for your response.

I was worried about being able to successfully tie the roll data back together later using the two table method, so I went ahead and used the subform method.

The press will only ever have 1 Roll of paper active at any time, so I created a query to pull the record that is missing the ending data.
I then created a subform based on that query on the change roll form.
I created an unbound text box with 'on change' and 'after update' events that feeds it's data into the BegFootage of the new roll and also populates the subform field EndFootage.
I then did the same thing with a combo box that populates the BegRollOperator and the subform field EndRollOperator.

I also hid all the unnecessary stuff from the operators.

Here's the design view of my form (everything in red is hidden):

[rpeare]

You don't need a form dedicated to a press. You can set up your forms so that you can select a press, then show the information related to that press. Then your subform would be a representation of the footage used off that same roll.

So really your table setup would be something like:

tblPresses
PressID  PressNum  PressName----> other press specific information 
1        179       Old Betsy
2        180       Old Hickory

This table would be your list of presses and only the information that is related to the press as a whole

tblPressRolls
RollID  PressID  BegFootage  BegRollOp  RollBegDate
1        1        10000       AAAA       1/1/2013
2        1        10000       DDDD       1/1/2013

this table would be your list of rolls that were started on the press, their initial footage, roll operator and starting date (and time if applicable)

tblRollUsage
UsageID  RollID  EndFootage  RollEndDate  EndRollOp
1        1       7800        1/1/2013     AAAA
2        1       3000        1/1/2013     BBBB
3        1       1500        1/1/2013     CCCC
4        1       0           1/1/2013     DDDD

this table would contain your ending roll balances at the end of each operator's shift

So on your main form, you could select a press from a combo box or list box. Your MAIN form would show the press information and you could select your roll (again from a combo box or list box probably sorted in date/descending order) then add usage for that roll for that press.

Your main form would be based on a table or query using table tblPressRolls
your sub form would be based on a table or query using the table tblRollUsage

They'd be linked through the RollID (an autonumber in tblPressRolls, a number in tblRollUsage (long integer))