Two Experts Meet

[CJ_London]

NeoPa:
This thread starts with an answer from a separate thread (Insert Existing Fields to a Form through code or macro), but it continues in a way that is neither relevant nor helpful to that thread.

Nevertheless, it is a valid discussion that may prove helpful in its own right - especially to the more experienced Access developers among us :-)

I use a similar technique to ADezii but I create the form with around 50 textboxes plus associated labels plus 50 labels in the header and 50 textboxes in the footer - so around 200 controls altogether.

Labels are named in a similar way T0, T1, T2 etc for textboxes, L0, L1 for associated labels, H0, H1.. for header labels and as you may guess F0, F1 etc for the footer textboxes.

50 may seem to be overkill, but I have had occasions where that number is required.

All controls are visible, but left, top, width and height are all set to 0. Textbox control sources are left empty

All form views allowed are allowed.

This technique also works with .accde

The technique is similar to that used for dynamic reports based on crosstabs

in the form on load event, you have code along these lines

Private Sub Form_Load()
Dim fld As DAO.Field
Dim i As Integer
 
Me.RecordSource = Me.OpenArgs 'pass name of query or sql in the openargs parameter of docmd.openform
i = 0
For Each fld In Me.Recordset.Fields
    With Me("T" & i)
        .ControlSource = fld.Name
        .Move (i * (2000 + 60), 0, 2000, 300   'change these to suit positioning - 60 is a touch over 1mm and provides a margin between controls
   End With
   'do the same for labels
    i = i + 1
Next fld
End Sub

There is potentially a lot more you might want to do in configuration - change form view, add code to a control event, add conditional formatting or change other control properties such as backcolor, forecolor, font etc. Can also apply code to resize width of control dependant on contents. Pretty much all the sorts of things you might want to do with a specifically designed form.

[isladogs]

Hi CJ_L
Good to see you here providing expert advice at Bytes.com

Can you clarify when you use this kind of approach

[NeoPa]

How do I say this without giving away information I shouldn't give away? I know Colin Riddington's (isladogs) identity is public here so I'll start with :
You two know each other. Do you both realise that?

[isladogs]

Yes, we both know each other well.
CJ_L is one of the presenters for the Access Europe User Group, a moderator at another forum and has expert knowledge of his subject

[CJ_London]

yup, we go back a few years!

Hi Colin

An example you have seen is Access Studio - it is used to display the lists of tables and fields, the drop downs for templates and queries and of course displaying multiple query results. There was another one not available in the released version to select and add projects. These are all subforms and not populated in the way I described above as not relevant to the OP's question. All those subforms, all using the same sourceobject.

A form is a class and I treat this form as such. It contains no event code, only formatting code and I use this like a class, using sets and lets to pass through the recordsource (which can be a value list), the view required (continuous, datasheet, single) whether to show tickboxes (for multi select) , etc. At the moment it only contains labels and textboxes (you've seen my example for colourful/resizeable tickboxes which uses a textbox) and currently considering adding an image control.

The first 4 could use listboxes and combo's instead, and the queries could use a querydef but I wanted the ability to do more with the presentation - not implemented in the version you have seen, but for example the ability to lift the subform off the main form into another instance as a popup - so you could view table or fields side by side - and images if I implement it.

But the primary purpose is to display the results of a query generated by a user via a user interface - such as the sql editor in Access Studio or dynamic crosstabs. Appreciate that can be done by creating a temporary query and assigning it to a subform recordsource, or opening directly but it may be I want to display the results as a continuous form for better presentation or I need to include some additional functionality such as conditional formatting or control events which you can't do with a query object.

Final example would be a combo where I want to display say first, last and full name. I can use two subforms, one to display the selected record and the other as the dropdown. Again - I could use two additional textboxes that reference the the combo columns, but again it comes down to presentation.

[isladogs]

Yes I thought that Access Studio would be one of the primary uses.

For the benefit of other readers, I think its fair to say that such usage scenarios are non-standard ways of stretching the capabilities of Access to provide additional functionality. Access Studio is a great product which was demonstrated to the Access Europe User Group in April
A free 'starter edition' can be downloaded from my website: Access Studio.
I strongly recommend it.

However, in the context of the OP of the original thread, such usage shouldn't be necessary

[CJ_London]

For how I am using it I don't disagree - but the solution I provided in the thread (post #1 on this one) was a very simple example for a dynamic form which avoids the need for a user to create a form and controls.

I wonder if there is any chance to Insert Existing Fields to a Form via a VBA code.. Because I created the table using import from excel and the field name may vary each time depending on the excel file heading rows.

You could just as easily use a query, but the OP was asking for a form - I presume a continuous form