Access - Loop to Change Label for Input Box of Subform's Subform

Posted on 2012-08-19
Last Modified: 2012-08-26
Hello All:

Another question from a "Access Dummie"

I am attempting to create a Loop that retitle's Label's in a Subform's Subform.

My Main form is named "frm_PRPayPdSummary"
My 1st Subform is named "frm_PRData"
My subform of the 1st Subform is named "frm_PRDataDetail"

I have a query that pulls a list of all Active Pay Types and creates a field called MySeq to sequentially number all Active Pay Types.

On the Main Form I have a Textbox to with a DCount to get the count of all Active Paytypes.  I have named that textbox "txtCountofActivePayTypes" and the formula I used is: =DCount("PRDetailPayTypeActive","[qry_PRDataPayType_CountActive]","PRDetailPayTypeActive=True").

That all seems to be working correctly.

On the Sub Subform each of the label's that I want to change are labeled "lblPRDetail#" where the #sign is actually the numbers 1, 2, 3 etc....

Now in the Form Load event I have the following Code
Private Sub Form_Load()
Dim i As Integer
i = 1

For i = 1 To Me.txtCountofActivePayTypes
    Me.Controls("[frm_PRData].Form![frm_PRDataDetail].Form![lblPRDetail" & i & "]") = qry_PRDataPayType_CountActive.MySeq.Column(2)
Next i

End Sub

Open in new window

When my query for "qry_PRDataPayType_CountActive" runs the results pull as:

It seems I need a SELECT statement somewhere in there to use the i value to SELECT the right MySeq line........

Any help?
Question by:wlwebb
    LVL 56

    Expert Comment

    by:Jim Dettman (Microsoft MVP/ EE MVE)
    <<Now in the Form Load event I have the following Code>>

     Which subform is this for?  Looks like the main form.

    <<My subform of the 1st Subform is named "frm_PRDataDetail">>

     What is this form displaying (what's it's recordsource)?


    Author Comment

    Very observant.....  
    I have a textbox on the Main Page that gets the Number of Active Paytypes when the whole form loads........  Then I'm going to pass that to the Sub's Subform to update all the Titles

    Based on your question I think you're going to suggest moving it to the Sub Sub....right.

    The record source for the labels is:
    LVL 56

    Expert Comment

    by:Jim Dettman (Microsoft MVP/ EE MVE)
    <<Based on your question I think you're going to suggest moving it to the Sub Sub....right.>>

     No, you can do it from the main form.  I'm just trying to make sure what, why, and how your trying to do what it is your trying to do.

    <<The record source for the labels is:
    [qry_PRDataPayType_CountActive] >>

    If that's the case, then why not make the labels text controls and bind them to the field MySeq?

     Just trying to understand what's going on so I don't take you in the wrong direction.


    Author Comment

    The way I defined the tables is that the Pay types are in a table named PRDataPayType.  Then the details of the input by a clerk is going to be in a table named PRDataDetail where I have a field in each record for the PRDataPayTypeID.

    Whenever the form loads I want it to automatically load however many "active" Paytypes that are in the PRDataPayType which will come from the query qry_PRDataPayType

    Author Comment

    Maybe this will help... see attached

    Nowhere near done so ignore rough forms

    OH PS this is in no way a PR system.... This is only for capturing and summarizing info from an actual PR system for a specific reporting purpose.  Got tired of every clerk reinventing the summarization wheel in Excel at all of the client's various locations.
    LVL 56

    Accepted Solution

    Sorry for the delay in getting back to you; yesterday was one heck of a day.

     Here's your OnLoad event code for the main form:

    Private Sub Form_Load()

      Dim db As DAO.Database
      Dim rst As DAO.Recordset
      Me.cboPayDays = Me.cboPayDays.ItemData(0)

      Set db = CurrentDb()
      Set rst = db.OpenRecordset("qry_PRDataPayType_CountActive")
      Do Until rst.EOF
       Forms("frm_PRPayPDSummary")("frm_PRData")("frm_PRDataDetail")("lblPRDetail" & rst![MySeq]).Caption = rst![PRDetailPayType]
      Set rst = Nothing
      Set db = Nothing
    End Sub

      This is using a recordset to open your query, read the records one at a time, and then populate the labels with the correct caption.


    Author Closing Comment

    Thanks Jim........  Always great when you chime in.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Suggested Solutions

    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    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…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

    754 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

    17 Experts available now in Live!

    Get 1:1 Help Now