Should Forms always be created from Queries

[Patho]

I've read quite a few posts and noticed that quite a few people have said that forms should be created from queries. Is this personal preference or a must.

Like I said on a previous post I am going on a one day Access soon, so maybe I should stop reading and playing until I have completed that, but my mind is running away with what I want to do.

 

[ScottGem]

In my opinion the answer is no. While there are advantages to binding a form to a single table query its not a necessity. The only time I bind a form to a query is when I want to filter the form based on some criteria.

 

[ChrisO]

"Should Forms always be created from Queries?"

Like a lot of other questions, the answer is no, and the problem is in the word always.

http://www.generationterrorists.com/quotes/abhotswh.html

Words like always and never and must should be taken in the conditional sense as they are only as good as such time they are disproved, and that is simply a matter of time.

Absolutes based on time are simple not absolutes but merely an idea waiting for an encounter with the inevitable exception.

Regards,
Chris.

 

[Bat17]

I am going on a one day Access soon, so maybe I should stop reading and playing until I have completed that

No Way

I knew more about Access from reading/playing than I eaver learnt on courses. The "Advanced" course did not include VBA nor did MS certification.

Peter

 

[Patho]

ok folks,

Thanks for your replies, much appreciated. I think I'll still go on the course though BAT17. Although it is not actually booked yet, I believe that I'll benefit from it. I want the database to do quite a lot of what appears to be basic functions, so I've no doubt I'll be posting and reading these boards for quite some time.

 

[Bat17]

Yep, do the couse, I just meant don't stop playing around

Peter

 

[wazz]

if i'm not mistaken, one person in particular stresses *always* binding forms to queries *mainly/especially* in order to avoid future problems/hassles when moving to SQL Server.

[notwithstanding inevitable exceptions to rules, there can be little excuse for ignoring *known* hazards (though we all do it)]... <|:^)

 

[Pat Hartman]

My position is that you should "always" use queries unless you have some reason not to. The primary reason for this is that in a properly designed application, only a limited set of records are needed for any particular user interaction so there is no point in presenting him with a pipeline to 10,000 records in a table starting with record 1 when what he really wants is record 456 and as soon as the form opens, he is going to filter or find the record he wants. If you allow him to identify the record he wants beforehand, the form's recordset will contain only that record.
So to summarize
1. the primary reason for the suggestion is constraining the recordset.
2. To control the row order of the recordset when it contains multiple records.
3. To allow calculations to be performed in the query rather than the form to eliminate problems with unbound controls in continuous forms.

 

[Patho]

The reason for the question is that I run a temp agency in the UK, my company is looking at an online db but we believe that it will be at least a further 12 months before our MD actions on it. Our IT dept doesn't like Access they believe that it is too unstable for multi user use over a remote server.

In my previous company we had Access operating through a pier to pier network and it worked ok, it fell over every now and then but generally it was adequate. The db was built by a friend of one of my colleagues and it had tables in one db and forms in another, he said this was best. The unique ID was the first three letters of either the temps surname or the client name and then sequential, I have seen various posts in regard to this method and have read the words of caution against it.


I am going to try and recreate the environment based on my memory of what we had the db doing and hopefully with some help extend what we can do.

What I am going to need the db to do is:

1) Record temp details, name address skills etc.
2) Record Client details name address etc
3) client bookings and the ability to assign available temps
4) a method of recording the hours worked/billed so that they can be pulled up a later date or exported to excel so that they can be emailed to our payroll.

 

[RV]

Our IT dept doesn't like Access they believe that it is too unstable for multi user use over a remote server.

Seems that your IT dept is not rather familiar with Access and therefore a bit biased
I'm not an expert on this one but perhaps other forum members can help you to convince your IT guys and girls they're not necessarily correct on this one.

RV

 

[ScottGem]

Our IT dept doesn't like Access they believe that it is too unstable for multi user use over a remote server.

I would not disagree with this, the key being over a remote server. Multi-user (up to about 20 concurrent users) on a LAN is fine. But over a wider area there will be issues.

 

[Pat Hartman]

Patho, are you aware that Access makes a fine front-end to just about any relational database on the market? If your IT department supports SQL Server, Oracle, DB2, or some other RDBMS, you can create your Access front end and link it to a server back end. You would distribute the Access fe to all the users so they would each have their own local copy and the copies would all link to the same database. In this case, it is IMPERATIVE that all forms and reports be based on queries. Do some reading on optimizing for client/server before you start. It will save you rework in the long run.

I always start my applications with an Access back end just because it is easier to work with in the early stages of development. As soon as the tables are stable, I convert them to whatever RDBMS the application is really going to use and continue development and testing while linked to the ODBC back end.

 

[The_Doc_Man]

Pat's advice is always good, but I have a different viewpoint to add to your thinking. When you base a form on a query, you have a good starting point for a report - based on the same exact query. In other words, seeing on a report the same thing that the form "saw" when it was active. They say that consistency is the hobgoblin of small minds, but you have to remember that YOUR mind is the one that is going to be confused when the reports and forms don't match... it will be your users. (Or, heavens forfend, your BOSS )

