Programtically create text boxes

[imarosel]

I have another one of my crazy innovative ideas that has probably been done a million times in a million better ways, but I can't find on the net or in my o'reilly books.

I have a lot of forms. A loooot of forms. Each form has one subform. The main body of the form is the same, the subform is what makes the forms different. Selections in the main part of the form change the data in the subform (datasheet view) Each subform has its own unique query and the values chosen in the main form change the where part of the query.

My question is this: Is there a way in vb to programmatically "build" the subform? All the fields in the subform query need to be put in the subform as text boxes.

I'll probably use queries but I still could use the knowledge of having vb build my forms for me.

 

[fneily]

As far as I know, you can only build forms and controls in design view. So you could use code like the following to create a form and a textbox:

Dim MyForm As Form, MyControl As Control
Set MyForm = CreateForm()
Set MyControl = CreateControl(MyForm.FormName, 109)
' the following code sizes, and moves a text box ' by changing the properties:
With MyControl
.Width = 1500
.Height = 200
.Top = 440
.Left = 200
End With

Couldn't you just open a subform based on a selection from the main form? In other words, just open the subform as another form based on the users selection?

 

[PHV]

Why not simply playing with the SourceObject property of the SubForm control ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[imarosel]

thanks PHV, that is actually what I have been playing with. The best way I have found to do this so far is create a "query container" that I change the values of with querydef.sql assignments with vb script. It doesnt' seem that I can directly insert SQL into the sourceobject property, but that I have to call a pre-set up query. Any hints?

 

[SgtJarrow]

imarosel,

Don't know if this will help, but I use the below code in one of my apps. All the reports have the same basic structure. So instead having various querys and the like in place, I create one query and save it. It is only for name purposes really. My report uses this as the "source". Before opening the report, I determine which data to use, then modify that one query's SQL...the report never know the difference becuase the query is always named the same.

Again, you might be abel to use something smiliar and manage everything through a bit of code...

Dim db As DAO.Database
    Dim qselAuditAverages As DAO.QueryDef
    Dim strSQL As String
    
    Set db = CurrentDb
    Set qselAuditAverages = db.QueryDefs("qselAuditAverages")

    strSQL = "SELECT TOP " & Forms![frmReports]![txtTopDrivers] & " qselFieldAuditTotalScoreAndNumberOfAudits.DriverName, " _
        & "[Total]/[NumberOfAudits] AS AuditAverage " _
        & "FROM qselFieldAuditTotalScoreAndNumberOfAudits " _
        & "WHERE (((AuditDate) Between #" & DateAdd("d", -1, Me![cboStartDate]) & "# And #" & DateAdd("d", 1, Me![cboEndDate]) & "#)) " _
        & "ORDER BY [Total]/[NumberOfAudits] DESC;"
    qselAuditAverages.SQL = strSQL
    
    Set qselAuditAverages = Nothing
    Set db = Nothing

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[jibberski]

Make a bunch of controls on your form and hide them.

'Keep count of what you've used in public vars
DropB = 1
CheckB = 1
TextB = 1

'DropDown List
Me("cmbBox" & DropB).RowSourceType = "Value List"
Me("cmbBox" & DropB).ColumnCount = 2
Me("cmbBox" & DropB).BoundColumn = 1
Me("cmbBox" & DropB).ColumnWidths = "0;2" 
Me("cmbBox" & DropB).LimitToList = True
Me("cmbBox" & DropB).ColumnHeads = False
Me("cmbBox" & DropB).RowSource = "0; ;" & MkeDDLst()'Text
Me("cmbBox" & DropB).ListWidth = "3000"
Set ctl = Me("cmbBox" & DropB)
ctl.OnLostFocus = ""
ctl.AfterUpdate = "=SaveCtl()"  'How I capture input
DropB = DropB + 1

'CheckBox
Set ctl = Me("Check" & CheckB)
ctl.OnLostFocus = ""
ctl.AfterUpdate = "=SaveCtl()" 'How I capture input
CheckB = CheckB + 1

'Textbox 
Set ctl = Me("txtBox" & TextB)
ctl.OnLostFocus = ""
ctl.AfterUpdate = "=SaveCtl()"  'How I capture input
ctl.InputMask = "##################"
ctl.TextAlign = 1
TextB = TextB + 1

Jibb