New AutoNumber for Sub table

[tl mike]

I am having problems with an invoicing program I am trying to setup. I currently have an autonumber for the OrderPK which the order details or line items are linked to the order via the PK(primary key)/FK(foreign key).

The problem arises When I go to invoice the order. If the order is shipped incomplete a Back order is created which then creates a new PK. I need some help on getting the new order PK to put into the FK for the order details.

My current code looks like this.

Private Sub cmdInvoice_Click()
 
'Check for invoice number and invoice date
If IsNull([InvoiceNo]) Or IsNull([InvoiceDate]) Then
MsgBox "Invoice number and date are required.", vbExclamation
Exit Sub
End If
[Invoiced] = True 'set invoiced flag
'Select record set
Dim rs As Recordset, blnBackOrder As Boolean
Set rs = CurrentDb.OpenRecordset("Select * From qryOrderDetails Where OrderFK=" & [OrderPK] & "")
With rs
'Check for Qunatity shipped
Do While Not .EOF And Not blnBackOrder
If Nz(!QtyShipped, 0) < !QtyOrdered Then blnBackOrder = True
.MoveNext
Loop
'Create Backorder
Dim strOrderPK As String, strProformaNo As String, strCustID As String, strSQL As String, strBackOrderPK As String
strOrderPK = [OrderPK]
strBackOrderPK = [OrderPK]
strBackOrderPK = [CustomerOrderNumber] & "BO"
 
If blnBackOrder Then
    DoCmd.SetWarnings False
    DoCmd.RunSQL "Delete * From tblTempOrder" 'Clear temp order table
    DoCmd.RunSQL "Insert Into tblTempOrder Select * From tblOrder Where OrderPK=" & [OrderPK] & "" 'Insert data to Temp table linking order number
    DoCmd.RunSQL "Update tblTempOrder Set CustomerOrderNumber='" & strBackOrderPK & "', BOOrderFK='" & strBackOrderPK & "', Invoiced=False,InvoiceNo=Null,InvoiceDate=Null" 'change invoice info
    DoCmd.RunSQL "Update tblTempOrder Set OrderPK=Null"
    DoCmd.OpenQuery "apdOrder", acViewNormal, acAdd
 
 
    Me.Refresh 'refresh Orders table
    .MoveFirst
 
'Insert backorder details into order details table
Dim strNewOrderPK As String
[B][COLOR=red]strNewOrderPK =[/COLOR][/B]

    Do While Not .EOF
    If Nz(!QtyShipped, 0) < !QtyOrdered Then
    strSQL = "Insert Into tblOrderDetail Values ('" & strNewOrderPK & "','" & !PartNumberFK & "'," & !DiscountedPrice & "," & (!QtyOrdered - Nz(!QtyShipped, 0)) & ",Null)"
    DoCmd.RunSQL strSQL
    End If
    .MoveNext
    Loop
End If
End With
'refresh order details subform
Me.Requery
If blnBackOrder Then 'go to back order
Set rs = Me.Recordset.Clone
rs.FindFirst "[BOOrderFK]='" & strBackOrderPK & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End If
DoCmd.OpenReport "rptOrders", acViewPreview, , "OrderNo='" & strOrderPK & "'", , "Invoice"
End Sub