Need help on loops saving data (1 Viewer)

R

Richard M

Registered User.
Local time
Today, 05:04
Joined
Sep 29, 2010
Messages
75
Having problem with loops. The inner loop updates a table. The outer loop pulls the record number from the "tblChangeOrderTable_Edit_Count" and is assigned to strRecordID . The inner loop uses strRecordID to find the right record. I keep getting errors like (Object variable or With Block variable not set.)

Need help in getting the loops right.

Richard


Code: 
 [FONT=Calibri]Private Sub btnClose_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)[/FONT]
 [FONT=Calibri]Dim db As Database, rs As Recordset, rs1 As Recordset2[/FONT]
 [FONT=Calibri]Dim Criteria As String[/FONT]
 [FONT=Calibri]Dim strAns1 As String[/FONT]
 [FONT=Calibri]Dim strAns2 As String[/FONT]
 [FONT=Calibri]Dim strCount1 As String[/FONT]
 [FONT=Calibri]Dim strAns3 As String[/FONT]
 [FONT=Calibri]Dim strRecordID As String[/FONT]
 [FONT=Calibri]Dim strCTR As String[/FONT]
 [FONT=Calibri]Dim CTR As String[/FONT]
 [FONT=Calibri]DoCmd.SetWarnings False[/FONT]
 [FONT=Calibri]DoCmd.OpenQuery "qryChangeOrderTable_Edit1_Delete"[/FONT]
 [FONT=Calibri]DoEvents[/FONT]
 [FONT=Calibri]DoCmd.OpenQuery "qryChangeOrderTable_Edit1"[/FONT]
 [FONT=Calibri]DoEvents[/FONT]
 [FONT=Calibri]strCTR = DCount("*", "tblChangeOrderTable_Edit_Count")[/FONT]
 [FONT=Calibri]Debug.Print strCTR[/FONT]
 [FONT=Calibri]DoCmd.SetWarnings True[/FONT]
 [FONT=Calibri]If Me.Frame36 = 1 Then 'Single Page[/FONT]
   [FONT=Calibri]‘ Donothing[/FONT]
 [FONT=Calibri]Else[/FONT]
 
     [FONT=Calibri]Set db = CurrentDb[/FONT]
     [FONT=Calibri]Set rs1 = db.OpenRecordset("tblChangeOrderTable_Edit_Count", DB_OPEN_DYNASET)[/FONT]
      [FONT=Calibri]If Me.cboTableNoTable = "T" Then[/FONT]
         [FONT=Calibri]Do[/FONT]
             [FONT=Calibri]Open "tblChangeOrderTable_Edit_Count" For Random As #1[/FONT]
                 [FONT=Calibri]Do While Not EOF(1)[/FONT]
 
                     [FONT=Calibri]strRecordID = rs("[ChangeOrderDataid]")[/FONT]
                     [FONT=Calibri]Set rs = db.OpenRecordset("tblChangeOrderData", DB_OPEN_DYNASET)[/FONT]
                     [FONT=Calibri]Open "tblChangeOrderData" For Random As #2[/FONT]
                     [FONT=Calibri]Do While Not EOF(2)[/FONT]
                          [FONT=Calibri]rs.Edit[/FONT]
                          [FONT=Calibri]If rs("ChangeOrderDataid") = strRecordID Then rs("ChangeOrderDate") = [/FONT][FONT=Calibri]Me!txtChangeOrderDate[/FONT]
                          [FONT=Calibri]If rs("ChangeOrderDataid") = strRecordID Then rs("ContractorDataID") = [/FONT][FONT=Calibri]Me!cboContractor[/FONT]
                          [FONT=Calibri]Debug.Print rs("ChangeOrderDate")[/FONT]
                          [FONT=Calibri]Debug.Print rs("ContractorDataID")[/FONT]
                          [FONT=Calibri]rs.Update[/FONT]
                          [FONT=Calibri]rs.MoveNext[/FONT]
                     L[FONT=Calibri]oop[/FONT]
                     [FONT=Calibri]Close #2[/FONT]
                [FONT=Calibri]rs.MoveNext [/FONT]
                L[FONT=Calibri]oop[/FONT]
                [FONT=Calibri]Close #1[/FONT]
         [FONT=Calibri]Loop[/FONT]
     [FONT=Calibri]End If[/FONT]
