Temp table to prevent orphans

[Tvanduzee]

Hello

I'm not sure how to get done, what I need done.
I have a table that will have work order numbers in it (and other data).
I need to be able to generate work orders before the data for them is filled out. The reason is that the office will provide work order forms for the welders in the shop. They fill them out and give it back to the office, where the data is entered into the db.
I do not want to make the table vulnerable to orphans and inconsistent data, so I do not want to generate the work orders in the main table.
I was thinking about creating a temporary holding table to generate the work orders, then when they are filled out and ready to be put in the db, have a form that will allow the user to select the work order number from the temporary holding table, but submit the work order number and all the data into the main table.

One thing more to mention. I will have a form that will generate up to 100 work order numbers at a time. So the work order numbers would be generated using a vba script to interate 1-100 adding 1 to the previous generated number. This I think I can do. But I will need to programmatically find the last Work Order number used in the main table, to see where to start generating numbers. Also, to ensure that the numbers do not currently reside in the temporary WO table.

Is this possible?
If so, where do I start?

Thank you
Terry

[Tvanduzee]

Ok,
I figured out how to get the values into the Temp Table using VBA on my form (as a Module).
Just incase someone else can get some use from my code, here it is.

Sub CreateWONos()

Dim strSQL As String
Dim NoOfWO As Integer
Dim i As Integer
Dim Lnum As Integer
Dim NewWOno As String
'Extract the last 3 numbers from the Work Order Number in order to increment the
'number for creating the next Work Order Number.
Lnum = Right(Forms!qry_WO_WOLast!LastOfWO_WorkOrderNo.Value, 3)

'Extract the first 4 Numbers/Letters of the Work Order Number to that it
'can be concatenated to the generated Lnum.
NewWOno = Left(Forms!qry_WO_WOLast!LastOfWO_WorkOrderNo.Value, 4) & Lnum

'Gets the current WO Number from the query.
NoOfWO = Forms!qry_WO_WOLast!txtNoOfWO.Value

'Initialize the first number of the iteration
i = 0

'Loop while the iterations is equal to the number typed into the form box.
Do While i < (NoOfWO)
'Increment the iteration
i = i + 1

'Concatenate the WO Number to be inserted into the Temp Table.
NewWOno = Left(Forms!qry_WO_WOLast!LastOfWO_WorkOrderNo.Value, 4) & Lnum + i

'SQL Statement to insert the newly generated WO number.
    strSQL = "Insert into TempWO (TempWO_WONumber) Values( " & "'" & NewWOno & "')"

'Prevent the "Insert Row" warning from coming up for each iteration
    DoCmd.SetWarnings False

'Execute the SQL Statement to insert the row.
DoCmd.RunSQL strSQL
Loop

'Set the Warnings back on.
DoCmd.SetWarnings True
End Sub

Now I need to use these numbers (in the new table) in a report so that the Work Order Numbers can be Printed.
Once the Work Orders are completed, they will select those available Work Orders in order to insert the rest of the data in the main table. And then the Work Order number used will be removed from the temp table. Hopefully, this will prevent orphaned Work Order Numbers.

I will post more as I progress, but still looking for easier solutions.

Thank You
Terry