Form Referencing

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a public procedure where I cycle through my labels and pass the
caption to a spreadsheet.

The thing I'm having trouble with is that the label names on the form I'm
referencing are "lblField1", "lblField2", etc, and I want to cycle through
them with a For Next loop.

I’m using [Forms]![frmUserCount]![lblField1], as in:

".Cells(I, K).Value = [Forms]![frmUserCount]![lblField1].Caption"

Instead of having a line like this for every label I want to reference (and
there are 46 of them), I want to use a For Next loop. Can anyone help with
this?

Thanks in advance,
Clint
 
2 approaches:
- I is incremented in both so that you are filling a column with your labels
and aren't constantly overwriting the same cell.
- I assume this is within a "With..EndWith" block because of your
..Cells(I,K) usage.

' Method1: Cycle through all controls and grab the captions of all labels.
Dim ctl as Control
For each ctl in [Forms]![frmUserCount].Controls
If ctl.ControlType = acLabel Then
' (acLabel =100 so "If ctl.ControlType = 100 Then" could also be
used)
.Cells(I, K).Value = ctl.Caption
I = I + 1
End If
Next ctl


' Method2: Get the captions of labels with specific names (lblField1 through
lblField46)
' Note: as long as labels with these names exist, this method also
guarantees the order in which
' captions will be retrieved, which Method 1 might not.
Dim c as Integer
For c = 1 to 46
.Cells(I, K).Value = [Forms]![frmUserCount].Controls("lblField" &
c).Caption
I = I + 1
Next c

HTH
 
Your 2nd example whas EXACTLY what I was looking for. I knew there was an
easier way to do this! Thank you.

George Nicholson said:
2 approaches:
- I is incremented in both so that you are filling a column with your labels
and aren't constantly overwriting the same cell.
- I assume this is within a "With..EndWith" block because of your
..Cells(I,K) usage.

' Method1: Cycle through all controls and grab the captions of all labels.
Dim ctl as Control
For each ctl in [Forms]![frmUserCount].Controls
If ctl.ControlType = acLabel Then
' (acLabel =100 so "If ctl.ControlType = 100 Then" could also be
used)
.Cells(I, K).Value = ctl.Caption
I = I + 1
End If
Next ctl


' Method2: Get the captions of labels with specific names (lblField1 through
lblField46)
' Note: as long as labels with these names exist, this method also
guarantees the order in which
' captions will be retrieved, which Method 1 might not.
Dim c as Integer
For c = 1 to 46
.Cells(I, K).Value = [Forms]![frmUserCount].Controls("lblField" &
c).Caption
I = I + 1
Next c

HTH
--
George Nicholson

Remove 'Junk' from return address.

cherman said:
I have a public procedure where I cycle through my labels and pass the
caption to a spreadsheet.

The thing I'm having trouble with is that the label names on the form I'm
referencing are "lblField1", "lblField2", etc, and I want to cycle through
them with a For Next loop.

I'm using [Forms]![frmUserCount]![lblField1], as in:

".Cells(I, K).Value = [Forms]![frmUserCount]![lblField1].Caption"

Instead of having a line like this for every label I want to reference
(and
there are 46 of them), I want to use a For Next loop. Can anyone help with
this?

Thanks in advance,
Clint
Click to expand...
Click to expand...
 
Back
Top