[FONT=Calibri][SIZE=3][FONT=Verdana][SIZE=2]      [FONT=Calibri][SIZE=3]S[/SIZE][/FONT][/SIZE][/FONT]et db = Nothing[/SIZE][/FONT]
        [FONT=Calibri][SIZE=3]Set rs = Nothing[/SIZE][/FONT]
        [FONT=Calibri][SIZE=3]Set rs1 = Nothing [/SIZE][/FONT]
 [FONT=Calibri][SIZE=3]End If[/SIZE][/FONT]
 [FONT=Calibri][SIZE=3]End Sub[/SIZE][/FONT]
 
Last edited:
J

JHB

Have been here a while
Local time
Today, 12:04
Joined
Jun 17, 2012
Messages
7,732
In which code line do you get the error?
 
Frothingslosh

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 06:04
Joined
Oct 17, 2012
Messages
3,276
Also, for the love of the FSM, indent your code. It makes it about a thousand times more readable when you do so.
 
R

Richard M

Registered User.
Local time
Today, 05:04
Joined
Sep 29, 2010
Messages
75
Hi,

I get the error at strRecordID = rs("[ChangeOrderDataid]")

Also indented the code to make it easier to read.

I'm not sure if I am using the right type or loops.

Richard
 
J

JHB

Have been here a while
Local time
Today, 12:04
Joined
Jun 17, 2012
Messages
7,732
The problem is that you're asking for a value from the recordset rs, before you are setting rs to anything, (swap the below lines if it really is from rs you want some value) .
strRecordID = rs("[ChangeOrderDataid]")
Set rs = db.OpenRecordset("tblChangeOrderData", DB_OPEN_DYNASET)
Click to expand...
 
R

Richard M

Registered User.
Local time
Today, 05:04
Joined
Sep 29, 2010
Messages
75
JHB the line strRecordID = rs("[ChangeOrderDataid]") should be strRecordID = rs1("[ChangeOrderDataid]"). Thanks for pointing that out.

The strRecordID = rs1("[ChangeOrderDataid]") picks up the first record in "tblChangeOrderTable_Edit_Count" and finds that record in "tblChangeOrderData" and udates the fields. Now a new problem came up where strRecordID does not advance to the next record in "tblChangeOrderTable_Edit_Count." The inner loop keeps going round and round and never exits to its outer loop to pick up the next record in "tblChangeOrderTable_Edit_Count." There are only two records to be updated in "tblChangeOrderData."

What am I missing?

Richard

