Update query (1 Viewer)

Eljefegeneo · Oct 24, 2016

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

Code:

  DMax("[ClientID]", "tblMain") + 1

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.

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

So, obviously my code is faulty. Is looping the best way to go or is there another way of doing this?

sneuberg · Oct 25, 2016

You could do it as shown in the following code. Note that I changed the While to Do While because that just my way of doing this. You could do this with UPDATE statement rather than open the second recordset if you want. If your Access database is multi user you should lock the tblMain for this.

Code:

Private Sub AddRecords_Click()
On Error GoTo ErrorHandler
  
Dim strSQL As String
Dim rsIn As DAO.Recordset
Dim rsOut  As DAO.Recordset
Dim NextNumber As Long

strSQL = "qryNewNamesAddClientID"
Set rsIn = CurrentDb.OpenRecordset(strSQL)
Set rsOut = CurrentDb.OpenRecordset("tblMain")

NextNumber = Nz(DMax("[ClientID]", "tblMain"), 0) + 1

Do While Not rsIn.EOF

    rsOut.AddNew
    rsOut!ClientID = NextNumber
    rsOut!ClientName = rsIn!NewName
    rsOut.Update
    rsIn.MoveNext
    NextNumber = NextNumber + 1
Loop

ExitSub:

rsIn.Close
rsOut.Close
Set rsIn = Nothing
Set rsOut = Nothing
'..and set it to nothing

Exit Sub
ErrorHandler:
Resume ExitSub
End Sub

If you wish to test this code you can find this code in the attached database.

Eljefegeneo · Oct 25, 2016

Thanks, will try it later. Dinner time now. Also saw your other post on this via email. I did try that and it gave the same number to every record.

Will advise you of the outcome as soon as I try it, but I am sure it will work. Thanks.

sneuberg · Oct 25, 2016

Eljefegeneo said:
Also saw your other post on this via email. I did try that and it gave the same number to every record.

Got the same when I tried it. That's why I deleted the post.

Eljefegeneo · Oct 30, 2016

Thank you. I made a slight modification to the code since I only wanted to update the records in one table, but you assistance was much appreciated and certainly saved my much head bashing.

Update query (1 Viewer)

Eljefegeneo

Still trying to learn

sneuberg

AWF VIP

Attachments

Eljefegeneo

Still trying to learn

sneuberg

AWF VIP

Eljefegeneo

Still trying to learn

Similar threads

Users who are viewing this thread