This brings up a question, probably a bad approach on my behalp. Right
now,
when the user click on the button to open the form, it open the form and
then
goes to a new record. So in fact, I do not filter to a new record (If I
understand correctly, with my current method it is loading all the
records,
then goinf to a new one).
Yes you as a generally rule should try avoid opening the form without any
kind of filtering.
And of course all of these things are kind of kind of a best practices type
of thing. In some cases if your tables are small, then you have a lot more
leeway and don't have to worry about these details so much. I mean if you
dealing with a table with a few hundred records at most, then you don't have
to be so worried about the design issues. If you scale to more users or are
to have more records, then little issues can start to become big issues
quite fast.
In other words the culprit here is very much that the fact that you're
opening up a form without restricting records being loaded in the first
place. (and I think it's a good design or interest on your part to think
about these things).
As a general rule, I try to avoid the existence or even allowing navigation
buttons on a form that's bound to a table with many records. This don't
apply to sub forms. The idea here is is that if you can ask the user what
record they want to work on then you are in good shape. You then use open
form command with a where clause as I showed my previous post.
Since this is not the right approach, how could I load the form on a new
record without loading all the records? I can't filter for a non-existant
rec id in this case.
Actually believe it or not, I've seen some people actually open up a form
with a where clause that is a foolish one or impossible answer, for example
they use:
docmd.openform "frmCustomers",,,"id = 0"
Since the record ID will never be zero, then the above does actually prevent
records from being loaded into the form.
However, Access does a pretty smart job if you actually open up the form in
add mode. so, you can use:
docmd.OpenForm "frmCustomers",,,,acFormAdd
The real trick here though is to set up a system in which you always have
some type of prompt form to ask the user what customer or record they want
to work on in the first place. If the user does not find a result when
using that search form, it's also that very search form that has an add
button which then can launch the form in add mode as per above.
If you look at most applications, the first thing they do is present some
type of search box, you enter a search, get some type of hit list (results),
and then can pick from that list to edit the one record. These designs tend
to be more user friendly, but they also are more bandwidth or resource
friendly also.
Take a quick read of the following sample little article of mine, where I
explain this whole application flow concept:
http://www.members.shaw.ca/AlbertKallal/Search/index.html
One more benefit of this approach is to encourage your users to search for
something before they are presented with a form that allows them to easily
add a record. In other words it kind of a social engineering thing that if
you throw up a prompt a search for name, your users will be more inclined to
search before they just type in a new name without bothering to check the
current database if the names are already entered.
Also, assuming I open the form as you mentioned using a WHERE clause, how
then do I allow the user to move to another record using a cbo to make his
selection from? Do you close the form and reopen it with the new WHERE
clause or is there a simpler approach that I am missing?
Great question. Yes what I do is have the user close the form. (take a look
that above article).
I also find that the user feels better that they do one task with the phone
and dealing with one customer. They then close the form and they feel it
they'd done their job and they can move on to the next task at hand. Giving
your users a sense of completing a task or closure for one thing tends to
make them feel also better.
Thank you for enlightening me.... again! And thank you for your time.
You're very welcome. Remember what works for me and what works for you will
always vary. I do think if one can come up a design in which will ask the
user what they want before you toss up that form, you'll be building some
really nice applications and they will scale lot better.
And now that Access 2010 also has web creation ability, this philosophy of
design will serve you well in the future if you so choose to get into web
development using ms access.