Code: 
 [SIZE=3][FONT=Calibri]Private Sub btnClose_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]Dim db As Database, rs As Recordset, rs1 As Recordset2[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]Dim Criteria As String[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]Dim strAns1 As String[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]Dim strAns2 As String[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]Dim strCount1 As String[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]Dim strAns3 As String[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]Dim strRecordID As String[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]Dim strCTR As String[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]Dim CTR As String[/FONT][/SIZE]
 
 [FONT=Calibri][SIZE=3]DoCmd.SetWarnings False[/SIZE][/FONT]
 [FONT=Calibri][SIZE=3]DoCmd.OpenQuery "qryChangeOrderTable_Edit1_Delete"[/SIZE][/FONT]
 [FONT=Calibri][SIZE=3]DoEvents[/SIZE][/FONT]
 [FONT=Calibri][SIZE=3]DoCmd.OpenQuery "qryChangeOrderTable_Edit1"[/SIZE][/FONT]
 [FONT=Calibri][SIZE=3]DoEvents[/SIZE][/FONT]
 [FONT=Calibri][SIZE=3]strCTR = DCount("*", "tblChangeOrderTable_Edit_Count")[/SIZE][/FONT]
 [FONT=Calibri][SIZE=3]'Debug.Print strCTR[/SIZE][/FONT]
 [FONT=Calibri][SIZE=3]DoCmd.SetWarnings True[/SIZE][/FONT]
 [FONT=Calibri][SIZE=3]If Me.Frame36 = 1 Then 'Single Page[/SIZE][/FONT]
   [FONT=Calibri][SIZE=3]'donothing[/SIZE][/FONT]
 [FONT=Calibri][SIZE=3]Else[/SIZE][/FONT]
 
    [FONT=Calibri][SIZE=3]Set db = CurrentDb[/SIZE][/FONT]
    [FONT=Calibri][SIZE=3]Set rs1 = db.OpenRecordset("tblChangeOrderTable_Edit_Count", DB_OPEN_DYNASET)[/SIZE][/FONT]
 
   [FONT=Calibri][SIZE=3]'Debug.Print "strRecordID: "; strRecordID[/SIZE][/FONT]
    [FONT=Calibri][SIZE=3]If Me.cboTableNoTable = "T" Then[/SIZE][/FONT]
 
      [FONT=Calibri][SIZE=3]Do[/SIZE][/FONT]
          [FONT=Calibri][SIZE=3]Open "tblChangeOrderTable_Edit_Count" For Random As #1[/SIZE][/FONT]
          [FONT=Calibri][SIZE=3]Do While Not EOF(1)[/SIZE][/FONT]
              [FONT=Calibri][SIZE=3]strRecordID = rs1("[ChangeOrderDataid]")[/SIZE][/FONT]
 
             [FONT=Calibri][SIZE=3]Set rs = db.OpenRecordset("tblChangeOrderData", DB_OPEN_DYNASET)[/SIZE][/FONT]
            [FONT=Calibri][SIZE=3]Criteria = "ChangeOrderDataid=" & strRecordID[/SIZE][/FONT]
            [FONT=Calibri][SIZE=3]Open "tblChangeOrderData" For Random As #2[/SIZE][/FONT]
            [FONT=Calibri][SIZE=3]Do While Not EOF(2)[/SIZE][/FONT]
                [FONT=Calibri][SIZE=3]Debug.Print "strRecordID: "; strRecordID[/SIZE][/FONT]
                [FONT=Calibri][SIZE=3]rs.FindFirst Criteria[/SIZE][/FONT]
                [FONT=Calibri][SIZE=3]rs.Edit[/SIZE][/FONT]
               [FONT=Calibri][SIZE=3]Debug.Print "First: "; rs("ChangeOrderDate")[/SIZE][/FONT]
               [FONT=Calibri][SIZE=3]Debug.Print rs("ContractorDataID")[/SIZE][/FONT]
               [FONT=Calibri][SIZE=3]If rs("ChangeOrderDataid") = strRecordID Then rs("ChangeOrderDate") = Me!txtChangeOrderDate[/SIZE][/FONT]
 [FONT=Calibri][SIZE=3]If rs("ChangeOrderDataid") = strRecordID Then rs("ContractorDataID") =  Me!cboContractor[/SIZE][/FONT]
              [FONT=Calibri][SIZE=3]Debug.Print "Second: "; rs("ChangeOrderDate")[/SIZE][/FONT]
              [FONT=Calibri][SIZE=3]Debug.Print rs("ContractorDataID")[/SIZE][/FONT]
              [FONT=Calibri][SIZE=3]rs.Update[/SIZE][/FONT]
             [FONT=Calibri][SIZE=3]rs.MoveNext[/SIZE][/FONT]
             [FONT=Calibri][SIZE=3]Loop[/SIZE][/FONT]
             [FONT=Calibri][SIZE=3]Set rs = Nothing[/SIZE][/FONT]
            [FONT=Calibri][SIZE=3]Close #2[/SIZE][/FONT]
 
 
        [FONT=Calibri][SIZE=3]rs.MoveNext[/SIZE][/FONT]
 
        [FONT=Calibri][SIZE=3]Loop[/SIZE][/FONT]
        [FONT=Calibri][SIZE=3]Close #1[/SIZE][/FONT]
    [FONT=Calibri][SIZE=3]Loop[/SIZE][/FONT]
 
    [FONT=Calibri][SIZE=3]End If[/SIZE][/FONT]
    [FONT=Calibri][SIZE=3]Set db = Nothing[/SIZE][/FONT]
    [FONT=Calibri][SIZE=3]Set rs = Nothing[/SIZE][/FONT]
    [FONT=Calibri][SIZE=3]Set rs1 = Nothing[/SIZE][/FONT]
 
 
 [FONT=Calibri][SIZE=3]End If[/SIZE][/FONT]
 
 
 [FONT=Calibri][SIZE=3]End Sub[/SIZE][/FONT]
 
