Link to home
Start Free TrialLog in
Avatar of puppydogbuddy
puppydogbuddy

asked on

Continuous subform using a DAO recordset/ Access 2000

Hello Experts,
I am stumped on this one, and I would appreciate your expert assistance.  The problem with my current code is that, although the continuous subform in question runs without error, the underlying recordset code does not execute the looping porton of the code.  It processes the first record in the loop, and then stops.

The subform with the problem, is the second of 2 embedded continuous subforms.  The way that it is supposed to work is as follows:  the main form has two cascading combo boxes, which combine to display (on the first subform) a filtered list of account codes associated to a selected line item (example- all general ledger codes for a line item code of 108 entitled “Accounts Receivable”).  The second subform, is intended to enable the user to select from another combo box (one combo for each record displayed on the continuous subform) so that users can change the value of the bound column (line item code) to which each of the accounts are associated, with the change being automatically propagated to all accounts on the filtered list after the first selection is made.  Although I can change each account one by one by making a selection in each combo box, I have not been successful in taking the first selection and propagating it to all of the account codes on the filtered list.  Here is the pertinent code for the second subform.

The SQL code that was used to create the recordset for subform2:
----------------------------------------------------------------------------------------------------------------

strSQLSF2 = "SELECT " & "[tblChartOfAccts]" & "." & varAcctClass & "," & "[tblChartOfAccts]" & "." & varAcctCode2
strSQLSF2 = strSQLSF2 & "," & "[tblChartOfAccts]" & "." & varCafrLink2 & " From [tblChartOfAccts]"
strSQLSF2 = strSQLSF2 & "  WHERE [tblChartOfAccts]" & "." & varAcctCode2 & " IN " & "(Select " & varAcctCode & " From TempQry)"
strSQLSF2 = strSQLSF2 & " ORDER BY " & varAcctCode2


-------------------------------------------------------------------------------------------------------------------
Option Compare Database
Option Explicit

Dim iOldBoundColumnValue As Integer
Dim iNewBoundColumnValue As Integer
Dim sNewBoundColumnDescr As String

Private Sub cboAcctClass_Change()
On Error GoTo Error_Routine

Dim dBs As DAO.Database
Dim rst As DAO.Recordset

Dim iRecCount As Integer             'record count
Dim intReturn As Integer               'return from message box

Me.Parent!cboInquiryFinder.Requery
Me.Parent!frmChartUpdate_DetailCtl.Form![txtAcctTitle].Requery

'open recordset clone to handle combo box processing
Set dBs = CurrentDb()
Set rst = Forms("frmChartUpdate_Main").Controls("frmChartUpdate_DetailCtl2").Form.RecordsetClone

'get record count
iRecCount = rst.RecordCount

If Not (rst.BOF Or rst.EOF) Then
   With rst
        rst.MoveFirst
        'capture selection from combo box
        Me.cboAcctClass.SetFocus
        iNewBoundColumnValue = Me!cboAcctClass.Value
        sNewBoundColumnDescr = Me!cboAcctClass.Column(1)

        intReturn = MsgBox("You are about to change the report classification to " & sNewBoundColumnDescr & " for all " & iRecCount & " records.", vbOKCancel)
        If intReturn = vbCancel Then
            MsgBox "You must use the Chart of Accounts to make selective changes."
             cboAcctClass.Undo
            Resume Exit_Continue
        Else
            'rst.MoveNext
            'change all rows to new value of cboSelectIdx
            Do Until rst.EOF
               rst.Edit
                   With cboAcctClass
                             'Retrieves the value of the bound column which may or may not be
                                    'displayed in the list box
                             If iOldBoundColumnValue <> iNewBoundColumnValue Then
                                  Me!cboAcctClass.Value = iNewBoundColumnValue
                             End If
                    End With
               rst.Update
               Me!cboAcctClass.Requery
              ' Forms("frmChartUpdate_Main").Controls("frmChartUpdate_DetailCtl2").Form.Requery
               If iRecCount > 1 Then
                    rst.MoveNext
               Else
                    Exit Do
               End If
            Loop
       End If
   End With
