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)]... <|:^)

 

[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.

 

[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!)

 

[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.

 

[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.

 

[ChrisO]

G’day Pat.

The answer that you have given seems to me that you have violated the principal as stated by Professor Hawking.

My limited understanding is that the theory needs to be modified on the new observation and not to modify the observation to conform to the theory.

By modifying the original question, “Should Forms always be created from Queries?”, by placing a qualifier upon it as in “That goes without saying”, I believe reverses the direction of information flow as stated by Professor Hawking.

In other words, the theory as in “always” should be modified based on the observation, “Should Forms always be created from Queries?” and not by modifying the observation as in, “Should Forms always be created from Queries where the ‘criteria goes without saying’”.

Our observation in this case is ‘that question’ in this thread and to change or modify the observation, as stated, helps no one.

The observations that Professor Hawking is talking about do not simply come from some mythical position, they emanate from Heisenberg’s Uncertainty Principal.

As it stands at the moment, the Uncertainty Principal is at the top of the tree in Physics. There is no way, at the moment, to violate that Principal. It may come but it will not come from rearranging logic to suit our personal needs.

Regards,
Chris.

 

[WindSailor]

Ouch...

Sorry for butting in... but since this is already getting way over my head...
The more I learn about Access or programming in general, the necessity that I still go by or set a goal to achieve by; are three simple steps.
Here they are in order:

Simplify
Simplify
Simplify

I guess that makes me a very basic, simple kind of guy.

-----------------------------------------------
Oh, forgot to put in my vote: "Maybe" but hedging on "Yes".

The only argument that I can consider is in the style of maintaining or ease of use in updating your MDE or MDB files, just how far do you want to go?
The only compromise that I can think of is making changes to your database over a period of time, say for instance versioning, and making the database backward compatible.
And that depends on correct database structure, of which, I am still striving to improve on.

 

[ChrisO]

Please, before we say anymore, may I respectfully suggest that there is no Ouch! implied…

It is an opinion on my part, which could/should be traced back to some fundamental standard.

And please also do it with some sense of humor, we will not always agree but at least we can argue.

Regards,
Chris.

 

[WindSailor]

My apologies if that was taken personally.

I guess it was a dumb way of trying to "lighten up" a serious point of view.

To carry on with the theme, this thread is what I have been considering "in a around about way".

In the past I have been mostly creating forms out of queries or involved code from modules etc.
Only on a few occasions have I created forms that are unbound or direct from tables and often wondered later if that was correct, but my databases are very small in comparison to others so size isn't really an issue here. And I am considerably far from being an expert on databases or Access.

The aspect that I have been working on back and forth is whether to create forms off of queries or just involve code through modules etc. In maintaining the database and using queries heavily I find it sometimes hard to retrace all the steps and modify the new database version to be backward compatible, and was wondering or considering about using modules with forms and modifying database structure to speed up the process. I believe queries run faster than code, but in maintaining or updating the database through modules may be beneficial as far as time is concerned.

Opinions, thoughts?

 

[ChrisO]

No apology required as far as I can see, I simply don’t look at things that way.

My method is to try and explain things that are, in what I perceive as traceable standards. I don’t give a flying rats backside in the quest other than correctness.

Access is no different, it is simply another flying rats backside and I would not be true to Access or myself if I ignore that fact.

You should feel no need to "lighten up" a serious point of view, but maintain your integrity.

In the long run, most people will get over their initial opinions and come to the inevitable conclusion that they really don’t understand anything and that includes me.

 

[wazz]

it seems to me that a crucial aspect of the question “Should Forms always be created from Queries?” has been ignored.

the answer, “My position is that you should "always" use queries unless you have some reason not to” clearly answers in the affirmative: you should always use queries.

stating that, "So it seems to me that both Pat Hartman and Professor Hawking have already answered the original question correctly and that answer is no" not only assumes that there is a correct answer and that it is already known, it verges on mere contradiction. ("this isn't an argument, it's just contradiction!" "No it isn't!" - monty python).

what is missing in the interpretation is the existence of the word 'should'.

a response such as, "If the answer, to the original question, was still yes then we would be logically prevented from using unbound forms" misses the existence of 'should' entirely. if something 'should' be done, it does not mean it must be or is always done; and that, in turn, does not mean that something shouldn't be done.

if a cosmological theory is disproved, does that mean we shouldn't have lived by that theory before it was disproved? it is illogical (to say the least) to think that that is possible. however, it is more important in a case such as this to realize that we are not talking about physics, though analogies can be made. we are discussing options. i'm not so sure that there is one answer where options exist. but, sometimes there is a best option, one that will not block pathways, will do the least harm and provide for the future. and that option might last a very long time.