Last edited:
J

JHB

Have been here a while
Local time
Today, 12:04
Joined
Jun 17, 2012
Messages
7,732
The first loop doesn't have any conditioner when it is finish.
Have you tried to step through the code by setting breakpoints?
If you can't get it, then post a stripped version of your database with some sample data, (zip it) + how to reproduce the error!
 
N

namliam

The Mailman - AWF VIP
Local time
Today, 12:04
Joined
Aug 11, 2003
Messages
11,695
What JHB said, no way to break the first loop...

On top of that I want to add, readable code = maintainable code... Indent your code properly if you want to make a maintainable database.
On top of that, if not for yourself, you are much more likely to recieve help if your code is readable to the people that are freely donating their time to help you.
 
R

Richard M

Registered User.
Local time
Today, 05:04
Joined
Sep 29, 2010
Messages
75
JHB, No I have not tried using breakpoints. Most of time I use it. But not in this case. That is the first thing I will do today. I will also check the first loop. Are you talking about the most inner loop?

As far as indenting the code, I indent all my code, but when I copied and pasted in this text box and then submitted it, the code went to left align. I think my problem is copying code to Word then copying to this text box and I will copy straight from Access on the next go-round.

Thanks for the patience
 
R

Richard M

Registered User.
Local time
Today, 05:04
Joined
Sep 29, 2010
Messages
75
JHB, I checked the code and found out I had too many loops. Redoing the code made the loops work except for one thing. When the outer loop is performed the strRecordID = rs1("[ChangeOrderDataid]") does not pick up the next record. There are two records 215 and 216 in this sample. but the outer loop doesn't advance to the next record (216). It just does the 215 record again. My problem now is how do I advance to the next record?


Code: 
Private Sub btnClose_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
Dim db As Database, rs As Recordset, rs1 As Recordset2
Dim Criteria As String
Dim strAns1 As String
Dim strAns2 As String
Dim strCount1 As String
Dim strAns3 As String
Dim strRecordID As String
Dim strCTR As String
Dim CTR As String
     DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryChangeOrderTable_Edit1_Delete"
        DoEvents
        DoCmd.OpenQuery "qryChangeOrderTable_Edit1"
        DoEvents
        strCTR = DCount("*", "tblChangeOrderTable_Edit_Count")  'sets up how many records are in "tblChangeOrderTable_Edit_Count"
        
    DoCmd.SetWarnings True
        If Me.Frame36 = 1 Then 'Single Page
        'donothing
        Else
        
        Set db = CurrentDb
        
        
        'Debug.Print "strRecordID: "; strRecordID
            If Me.cboTableNoTable = "T" Then
            CTR = 0
            
                          Do
                            Set rs1 = db.OpenRecordset("tblChangeOrderTable_Edit_Count", DB_OPEN_DYNASET)
                            strRecordID = rs1("[ChangeOrderDataid]")
                             Criteria = "ChangeOrderDataid=" & strRecordID
                            Set rs = db.OpenRecordset("tblChangeOrderData", DB_OPEN_DYNASET)
                            
                            
                            Debug.Print "strRecordID: "; strRecordID
                            rs.FindFirst Criteria
                            rs.Edit
                            Debug.Print "First: "; rs("ChangeOrderDate")
                            Debug.Print rs("ContractorDataID")
                            If rs("ChangeOrderDataid") = strRecordID Then rs("ChangeOrderDate") = Me!txtChangeOrderDate
                            If rs("ChangeOrderDataid") = strRecordID Then rs("ContractorDataID") = Me!cboContractor
                            Debug.Print "Second: "; rs("ChangeOrderDate")
                            Debug.Print rs("ContractorDataID")
                            rs.Update
                           
                            Set rs = Nothing
                            rs1.MoveNext
                            
                                                       
            
                
            Debug.Print "outerloop"
            'Loop
            
            
            CTR = CTR + 1
            Debug.Print " ctr: "; CTR
            If CTR = strCTR Then Exit Do
            Loop
            Close #1
            End If
                Set db = Nothing
                
                Set rs1 = Nothing
            
               
    End If
       
  
