What is the most "generally accepted" way to use a single form to populate multiple tables?

[Xixor]

When my users go to add data to the database, they will need to enter data consisting of several items that, due to normalization, will be stored in separate tables.

I really need my users to only interact with ONE form. Suffice to say that having the users open multiple separate forms when using the database would not be intuitive in my situation.

I think one way to accomplish what I need to do is possible by having one "master" form who's record source is one of the tables, and then "embedding" subforms into that master form, with each subform's record source connected to their respective tables. I can probably mess with the formatting of each subform to make them "blend in" with the master form so from my user's perspective, they are simply interacting with one form, when in reality they are populating several different forms.

My main question is, is there a simpler alternative to this method by using only one form? Possibly by having individual combo/text boxes who's record sources are connected to the other tables?

The main reason I shy away from subforms is because my VBA skills are very rudimentary, and one of the issues that I will encounter with using subforms is related to access "autosaving" data when moving focus from the main form to the subforms. I really need to have ONE save button that the user is forced to click to save all changes to the master form and subform, and NOT have access autosave when moving between the forms, and from my reading on many other websites and forum posts, I fear the coding requirements of accomplishing this are complicated at best, and beyond my current ability. I probably can figure out how to have one save button if I am only using one form though.



Make sense?

[orange]

XiXor,

It is a theoretical question at best --until we have more details.
In simple terms, what is the business involved?
What is it exactly that "you need to do"?
The devil is always in the details -and there many be options to accomplish what you need.
I recommend that you do not prejudge the user interface. Determine the requirement, then look at options, then feasible options. Make a plan, and a model, then vet the model with the "users". If you involve the users during design and test, they will become part of the team/solution.
Good luck.

[Xixor]

My users will go into the database and enter information related to grant applications. Each application will have a number of different pieces of info that need to get captured in the database. With help from another forum member here I was able to normalize my scenario into multiple different tables. My users have unanimously and vociferously indicated they want one single form to enter data related to one single grant application and they want a save button that they have to intentionally push to record their changes to the database. This is intuitive and I agree with them.

When my database was not normalized and I have everything going into a single table, it more or less worked; I was able to design some conditional formatting for the form to highlight fields that needed to be filled in, and added a save button that would save all changes to the form to the underlying table without having access autosave anything. Now that I have multiple tables that probably need to have their own subforms, I am pretty lost on how to accomplish the single-save button feature.

Here is a post that more or less describes my dilemma: https://stackoverflow.com/questions/...ng-next-record

[orange]

When my database was not normalized and I have everything going into a single table, it more or less worked;

I don't think more or less is the quality expected by your users/proponents.
Everything in one table is more spreadsheet technique than relational database.

My reading of the stack overflow link suggests a specific process (enter data related to one single grant application ) with a form interface and some validation logic to ensure fields were filled according to rules, values met the "smell test" and the logic would save/reject as necessary. On validation failure/rejection, what do you want to occur? Normally depends on your validation rules -- re-ask the questions; revisit fields not filled etc.

I detect a certain fear of the unknown --and I can assure you we have all been there.
Analysis of requirements; mockups to clarify uncertainty; get the design correct before getting too involved in physical database.

You could show us your data model; some test data and some sample scenarios. Or you could post a copy of your current database (anonymize the names or private data) with test data and sample scenarios.

You might also get some insight from a google search with youtube ms access user interface design.

Again, good luck with your project.

[Xixor]

Here is a screenshot of the relationships.

Users need to go into the database and enter information related to individual grant submissions.

Grant submissions involve data on many different tables, as well as the other fields in tblSubmissions.

Users want/need a single form to interact with when entering data on a grant submission. They do not want to open multiple forms to enter different pieces of data for a single grant submission. They do not want to navigate multiple tabs within a single form. They want everything in one place that can be seen at a glance. They want conditional formatting to highlight fields that need to be filled in (some fields will remain Null for a grant submission based on the value in other fields and the highlighting needs to dynamically update as they are filling out fields). They want a single save button that captures all edits made to the single form.

Any general ideas on how to approach this would be helpful!

Thanks.

[orange]

Do you have sample data and a sample scenario for creating an individual grant submission to test your model?

[Xixor]

I don't have a pre-packaged example that I can easily share.

Each grant submission will have a principal investigator, due date, analyst, prime sponsor, anticipated start date, anticipated end date.
Each submission may or may not have co-investigators, mentors, subaccounts.
Principal Investigators, Co-Investigators, and Mentors all should pull from the Investigators table.
Subaccounts (in and out) would need to pull from the Departments Table.

The users go into the database and fill out the applicable information for each submission record. They want one single form to represent that "submission" even though some of the data applicable to that submission will be stored in multiple different tables.

Here is a screenshot of the main "submissions form" from my old database (before it was normalized). This form worked perfectly, and all the fields were linked to a single table, but there were other problems related to the database not being normalized which prompted a re-design with everything normalized into multiple tables.



You are correct - I have a lot of fear of the unknown at this point because it took me about 50 hours of trial and error, learning VBA from scratch, google and forum searching, to get that form working perfectly but now faced with accomplishing the same thing (the conditional formatting and the save feature which was tied to the close button in the old form) in this new database with up to 7 subforms seems insurmountable to me at the moment.

[Micron]

IF you can build a query that joins all the necessary tables, each of which has the necessary characteristics AND you can update any of the query fields that you would need to if it were a form, then you could probably base a single form on this query. However, as mentioned, all of the necessary parts would have to be in place to ensure that you cannot create orphan records through missing data or data that gets updated and no longer relates. Table relationship design (referential integrity, cascading updates and deletes) may provide that protection for you. However, other things would have to be in place as well - such as only equal joins on tables with indexes.

Perhaps there are other alternatives
- unbound form(s) but would require some code to at least write to tables and possibly do form data validation
- simple table level validations and restrictions (e.g. a field value must be between 5 and 10, cannot be more than 6 characters, etc). This can minimize validation code

There is a lot you can do to control user flow (e.g. make subform invisible until parent record is complete) but unfortunately requires code. The upside is that whatever you can think of, it's likely that at least hundreds of people have already had that issue solved, meaning there's many sources of sample code available. The trick is to know how to phrase your questions.