Dynamically Assigning onClick Event to static buttons with VBA

[Jon-G]

I have a form which is going to simulate a map and will have >700 command buttons on it to simulate plots (which will be called A_1 through Z_255 etc). The form I will draw beforehand and put all the buttons in place, but I dont want to set the onClick (which opens the plot information form filtered for that plot) manually for each button.



The code I have so far is creating the events in code associated with the form, but it appears to be actually modifying the source, rather than just creating them a runtime, so I have put checks in the loop to see if we have already created the onClick, and also to only create it for these button types (where the button is names after the block and plot number, separated by an underscore, and not prefixed with 'btn' like any other buttons I will put on the form)

At the moment, the events for each button are being created, but the code doesnt fire at all from the form. When I put the form in design view and look at the properties, the onClick event isnt linked to a procedure.

First question is: Is this the right way to be going about this?
Second: How can I link the events to the buttons?

Here is my code so far:

Option Compare Database


Private Sub setButtonEvents()

  Dim ctl As Control
  
  Dim sManzanaLetter As String
  Dim nLoteNumber As Byte

  Dim lngReturn As Long
  
  Dim mdl As Module
  Set mdl = Me.Module
     
  For Each ctl In Me.Controls
    If ctl.ControlType = acCommandButton Then
      
        'Check we have a plot button (contains an underscore in button name)
        If InStr(1, ctl.Name, "_") > 0 Then
            
            'Check to see if we have already created the onClick event
            If mdl.Find(ctl.Name + "_Click", 0, 0, 0, 0) = False Then
            
                'Parse button name to get block and plot
                sManzanaLetter = Left(ctl.Name, (InStr(1, ctl.Name, "_") - 1))
                nLoteNumber = Right(ctl.Name, (Len(ctl.Name) - InStr(1, ctl.Name, "_")))
                
                ' Add event procedure
                lngReturn = mdl.CreateEventProc("Click", ctl.Name)
                mdl.InsertLines lngReturn + 1, vbTab & "DoCmd.OpenForm ""frmLote"", , , ""Projecto = 'Peten' AND Sector = '1' AND Manzana = '" + sManzanaLetter + "' AND Lote_No = " + CStr(nLoteNumber) + ""
                
        
            End If
        End If
        
    End If
  Next ctl
 
End Sub

'THE 2 SUBS BELOW ARE BEING CREATED BY setButtonEvents()....

Private Sub A_1_Click()
    DoCmd.OpenForm "frmLote", , , "Projecto = 'Peten' AND Sector = '1' AND Manzana = 'A' AND Lote_No = 1"

End Sub

Private Sub A_2_Click()
    DoCmd.OpenForm "frmLote", , , "Projecto = 'Peten' AND Sector = '1' AND Manzana = 'A' AND Lote_No = 2"

End Sub

Private Sub Form_Load()
   setButtonEvents
End Sub

Any help would be greatly appreciated.

TIA,
Jon

[drexasaurus]

Access isn't really good at dynamically created forms--I don't think it can do it at all. It seems like you are automating the process for creating all that text. If the only issue is not wanting to type all that text by hand, then I would output the code to a text file and then copy and paste it into the module...

...I don't know how well an access form with so many controls will perform, however.

[Jon-G]

Thanks for your input, but access does have the functionality to create forms and their controls dynamically, so Im sure MS tested this before its release. Im only trying to utilise a small part of this functionality by creating a single simple event per control.

I take on board your comment about having 700+ controls on the form, but I need to drop them on and see how it performs. All of the systems that will be running this will be on new hardware so Im not worried about that.

Anyone else have any input on how I can get this loop to work?

[Jon-G]

b-b-b-bump

[Jon-G]

all I really need to know is how I can link the event to the controls via VBA. The code is being created ok, but the link of the event to the button isnt

[boblarson]

Check out this sample database that ChrisO did for us over on Access World Forums. It sets like 10,000 control events. This is what you're looking for, not creating events like you are attempting.

[Jon-G]

Thanks bob, Ill have a look now

[Jon-G]

Check out this sample database that ChrisO did for us over on Access World Forums. It sets like 10,000 control events. This is what you're looking for, not creating events like you are attempting.

Hi Bob,
I've had a look at that db and I think I might have confused the issue with my explanation of trying to simulate a map.

The map Im trying to recreate is a top-down view of a piece of land, and each one of my buttons represents a plot of land, and when clicked the details for that plots status are loaded up in the frmLote (plot info) form.

The layout of my form needs to represent the actual layout of the piece of land (even if only roughly), and the onclick of a plot load up the data specific to it.

Im not sure how the matrix arrangement with mouse position tracking in the 10000 controls db can help?! Or am I missing something obvious?

[boblarson]

It looked to me that you had, in your current code, the application of the variables like:

AND Manzana = '" + sManzanaLetter + "' AND Lote_No = " + CStr(nLoteNumber) + ""

so you can set your event handler to pass the two pieces of information based on their name just like you have set this up. So if you pass the control name like:

=MyClickEventHandler("ControlNameHere")

Then you can handle it in your function. And the code that sets the event (in the sample) can iterate through the controls like it does and it can add the name to the call like the InitializeMouseMoveEvents function in this sample (sorry I forgot which of the two samples set the event. This other one I have attached should fit your situation better).

[Jon-G]

Thanks Bob,
I found this example here which looks like it references the same code from this sample database. Its reasurring to know Im heading down the right path. I have it attaching the events to the controls at runtime but Im still trying to get it to work so the button knows what to values write into the SQL query!

[boblarson]

What is the code you are using to build the event handler for the button? That is where you want to include the control name and then you can fix your function which runs the function to have that control name as the parameter.

[Jon-G]

Cracked it! Heres my code for folks future reference. Thanks for the help

Function ParseBlockName(ByVal ctlName As String) As String
  
  ParseBlockName = left(ctlName, (InStr(1, ctlName, "_") - 1))
End Function

Function ParsePlotNumber(ByVal ctlName As String) As Byte

  ParsePlotNumber = right(ctlName, (Len(ctlName) - InStr(1, ctlName, "_")))
End Function

Private Sub setButtonEvents()

  Dim ctl As Control
  Dim sManzanaLetter As String
  Dim nLoteNumber As Byte
     
  For Each ctl In Me.Controls
    If ctl.ControlType = acCommandButton Then
      
        'Check we have a plot button (contains an underscore in button name)
        If InStr(1, ctl.Name, "_") > 0 Then
          
            'Parse button name to get block and plot
            sManzanaLetter = ParseBlockName(ctl.Name)
            nLoteNumber = ParsePlotNumber(ctl.Name)
            
            ' Add onClick event procedure
            ctl.OnClick = "=MapButtonClick('" & sManzanaLetter & "', '" & nLoteNumber & "')"
            
            ' Add MouseMove event procedure
            ctl.OnMouseMove = "=MapButtonHover('" & sManzanaLetter & "', '" & nLoteNumber & "')"

        End If
     End If
  Next ctl
End Sub

Public Function MapButtonClick(ByVal sManzanaLetter As String, ByVal nLoteNumber As Byte)
 
    DoCmd.OpenForm "frmLote", , , "Projecto = 'Peten' AND Sector = '1' AND Manzana = '" & sManzanaLetter & "' And Lote_No =" & nLoteNumber
End Function

Public Function MapButtonHover(ByVal sManzanaLetter As String, ByVal nLoteNumber As Byte)
                                
    lblHoverInfo.Caption = "Ver Manzana: " + sManzanaLetter + "  Lote: " + CStr(nLoteNumber)
End Function