• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 598
  • Last Modified:

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

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?
  • 4
  • 3
1 Solution
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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)?

wlwebbAuthor Commented:
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:
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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.

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

wlwebbAuthor Commented:
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
wlwebbAuthor Commented:
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.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.

wlwebbAuthor Commented:
Thanks Jim........  Always great when you chime in.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now