I use queries as the basis for forms and reports, say, 11 times out of 10.

(Us Cajuns were never good at math, but we know how make a good gumbo, cher!)

 

[Pat Hartman]

Is is possible to make gumbo without okra or seafood?

 

[Pat Hartman]

Notice how humans use OR when they really mean AND. My question more precisely is:
Is it possible to make gumbo without okra AND without seafood?

 

[ChrisO]

Not directed to anyone in particular and just a personal opinion…

Well I wasn’t going to state what I think is obvious but I will anyway.

Not liking to quote people but;
From Pat…
“My position is that you should "always" use queries unless you have some reason not to.”
Does not that simply make sense within the context of a database?

And from Professor Hawking…
“but if ever a new observation is found to disagree, we have to abandon or modify the theory.”
Does not that simply make sense in the larger context?

Please consider the following:
Try to bring together each context and compare them with the original question “Should Forms always be created from Queries?”

If the answer, to the original question, was still yes then we would be logically prevented from using unbound forms. We would be prevented from using date picker and colour picker forms for a start, since most are unbound.

Another form that would need to be dropped would be the ‘de-normalized’ form. Such forms are usually user interface forms that look a lot like spreadsheets and are almost impossible to bind to any table or query. In this case the binding should be done in code as the form remains unbound. These forms can’t generally be bound to a de-normalizing query because the query tends to become non-updateable, which kind of defeats the purpose.

Another thing to consider is a thought experiment and it goes like this…
You are going to be asked a question for which there is only one of two possible answers.
“Should Forms always be created from Queries?”
Please tick the Yes XOR the No box.
Which box do you tick?

So it seems to me that both Pat Hartman and Professor Hawking have already answered the original question correctly and that answer is no.

Now it also seems to me that there is another issue here…
The simple ‘always’ or ‘never’ answer is only expedient within any context and the ‘maybe’ takes more time to explain. Added to that, it also seems to me that stating ‘always’ or ‘never’ tends to reduce originality for the reader. They may actually think that it should ‘always’ or ‘never’ be done that way and ignore the possible mutation that works.

Ignoring all such mutations would lead to stagnation of thought.

“Should Forms always be created from Queries?”

Regards,
Chris.

 

[ChrisO]

I have been told I do a reasonable galantine of duck.
(Whole boned out duck stuffed with chicken, currents…and a few other things…served cold.)

I’ll take the full gumbo recipe in exchange.

 

[Pat Hartman]

Sorry Chris but I did not say NO. I said YES - forms should ALWAYS be based on queries. The rest of the sentence was a joke because always tends to turn people off. But there simply is no advantage to ever binding a form directly to a table.

I have been working with Access for over 10 years and I have NEVER found a reason to bind a form directly to a table. I ALWAYs (I know that's a purple word) bind my forms to queries. Even if I needed to create an unbound form, I would still use a query to create the recordSet. Queries allow me to specify criteria which limits my recordset to only the rows I want to deal with. Queries allow me to specify which columns I want which limits the columns extracted from the table. Queries allow me to specify an order by clause that always works (you can't say that about certain situations with the form properties). Queries allow me to perform calculations so that my continuous forms work properly. Tables allow me to what? Nothing. There is no advantage that I have ever found to working directly with a table. I suppose if you were coding seeks there would be an advantage because they could use an index but that only applies to local tables. It does not apply to linked tables. And why wouldn't you use a query with criteria anyway? That way you would only have to process the records you were interested in.

Also, FYI, how do you think tables are turned into Recordsets? A query is run that creates the recordset!!!!! The difference is, you have no control over any option that will improve performance!

 

[ChrisO]

G’day Pat and I hope I get the smilies correct.

Oh well it looks like I took you too literally and you are correct it is a 'purple word' at lease to me.

I would agree that there little or no reason to bind a form directly to a table but that does not mean that forms always need binding to a query.

Take for example the Popup Calendar available from Allen Browne’s site.

This style of form is not based on a table or query and therefore it would seem unnecessary to bind it to a query simply to comply with the original question asked in this thread.

So I think if we look again at the quote from Professor Hawking we 'have to abandon or modify the theory' because a 'new observation is found to disagree'.

And while I must agree with Professor Hawking, simply because I know no better , I find I must also agree with your original statement as quoted.

So in a rather strange sense, I seem to agree with you more than you do.

Regards,
Chris.

 

[Pat Hartman]

I wasn't suggesting that every form needed a RecordSource. I was suggesting that IF it had a RecordSource, the RecordSource should be a query. It makes no sense to bind a form to a table. If your tables are Jet performance is unlikely to be different due to the way a file server works. However, if your tables are ODBC, there can be HUGE performance gains achieved by using queries with where clauses rather than binding to tables or even binding to unqualified queries.

Of course a form like the PopUp Calendar wouldn't be bound to anything. That goes without saying. I have several forms in each database that fall into the same category. They are usually used to capture parameters to be used for running reports or exporting files, etc.

And by the way, I agree with Professor Hawking's statement. When/If I ever discover some reason to use a table rather than a query, I will modify my recommendation.