I want to create a form with subform based on table using VBA. I find three methods.
The first is to create a form -- create a subform control on it --- set the subform control's SourceObject to be the name of the table. But I can not set the properties of allowadditions, allowedits, etc. The first methd is the sub procedure of C_SourceObject_Click().
The second is to create a form (form1) --- set the RecordSource linked with the table --- save this form1 --- create another form (form2) -- create a subform control on it -- set the SourceObject to be the name of form1. The form2 show all of the records but without fields. The second method is the sub procedure of C_Direct_RecordSource_Click().
The third is create a form (form1) --- set the RecordSource linked with the table --- create text controls binding with the fields in this form1 --- save this form1 --- create another form (form2) -- create a subform control on it -- set the SourceObject to be the name of form1. The form2 show all of the records with fields. The third method is the sub procedure of C_InDirect_RecordSource_Click().
The above third method realize what I want. But I think it is some verbose. Any suggestion of simpler methods? I just want to do this in completely VBA situations. Thanks in advance.
The following is the code:
The first is to create a form -- create a subform control on it --- set the subform control's SourceObject to be the name of the table. But I can not set the properties of allowadditions, allowedits, etc. The first methd is the sub procedure of C_SourceObject_Click().
The second is to create a form (form1) --- set the RecordSource linked with the table --- save this form1 --- create another form (form2) -- create a subform control on it -- set the SourceObject to be the name of form1. The form2 show all of the records but without fields. The second method is the sub procedure of C_Direct_RecordSource_Click().
The third is create a form (form1) --- set the RecordSource linked with the table --- create text controls binding with the fields in this form1 --- save this form1 --- create another form (form2) -- create a subform control on it -- set the SourceObject to be the name of form1. The form2 show all of the records with fields. The third method is the sub procedure of C_InDirect_RecordSource_Click().
The above third method realize what I want. But I think it is some verbose. Any suggestion of simpler methods? I just want to do this in completely VBA situations. Thanks in advance.
The following is the code:
Code:
Private Sub C_InDirect_RecordSource_Click()
Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Set conn = CurrentProject.Connection
y1 = "F_InDirect_RecordSet"
sub13 = "sub_InDirect_RecordSource"
SQL = "select * from AAA"
rs.Open SQL, conn, 3, 1
For mm = 1 To 1
Set x3 = CreateForm
xx = x3.Name
Forms(xx).RecordSource = SQL
For Each uu In rs.Fields
Set x9 = CreateControl(xx, acTextBox, , , uu.Name)
Set x10 = CreateControl(xx, acLabel, , x9.Name)
x10.Caption = Forms(xx).Controls(x9.Name).ControlSource
Next uu
Forms(xx).DefaultView = 2
DoCmd.OpenForm xx, acNormal
Set Forms(xx).Recordset = rs
rs.Close
DoCmd.Close acForm, xx, acSaveYes
DoCmd.Rename sub13, acForm, xx
Set x5 = CreateForm
Set x7 = CreateControl(x5.Name, acSubform, , , , 400, 500, 2500, 2000)
Forms(x5.Name).Controls(x7.Name).SourceObject = sub13
x8 = x5.Name
DoCmd.Close acForm, x8, acSaveYes
DoCmd.Rename y1, acForm, x8
Next mm
End Sub
Private Sub C_Direct_RecordSource_Click()
y1 = "F_Direct_RecordSource"
sub13 = "sub_Direct_RecordSource"
Set x33 = CreateForm
xx = x33.Name
Forms(xx).RecordSource = "select * from AAA"
Forms(xx).DefaultView = 2
DoCmd.Close acForm, xx, acSaveYes
DoCmd.Rename sub13, acForm, xx
Set x9 = CreateForm
Set x10 = CreateControl(x9.Name, acSubform, , , , 400, 500, 2500, 2000)
Forms(x9.Name).Controls(x10.Name).SourceObject = sub13
xx = x9.Name
DoCmd.Close acForm, x9.Name, acSaveYes
DoCmd.Rename y1, acForm, xx
End Sub
Private Sub C_SourceObject_Click()
y1 = "F_SourceObject"
Set x9 = CreateForm
Set x10 = CreateControl(x9.Name, acSubform, , , , 400, 500, 2500, 2000)
Forms(x9.Name).Controls(x10.Name).SourceObject = "table.AAA"
xx = x9.Name
DoCmd.Close acForm, x9.Name, acSaveYes
DoCmd.Rename y1, acForm, xx
End Sub
Last edited:
