MS Access your Data with Sub-Forms (1 Viewer)

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 05:44
Joined
Jul 9, 2003
Messages
16,224
MS Access is such a brilliant tool, so easy to use, so powerful that I sometimes think people miss its obvious simplicity, the simple ability that makes it so powerful.

I’m not talking about Relationships
Many people get bogged down in the relationship issue, working out the relationships early on and imposing them on the system. My advice is don’t; don’t use relationships, only use then when you can see the benefit, and if you do use them use them right at the end when you know how your database fits together. Incorrectly formed relationships cause problems that are difficult to locate and can cause you headaches in the development process. And they’re not necessary; your database will function quite happily without relationships.

I’m talking about Sub Forms
Yes that’s right; I said “sub forms” Subforms are what makes MS Access so powerful and so easy to use. Now you might be saying “how’s that then”? Well, in my last article “Excel in Access” I wrote about a fundamental misunderstanding of how to structure your data in MS Access, once you understand the difference between “flat file” and “relational” see note (a) you will see that sub-forms used correctly offer a simple and easy way to display information from the extra tables you have created. If you haven’t found it necessary to use Subforms then you probably have a very simple data structure or you have a problem!

Sub-Form Windows
When I said “sub forms are what makes MS Access so powerful” that’s not strictly true, I should have said it’s “sub-form windows” see Note (c) are what makes MS Access so powerful”, I know it’s a moot point because they are one in the same, you can’t have one without the other. When you drag a sub-form on to a main form a “sub-form window” is automatically created, what you actually see is a sub-form positioned on the main form, what you don’t see is the sub-form window surrounding the form; the sub-form window is the container containing the sub-form within the main form. see Note (c) It may seem like I’m being pedantic about this minor difference, however it’s this intermediary control, this sub-form window that has all of the power.

Link Sub & Main
The sub-form window see Note (c) has some interesting properties that you can set, one of the most interesting properties is the ability to match the field on the form within it to the matching field on the main form, by identifying these two fields, you in effect link them, causing the field on the contained form to update automatically when a new record is added to the main form. (This alludes to another frequent problem would be programmers have with MS Access, see note (b))

Fragment your Table
To use MS Access effectively (as covered in my article “Excel in Access”) particularly if you start off with data in a flat file format, then you need to modify this data structure by counter intuitively dividing it up into several smaller tables. The problem becomes “how do you display this fractured structure? Before, you had a very nice flat file in which it was easy to see how the data related together. Now you have all these extra tables which have to be combined to re-form the original data. That’s where sub-forms come in, they very neatly and easily offer you a way to display the recombined data.

Why MS Access is so Powerful
Most people start off with data in a “flat file format” they then find themselves having to fragment this nice easy to understand flat file structure into several apparently disjointed tables. This fragmentation creates the next problem, how to assemble this data into a presentable user interface? The answer “sub-forms” it is this simple ability offered by sub-forms (sub-form windows) that makes MS Access so powerful.

Note (a)
I had, and still have the misconception that "Relational" refers to relating data from different Tables via a key value. The following paragraph exposes this misconception of mine... (It’s a useful misconception so I will keep it!)

Extract:
The relational model is thus named, not because you can relate tables to one another (a popular misconception), but as a reference to the relationships between the columns in a table. These are the relationships that give the model its name; in other words, relational means that if several values belong to the same row in a table, they are related. The way columns are related to each other defines a relation, and a relation is a table (more exactly, a table represents one relation).

From:
The Art of SQL --- Authors: Stephane Faroult & Peter Robson
1.1. The Relational View of Data

Note (b)
I note from the forum where I answered questions (UKAWF) that a common problem people have is wanting to enter data into a sub-form before data is entered on the master form. This won’t work because the subform window uses the unique reference number of the main form to give the subform record the linking information.

Note (c) - (the correct term for this sub-form window is “Subform/Subreport Control”) Thanks to Bob Larson for pointing out this error.

I'm not sure but when I get the time I’m going to explore this issue and see if there is a way round it. My suspicion is it will be possible but difficult to implement in a “safe” way. I’m pretty sure there’s something to be learnt about the fundamental operation of this subform master form relationship from attempting this “backward” operation and that’s a good enough incentive for me to undertake what will probably be a fruitless task.

Uncle Gizmo (Tony Hine) is a prolific blogger (Please note prolific does not mean good) not just blogging about MS Access all sorts of issues anything he finds amusing. He's not really bothered if other people don't find it amusing either! He is particularly amused while writing this in the third person! (I had to write this "third person" line after I saw something similar written by an MS Access book author~)
 
Last edited:

MStef

Registered User.
Local time
Today, 05:44
Joined
Oct 28, 2004
Messages
2,251
Hello Uncle Gizmo!
I agree with you, MS-Access is so simple and powerful.
 

mdlueck

Sr. Application Developer
Local time
Today, 01:44
Joined
Jun 23, 2011
Messages
2,631
I completely disagree with those statements. You should know BEFORE you start building your database what those relationships are going to be.

<snip>

Relationshps SHOULD be used - to handle Referential Integrity.

