Hi,
Responding a little late - and not sure exactly what you are up to, but you can build controls and even forms dynamically from code.
Some of the bits and pieces here might be useful.
Below is code that builds a simple form and puts code in a module for that form so the form appears with working VBA. It gets created only from this code - then vanishes unsaved when closed.
Best,
C
' ' ' ' ' code start ' ' ' ' '
Private Sub NewControls()
Dim frm As Form
Dim mdl As Module
Dim ctlLabel As Control
Dim ctlText As Control
Dim ctlText2 As Control
Dim BtnTest As Control
Dim a As String
Dim b As String
Dim strAddCode As String
Dim StrSQL As String
Set frm = CreateForm ' Create new form
a = frm.Name
StrSQL = ""
StrSQL = StrSQL & "SELECT TblContacts.ContactID, TblContacts.FirstName, TblContacts.LastName"
StrSQL = StrSQL & " FROM TblContacts"
StrSQL = StrSQL & " WHERE (((TblContacts.ContactID) = [Forms]![FrmSmallFoot002]![contactID]))"
StrSQL = StrSQL & " WITH OWNERACCESS OPTION;"
frm.RecordSource = StrSQL
Set ctlText = CreateControl(frm.Name, acTextBox, , "", "FirstName", 2300, 100, 3000)
With ctlText
.TabIndex = 0
End With
' hmmm...width of label seems to be dependent on text within
Set ctlLabel = CreateControl(frm.Name, acLabel, , ctlText.Name, "First Name", 1300, 100, 1000)
With frm
.DividingLines = False
.ScrollBars = False
.RecordSelectors = False
.AutoResize = True
.AutoCenter = True
.BorderStyle = 3 ' 0-borderless,1-thin,2-sizable,3-dialog
.Caption = "Contact for " & Forms![FrmSmallFoot002]![CompanyName]
.Modal = True
.ControlBox = False
.HasModule = True
.NavigationButtons = False
.Cycle = 1 ' 0-AllRecords, 1-CurrentRecord
.KeyPreview = True
End With
Set BtnTest = CreateControl(frm.Name, acCommandButton, , , "", 300, 150, 600, 600)
b = BtnTest.Name ' KEEP c is used in code to close form
Set ctlText2 = CreateControl(frm.Name, acTextBox, , "", "LastName", 2300, 500, 3000)
With ctlText2
.TextAlign = 1 ' left align
.TabIndex = 1
End With
Set ctlLabel2 = CreateControl(frm.Name, acLabel, , ctlText2.Name, "Last Name", 1300, 500, 3000)
BtnTest.OnClick = "[Event Procedure]"
Set mdl = Forms![Form1].Module ' refer to the module
strAddCode = "" ' start to build code
' add code below to close form w/o saving it
strAddCode = "Private Sub " & b & "_Click()" & vbCrLf
strAddCode = strAddCode & "DoCmd.Close acForm, me.name, acSaveNo"
strAddCode = strAddCode & vbCrLf
strAddCode = strAddCode & "End Sub" & vbCrLf
strAddCode = strAddCode & vbCrLf
' add code to keep it on one record despite page up pagedown keystrokes
strAddCode = strAddCode & "Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)"
strAddCode = strAddCode & vbCrLf & "Select Case KeyCode"
strAddCode = strAddCode & vbCrLf & "Case 33, 34, 18"
strAddCode = strAddCode & vbCrLf & "KeyCode = 0"
strAddCode = strAddCode & vbCrLf & "Case Else"
strAddCode = strAddCode & vbCrLf & "End Select"
strAddCode = strAddCode & vbCrLf & "End Sub"
With BtnTest ' describe button
.Caption = "&Close"
.Cancel = True
.TabIndex = 2
End With
With mdl ' add code to module here
.InsertText strAddCode
End With
DoCmd.OpenForm a, acNormal ' open form to normal view from design view
DoCmd.MoveSize , , 5700, 1400 ' set size
End Sub
' ' ' ' ' code end ' ' ' ' ' '
