Advise not to use punctuation/special characters in naming convention. Why an asterisk at beginning of field name?
Field "Referance" is a misspelling of "Reference".
Why does Bo not get a number assigned?
Why are there multiple records for each Auth number?
I think this is going to require VBA if you want to populate all Contact records in one process. Something like:
Code:
Sub SetRef()
Dim db As DAO.Database, rs As DAO.Recordset, strAT As String
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT DISTINCT ServiceUser_ID FROM tbl_Temp_Invoice_Holding WHERE Referance IS NULL AND ServiceUser_ID<>52935426")
Do While Not rs.EOF
strAT = DMin("TriCare_TA_Auth", "tbl_TriCare_TA_Auths", "DateUsed IS NULL")
db.Execute "UPDATE tbl_TEMP_Invoice_Holding SET Referance='" & strAT & "' WHERE ServiceUser_ID=" & rs!ServiceUser_ID
db.Execute "UPDATE tbl_TriCare_TA_Auths SET DateUsed = Date() WHERE TriCare_TA_Auth = '" & strAT & "'"
rs.MoveNext
Loop
End Sub