The one sharp spot I have found with working with Access and Forms is that it expects all records to be in one table on said Form, else requires the use of subforms.

For the application I am building, I did my pretty / organized DB schema on the SQL back end, and for front-end I end up doing large / complicated SELECT transactions to a SINGLE FE temp table and merge all of the various fields from the various BE tables.

One screen has six or seven value look-up tables associated with it. The complex SELECT statement for that screen selects both the ID from the main table and the associated string "name" of the ID from the look-up table. From that edit screen, dialog forms may be opened to select a record from the pick list, aka different ID number and associated string.

Then a Stored Procedure is used to COMMIT the data back to the SQL BE.

Thus only ONE table has actually been edited by that Form, just to get all of the data needed to populate the edit screen many JOIN statements were needed.

Paradox for Windows, OTOH, fully supported Data Models being attached to forms. It was possible to combine/join several tables and edit/display them in a DB grid control. R.I.P. Paradox for Windows. (Access has much better drop down Combo boxes... win some / loose some.)
 

dportas

Registered User.
Local time
Today, 05:44
Joined
Apr 18, 2009
Messages
76
You should know BEFORE you start building your database what those relationships are going to be. If you do a PROPER DESIGN up before just jumping in and starting to build tables and forms, etc. you will KNOW what your relationships are going to be and setting them upon initial design is going to help keep you from getting frustrated when you go to try to set them and existing data in the tables keeps you from properly defining them.

Let's be clear what we are talking about here. A "relationship" (in Access terms) means at least three quite different and mostly unrelated things: 1) A line drawn on a diagram; 2) A default method for joining tables in queries; 3) A foreign key constraint.

Gizmo is correct in that none of those three things is an essential feature of relational databases. Items 1 and 2 are utterly irrelevant in database design. Only item 3 - foreign keys constraints - is really worth thinking about. Foreign key constraints are of course a useful and often important data integrity feature but they aren't a defining feature of every good database design. Sometimes other data integrity constraints are more important.

In fact a foreign key constraint is merely a special case of an inclusion dependency constraint where one side of the dependency just happens to be a candidate key. If your DBMS supports more general or more powerful kinds of constraint enforcement or if your database design doesn't happen to require the support of foreign keys (as some temporal database designs do not) then you could quite reasonably do without them.

Database deisgn should be concerned with accurate representation and data integrity issues. It should not be driven or constrained by UI features in any developer tool. Arguably Microsoft did a disservice to its Access users who (perhaps without proper training in database design) are presented with the peculiar construct that Access calls a "relationship" as if it were some kind of key to a successful database.

Regarding development methodology: agile, iterative development is commonly the rule rather than the exception these days. It's fairly common not to start with a fully formed database design but to evolve the design frequently throughout the development lifecycle. My own approach is to implement integrity constraints as early as possible and then remove them if they later prove to be inadequate. I would never asssume that every constraint can be added before coding begins however. It is a principle of the agile approach that requirements are subject to change and not fixed in advance.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 05:44
Joined
Jul 9, 2003
Messages
16,224
Someone just posted to this thread and in effect "resurrected it". In recapping what is written with the benefit of some "thinking space" I would just like to say I think it's very important that we exchange our views on these things because (speaking for myself) it definitely helps me think more clearly about how I go about my database design.

I found both boblarson and dportas comments helpful and enlightening.

I'm not sure if I was thinking this at the time I wrote it, but I'm definitely thinking it now, one of the reasons I wrote this threa is because I find "relationships" can be constricting.

In a particular instance I needed a field which had mostly unique values, but would occasionally allow two or more values the same.

So I wrote a small function which was basically an SQL statement for counting the number of records, it would return zero if there was no match, one if there was only one match,and two if there were two or more matches.

This meant I could program and decide what would happen in each case.

The relevance to this thread, it's easy to get in a situation where you try and make everything you do fit the way MS Access works, and really if you're learning, that's the best approach, however as you get more experience you must start to think about constructing your own tools.

In other words don't be restrained by what is available in MS Access.
 
Last edited:

sgbotsford

Registered User.
Local time
Yesterday, 23:44
Joined
Feb 5, 2013
Messages
11
One of the things I like about access is that I DON'T have to think things out completely.

Does this get me in trouble? Certainly.

I mostly use access for personal projects. Right now I'm working up a database that can be used to create an online catalog for my tree farm.

But when I start a project I don't know exactly what I want. So I tend to go with a 'fast prototyping' model. Get a simple thing done that does some of what I want. Enter some data. Write a report. Discover that the data entry doesn't really use one feild. Take it out of the form, take out of the database. Need the data organized a different way, write a new form.

I like the idea of subform as opposed to formal relationship, as I'm MUCH less likely to lose data when puttering around with it.
 

jdraw

Super Moderator
Staff member
Local time
Today, 01:44
Joined
Jan 23, 2006
Messages
15,352
How can i get a good tutor on access?

You are going to have to put some context on this question. What exactly are you having trouble with? Can you identify some things in some order of priority?

For a good ebook on ACCESS, I recommend
Crystals material

For naming convention see Rains

