How to Build a Form in VBA?

Hello Fellow Contributors,

I am hoping this is possible, but I have yet to find an answer in the world wide web.
I have a subform in datasheet view that contains all of the fields of its recordsource, which in this case is a query.
My question is, is it possible in VBA to clear out all fields and labels on the subform, then change the subform's recordsource to another query, then re-populate the subform with all the new fields of the recordsource.

Background:
I am building a QA App that will display data to the QA resource for review, but depending on the data, I need to display different sets of fields. And based on the number of different datasets, I would rather not create a subform for each possible dataset, because this would total in the 20's with the possibility to grow further.

Also, any and all ideas are welcome if you have a better one than the above mentioned.

Any help would be appreciated,
-AJ

P.S. Even though I have spent the last few months answering questions, this is my first question asked, so I know I probably suck at it. =P

re: How to Build a Form in VBA?

Hi AJ. It is possible to modify under program control the recordsource of a form or subform and the individual properties of the controls defined for the form (for example, position, font, background colour etc). Adding controls at run-time is not practicable as far as I know, as if I remember correctly this can only be done in design mode for the form concerned.

I prefer to design subforms using Access's own facilities as much as possible - thinking of future maintenance by persons other than me.

One of my forms is part of an automated reporting system outputting data in three different ways. For one of those the mainform does not require a subform. The other two ways require different subforms linked on the same parent-child fields. To accommodate the latter I use the form's on-current event to test a value which tells me what type of subform I should be using, then change the sourceobject of the subform embedded in the mainform from the default value (the subform which is embedded in the form) to another subform which is present in the physical Forms collection should a maintainer need to change it.

The IF below selects between the two subforms, and could be replaced by a SELECT CASE if more than two were involved:

I designed the subforms in the normal way, so there was no need to change form control assignments and positions etc by VBA code.

In one other case where I needed to modify controls in a subform at run-time I designed a general subform then switched controls 'off' or 'on' by setting the .Visible property as necessary, altering the .Left positions of other remaining controls to fill gaps.

Either way, I'd advocate the hybrid approach of designing your forms as normal and making changes at run-time which fall far-short of a full from-scratch form design.

If I was faced with a situation where 20+ subform choices might present themselves I'd look for the common patterns involved and use the techniques above in combination - switching subforms in or out for major pattern changes, and controls on or off + moved if necessary for minor pattern changes.

Note that datasheet views are nowhere near as flexible when making such changes compared to continuous forms.

-Stewart

re: How to Build a Form in VBA?

This is something like one of the options I was keeping in my back pocket, I was thinking if all else fails, I would create a form full of all possible fields then I would hide those fields that do not belong in this current form view.

The reason I am using datasheet view is that it is as low maintenance of all options in my opinion, because all i have to do is hide the fields I don't want to see and the order takes care of itself without having to justify the fileds and label positions.

Sounds like my idea won't be possible, which I felt it wouldn't, thanks for your assistance Stewart Ross Inverness, I will keep this in mind and if no better ideas come to light I will go with something discussed here.

-AJ

re: How to Build a Form in VBA?

Using something similar to Stewart's approach, you could set up a certain number of unbound fields in a template, then, when you select the key data in the parent form you may be able to run code that would:

a) Change the recordsource of the child form to whatever query (recordset) you want.
b) Create a clone to enumerate the fields in that recordset.
c) set the controlsource of each appropriate field to the recordset's field.
d) make needed # of fields visible and any extra fields invisible.

something like this:

You'd obviously have to customize this based on your specific setup, but something like this ought to work.

re: How to Build a Form in VBA?

That sounds like it may work :)

Hopefully AJ will let us know how he gets on with it (for our curiosity).

re: How to Build a Form in VBA?

I really like that piece of code topher23 provided, it will probably take me a couple of days to get this together fully, but I will definitely let you know what I come up with in the end.

Thanks for the ideas so far,
-AJ

re: How to Build a Form in VBA?

Hi AJ. With Topher's code and the ideas so far it will be interesting to hear how you get on.

If you work with a recordset (via recordsetclone or by opening a recordset directly) you can find out the underlying data type of the fields concerned (an integer value from a set of vb constants) and this can assist in knowing whether you need to apply specialist formatting to a textbox assigned to a recordset field (e.g. currency).

If you want to cope with any number of potential custom setups - including setting the width of datasheet fields in accordance to nature of use and so on - consider storing the settings which determine custom processing in a table that is maintainable, in preference to being hard-coded into your VBA module (as this is less straightforwardly maintainable if changes are required in the future).

-Stewart

re: How to Build a Form in VBA?

I do something slightly similar to this with a list box - depending on which tab a user selects on a reports selection form, the list box is populated with a recordset, the number of columns, their widths, etc., get reset to display the proper information for the option. I've never done it with a subform, so I am very curious to see if it actually works!

re: How to Build a Form in VBA?

Well I am happy to report it that with some tweaking, and dumb-ing down, it did in fact work!

Here is the code:
The code would have worked right off the site if all the controls I had on the subform were text boxes, problem was I also had labels, so I needed a way to only assign "rs.Fields(x).Name" to my text boxes. Also, I needed to start x at zero since recordsets are zero based.

So - and I am sure there was a better way =) - I named my text boxes "txt0" thru "txt55" and my lables were named "lbl0" thru "lbl55", and the code then allows me to manipulate both with one run through.

was put in because I only needed to hide each column, not each control, so I know there is 2 controls per one valid column (Tetxbox and Label).

All in all this was the perfect idea that got me where I needed to get.

Thanks topher23 & Stewart for your inputs. =)
-AJ

P.S. I'd be more than happy to clarify anything if needed.

re: How to Build a Form in VBA?

I actually don't care about formatting in this instance because this will only be for displaying purposes and the person looking it is only looking for discrepancies in the data. But that is a thought if the need arises, thanks.

-AJ

re: How to Build a Form in VBA?

Awesome, glad it worked for you!