Procedure behind unknown buttons 1

[KarelNeedsHelp]

Hello everyone

I got a quite annoying problem :
I've got multiple buttons in an Access Form, but I don't know the names of them or the amount of buttons. This is because they are automatically created when clicked on a button in another form. Now my question is : How can I make a procedure when clicked on these buttons if I don't know their name?

* Is there a groupbox or something similar that can conclude these different buttons, so if I click within the groupbox that I can check on which button is clicked?
* Is there a procedure within the form itself that happens when a button in the form is clicked, again so I can check what button is clicked?

Help will be much appreciated, thanks in advance
Karel

There is always a sollution

 

[dhookom]

I would think your code could use Screen.ActiveControl to get the name of the control.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

Are you saying that you build controls dynamically on a form and then want to use vba extensibility to write code for those dynamic controls? If so that just sounds like a horrible mess and bad design. I would not even bother. If this is what you are trying to do, then explain why. There has to be a better simpler design. You could use an object group/frame, but I am still not a big fan of dynamic controls.

 

[KarelNeedsHelp]

At first I want to thank you for your quick response.

And you're right, my explanation is a bit vague. Let me try to make it clear :
I have a database and in this database there is a table with different categories, but I don't know how many categories or what their name is. When I click on a button in a form (frmMainscreen) the form (frmCategories) loads, and while this form loads, I create a loop to create as many buttons as there are categories in the table. And when one of these button is clicked I want to make another procedure, but can't manage to find a way to do that.

Thank you for your time
Karel

There is always a sollution

 

[MajP]

I assume the buttons represent categories. So when you build the buttons I would put the category name into the tag property. I assume that when you click the button they all do the same thing. For example the button opens another form to that category. So create a generic function that reads the activecontrols tag and then does something. Example

private function someFunction()
dim ctrl as access.control
ctrl = screen.activecontrol
docmd.openform "SomeForm",,,"Category ='"& ctrl.tag & "'"
end function

then when you are creating the controls set the click event property to
"=SomeFunction()"

Now that function will fire for each button when clicked and open another form based on the tag property of the button.

This is still a complete waste of time. Do it with a native control like a single Listbox or combobox. You do not need to be creating a bunch of dynamic controls.

 

[KarelNeedsHelp]

I tried to do it with buttons using your code, but couln't solve the problem.
Since I've been trying way too long with buttons, I'll just use a listbox instead, it'll be way easier but not really what the customer wants.

Thanks a lot for your time and advice!
Karel

There is always a sollution

 

[MajP]

This is a demo of how to do what you want correctly. The only limit is you cannot "make" more than 200 buttons (because that is what I designed it to). I used code to build a form with 200 buttons with a similar naming structure. The buttons are all stacked and hidden in the corner of the form. I am not however making any controls at runtime, because this is really problematic in access in my opinion. This form appears to be dynamic. Make a 5x5 grid, then make a 10x20 grid. This will appear to any user as dynamic. Do not make it wider than 10 rows (I should trap that, but it will through an error). Notice how I assign a common function to all controls.

http://www.4shared.com/file/t2KwJgJ3/MajP_CreateDynamicControls.html

Here is the general code, but you probably need to see it work

Private Sub cmdMakeGrid_Click()
  Dim frm As Access.Form
  DoCmd.OpenForm "frmDefineGrid", , , , , acDialog
  If CurrentProject.AllForms("frmDefineGrid").IsLoaded Then
    Set frm = Forms("frmDefineGrid")
    If IsNumeric(frm.txtRows) And IsNumeric(frm.txtColumns) Then
       If frm.txtRows * frm.txtColumns <= 200 Then
         formatGrid frm.txtRows, frm.txtColumns
       Else
         MsgBox "Max controls is 200"
       End If
    End If
    DoCmd.Close acForm, frm.Name
  End If
