Access: Reducing several very similar forms to a single form (not so trivial)

[ahetland]

Greetings!

I am currently working on a fairly large Access project for my employer (an insurance company). In addition to the main menu and a number of forms, I have 18 separate forms, which fulfil essentially the same functionality, albeit with enough variation to make it difficult to fit it into a single form altogether. It almost goes without saying, that even minor layout changes are a major inconvenience. I would like to ask you, if you have any ideas on how to fit these separate static forms into one dynamic form.

About the system: It is a deal capture system for financial transactions. The primary functionality is to be able to save information about a transaction to a database and then be able to edit a transaction afterwards. However, there are 9 different financial instruments, which all have different fields; hence, depending on the instrument, the relevant form displays the appropriate fields. Additionally, there are two forms for each instrument, a create form and an edit form. This results in 2*9=18 very similar forms. My thought is that it would be neat to have one form, which could take account of the instrument and whether the transaction is being created or edited. However, it is not apparent to me how this should be accomplished; it also seems there are no discussions anywhere on the matter.

Below is an example of one of the forms. All 18 forms are similar in functionality and layout, but thir fields (as well as the number of these) vary. Also, some of the text boxes display calculated values based on VBA functions.

Any ideas and suggestions are warmly welcomed, I have been toiling with this problem for some time by now, but to no resolve.

[NeoPa]

Ideas would include designing controls for all requirements. Common controls need appear only once, but the controls that are required for all of the forms should also be designed in the one, all-purpose, form.

In the Form_Load() event procedure you can hide and show controls depending on your requirements, as well as move their positions and sizes around. This is the general approach behind flexible forms.

Another, simpler, approach would be to use Tab Controls, or Pages, to have the various definitions on a single form and simply force the required Page in the same procedure.

While the first may be a little more fiddly, I find it actually easier in the long run (but then I have little experience with Tab Controls).

Welcome to Bytes!

[patjones]

Bear in mind that it is possible to use the same form for both data entry and retrieval. Granted, doing that would still leave you with nine separate forms, but it is an improvement!

What you should do is take a long look at each control and categorize each one as 'common' or 'instrument specific'. One approach that you could take is to sensibly organize your common controls in the upper portion of a consolidated form, and then have the instrument specific controls arranged in tabs on the bottom portion of the form. Essentially, this approach would be a hybrid of what NeoPa is suggesting.

One other minor point...I notice in your sample form that the text and combo boxes are all the same width. If you adjust the width of the various controls to correspond to what will go in the control, you'll have more flexibility in laying them out on the form. For instance, you may want to put two text boxes on the same 'line' if their width permits it, and if they contain related information.

Layout definitely matters. It can make a form much easier to use if it is done right.

Pat

[munkee]

I would say take a look at tabbed controls as neopa stated, however keep back ups of your old system layout as you may find that the form takes a long time to load when you use tabs.

Tabs essentially just split your huge long form in to sections allowing you to click a tab or button to switch between the section that is visible. A draw back of this is that when the form loads it will need to load every control, which can take time.

There is actually a solution to this that has been found but I cant remember the exact location, but a google of "ms access tab control load on demand" might come up with the answer again. This basically only loads the data for the current tab when it is required.

I would also look in to grouping everything that is common on each of your forms to the top of the page and you can just give this section a name.

Below that you can have a sub form. This subform can have content loaded in to it dependent on a selection made in a combobox/dropdown menu. So the common information will be filled out, then the user will select the reamining work stream, this will tell the combobox which page to point the subforms recordsource towards and voila.

Your tables would likely need some tweaking so that your subform data is held in seperate child tables, whilst your main form data is held within a main parent table.

[ahetland]

Thank you for the advice and the nice welcome!

@NeoPa
Is there a particular advantage to Form_Load() compared to Form_Open? I think I will give this approach a try today – I have no prior experience with tabs, so that’s plan B.

@Zepphead80
I agree with you, there are changes which could improve the readability of the form. However, the layout has been specified to me by those who will be using the system. They are using it in its current layout and are happy with it.

@Munkee
I like the idea of using a subform to swap the variable fields. The biggest problem with this approach is that it would likely be too complicated to strip the transactions out of the current catch-all table and place them in separate tables. That would entail changes to the majority of the other system components to accommodate this change.

Once again, thanks for the replies, they are much appreciated! I'll try be back with an update later today!

[munkee]

Open (form) → Load (form) → Resize (form) → Activate (form) → Current (form) → Enter (control) → GotFocus (control)

Form open just occurs before form load and I believe is cancellable. So you are able to perform some easy security checks in Form open and if they fail cancel the opening before anything is loaded in.

It is as simple as

Cancel = True

It is important to remember form Open will only occur as the name suggests, as the form opens. If you have a form that is in the background and you bring it back into focus form open will not fire again. So it is important to understand the event order to ensure your code resides in the correct one.

[NeoPa]

AHetland:
Is there a particular advantage to Form_Load() compared to Form_Open? I think I will give this approach a try today - I have no prior experience with tabs, so that’s plan B.

I think Chris (Munkee) has answered that first point adequately. I see no good reason you shouldn't use Form_Open if you prefer for this.

As for the Tabbed form - I would not recommend using that unless you find the other approach too difficult. That and subforms would tend to highlight the differences, while I'm guessing you want each usage to have the appearance of being designed for purpose. I suppose what I'm saying is that having a design where the differences are handled in a way visible to the operator would be inappropriate.

I only raised the subject originally to cover the scenario where you felt handling the design in code beyond your scope.