Designing Forms Questions (1 Viewer)

jamesave

New member
Local time
Today, 10:16
Joined
Apr 26, 2020
Messages
16
Just wondering on how a good form is being created.. A bit confused to differentiate a form that will show the result of a query in the subform and how to differentiate a form that would allow edit or new record of the said subform.
Looking at northwind databases, one can understand from the main menu that they have two different links: one to create a new record via new purchase order or new customer order and another ones to simply view all the different table or queries.. is this how it's typically done?

Looking at the various training videos, one simply create a form and the subform without mentioning that there is a need to differentiate within these two substantially different functioning forms. What's your thought on this matter? Is there any good practices guide that one should follow?
 

June7

AWF VIP
Local time
Today, 09:16
Joined
Mar 9, 2014
Messages
5,423
Not really sure what you are asking. Design objects to serve the need. If you want to allow user different pathways for entering data, then yes build forms to accommodate.

I try to create as few objects as possible. I have designed form to serve multi-purpose - edit existing as well as add new as well as just to view - then use code to manage how form is presented to user.
 

Isaac

Lifelong Learner
Local time
Today, 10:16
Joined
Mar 14, 2017
Messages
8,738
Many will disagree with me, but personally for me it has been easiest to design one form for editing and one form for adding new, the latter where the end user is just heads down data entry and wants to use all their keystrokes to that end. Of course, it can be done either way with enough intricate programming behind the form.

Personally if you are starting out brand new I would not start out creating subforms, but just creating a form that works for adding (or editing, whatever) data to a table. Start simple. Just my opinion.
 

plog

Banishment Pending
Local time
Today, 12:16
Joined
May 11, 2011
Messages
11,611
A bit confused to differentiate a form that will show the result of a query in the subform and how to differentiate a form that would allow edit or new record of the said subform.

If I want users to be able to just see data I build a report. If I want users to interact with data I build a form.
 

Micron

AWF VIP
Local time
Today, 13:16
Joined
Oct 20, 2018
Messages
3,476
Well, for the OP's sake, I'll add my vote to the discussion - 1 version of a report or form whenever possible. Otherwise,
a) what better way to learn Access concepts
b) else where do you draw the line? 1 form each for user levels 1, 2 and 3 and the fields they are allowed to alter, plus edit, append, view-only versions for each - 9 similar forms in all? I realize that's not what was said, but in following that approach, that could be the extreme result. I have helped people who have uploaded db's here with objects that follow that approach.
 

Isaac

Lifelong Learner
Local time
Today, 10:16
Joined
Mar 14, 2017
Messages
8,738
that could be the extreme result. I have helped people who have uploaded db's here with objects that follow that approach.
Yes, it can be taken way to far to an extreme. For the sake of clarification, I'll demonstrate how much I agree with not taking it too far in this: Years ago I switched to a "one size fits most" approach to my databases. First of all, I stopped creating an new access database every time I turned around. Common mistake, I noticed! Instead I had one database for an entire department - for a plethora of needs - and simply used the guts of the database to work out the details. Up until that point, my predecessors had created dozens and dozens - I"m not sure if it might not have been 100's - of databases for various "needs". It was crazy but I've found it's common. One funny thing about that...nomenclature. In my earlier tenure, a database might start out being called "The title release reports". Later, when workflows were added, I wished I'd named it just "Title Release". Later, when tons of workflows were added for totally different work, I wished I'd just named it "Production.accdb". Nothing terribly important, but was a learning experience.

Then I switched to using one huge menu form with a tab control on it, and I absolutely loved that and my users found it simple too. Different tabs for different user functions, levels, etc. I still had a few popups here and there, but everything revolved around the big tabbed form.

Same with queries. Often having one qryBucket, whose querydef's .sql property I would manipulate, instead of 10 different versions of the same query, or even 10 different queries.
So I do agree. :)
 

AccessBlaster