End Sub
Public Sub formatGrid(x As Long, y As Long)
  Const ctlWidth = (0.75 * 1440)
  Const ctlHeight = (0.25 * 1440)
  Const conStartLeft = (0.5 * 1440)
  Const conStartTop = (1 * 1440)
  
  Dim startLeft As Long
  Dim startTop As Long
  Dim lngRow As Long
  Dim lngCol As Long
  Dim intCounter As Long
  Dim ctl As Access.Control
  
  startLeft = conStartLeft
  startTop = conStartTop
  
  For lngRow = 1 To y
     For lngCol = 1 To x
     intCounter = intCounter + 1
     Set ctl = Me.Controls("lblGrid" & intCounter)
     With ctl
         .Height = ctlHeight
         .Width = ctlWidth
         .Left = startLeft
         .Top = startTop
         .Caption = "Row" & lngRow & " Col" & lngCol
         .FontSize = 8
         .Visible = True
         .Tag = lngRow & ";" & lngCol
         'Define the function here that they do when clicked.
         .OnClick = "=gridClick()"
       End With
       startLeft = startLeft + ctlWidth
     Next lngCol
     startLeft = conStartLeft
     startTop = startTop + ctl.Height
  Next lngRow
 
 End Sub

Public Sub makeAllInvisible()
  Dim ctl As Access.Control
  'need somewhere to set focus it is hidden below the command button
  Me.txtFocus.SetFocus
  For Each ctl In Me.Controls
    If Left(ctl.Name, 7) = "lblGrid" Then
      ctl.Visible = False
    End If
  Next ctl
End Sub
Public Sub stackControls()
  Dim ctl As Access.Control
  For Each ctl In Forms("frmGrid")
    If Left(ctl.Name, 7) = "lblGrid" Then
    ctl.Left = 0
    ctl.Top = 0
    ctl.Height = 0.2 * 1440
    ctl.Width = 0.2 * 1440
    End If
  Next ctl
End Sub

Public Function gridClick()
  'This function fires when any dynamic control clicks
  Dim ctl As Access.Control
  Dim lngRow As Long
  Dim lngCol As Long
  Set ctl = ActiveControl
  
  lngRow = Split(ctl.Tag, ";")(0)
  lngCol = Split(ctl.Tag, ";")(1)

   MsgBox ctl.Name & "Row " & lngRow & " Column " & lngCol
   If ctl.ForeColor = vbRed Then
     ctl.ForeColor = vbBlack
    Else
      ctl.ForeColor = vbRed
    End If
   
End Function

Private Sub Form_Open(Cancel As Integer)
  Call makeAllInvisible
  DoEvents
  MsgBox "Click Button to create grid"
End Sub

 

[KarelNeedsHelp]

I appreciate the effort you have put in my question
But I am going to stick with the listbox because I already made the code and it works properly.
Also because there can me a lot of categories, even more than there could be buttons on the screen, which is a problem because I can't use a scrollbar in the form, but can use one in the listbox. (I know it's weird, but customer is king ;-) )


There is always a sollution

 

[MajP]

I appreciate the effort you have put in my question

Actually this was one of my hundreds of code samples, so not much effort was needed. But bottom line if you ever need to do something like this you can achieve it without dynamic controls. In Access dynamic controls are problematic, but you can almost always get the same effect by having pre built controls. Then you can hide, show, move, and format these controls to get the effect you want. That demo has 200 unformatted command buttons, but when you look at it in design view it appears that none are there. You can also use this technique to make complex forms that would take a long time to build and format.

 

[KarelNeedsHelp]

Well anyways, I still appreciate all the advice and help!
I'll keep all this in mind for the future, you made me a smarter man!

There is always a sollution

 

[dhookom]

MajP deserves a star for going above and beyond (again) in providing direction as well as source code.

Duane
Hook'D on Access
MS Access MVP

 

[KarelNeedsHelp]

I know haha! I already gave him a star, he's a bauws when it comes to programming!

There is always a sollution

 

[lionelhill]

Just a thought, but if you have a button for every record in a table, why aren't you just displaying it as a subform and putting the action behind the events handling clicks on the subform's controls? We tend to get a bit button-biased, but anything is clickable, not just buttons.

 

[dhookom]

lionelhill,
You have captured why KarelNeedsHelp has decided to use a list box rather than buttons (even though the customer wants buttons).

Duane
Hook'D on Access
MS Access MVP

 

[lionelhill]

Yup..sorry. My only reason for suggesting a subform instead of a listbox is that if client insists on buttons, you can put buttons on a subform, show it as a continuous form and simulate dynamically-created buttons. Won't draw buttons in a nice grid pattern though!