need help adding controls to subform

[DougP]

I have 5 tables that I want to allow the user to choose from, and then show records based on that table. I thought I would have just one subform and change the recordsource and textboxes /labels each time they choose one.
the SQL below is for one table from a Select (thus the End Select, ignore it for now)
If I have the main form open and the subform showing in it, it gives error can only add controls in design view.
Any ideas which is the best was to do this? Or is it better to have 5 subfoms already set up and hide them all and show which ever one they choose?

            SQLCode = "SELECT [Clearance Checklist].[Project Number], [Clearance Checklist].Client, " & _
                      "[Clearance Checklist].Contact, [Clearance Checklist].Date " & _
                      "FROM [Clearance Checklist] LEFT JOIN [Clearance Checklist1] ON " & _
                      "[Clearance Checklist].[Project Number] = [Clearance Checklist1].[Project Number] " & _
                      "WHERE ((([Clearance Checklist1].[Project Number]) Is Null));"
    
    End Select
    
    Rs1.Open SQLCode, Conn2, adOpenStatic, adLockOptimistic
    TotRecs = Rs1.RecordCount
    Me.txtRecords = TotRecs
    Set frm = [frmExport subform].Form
    frm.RecordSource = SQLCode
    For Each fldLoop In Rs1.Fields
        Debug.Print fldLoop.Name
        Set ctlText = CreateControl(frm.Name, acTextBox, acDetail, , fldLoop.Name, 5, 5, 30, 7)
       ' , "", "", intDataX, intDataY)
    ' Create child label control for text box.
    Set ctlLabel = CreateControl(frm.Name, acLabel, , ctlText.Name, fldLoop.Name, intLabelX, intLabelY)
    Next

TIA



DougP
< I Built one

http://www.robots2000.com

 

[MajP]

Build five subforms and just change the "sourceobject" of the subform in code.

 

[MajP]

If these forms are very similar in format and number of fields, then you can go with just one subform and you unhide any extra controls.

It is possible to add controls dynamically:
open a form in design view but hidden
add/delete controls
save the hidden form
open the form

However, I do not recommend doing it this way. There is a lot of room for problems doing this, and you will not save any time or resources.

 

[DougP]

OK I created a common subform, but when I make a control not used in that SQL statement .visible = false it does not hide it just shows up with #Name? in the box.

        Case "Air Monitor"
            SQLCode = "SELECT AirMonitor.[Project Number], AirMonitor.[Building Name], AirMonitor.DateAssigned " & _
                      "FROM AirMonitor LEFT JOIN AirMonitor1 ON (AirMonitor.DateAssigned = AirMonitor1.DateAssigned) " & _
                      "AND (AirMonitor.[Building Name] = AirMonitor1.[Building Name]) AND (AirMonitor.Number = AirMonitor1.Number) " & _
                      "AND (AirMonitor.Service = AirMonitor1.Service) AND (AirMonitor.[Project Number] = AirMonitor1.[Project Number]) " & _
                      "WHERE (((AirMonitor1.[Project Number]) Is Null));"
                      
                      Me.frmExport_subform.Form![Project Number].SetFocus
                      
Me.frmExport_subform.Form.RecordSource = SQLCode                      Me.frmExport_subform.Form.Service.Visible = False
                      Me.frmExport_subform.Form.Number.Visible = False
                      Me.frmExport_subform.Form.Date.Visible = False
                      Me.frmExport_subform.Form.[Client Job Num].Visible = False

DougP
[r2d2] < I Built one
[URL unfurl="true"]www.robots2000.com[/URL]

 

[DougP]

It seems nothing changes the controls if they are on a subform. can't make them invisible or even make their width=0. so that's not going to work.

DougP
< I Built one

http://www.robots2000.com

 

[DougP]

Ok I got it
set the ColumnWidth to whatever 0 or 10 or ????
Me.frmExport_subform.Form.Number.ColumnWidth = 0 to hide it
Me.frmExport_subform.Form.Number.ColumnWidth =10 if its a small amount of data
To see how wide a column is, while you are in View mode not Design mode, highlight the text box in the subform and click the Format menu then Column Width. the value it is currently set at will show.

DougP
< I Built one

http://www.robots2000.com

 

[DougP]

Oh One more thing if you can't see your columns for some reason When in View mode, click the Format menu then unhide columns and check the boxes next to the ones that are hidden.

DougP
< I Built one

http://www.robots2000.com

 

[MajP]

Yes you can make controls on a subform invisible/visible. Not sure what you are doing incorrectly, but do not assume just because your code does not work that the functionality is not available.