Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Not all records being updated with recordset loop

Posted on 2014-01-04
17
Medium Priority
?
523 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 52

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 40

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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
LVL 52

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 52

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 52

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 52

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 40

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 40

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 52

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 52

Expert Comment

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

/gustav
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

824 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