Solved

Continuous subform using a DAO recordset/ Access 2000

Posted on 2006-10-25
21
736 Views
Last Modified: 2008-01-09
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
0
Comment
Question by:puppydogbuddy
  • 9
  • 7
  • 5
21 Comments
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17804972
PDB,
Where does this get it's initial comparative value?

iOldBoundColumnValue
0
 
LVL 38

Author Comment

by:puppydogbuddy
ID: 17805031
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
0
 
LVL 38

Author Comment

by:puppydogbuddy
ID: 17805110
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
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17805192
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.
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17805239
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 ;-)
0
 
LVL 38

Author Comment

by:puppydogbuddy
ID: 17805626
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.....
 
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17805676
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?
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17806130
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
0
 
LVL 38

Author Comment

by:puppydogbuddy
ID: 17806396
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.
0
 
LVL 34

Accepted Solution

by:
jefftwilley earned 350 total points
ID: 17806591
I would expect to see it done this way

          Do Until rst.EOF
               rst.Edit
                  rst.fields("TheFieldYouAreUpdating").value = iNewBoundColumnValue
                rst.Update
          rst.movenext
          loop

Seems you are looping through the recordset setting the same field each time using this method

Me!cboAcctClass.Value = iNewBoundColumnValue
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17806621
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  :-)
0
 
LVL 38

Author Comment

by:puppydogbuddy
ID: 17807008
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.
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17807156
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
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17807189
No chance of an EEStuff upload?
0
 
LVL 38

Author Comment

by:puppydogbuddy
ID: 17807304
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.  
0
 
LVL 44

Assisted Solution

by:Leigh Purvis
Leigh Purvis earned 150 total points
ID: 17807377
Not to worry...

OK - I've read it!  lol

Have you stepped through the code in break mode - to see where it steps out of your loop?  (i.e. does it do so prematurely?)
If not then (with the change suggested by Jeff of course - where you're actually updating the recordset and not the same combobox each time) does it seem to complete?

Which event does the code fire on?
Do you save the current record before proceeding?  And refresh when it's done?
0
 
LVL 38

Author Comment

by:puppydogbuddy
ID: 17810923
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
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17810940
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)
0
 
LVL 38

Author Comment

by:puppydogbuddy
ID: 17810978
the field name changes.
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17811021
Ah - that makes sense then.
I don't think I'd gathered that from the question text.
0
 
LVL 38

Author Comment

by:puppydogbuddy
ID: 17811043
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
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

708 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now