Registered User.
Local time
Today, 10:16
Joined
May 22, 2010
Messages
5,824
The Northwind employee sample feels polished when you interact with it, one form for viewing and a popup for editing. But I usually allow for viewing and editing within the same object. I create for 10 or less users, and have very few issues.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:16
Joined
May 21, 2018
Messages
8,463
I think many novice users tend to do too much in a single form. They just keep adding and adding functionality. Search, filter, add, edit, view in one form. Then maybe the ability to add both parent and child records at the same time. I look at the form and I can not figure it out. This can be done but this takes experience to make this usable and understandable. I personally like to separate these functions, something like an Email utility, think Outlook. You can search, filter, sort your emails on the main page. To add new you click on a button that brings up a single form view. To view details and edit you click on an existing record to bring up single form view of that email. I think new users will find it easier to separate
viewing, filtering, searching, sorting
adding
editing
 

jamesave

New member
Local time
Today, 10:16
Joined
Apr 26, 2020
Messages
16
Thanks for all the responses.

What Micron said is what's on my mind. Reading from all the responses here, I think there won't be such distinct line. It maybe depends on the original intent of the application, and those itself can change as the application has been built. Maybe the analogy that I can take is when we originally intent to design a car, but as people see how useful it is they would fit in more people and force the car to be a bus. As such, maybe the need to redesign the whole application is unavoidable. There won't be any right or wrong path, simply experience it and try to maintain less objects as possible.

I probably need to add that I started my database based on the normalized tables and not a simple database design. (in my opinion, please correct me if I am wrong). I am using the following data model: tracking_inventory_for_restaurants from databaseanswers.org

(Just the Menu_item, Menu_item_ingredients and ingredients Tables)
This model necessitates me to create a form with subform .. (Again, please correct me if I am wrong)

Creating a subform, the strength of Access, looks quite simple at the beginning. But perhaps, I am getting lost on how the data are entered and maintained within those tables through the forms.. and this is why I posted this question.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:16
Joined
May 21, 2018
Messages
8,463
Not sure I understand your statement/question, but I will guess.
You picked a hard one to start with
Menu_item, Menu_item_ingredients and ingredients Tables

You have a many to many relationship with a junction table (Menu_item_ingredients). So a menu_item can have lots of ingredients and an ingredient can be related to many different menu_items.

The subform control is really powerful and does a lot for you. If you had to code all the functionality from scratch it would be difficult.

Menu_Item
Menu_ItemID ' primary key
.... other fields for a menu item

Ingredients
IngredientID ' primary key for an ingredient

Menu_Item_Ingredients
Menu_ItemID_FK ' foreign key to menu item
IngredientID_FK 'foreign key to ingredient


In order to assign an ingredient to a menu item you need to assign both foreign keys above. So
1 1
1 2
1 3
1 7
2 2
2 3
2 8

So MenutItem 1 has ingredients 1,2,3,7
Menu Item 2 has ingredients 2,3,8
(many to many since ingredients 2 and 3 are related to both)

In the mainform you can move to a MenuItem. The subform will automatically filter to the related ingredients. Also when you select a new ingredient in the subform it will associate it to the menu item in the main form. You will just pull down choices from a combobox and the subform will create those foreign keys.

If you tried to code this another way it would be difficult to replicate these features.


Now some people will allow on this form the ability to add new menu items and new ingredients. This will get super confusing. I would have pop ups to add a new menu item and add new ingredients. Then you can select associate the new items.
 

Micron

AWF VIP
Local time
Today, 13:16
Joined
Oct 20, 2018
Messages
3,476
It maybe depends on the original intent of the application,
That is certainly key. Can't disagree with what MajP says about complicating an already complicated process. I had something simpler in mind, like some of the stuff that gets posted here: a single record form to look at data, one for adding records to it, one for editing - all the same otherwise. Reports might be another/better example: one for last quarter, one for next quarter, one for processA, one for processB - all the same otherwise.
 

jamesave

New member
Local time
Today, 10:16
Joined
Apr 26, 2020
Messages
16
Now some people will allow on this form the ability to add new menu items and new ingredients. This will get super confusing. I would have pop ups to add a new menu item and add new ingredients. Then you can select associate the new items.

I got confused from this possibilities, and your ideas are very clear and concise. Thanks!
 

Users who are viewing this thread

Top Bottom