Replacing Forms w/Unbound populating w/Query

Thank you once again for any and all assistance. I'm building an application
that's getting quite bulky due to the number of forms, macros and procedures. I
was wondering if there's a way to use 1 (one) unbound form, if all the fields
are the same for multiple forms populating them with Queries?

I have a makeshif menu form with command buttons and I would like to eliminate
all the forms and replace them with one unbound form for three areas;

Customers
Litters
Animals

Right now there are about six forms or more for each of these categories, but
all the fields and data come from the same table, just various query pulls.

Michael

re: Replacing Forms w/Unbound populating w/Query

Your question is too general for a specific answer.

My question would be, perhaps, why are you using multiple forms if al the
fields are the same? If, as I could infer, only the queries differ in which
records are to be displayed, then why would you not use the same form... but
bound, not unbound... with the WhereCondition of the DoCmd.OpenForm
specifying the criteria for which records are to be displayed.
Alternatively, and perhaps more efficient, would be to create the entire SQL
statement for the RecordSource and replace the Form's RecordSource in its
Open event.

In my experience, there are few situations in which unbound forms have any
advantage over bound forms. But, perhaps you only meant a form into which
the RecordSource is inserted at runtime, not in design view?

If my understanding is not correct, please follow up to clarify.

Larry Linson
Microsoft Access MVP




"Lumpierbritches" <lumpierbritches@aol.com> wrote in message
news:20040801151538.04751.00000780@mb-m12.aol.com...[color=blue]
> Thank you once again for any and all assistance. I'm building an[/color]
application[color=blue]
> that's getting quite bulky due to the number of forms, macros and[/color]
procedures. I[color=blue]
> was wondering if there's a way to use 1 (one) unbound form, if all the[/color]
fields[color=blue]
> are the same for multiple forms populating them with Queries?
>
> I have a makeshif menu form with command buttons and I would like to[/color]
eliminate[color=blue]
> all the forms and replace them with one unbound form for three areas;
>
> Customers
> Litters
> Animals
>
> Right now there are about six forms or more for each of these categories,[/color]
but[color=blue]
> all the fields and data come from the same table, just various query[/color]
pulls.[color=blue]
>
> Michael[/color]

re: Replacing Forms w/Unbound populating w/Query

Larry,

As usual you are so helpful. I'm sorry for my general inference. I'm trying to
cut down on some bulk in Access 97.

I have Forms bound currently in three categories; Animal; Customers; Litters.
Each one of these forms have one table, but I have more than 4-6 bound forms,
i.e.

Animals: All; Female; Male; Sold; Deceased; For Sale; Owned; Pedigree Only. All
of these use the same table, just variations of each table created in Queries.

Customers: All; Prospects; Kennels; Brokers; Pet Stores; Purchasers.

Litter: Current; Future; Past; All

Either using bound checkboxes in the query criteria or using DateDiff to
separate data for each Query. With so many forms, the program has gone from a
small 3 meg file to almost a double. I'm wondering if by eliminating all the
excess forms and using one unbound for each, populating them in the various
categories with code would be a slimming solution? I also have the developers
edition of AC97 and intend on eventually migrating to VB.Net, since I also
purchased an MSDN subscription!

Local Kennel Breeders have asked me to build the application and they're
willing to help me debug it so it will do what they want, but I'm still
learning AC97 - 2003.
Michael

re: Replacing Forms w/Unbound populating w/Query

It certainly appears that you can do what you want with bound forms, and
perhaps as few as three (one for each category) for the information you
describe here. At least, my understanding of what you say is that the only
difference in the Queries would be in the criteria.

Without details of table layout and content, I can't be very specific, but
it will be a matter, I'd think, of (1) a combo box in the form header for
selecting the criteria you want to use to select the data to display, and in
the AfterUpdate, creating the WHERE clause for the RecordSource,
concatenating that with the base of the SQL for the RecordSource, and
replacing the Form's RecordSource. In those cases, where you may want to be
more specific, you might need a second Combo from which the user chooses a
particular Broker, Pet Store, or Purchaser, for example.

You'll need to learn/know/use some VBA, or hire someone who can do it, or
get very detailed in describing what you have and what you want to do in
subsequent posts.

Larry Linson
Microsoft Access MVP




"Lumpierbritches" <lumpierbritches@aol.com> wrote in message
news:20040802062034.22984.00002815@mb-m07.aol.com...[color=blue]
> Larry,
>
> As usual you are so helpful. I'm sorry for my general inference. I'm[/color]
trying to[color=blue]
> cut down on some bulk in Access 97.
>
> I have Forms bound currently in three categories; Animal; Customers;[/color]
Litters.[color=blue]
> Each one of these forms have one table, but I have more than 4-6 bound[/color]
forms,[color=blue]
> i.e.
>
> Animals: All; Female; Male; Sold; Deceased; For Sale; Owned; Pedigree[/color]
Only. All[color=blue]
> of these use the same table, just variations of each table created in[/color]
Queries.[color=blue]
>
> Customers: All; Prospects; Kennels; Brokers; Pet Stores; Purchasers.
>
> Litter: Current; Future; Past; All
>
> Either using bound checkboxes in the query criteria or using DateDiff to
> separate data for each Query. With so many forms, the program has gone[/color]
from a[color=blue]
> small 3 meg file to almost a double. I'm wondering if by eliminating all[/color]
the[color=blue]
> excess forms and using one unbound for each, populating them in the[/color]
various[color=blue]
> categories with code would be a slimming solution? I also have the[/color]
developers[color=blue]
> edition of AC97 and intend on eventually migrating to VB.Net, since I also
> purchased an MSDN subscription!
>
> Local Kennel Breeders have asked me to build the application and they're
> willing to help me debug it so it will do what they want, but I'm still
> learning AC97 - 2003.
> Michael[/color]

re: Replacing Forms w/Unbound populating w/Query

lumpierbritches@aol.com (Lumpierbritches) wrote in message news:<20040802062034.22984.00002815@mb-m07.aol.com>...[color=blue]
> Larry,
>
> As usual you are so helpful. I'm sorry for my general inference. I'm trying to
> cut down on some bulk in Access 97.
>
> I have Forms bound currently in three categories; Animal; Customers; Litters.
> Each one of these forms have one table, but I have more than 4-6 bound forms,
> i.e.
>
> Animals: All; Female; Male; Sold; Deceased; For Sale; Owned; Pedigree Only. All
> of these use the same table, just variations of each table created in Queries.
>
> Customers: All; Prospects; Kennels; Brokers; Pet Stores; Purchasers.
>
> Litter: Current; Future; Past; All
>
> Either using bound checkboxes in the query criteria or using DateDiff to
> separate data for each Query. With so many forms, the program has gone from a
> small 3 meg file to almost a double. I'm wondering if by eliminating all the
> excess forms and using one unbound for each, populating them in the various
> categories with code would be a slimming solution? I also have the developers
> edition of AC97 and intend on eventually migrating to VB.Net, since I also
> purchased an MSDN subscription!
>
> Local Kennel Breeders have asked me to build the application and they're
> willing to help me debug it so it will do what they want, but I'm still
> learning AC97 - 2003.
> Michael[/color]

Okay, I'm definitely NOT Larry, but I would think the option to filter
when you open the form is the easiest. You could have an unbound
combobox or whatever on another form to collect your criteria and then
open this form from there. One form, lots of different filters
passed. Nice and flexible. I think using unbound forms is making
work for yourself. There are places for them, but you lose all the
data validation etc that goes on under the covers.