Creating a table similar to word in access form (1 Viewer)

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

Public Sub FormatTable(numberRows As Integer, NumberColumns As Integer)
  Const TableWidth = (8 * 1440)
  Const conStartLeft = (0.5 * 1440)
  Const conFont = 12
  Const conHeight = (0.25 * 1440) 'needs to be bigger than current detail hieght or you will get a gap
  Dim ctlWidth As Double
  Dim startLeft As Long
  Dim startTop As Long
  Dim I As Integer
  Dim ctl As Access.TextBox
  Dim strSql As String
  
  makeAllInvisible
  If NumberColumns > 10 Or NumberColumns < 1 Or numberRows < 1 Then
    MsgBox "Invalid number of rows or columns. Max columns is 10. ", vbInformation, "Invalid"
    Exit Sub
  End If
  ctlWidth = TableWidth / NumberColumns
  startLeft = conStartLeft
  MakeGrid numberRows
  Me.Requery
  For I = 1 To NumberColumns
     Set ctl = Me.Controls("txt" & I)
     With ctl
         .Height = conHeight
         .Top = 0
         .Width = ctlWidth
         .Left = startLeft
         .FontSize = conFont
         .BorderColor = vbBlack
         .BorderWidth = "3"
         .Visible = True
         .Tag = ""
         .ControlSource = "fld" & I
       End With
       startLeft = startLeft + ctlWidth
  Next I
 End Sub

Private Sub makeAllInvisible()
  Dim ctl As Access.Control
  'need somewhere to set focus it is hidden below the command button
  'Stack them to get out of the way
  Me.textBox_Focus.SetFocus
  For Each ctl In Me.Controls
     If Left(ctl.Name, 3) = "txt" Then
      ctl.Visible = False
      ctl.Left = 0
      ctl.TopPadding = 0
     End If
  Next ctl
End Sub

Private Sub Form_Open(Cancel As Integer)
  Dim strSql As String
  
  Dim rs As DAO.Recordset
  Dim fld As DAO.Field
  Call makeAllInvisible
  strSql = "Delete * from tblGrid where RowNumber <> 1"
  'need 1 row or you cannot set focus to text_focus
  CurrentDb.Execute strSql
  strSql = "Select * from tblGrid"
  Set rs = CurrentDb.OpenRecordset(strSql)
  For Each fld In rs.Fields
    If fld.Name <> "RowNumber" Then
      rs.Edit
        fld = ""
      rs.Update
    End If
  Next fld
End Sub

Private Sub MakeGrid(numberRows)
  Dim strSql As String
  Dim I As Integer
  strSql = "Delete * from tblGrid"
  CurrentDb.Execute strSql
  For I = 1 To numberRows
    strSql = "Insert INTO tblGrid (RowNumber) values (" & I & ")"
   CurrentDb.Execute strSql
  Next I
 
End Sub
Public Function GetTableHeight() As Double
  GetTableHeight = Me.txt1.Height * Me.Recordset.RecordCount + 720
End Function

Private Sub cmdBuild_Click()
  Dim frm As Form_frmFakeTable
  Set frm = Me.frmFakeTable.Form
  If IsNumeric(Me.txtRows) And IsNumeric(Me.txtColumns) Then
    frm.FormatTable Me.txtRows, Me.txtColumns
  End If
End Sub