Solved

Not all records being updated with recordset loop

Posted on 2014-01-04
17
470 Views
Last Modified: 2014-01-08
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
Option Explicit

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"
    Exit Sub
   
    End If
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
    With rs1
        .AddNew
        !SvcCode = rs!SvcCode
        !Service = rs!Service
        !Present = rs!Present
        !SampleID = rs!SampleID
        .Update
        End With
        rs.MoveNext
Loop

'user feedback that the process worked
If rs.RecordCount = 0 Then
    MsgBox "You didn't select any tests."
Else
    MsgBox "Done! " & rs.RecordCount & " services added."

Exit Sub
End If


myerror:
If Err.Number = 3167 Then
    Resume Next
    Else
    MsgBox Err.Number & " " & Err.Description & "has occurred."
    Exit Sub
End If
End Sub
0
Comment
Question by:esbyrt
  • 7
  • 7
  • 3
17 Comments
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39756633
For some reason SampleID is empty for all but the first record.
Try inserting this line:

        !Present = rs!Present
    Debug.Print "SampleID: ", rs!SampleID
        !SampleID = rs!SampleID


and study the output.

/gustav
0
 

Author Comment

by:esbyrt
ID: 39756713
SampleID:     Null
SampleID:     Null
SampleID:      5

SampleID:     Null
SampleID:      5
SampleID:     Null

This is what I see in the immediate window after running it twice.  The first time it added the first record and left the next 2 blank.  The second time it added the middle record and left the first and last blank.  5 is the correct sampleID.  Ideas?
Thanks!
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 39757011
If you run the select query by itself, what do you get?

Select * from temptblServices where present = true;
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39757036
OK. That shows that your posted code is correct and functioning. It can and will only fill in the sample ID when it has a value - like 5 in this case.

Thus, the error is located in your form or function where you select or add records to table temptblServices. It only fills sample ID to one record.

/gustav
0
 

Author Comment

by:esbyrt
ID: 39757667
Hm the problem must be with the combo box where I select the SampleID.  I put the SampleID field in my continuous form to test it.  When I select some services and then the sample ID from the combo box it only puts the SampleID in the first record of the continuous form.  The combo box is based on another table, tblResults, which is actually where the data is going.  I need to be able to choose a SampleID to update the records.  I'm guessing I need to be able to add it to the current recordset in order to update properly but I'm not sure how to do that.  Ideas?
Thanks for having a look!
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39757932
You could run a second loop:

Dim varSampleID As Variant
varSampleID = Null
Do Until rs.EOF 'keep adding until all entries are done
    ' Record a SampleID not Null.
    varSampleID = Nz(varSampleID, rs!SampleID)
    With rs1
       .AddNew
        !SvcCode = rs!SvcCode
        !Service = rs!Service
        !Present = rs!Present
        .Update
        End With
        rs.MoveNext
Loop
rs.MoveFirst
Do Until rs.EOF 'Update SampleID.
    With rs1
        .Edit
        !SampleID = varSampleID
        .Update
        End With
        rs.MoveNext
Loop

But it would be neater to redesign your form.

/gustav
0
 

Author Comment

by:esbyrt
ID: 39760874
When I try that code I get a "no current record" error.  Debug highlights edit in the lines below.

  With rs1
        .Edit
        !SampleID = varSampleID
Any ideas?
Thanks!
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39761330
Yes, sorry. It is rs1 to handle after the first loop:

With rs1
    .MoveFirst
    Do Until .EOF 'Update SampleID.
        .Edit
            !SampleID = varSampleID
        .Update
        .MoveNext
    Loop
End With

/gustav
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:esbyrt
ID: 39762559
Okay here's the updated code.  When I run my form and click add to sample I get the confirmation that records have been added, and they have, but now none of them have the sampleID.  Do I have something in the wrong order or missed some syntax somewhere?

Thanks!


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"
    Exit Sub
   
    End If
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

Dim varSampleID As Variant
varSampleID = Null

Do Until rs.EOF 'keep adding until all entries are done
' Record a SampleID not Null.
    varSampleID = Nz(varSampleID, rs!SampleID)
    With rs1
        .AddNew
        !SvcCode = rs!SvcCode
        !Service = rs!Service
        !Present = rs!Present
           Debug.Print "SampleID: ", rs!SampleID
    '    !SampleID = rs!SampleID
        .Update
        End With
        rs.MoveNext
Loop
With rs1
.MoveFirst
Do Until rs.EOF 'Update SampleID.

        .Edit
        !SampleID = varSampleID
        .Update
        .MoveNext
Loop
End With

'user feedback that the process worked
If rs.RecordCount = 0 Then
    MsgBox "You didn't select any tests."
Else
    MsgBox "Done! " & rs.RecordCount & " services added."

Exit Sub
End If


myerror:
If Err.Number = 3167 Then
    Resume Next
    Else
    MsgBox Err.Number & " " & Err.Description & "has occurred."
    Exit Sub
End If
End Sub
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39762695
Try inserting this line and watch the output:

    ' Record a SampleID not Null.
    varSampleID = Nz(varSampleID, rs!SampleID)
    Debug.Print "var:", varSampleID, "rs:", rs!SampleID
 
/gustav
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 39762842
Two days ago I suggested that you run the input query by itself and review the results.  Did you do that?  Do all the records have SampleID filled in?
0
 

Author Comment

by:esbyrt
ID: 39763291
This is what I get when i add 3 services to sample ID 5.

var:          Null          rs:           Null
SampleID:     Null
var:          Null          rs:           Null
SampleID:     Null
var:           5            rs:            5
SampleID:      5
0
 

Author Comment

by:esbyrt
ID: 39763312
PatHartmen - I did see your comment but I'm not sure how to go about it.  My form is based on a temporary table in order to choose multiple dynamic items as in this article http://www.experts-exchange.com/A_6692.html.  I have an unbound textbox that lists the service categories and a command button that loads services for that category.  I can then select services, choose a sampleID to add to and click a command button that does that (that command button is the code I have posted here).  Here is a screen shot of my form.
Capture.PNG
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 39763414
temp tables are real.  Put a stop in the code just before you attempt to open the table.  Copy and paste the SQL string into the SQL view of the QBE and run it.  I think you will see that the SampleID is empty.  That means you need to go back to wherever that temp table is being made and fix the problem.
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 39764420
The sample id exists:

var:           5            rs:            5
SampleID:      5

> Do Until rs.EOF 'Update SampleID.
This is not from me. The error is, that you didn't use my code.
Correct your code 100% to that I posted and it will work:

With rs1
    .MoveFirst
    Do Until .EOF 'Update SampleID.
        .Edit
            !SampleID = varSampleID
        .Update
        .MoveNext
    Loop
End With

/gustav
0
 

Author Closing Comment

by:esbyrt
ID: 39766625
That did the trick!  Thanks so much.  I'm not sure where that extra "rs"  came from but getting rid of it, the code works perfectly now.  Wonderful!
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39766682
Great. You are welcome!

/gustav
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now