Populating a hard coded form number

[mwilliams4]

I have a database which has a Orders / Lineitems structure.

For each Order number certain lineitens are associated.

Each order is dot matrix printed on a triplicate state form that are sequentially ordered that can only accomodate 4 lineitems.

So this is an example;

order lineitem Form number
00001 xxxxx1 123456780
00001 xxxxx2 123456780
00001 xxxxx3 123456780
00001 xxxxx4 123456780

00001 xxxxx5 123456781
00001 xxxxx6 123456781

00002 xxxxx1 123456782

00003 xxxxx1 123456783
00003 xxxxx2 123456783
00003 xxxxx3 123456783

What is the easiest way to update the form number used in the lineitem table?

[Rabbit]

Update the form number to what? I'm confused as to what you're updating and what you want to update it to and how you want to tie those two elements together.

[mwilliams4]

lineitem form number is blank initially in the lineitems table as it has not printed yet, then after printing the actual number of the form needs to get populated back to the lineitems table. They are continuous forms, so form number123456780 is captured once (in a form), then needs to get populated back using the logic of 4 per page, then next form number. sorry, it's really hard to explain.

[Rabbit]

You have a couple of options.

You can either use VBA to step through the recordset row by row and assign the form numbers.

Or you can join the table to itself to create a ranking column that you use to assign the form numbers.

Pick whichever method you feel most comfortable using.