End If
rst.Close
Exit_Continue:
    'clean up
    Set rst = Nothing
    iRecCount = 0
    iOldBoundColumnValue = Empty
    iNewBoundColumnValue = Empty
    sNewBoundColumnDescr = Empty
    intReturn = 0
    Exit Sub
Error_Routine:
        MsgBox "Error# " & Err.Number & " " & Err.Description
        Resume Exit_Continue
End Sub

Private Sub cboAcctClass_Enter()
On Error GoTo Error_Routine
     If Not IsNull(Me!cboAcctClass.Value) Then
         iOldBoundColumnValue = Me!cboAcctClass.Value
     End If
   
Exit_Continue:
        Exit Sub
Error_Routine:
        MsgBox "Error# " & Err.Number & " " & Err.Description
        Resume Exit_Continue

End Sub
Avatar of jefftwilley
jefftwilley
Flag of United States of America image

PDB,
Where does this get it's initial comparative value?

iOldBoundColumnValue
Avatar of puppydogbuddy
puppydogbuddy

ASKER

Hi Jeff,
Thanks for your assistance.  it is captured in the onEnter event of the combo box before any selection is made as own above and repeated below:

Private Sub cboAcctClass_Enter()
On Error GoTo Error_Routine
     If Not IsNull(Me!cboAcctClass.Value) Then
         iOldBoundColumnValue = Me!cboAcctClass.Value
     End If
   
Exit_Continue:
        Exit Sub
Error_Routine:
        MsgBox "Error# " & Err.Number & " " & Err.Description
        Resume Exit_Continue

End Sub
Jeff,
For what its worth, the problem seems to be that focus remains with the combo box in the first record, which somehow prevents it from going to the next record.  Are you aware of anything like that?

PDB
If you're capturing that value once to update selected records in your rst loop, why do you have it inside your loop? I understand why you would if you were doing it one at a time, but seems you're trying to cheat. Treat it as aon outside value, then throw it into your loop.
Avatar of Leigh Purvis
Hi PDB.

