Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Not all records being updated with recordset loop

Posted on 2014-01-04
17
Medium Priority
?
519 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
  • 3
17 Comments
 
LVL 51

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 39

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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 51

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 51

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 51

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
 

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 51

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 39

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 39

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 51

Accepted Solution

by:
Gustav Brock earned 2000 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 51

Expert Comment

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

/gustav
0

Featured Post

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

688 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