End Sub
 
J

JHB

Have been here a while
Local time
Today, 12:04
Joined
Jun 17, 2012
Messages
7,732
Richard M said:
...When the outer loop is performed the strRecordID = rs1("[ChangeOrderDataid]") does not pick up the next record.
... but the outer loop doesn't advance to the next record (216). It just does the 215 record again. My problem now is how do I advance to the next record?
Click to expand...
Have you used breakpoints this time and followed the code step by step?
Didn't you recognize that you are setting rs1 to the same again and again because you've it in the inner loop, (so it would always point to the first record even if you've a MoveNext in your code)?
As other mention, you code is very difficult to read, because your indents are flying back and forth!
Code: 
Private Sub btnClose_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
  Dim db As Database, rs As Recordset, rs1 As Recordset2
  Dim Criteria As String
  Dim strAns1 As String
  Dim strAns2 As String
  Dim strCount1 As String
  Dim strAns3 As String
  Dim strRecordID As String
  Dim strCTR As String
  Dim CTR As String
  
  DoCmd.SetWarnings False
  DoCmd.OpenQuery "qryChangeOrderTable_Edit1_Delete"
  DoEvents
  DoCmd.OpenQuery "qryChangeOrderTable_Edit1"
  DoEvents
  strCTR = DCount("*", "tblChangeOrderTable_Edit_Count")  'sets up how many records are in "tblChangeOrderTable_Edit_Count"
  DoCmd.SetWarnings True
  If Me.Frame36 = 1 Then 'Single Page
    'donothing
  Else
    Set db = CurrentDb
    'Debug.Print "strRecordID: "; strRecordID
    If Me.cboTableNoTable = "T" Then
      CTR = 0
      Do
[B][COLOR=Red]        'You should have the below line outside the loop
        Set rs1 = db.OpenRecordset("tblChangeOrderTable_Edit_Count", [/COLOR][/B]DB_OPEN_DYNASET)
        strRecordID = rs1("[ChangeOrderDataid]")
        Criteria = "ChangeOrderDataid=" & strRecordID
        Set rs = db.OpenRecordset("tblChangeOrderData", DB_OPEN_DYNASET)
        Debug.Print "strRecordID: "; strRecordID
        rs.FindFirst Criteria
        rs.Edit
        Debug.Print "First: "; rs("ChangeOrderDate")
        Debug.Print rs("ContractorDataID")
        If rs("ChangeOrderDataid") = strRecordID Then rs("ChangeOrderDate") = Me!txtChangeOrderDate
        If rs("ChangeOrderDataid") = strRecordID Then rs("ContractorDataID") = Me!cboContractor
        Debug.Print "Second: "; rs("ChangeOrderDate")
        Debug.Print rs("ContractorDataID")
        rs.Update
        
        Set rs = Nothing
        rs1.MoveNext
        Debug.Print "outerloop"
        'Loop
        CTR = CTR + 1
        Debug.Print " ctr: "; CTR
        If CTR = strCTR Then Exit Do
      Loop
      Close #1
    End If
    Set db = Nothing
    Set rs1 = Nothing
  End If
End Sub
 
N

namliam

The Mailman - AWF VIP
Local time
Today, 12:04
Joined
Aug 11, 2003
Messages
11,695
Readable code is good code notice the significant difference betweter your post and jhb's
 
R

Richard M

Registered User.
Local time
Today, 05:04
Joined
Sep 29, 2010
Messages
75
Thanks JHB for your help, It worked! Now I will add this to my own help list on loops.
 
You must log in or register to reply here.

Similar threads

S
Issue with Access Data Right after saving
Replies
11
Views
587
jdelano
J
N
Solved Big variation when sending data through Cloud SQL Server Vs MS Access
Replies
10
Views
411
Pat Hartman
P
W
Trouble Accessing Linked Tables in Access 2021 via OLEDB Connection: 'Could Not Find Installable ISAM' Error
Replies
6
Views
654
wardah
W
B
Retrieve 6 numbers from string
Replies
2
Views
228
arnelgp
arnelgp
mloucel
Solved SubForm [Continuous Form] not updating data.
Replies
18
Views
257
mloucel
mloucel

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom