Dynamic Labels in a new form

[markryan57]

Greetings,
I searched for this, but cannot find exaclty what I am looking for. I am trying to dynamically create labels on a form just before it opens. I am reading from a database, building the labels then opening the form.

Currently I have put the labels (in a control array) on the form I want to receive the variables. Then I create a loop to read in the db and build the labels, then open the form.

I have like 300 control arrays to build, and would love to dynamically create everything.

the code I have thus far is:

While Not rs.EOF
    If IsNull(rs("Eclient")) = False Then
        frmClientStatus.lblClient(i).Caption = rs("Eclient")
    End If
Load frmClientStatus
With frmClientStatus
    .lblTestClient (i)
    .Left = "500"
    .Top = "500"
    .Caption = "test"
    .Visible = True
End With
    If IsNull(rs("InforceDate")) = False Then
        frmClientStatus.InforceDate(i).Caption = rs("InforceDate")
    End If
 

there are more if statements below, but I cut them out for brevity.

love to hear any ideas - thanks a mil.

Mark

[willakawill]

Hi. You have omitted the end of the while loop which is important in this context. Can you say what it is you are trying to achieve without talking about it in terms of a solution?

[markryan57]

Thanks for the prompt response, yes I let off several steps below just for brevity sake. What I am trying to do is place labels on a form, poplate their caption with a database recordset, then open the form to display the status results.

Sorry - looking at it, I did not make that terribly clear eh?

thanks

Mark

[willakawill]

We are still without the remainder of the loop. I know that you have said you left out parts that don't matter for brevity. You have also left out parts that do matter.
Are you planning to be displaying a different number of labels each time? Will this number of labels be unlimited?

[markryan57]

Sorry I was trying to not clog up the screen with the code, here it is the entire routine. Yes the number of labels will or could change each time based on how many cllients are put in by each analyst.

The method below is where I actually went into the status form and created labels (in a control array) for each one (a pain). The parts that are commented out are the new method of dynamically creating the labels.

sorry for the confusion.

and thanks for the help
mark

Begin Sub
Dim conn1 As ADODB.Connection
Set conn1 = New ADODB.Connection
Dim constr As String
Dim rs As ADODB.Recordset
Dim i As Integer
Dim lblClient() As Label
Dim lblCleanup() As Label
Dim lblAccess() As Label
Dim lblExcel() As Label
Dim lblSeriatim() As Label
Dim lblInforceDate() As Label
Dim Import() As Label
Dim lblResearch() As Label
Dim lblMaster() As Label
Dim lblCopyToClient() As Label
Dim lblRetrieveResearch() As Label
Dim lblDone() As Label
Dim lblTestClient() As Label
 
i = 0
constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=L:\vbfolder\????.mdb"
conn1.Open constr
Set rs = conn1.Execute("Select * from InforceLog WHERE AnalystName = '" & cboSelectName.Text & "'")
 
 
While Not rs.EOF
    If IsNull(rs("Eclient")) = False Then
        frmClientStatus.lblClient(i).Caption = rs("Eclient")
    End If
'Load frmClientStatus
'With frmClientStatus
'    .lblTestClient (i)
'    .Left = "500"
'    .Top = "500"
'    .Caption = "test"
'    .Visible = True
'End With
    If IsNull(rs("InforceDate")) = False Then
        frmClientStatus.InforceDate(i).Caption = rs("InforceDate")
    End If
    If IsNull(rs("Cleanup")) = False Then
        frmClientStatus.lblCleanup(i).Caption = rs("Cleanup")
    End If
    If IsNull(rs("Accessdb")) = False Then
        frmClientStatus.lblAccess(i).Caption = rs("Accessdb")
    End If
    If IsNull(rs("Excel")) = False Then
        frmClientStatus.lblExcel(i).Caption = rs("Excel")
    End If
    If IsNull(rs("seriatim")) = False Then
        frmClientStatus.lblSeriatim(i).Caption = rs("Seriatim")
    End If
    If IsNull(rs("ImportSeriatim")) = False Then
        frmClientStatus.Import(i).Caption = rs("ImportSeriatim")
    End If
    If IsNull(rs("Research")) = False Then
        frmClientStatus.lblResearch(i).Caption = rs("Research")
    End If
    If IsNull(rs("RetrieveResearch")) = False Then
        frmClientStatus.lblRetrieveResearch(i).Caption = rs("RetrieveResearch")
    End If
    If IsNull(rs("UploadToMaster")) = False Then
        frmClientStatus.lblMaster(i).Caption = rs("UploadToMaster")
    End If
    If IsNull(rs("Eclientcopy")) = False Then
        frmClientStatus.lblCopyToClient(i).Caption = rs("EclientCopy")
    End If
 
    If IsNull(rs("Eclient")) = False And IsNull(rs("InforceDate")) = False And IsNull(rs("Accessdb")) = False And IsNull(rs("Excel")) = False And IsNull(rs("Seriatim")) = False And IsNull(rs("ImportSeriatim")) = False And IsNull(rs("UploadToMaster")) = False And IsNull(rs("EclientCopy")) = False Then
        frmClientStatus.lblDone(i).Caption = "*"
    End If
 
    i = i + 1
rs.MoveNext
Wend
    If cboSelectName.Text = "" Then
        MsgBox "Please select name before continuing", vbOKOnly + vbInformation
        Exit Sub
    End If
 
  frmClientStatus.Show
  Set conn1 = Nothing
  Set rs = Nothing
 
    frmClientStatus.Show
 
end sub
 

[willakawill]

And the second part of this question:
Are you planning to be displaying a different number of labels each time? Will this number of labels be unlimited?

[markryan57]

The labels for each run are laid out in a grid format

Client Date Step1 Step2 Step3 Step4 .......

XXX xx/xx/xx * * * *

this is the basic layout of the data once the code has run. the headers across are constant, the amount of clients going down is not unlimited however it could range from just 5 or 6 to 30 - 40, but never more than that.

Thanks again for your response ...

Mark

[willakawill]

Then, perhaps, a listbox or datagrid would be a better tool for you

[markryan57]

Yes agreed,

I am looking into flexgrid now, what I have thus far will work temporarily, I just need to find a flexgrid solution before too long...

thanks so much for your time

Mark