All the forms in Navigation Form used for the same record?

[PEBCAK]

I have a very large structured multi-level questionnaire, not really suited to be filled as a single Access form.



Hence, I created forms for different questionnaire sections (they do contain VBA to perform some complicated validations and improve user experience, e.g, events Current, BeforeUpdate, BeforeInsert). This helped to keep the forms controls and code at a manageable size.

I now wanted to use Navigation Form with tabs on left and top, to make editing the record user friendly and logically split into structured sections matching the original questionnaire.

However, I could not figure out how to make the Navigation Form keep the individual forms in sync for the record, when switching between the forms or moving between the records. I turned off the navigation buttons and record selector in the individual forms and turned them on in the Navigation Form. I do understand that only one individual form is loaded at a time in a Navigation Form but I am still stuck after trying and failing for almost a whole day.

My naive solution would be to store a record ID in a bound control in the Navigation Form and use it when loading the "child" individual forms, but I am not sure if this is the way. Nor do I know how to best implement this, i.e., create/load/store a record with a given ID in a form and not risking something goes wrong and the data go out of sync.

I am on Office 365 and Windows 10.

While I have decent programming experience and some database knowledge, I am not really familiar with VBA or Access, so I may have missed something fundamental. Out of desperation, I briefly tried adding the individual forms as subforms on pages of a tab control (that would be considered largely inferior to using navigation form with two-level structure), did not seem simple there either.

Thanks

[June7]

Don't think I've ever used Navigation Form - don't like it.

You have multiple forms to edit the same record? Saving the ID on main form does seem reasonable (or use Global variable or TempVars) then use BrowseTo method to go to that record when form is loaded.

Can use code to emulate Navigation form behavior of loading form when need by setting subform container control SourceObject property. Arrange multiple command buttons to look like navigation tabs.

[PEBCAK]

Thanks for the quick reply.

In my original post, I failed to communicate that one of the main issues I am battling with is that with Navigation Form, only one "child" form is loaded but I need to validate the data with VBA for all the forms.

I do the validation in individual forms from BeforeUpdate. So I would either need to catch adding/modifying the record in the Navigation Form and somehow iterate through all the child forms and validate them.

It is probably not a good idea, particularly for a person like me who is new to Access. In the end, I may end up degrading the UI and use (very cluttered) tab control instead.

[June7]

You already understand that only one form is loaded at a time so how could you validate inputs on other forms? Even if a form is set up for input to only certain fields, all fields could be available for reference if the form RecordSource includes them. So if you need to check for value of a field not displayed in order to validate a user input on current form, that is possible.

[PEBCAK]

You already understand that only one form is loaded at a time so how could you validate inputs on other forms?

My idea (perhaps not the brightest one) was when validation was needed to display a some UI blocking "Validating, wait please..." window and load all the forms programatically one by one and validate them. When a data does not pass validation, I believe it is good manners to show the form, focus the offending control, and then tell the user what went wrong.

But as I said, am new to Access and perhaps this approach is not feasible, so I may end up doing something entirely else.

[June7]

But data entered by those other forms should have been validated at that time. Why would those inputs no longer be valid due to entry on another form? And if you switch to form with offending data, what should happen to the inputs on form that will have to be closed? Really sounds like should abandon Navigation Form altogether.

[Micron]

pages of a tab control (that would be considered largely inferior to using navigation form with two-level structure)

I don't agree with that at all. There's a reason why June7 never uses them. Add me to that list, save for experimenting and trying to help out those that stick with them. Maybe there's something wrong with the underlying design? I mean, why would one questionnaire need several forms?

[PEBCAK]

But data entered by those other forms should have been validated at that time. Why would those inputs no longer be valid due to entry on another form?

By request, some database fields do not have default values nor they can be null (at the form level). With Navigation Form, the individual forms are loaded by user actions, so some forms may not be shown at all when adding a record so the VBA validation (called from indivdiual forms BeforeInsert handlers) was never triggered.

And if you switch to form with offending data, what should happen to the inputs on form that will have to be closed?

I did not even come that far to think about this.

[PEBCAK]

I don't agree with that at all. There's a reason why June7 never uses them. Add me to that list, save for experimenting and trying to help out those that stick with them. Maybe there's something wrong with the underlying design? I mean, why would one questionnaire need several forms?

I am not sure what you disagree with but here is my explanation why I tried to use Navigation Form:

At first glance, Navigation Form seemed as a great solution to deal with a very large, multiple-level questionnaire.

It would allow me to naturally structure the Access form into two levels, main sections tabs on the top and tabs with subsections for a given section on the left. While a similar effect may (?) be possible with nested tab controls, it would mean losing a precious vertical screen space.

I could probably achieve a similar effect using buttons on the left showing/hiding the nested tab controls on the fly, but doing this seems to be overly complicated and error-prone.

[Micron]

Even one page on one tab control can load umpteen different forms, just like the nav form. Not that you'd necessarily want to do that; just sayin' 'bout anything is possible if you know how. FWIW, I never said to nest them, but maybe you're thinking you need to do that. One thing I've never tried is to put any controls in the navigation form header (or additional ones in the detail section) but suspect that's a way to make "connections" between different loaded forms in a navigation form. Not really sure what you're trying to do re validation so I haven't the understanding to offer focused suggestions.

[PEBCAK]

In the end, I decided to settle down and use TabCtrl.

I also had to abandon using subforms, as using them seemed to run into similar issue, i.e., keeping the full record valid using different forms validation VBA code. This could be somehow solvable, but at the moment, I am lacking both time and knowledge.

Putting everything on a single form has unfortunate consequences when it comes to form controls and code maintainability, but I guess it cannot be helped.

I am still far from finished, so I hope I will not run into some maximum controls per form limitation.

Thanks to all, for now

[Micron]

I believe the max is 255 and is based on accumulated history and not just how many happen to be on there at any given time.

[isladogs]

Officially the max number of controls over the lifetime of a form is 754 - see Access specifications (microsoft.com)
However that isn't up to date. Tests done a couple of years ago gave a max limit of 1038.
See post #11 in Exceeding Limits via Code? | Access World Forums (access-programmers.co.uk)

Even so, I wouldn't recommend creating any form with anything like that number of controls

[Micron]

754? I was close...

[isladogs]

Close but no cigar!
And even further away from 1038!
My motto in such cases is, if in doubt, its probably 255