Create Button or Label Grid with VBA (Access 2007)

[Frizzellio]

I want to create a button or label grid on a form based on two tables:

tblHorizontal (1 - x)
tblVertical (1 - x)

The buttons would be arrayed in a typical grid/matrix format with the number of rows based on the number of records in tblHorizontal, and the number of columns based on the number of records in tblVertical.

I've looked at this FAQ, but the MSForms 2.0 library is not a part of Access 2007, and I would like to use a more up-to-date library.

Any help would be greatly appreciated!

 

[Frizzellio]

The FAQ I referred to is [link "

http://www.tek-tips.com/faqs.cfm?fid=5757"

]here[/url].

 

[MajP]

I've looked at this FAQ, but the MSForms 2.0 library is not a part of Access 2007, and I would like to use a more up-to-date library.

Not sure what that means. Do you want to do this this with a MS Form or with an Access form? Do you want to do this in access or in Excel/Word etc? Since you are talking about tables I assume you mean Access, but you cannot use an MS Form in Access on Access forms. Provide more information. And what do you mean by more up to date library? What you wrote makes little sense.

 

[Frizzellio]

This is an Access 2007 form using the tables mentioned in the initial post.

MS Forms 2.0 Object Library is a reference library used to build a form via VBA in this FAQ:

http://www.tek-tips.com/faqs.cfm?fid=5757

The FAQ author used that library (among others) to build the form objects.

Disregarding the library reference, the question remains: how do I build the matrix form dynamically based on the tables?

 

[MajP]

You do not mention anything about the tables except a count. Do you need to read data out of the tables to build the form.

I can get the recordcount using a dcount and pass that in.

You can build an access form like this

Public Sub makeForm(x As Integer, y As Integer)
  Const ctlWidth = (0.75 * 1440)
  Const ctlHeight = (0.5 * 1440)
  Const conStartLeft = (0.5 * 1440)
  Const conStartTop = (0.5 * 1440)
  
  Dim frm As Access.Form
  Dim ctl As Access.Control
  Dim startLeft As Long
  Dim startTop As Long
  Dim lngRow As Long
  Dim lngCol As Long
  startLeft = conStartLeft
  startTop = conStartTop
  Set frm = CreateForm()
  With frm
    .Visible = False
    .DefaultView = 2
    .NavigationButtons = False
    .RecordSelectors = False
    .DividingLines = False
    .DefaultView = 1
  End With

  For lngRow = 1 To x
     For lngCol = 1 To y
     Set ctl = CreateControl(frm.Name, acCommandButton)
       With ctl
         .Height = ctlHeight
         .width = ctlWidth
         .Left = startLeft
         .Top = startTop
         .Name = "lblRow_" & lngRow & "_Col _" & lngCol
         .Caption = "SomeCaption"
       End With
       startLeft = startLeft + ctlWidth
     Next lngCol
     startLeft = conStartLeft
     startTop = startTop + ctl.Height
  Next lngRow
 
  DoCmd.Save acForm, frm.Name
  DoCmd.Close acForm, frm.Name
  
End Sub

Public Function FormExists(frmName As String) As Boolean
  Dim frm As AccessObject
  For Each frm In CurrentProject.AllForms
    If frm.Name = frmName Then
     FormExists = True
     Exit Function
    End If
 Next frm
End Function

Public Sub testMake()
  makeForm 6, 6
End Sub

Why not spend a few minutes and write a real clear post, then maybe we can give you a reasonable answer. Also explain what you are really doing. IMHO, building a dynamic form in Access is usually a sign of poor database design. I have built 100s of databases and only done that a few times. Normally it was to use images as controls.

The above code would build this:

 

[Frizzellio]

Thank you.

 

[MajP]

As I said I strongly recommend against doing this. If you add and delete controls from a form each time you open it you will eventually crash the form. Access can have ~700 controls created on a form in its lifetime to include deletes. After that the form will crash. If you know the max limit of controls that the user would ever have, say a 10 X 10 grid. Then build 100 controls. You can stack them all on top of each other to move them out of position and set their property to invisible. Then when the form opens you move X controls into position, show X, and format appropriately. Use the tag property to record the x,y position for easy referece. This is a far safer solution.
Do you need to know how to set it up so that each control reacts to a single event?

 

[MajP]

So here is how I would do it. It also demonstrates how each control will act to a single click event. This is a much better solution then building the form or controls dynamically.
1) I used the original code to build a form with 200 controls with a name "lblGrid1" to "lblGrid200". Did this just for ease to get the correct 200 names. I stacked them all on top of each other and put in the corner of the form out of the way.
2) Form opens and picks as many controls as it needs to build the grid, and then hides the rest. It moves the ones it needs into position and formats as desired.
3) If you click on any control the event fires. It displays the X, Y position and turn from black to red.

Private Sub Form_Load()
  formatGrid 8, 15
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 = (0.5 * 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
  Call makeAllInvisible
  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
         .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
  For Each ctl In Me.Controls
    Me.lblGrid1.Visible = True
    Me.lblGrid1.SetFocus
    If Left(ctl.Name, 7) = "lblGrid" And ctl.Name <> "lblGrid1" 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()
  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