Here is an example VBA sub that is intended to automate the process as I described in my previous post. I've highlighted items in red that you will need to change to correspond to your table and field names, do this carefully! I noticed in your example data that in one table the field was [PO Number] while the other table was [PO_Number], small differences like that can lead to headaches. (This relationship should really be an ID field btw)
This is untested. If you want to try this do so in a backup database!
Code:
Private Sub BatchAssignInvoicesToPO()
On Error GoTo ErrHandler
Dim db As DAO.Database
Dim rsClientIDs As DAO.Recordset
Dim rsPO As DAO.Recordset
Dim qry As String
Set db = CurrentDb
'Get a list of Clients by ID that have unassigned invoices
qry = "SELECT DISTINCT [ClientID] FROM [Invoice Table Name] WHERE [PO Number] Is Null;"
Set rsClientIDs = db.OpenRecordset(qry, dbOpenSnapshot)
'If the list is not empty
If Not (rsClientIDs.BOF And rsClientIDs.EOF) Then
'move to the first record in the list (maybe not necessary? nothing wrong w/ being explicit)
rsClientIDs.MoveFirst
'loop through list of client IDs until we reach the end
Do While Not rsClientIDs.EOF
'lookup an unused PO Number. EDIT: using 'order by' clause to get the next sequential po_number
qry = "SELECT TOP 1 [PO_Number], [DateUsed] FROM [PO Table Name] WHERE [DateUsed] Is Null AND [ClientId] = " & rsClientIDs![ClientId] & " ORDER BY [PO_Number] ASC;"
Set rsPO = db.OpenRecordset(qry, dbOpenDynaset)
'make sure we actually found an unused PO
If Not (rsPO.BOF And rsPO.EOF) Then
'assign all unassigned invoices for this client to the PO
qry = "UPDATE [Invoice Table Name] SET [PO Number] = '" & rsPO![PO_Number] & "' WHERE [PO Number] Is Null AND [ClientID] = " & rsClientIDs![ClientId]
db.Execute qry, dbFailOnError
'set the date used on the PO
rsPO.Edit
rsPO![DateUsed] = Date
rsPO.Update
End If
rsPO.Close
rsClientIDs.MoveNext '<-- EDIT: Don't forget this or you'll get hung up in an infinite loop... don't want to cause a gravitational singularity, that was a close one!
Loop
End If
rsClientIDs.Close
ExitHandler:
Set rsClientIDs = Nothing
Set rsPO = Nothing
Set db = Nothing
Exit Sub
ErrHandler:
MsgBox Err.Description, , "BatchAssignInvoicesToPO ERROR #" & Err.Number
Resume ExitHandler
End Sub
Does your [PO Number] field allow zero length values in the invoice table?