For vba and sql Martin Green

For free video tutorials Datapig

For vba usage and samples FunctionX and
http://www.functionx.com/vbaccess/index.htm

But I like to do relationships up front - and recommend this for organizing facts and getting to a normalized database.

And more info on design and normalization etc here
 

godofsix

Registered User.
Local time
Today, 06:44
Joined
Apr 17, 2013
Messages
13
You are going to have to put some context on this question. What exactly are you having trouble with? Can you identify some things in some order of priority?

For a good ebook on ACCESS, I recommend
Crystals material

For naming convention see Rains

For vba and sql Martin Green

For free video tutorials Datapig

For vba usage and samples FunctionX and
http://www.functionx.com/vbaccess/index.htm

But I like to do relationships up front - and recommend this for organizing facts and getting to a normalized database.

And more info on design and normalization etc here



Thanks J, check the other thread i've uploaded a sample. Pls check it out
 

syswizard

Registered User.
Local time
Today, 01:44
Joined
Dec 27, 2008
Messages
61
Access could have been great, but with the poor QA by MSFT, working around all of the bugs really dampens the value...a lot.
On top of that, MSFT made a feable attempt to provide a web-based version of Access....and that really makes it of questionable value now that everyone is clamoring for web-based solutions in the cloud.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 05:44
Joined
Jul 9, 2003
Messages
16,224
questionable value now that everyone is clamoring for web-based solutions in the cloud.

Many companies don't trust the cloud, MS Access will be around for a long time, but you are right, the cloud is on the horizon, and my advice to anyone is to start learning something more flexible, but what? I'm not sure yet...


I personally think google is the way to go... We will see...
 

DemonDNF

Registered User.
Local time
Today, 01:44
Joined
Jan 31, 2014
Messages
77
I was fortunate enough to get on-the-job training in database architecture as a junior programmer way back in the early 80s. I had exposure to IMS batch and online systems, both hierarchical and relational. I didn't see much behind the scenes view of hierarchical DBA, but I was lucky to do a lot of relational stuff most programmers never do.

I'm a fan of rapid prototyping - to a degree. I use it to explore ideas, set out a baseline how I want things to look, what data I want to store, it's a refinement process. After all, I'm the user, manager, analyst, programmer and customer support of my own little world.

I start with laying out data in tables, then setting relationships, making sure data is unique and stored with other related data. If I can't even organize tables, it's gonna be a long and winding road ahead. Then it's a basic menu and a few low-functionality forms - nothing fancy, just to see how things flow, see if I missed something.

I started with a vague "I want to make labels for parts cabinets so I don't buy stuff I already have" and now I'm better informed on how to get there. I can even add extra features, incorporate nifty ideas and code picked up here and there.

There is no right or wrong way to do a system; it's what works for you that is most important. As long as it compiles, can generate an MDE, remains stable and you understand everything that's going on inside; that's what really counts.

I know my limits, I know I can learn and expand those limits, somewhat, slowly, but as long as the end result does what I need it to do; who has the right to say what I do is wrong. Sure, they can suggest other ways of doing things (already happened), that's why we have these forums.

Happy trails.

Robert
:)

P.S.: Just to illustrate the difference between the "ideal" way we learn in school and what goes on in the real world. In school we learn structure, respecting user needs, interaction between groups, code efficiency, documentation, and then we get a job...

Here's an example of a contract I had in a credit card company: the Marketting department sets a deadline for a new product. Computer Operations reserves the last 2 weeks for implementation, Quality Control reserves another slot in front of that, then comes Systems Testing, then User Testing, and whatever you're left with is the time you have to Review User Requirements, Analyse, Design, Code, Unit Test.

It doesn't mater if it should take 6 months to do the job properly, you have 3 months, deal with it. And that's why we cut on documentation, comments, extensive testing, etc. Either you get the programs done on time for the next department or that's your last contract there. This causes conflicts between units, everyone is too busy saving their neck, no sense of unity.

This is totally backwards from what we learn in school, but if you want to pay your mortgage next month, you had better start playing the game with their rules. In their eyes, data processing is an expense, a liability, a necessary evil.

Sad, because used wisely it can be an asset. Too bad management generally has short-term goals. A well-used computer department can improve user productivity, in turns makes them hate their job a little less because the screens do what they want them to do, less stress, less sick days to a degree, less conflicts. Probably the biggest saving, less software support to clean up the parts you missed while running like a chicken with no head.

(insert 20-80 rule here)

There's the way things "should" be done, and then there's reality. To a degree, the same applies to our own miniscule home projects.

:)


EDIT: On one contract, I went through my code, documented each IF, and then set out test cases that tested each one. Yes, I was reprimanded. I was told to test "enough" to make sure it worked and move on.

In my mind, testing each IF was the least I could do. Apparently I was wrong.
 
Last edited:

RobertGreene

New member
Local time
Yesterday, 22:44
Joined
Jun 7, 2018
Messages
6
The fact is that access classes concerns that how to show data in the in a form,when the data are come from one or more table.The solution is that to create a sub-form in a form.
 

Users who are viewing this thread

Top Bottom