[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Access Code to loop through table records and update

Posted on 2014-08-18
6
Medium Priority
?
1,262 Views
Last Modified: 2014-08-18
Hello Experts.

I need your assistance with the following code.  For some reason, the code only updates the first record and will not repeat steps for following records in table.

Would you please assist?

Private Sub cmd_UpdateCheckFields_Click()

Dim MyDB As DAO.Database
Dim MyRst As DAO.Recordset

Set MyRst = CurrentDb.OpenRecordset("tbl_TEMP_RFI")

MyRst.MoveFirst

Do Until MyRst.EOF
    Me.chk_ProductInfo_NDC = chk0(Me.ProductInfo_NDC)
    Me.chk_ProductInfo_ExpectedLaunchDate = chk1(Me.ProductInfo_ExpectedLaunchDate)
    Me.chk_ProductInfo_PkgSize = chk2(Me.ProductInfo_PkgSize)
    
    MyRst.MoveNext
    
Loop
       
MyRst.Close
Set MyRst = Nothing

End Sub

Open in new window


Thank you.
0
Comment
Question by:dhemple
  • 3
  • 2
6 Comments
 
LVL 12

Assisted Solution

by:pdebaets
pdebaets earned 600 total points
ID: 40267974
You'll probably need the .edit and .update methods in there. Try this

Private Sub cmd_UpdateCheckFields_Click()

Dim MyDB As DAO.Database
Dim MyRst As DAO.Recordset

Set MyRst = CurrentDb.OpenRecordset("tbl_TEMP_RFI")

MyRst.MoveFirst

Do Until MyRst.EOF
    MyRst.Edit
    Me.chk_ProductInfo_NDC = chk0(Me.ProductInfo_NDC)
    Me.chk_ProductInfo_ExpectedLaunchDate = chk1(Me.ProductInfo_ExpectedLaunchDate)
    Me.chk_ProductInfo_PkgSize = chk2(Me.ProductInfo_PkgSize)
    MyRst.Update

    MyRst.MoveNext
    
Loop
       
MyRst.Close
Set MyRst = Nothing

End Sub

Open in new window

0
 

Author Comment

by:dhemple
ID: 40268011
Thank you for your quick response. I've entered the Edit and Update commands you suggested and now I'm getting a run time error when the code tries to perform the steps after MyRst.Edit.  "You can't assign a value to this object."  

I was able to update the fields prior to entering the Edit and Update commands.

Any other thoughts? Anyone.  Thank you.
0
 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 1400 total points
ID: 40268026
<<Do Until MyRst.EOF
    Me.chk_ProductInfo_NDC = chk0(Me.ProductInfo_NDC)
    Me.chk_ProductInfo_ExpectedLaunchDate = chk1(Me.ProductInfo_ExpectedLaunchDate)
    Me.chk_ProductInfo_PkgSize = chk2(Me.ProductInfo_PkgSize)
   
    MyRst.MoveNext
   
Loop>>

  Your referring to the controls/fields on the form, which is pointing to a single record.  You want to refer to the fields in the recordset that you opened.

Do Until MyRst.EOF

    MyRst.Edit
    MyRst![<field>] = chk0(Me.ProductInfo_NDC)
    MyRst![<field>]  = chk1(Me.ProductInfo_ExpectedLaunchDate)
    MyRst![<field>]  = chk2(Me.ProductInfo_PkgSize)
    MyRst.Update
   
    MyRst.MoveNext
   
Loop

 And as Peter said, the .edit/.update are needed as this is a DAO record set.

 This is assuming you want to update the records in the record set itself.

Jim.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 12

Expert Comment

by:pdebaets
ID: 40268091
Good catch Jim. I missed the fact that his code was updating form fields instead of the recordset.
0
 

Author Comment

by:dhemple
ID: 40268171
Here's what worked for me.

Private Sub cmd_UpdateCheckFields_Click()

Dim MyDB As DAO.Database
Dim MyRst As DAO.Recordset

Set MyRst = CurrentDb.OpenRecordset("tbl_TEMP_RFI")

MyRst.MoveFirst

Do Until MyRst.EOF

    MyRst.Edit
    MyRst![chk_ProductInfo_NDC] = chk0(MyRst![ProductInfo_NDC])
    MyRst![chk_ProductInfo_ExpectedLaunchDate] = chk1(MyRst![ProductInfo_ExpectedLaunchDate])
    MyRst![chk_ProductInfo_PkgSize] = chk2(MyRst![ProductInfo_PkgSize])
    MyRst.Update
    
    MyRst.MoveNext
    
Loop
       
MyRst.Close
Set MyRst = Nothing

End Sub

Open in new window

0
 

Author Comment

by:dhemple
ID: 40268194
Thanks for your help on this.  I have another issue that surfaced as a result of this solution.  I'll create another question in hopes of a solution.  Thanks again!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

873 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