Eljefegeneo
Still trying to learn
- Local time
- Today, 04:39
- Joined
- Jan 10, 2011
- Messages
- 904
I am trying to update a number field in a table based on a number from a second table as shown below. I have tblMain with a field called [ClientID]. This field is updated on new records by
I have records in a second table, "tblNewNames", that I would like to add to tblMain updating the field [ClientID] by adding a sequential number per the code above.
I attempted to do this via a simple append query but it will not transfer the names because I do not have a corresponding field in tblNewNames to transfer to [ClientID]. So I created a field in tblNewNames, [NumberUSAGen] to correspond to [ClientID].
I then created a query to select those records I would like to transfer from tblNewNames to tblMain, "qryNewNamesAddClientID"
Having a little experience in looping through a dataset, I created the following. Unfortunately it only updates one record with a new [NumberUSAGen], and it does not even use the query as a recordset.
So, obviously my code is faulty. Is looping the best way to go or is there another way of doing this?
Code:
DMax("[ClientID]", "tblMain") + 1
I attempted to do this via a simple append query but it will not transfer the names because I do not have a corresponding field in tblNewNames to transfer to [ClientID]. So I created a field in tblNewNames, [NumberUSAGen] to correspond to [ClientID].
I then created a query to select those records I would like to transfer from tblNewNames to tblMain, "qryNewNamesAddClientID"
Having a little experience in looping through a dataset, I created the following. Unfortunately it only updates one record with a new [NumberUSAGen], and it does not even use the query as a recordset.
Code:
On Error GoTo ErrorHandler
Dim strSQL As String
Dim rs As DAO.Recordset
strSQL = "qryNewNamesAddClientID"
Set rs = CurrentDb.OpenRecordset(strSQL)
With rs
If Not .BOF And Not .EOF Then
.MoveLast
.MoveFirst
While (Not .EOF)
NumberUSAGen = DMax("[ClientID]", "tblMain") + 1
.MoveNext
Wend
End If
.Close
End With
ExitSub:
Set rs = Nothing
'..and set it to nothing
Exit Sub
ErrorHandler:
Resume ExitSub