Sorry to seem lazy - I've not read thoroghly yet.
I was just browsing at it - and it seems you retireve a value for iRecCount - using the recordcount.
Is that accurate in the messagebox that pops up to confirm to you?  (i.e. it's greater than 1?)
As you use it to decide to progress to subsequent records if it's greater than 1 - but DAO recordsets often don't retrieve accurate recordcounts til having visited the last record.

I'll look more thoroughly after this - honest ;-)
Jeff,
Good thought, but that wasn't the problem.  I moved the comparison statement out of the do loop to before the do loop, but after the movefirst (the first selection), and the same thing happened..... the change is reflected in the first record (which was selected from the combo), but is not reflected in any of the subsequent records....focus remains on the combo box of the first record. I checked the values for the old and new bound column variables and they were correct.

Leigh,
LOL! Thanks for your"lazy" assistance....it is welcome at any time.  I forgot about the movelast, but I was getting the correct record counts with the movefirst.  I implemented your suggestion by inserting a movelast statement before the movefirst, but it didn't change anything.  The problem still remains that the only record that is changed is the first one that contained the combo box that I used to select the newBoundColumnValue.....and focus remains with that combo box after the recordset has been processed.....
 
OK - is the table local at all?  (That's usually enough as a requirement to not need a movelast)

What happens when you step through it?
Upon which line does it exit your loop?
I'm confused some where you're going with this

           Do Until rst.EOF
               rst.Edit
                   With cboAcctClass
                             'Retrieves the value of the bound column which may or may not be
                                    'displayed in the list box
                             If iOldBoundColumnValue <> iNewBoundColumnValue Then
                                  Me!cboAcctClass.Value = iNewBoundColumnValue
                             End If
                    End With
               rst.Update
               Me!cboAcctClass.Requery
you're not actually editing anything in the recordset....why do you open it for edit?
Is this there because this code was originally for doing these one at a time?

Since you have the recordset open, why not just continue with it and use the rst.fields("AcctClass").value
Leigh,
I thought you solved the problem, when i put a record counter inside the loop and realized that I was not updating the variable I set up for record count and that it was not changing as it was going thru the loop....I then put a  decremental counter before the move next as shown below:
                    iRecCount = iRecCount -1
                    If iRecCount >= 1 Then
                         rst.MoveNext
                    Else
                          Exit Do
                     End If

I then ran a test ....it executed the entire loop, with the counter properly decrementing down to zero before exiting the loop.....but alas.....the boundcolumnvalue did not change. Yes table is local.  Changed constraint  on iRecCount to >= 1.
---------------------------------------------------------------------------------------------------------
Jeff,

Based on one of your previous suggestions, my code was changed as follows:

rst.Edit
                   With cboAcctClass
                             'Retrieves the value of the bound column which may or may not be
                                    'displayed in the list box
                                  Me!cboAcctClass.Value = iNewBoundColumnValue
                    End With

You may be on to something re: the edit .  I thought that I was editing the recordset with the following statement  ....it looks like I am about to learn something new. Please explain to me. :
          With cboAcctClass
                    Me!cboAcctClass.Value = iNewBoundColumnValue
           End With

I am going to try your recommendation shortly and will get back to you.
ASKER CERTIFIED SOLUTION
Avatar of jefftwilley
jefftwilley
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
So is the intention to change each value in the subform to a value on the main form?
Would it not make more sense to just use the subform's recordset object directly?

(Sorry - still haven't had time to read it all properly yet lol  :-)
Jeff/Leigh,
It is complicated to explain, but there is more than one tier to the Chart of Accounts, depending on the type of report being run.  Consequently, for purposes of this form I use variables that are set in the main form and the other continuous subform to capture the type of report and other relevant info that determines which field in the Chart of Account is the governing linking field for that report.  For this reason, the BoundColumnValue of cboAcctClass is not determined until runtime, when the actual rowsource of cboAcctClass is known.

Consequently, I can't execute Jeff''s suggested statement unconditionally.....I think I am going to have to put a constraint based on the rowsource like:
if cboAcctClass.RowSource = "                              " then

I assume the rowsource connection is what makes the combo work when I make a selection from it, but now I need to clear my head (ouch!!) and think it thru.

Thanks to you both for your help so far in getting me poined in the right direction. Any other thoughts you may have are welcomed.
It wouldn't hurt to put that bit back in....a conditional edit will keep it from updating anything that alredy has the Rowsource value equal to the selected combo box. so thats not a bad thing.
when you open the recordsetclone, it would be defined as the combo box's rowsource...meaning it would still have field names that you can reference. so the Edit should work that way.
J
No chance of an EEStuff upload?
Leigh,
As much as I would like too, I can't.  My application is copyrighted, so it would not be wise to send it over carte blanche with source code. ......and the applicaton has too many related tables to break out this one form and send it over.  
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Jeff/Leigh
I've got it working now thanks to you both.  I was able to use a reference to a variable instead of a direct reference to a field name in the final solution.  See below:

                'change all rows to new value of the BoundColumn
                Do Until rst.EOF
                    rst.Edit
                        With cboAcctClass
                              rst.Fields(CStr(varAcctClass)).Value = iNewBoundColumnValue   '<<<<<
                        End With
                    rst.Update
                    Me!cboAcctClass.Requery
                   
                    If iRecCount >= 1 Then
                        rst.MoveNext
                        iRecCount = iRecCount - 1
                    Else
                        Exit Do
                    End If
                Loop
            End If
You're saying the field name changes?
Or that using a fixed variable and not using a fixed string made a difference?  (Which is crazy)
the field name changes.
Ah - that makes sense then.
I don't think I'd gathered that from the question text.
additional info:

the field name changes corresponding to the value of varAcctClass in the SQL that was used to create the recordset (see below):

The SQL code that was used to create the recordset for subform2:
----------------------------------------------------------------------------------------------------------------

strSQLSF2 = "SELECT " & "[tblChartOfAccts]" & "." & varAcctClass & "," & "[tblChartOfAccts]" & "." & varAcctCode2
strSQLSF2 = strSQLSF2 & "," & "[tblChartOfAccts]" & "." & varCafrLink2 & " From [tblChartOfAccts]"
strSQLSF2 = strSQLSF2 & "  WHERE [tblChartOfAccts]" & "." & varAcctCode2 & " IN " & "(Select " & varAcctCode & " From TempQry)"
strSQLSF2 = strSQLSF2 & " ORDER BY " & varAcctCode2