[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 595
  • 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:
PRDetailPayTypeID
MySeq
PRDetailPayType
PRDetailPayTypeActive

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

Any help?
0
wlwebb
Asked:
wlwebb
  • 4
  • 3
1 Solution
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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)?

Jim.
0
 
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:
[qry_PRDataPayType_CountActive]
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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.

Jim.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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
0
 
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.
MCaidPR.accdb
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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]
   rst.MoveNext
  Loop
 
  rst.Close
  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.

Jim.
0
 
wlwebbAuthor Commented:
Thanks Jim........  Always great when you chime in.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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