Create Dynamic Form with VBA 1

[Waynest]

Access 2003/SQL 2000

I'm writing some vba to create access forms on the fly, reading records from sql server to get details of controls to create.

Q1. I can handle the form size ok, but can I use vba to determine the size of the 'window' that the form is created in?

Q2. Say I create a command button on the form, can I programmatically define it's On Click event?

Thanks!

 

[CautionMP]

Waynest,
I don't see why not, Below is a rough example with a couple of thoughts:
[ol][li]You can use the [tt]InsideHeight[/tt] and [tt]InsideWidth[/tt] to check/set the size of the new form. I beleive in A2K3 you can get the size of the application window so you can resize the new form, I'm using A2K so I just maximize the form to find the maximum dimensions I could resize the form to.[/li]
[li]As far as adding code you can set the event handler (on click event for this example [tt].OnClick = "[Event Procedure]"[/tt]) of the control then use [tt]Module.AddFromString[/tt] to actually add the event procedure.[/li][/ol]

Sub CreateTheForm()
  Dim frmNew As Access.Form
  Dim ctlNew As Access.Control
  Dim intSection As Integer
  
CreateTheForm_AddForm:
  Set frmNew = CreateForm
  DoCmd.Maximize
  frmNew.HasModule = True
  
CreateTheForm_InspectSize:
  'You can inspect the current form size and change the dimensions
  'if you want
  Debug.Print "Maximized form inside size (twips) is " & _
  Format$(frmNew.InsideWidth, "#,##0") & "w X " & _
  Format$(frmNew.InsideHeight, "#,##0") &  "h"
  
  'Cycle through the sections in the form to see if they exist
  'and what their height is. Sections could also be added and
  'resized here.
  'NOTE: Use inline error handling to detect if the section is present
  On Error Resume Next
  For intSection = 0 To 4
    If frmNew.Section(intSection).Visible Then
      If Err.Number = 0 Then
        Debug.Print Choose(intSection + 1, "Detail", "Header", "Footer", "Page Header", "Page Footer") & _
        " height is " & _
        Format$(frmNew.Section(intSection).Height, "#,##0") & "h"
      Else
        Debug.Print "Form has no " & _
        Choose(intSection + 1, "Detail", "Header", "Footer", "Page Header", "Page Footer")
        Err.Clear
      End If
    End If
  Next intSection
  On Error GoTo 0

CreateTheForm_AddControl:
  'Create a command button in the details section
  Set ctlNew = CreateControl(frmNew.Name, acCommandButton, acDetail)
  With ctlNew
    .OnClick = "[Event Procedure]"
  End With
  
CreateTheForm_AddControlCode:
  'Add the OnClick event for this button
  frmNew.Module.AddFromString "Private Sub " & ctlNew.Name & "_Click()" & _
  vbCrLf & "  MsgBox " & Chr(34) & "Hello" & Chr(34) & _
  vbCrLf & "End Sub"

CreateTheForm_Cleanup:
  DoCmd.Close acForm, frmNew.Name, acSaveYes
  Set ctlNew = Nothing
  Set frmNew = Nothing
End Sub

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[Waynest]

Thats a real help, thanks very much.