Um....I'm lost please help

[ae_jester]

This isnt really VB but its VBA close enuff (and nobody in access sites has any clue)

In Access I need to create a form dynamically that shows a label for each employee in the company. What I would like to do is add code so that when the user clicks on one of those labels, it opens a different form that shows the employees schedule for a 24 hour period.

this is what i have done to create the controls.

Public Sub ShowSchedule()
    Dim DisplayForm As Form
    Dim NewControl As Control
    Dim NumLocators As Integer
    
    'for testing purposes just set it to 20 employees
    NumLocators = 20
    
    Set DisplayForm = CreateForm()
    DoCmd.OpenForm DisplayForm.Name, acDesign, , , , acHidden
    
    Dim i As Integer
    For i = 1 To NumLocators
        Set NewControl = CreateControl(DisplayForm.Name, acLabel)
        With NewControl
            .Left = 100
            .Top = 500 + i * 250
            .Width = 1000
            .Height = 200
            .Caption = "WhoCaresForNow"
        End With
        Set NewControl = Nothing
    Next i
    
    Dim FormName As String
    FormName = DisplayForm.Name
    DoCmd.Save acForm, DisplayForm.Name
    DoCmd.Close acForm, DisplayForm.Name
    
    DoCmd.OpenForm FormName
    
    Set NewControl = Nothing
    Set DisplayForm = Nothing
End Sub

This all works fine. But....
My quesiton is....How do you set the OnClick event property code during runtime. For example I would like to make it so that if you click "Joe Schmoe" on the employee list it calls a sub called ShowEmployeeSchedule() passing his employeeID along with it.
Remember I have to set this in runtime NOT design time.

Thanx in advance

Clint

[vbgladiator]

Store the employee id in the label's tag property. Then when they click on it, just pass the value that tag is holding.

[Skitchen8]

do theses labels end up in a control array (i hate not having VB installed cuz i gotta format this comp this weekend) if they do then just use one original label, and use the index in the onclick to determine which person it is

[ae_jester]

Sorry to confuse you guys but this code is in access as well.....You cant even use control arrays in access 97 can you? Also, I understand how to set up the employee ID for each employee....ONly problem is actually setting the OnCick event for that object on the actual Access Form. I know how to do this in design view without problem but doign it in runtime is what i need to know. Thanx

[ae_jester]

Can anyone help

[T-Bone]

OK, I am Lazy, and I didn't exactly read what you were trying to achieve however I know that when using access, what you can do when you want Dynamic form's is you can Work them entirely off Queries (or Code).

Now code, requires little explanantion. However Queries, let's look at them a little more closely...

For instance, If I want to include information in a Form which is Dynamic, I'll use Sub-Forms, and sometimes even Subforms of Subforms.

Then the Question arises, how does one make sure that the Queries are in direct relation to the information on the form? Well that can be resolved by setting the Where statment equal to the Value (property) of an object such as a TextBox, Label, etc. Then whenever the value in the text box changes, simply do a requery of the associated Sub-Forms (and the Queries that they are linked to), and Tada (to use MS's Expression), you have yourself a dynamic form.

Note: You can also use the result from Public Functions (that are stored in your DB), as fields or in the Where Clause of your Query.

Hint: Whenever tryint to do something tricky with Queries in access, its best to use the Expression Builder. This can be a useful tool accessed by right-clicking on the Criteria of Where field for that query (in Design View).

Also using Reports for simply displaying info rather than forms (ie. a Sub-Report in a Form), can bu useful in Read-Only situations.