Not all records being updated with recordset loop
Posted on 2014-01-04
In the following code, all is updating okay except for the SampleID. If I choose 3 services to add, Service, serviceCode and Present will all update properly to the 3 records but SampleID only updates in one record and then leaves that field blank in the other 2. Can anyone tell from this code what might be wrong?
Thanks for having a look!
Option Compare Database
Private Sub cmdAddtoSample_Click()
On Error GoTo myerror
Dim db As Database
Dim rs As Recordset
Dim rs1 As Recordset
Dim itm As Variant
Me.Dirty = False 'save all changes on the form as a precaution
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "You haven't loaded any data to add"
Set db = CurrentDb
Set rs = db.OpenRecordset("Select * from temptblServices where present = true;", dbOpenDynaset) 'user chosen entries to add
Set rs1 = db.OpenRecordset("Select * from tblResults where 1=2;", dbOpenDynaset) ' the place where they are going
Do Until rs.EOF 'keep adding until all entries are done
!SvcCode = rs!SvcCode
!Service = rs!Service
!Present = rs!Present
!SampleID = rs!SampleID
'user feedback that the process worked
If rs.RecordCount = 0 Then
MsgBox "You didn't select any tests."
MsgBox "Done! " & rs.RecordCount & " services added."
If Err.Number = 3167 Then
MsgBox Err.Number & " " & Err.Description & "has occurred."