ACCESS FEED: Re: [MS_AccessPros] Forms with many combo boxes that look up other records

Selasa, 30 Oktober 2012

Re: [MS_AccessPros] Forms with many combo boxes that look up other records

Connie-

Are there cases where you might want to limit your lookups to "active"
properties and addresses only? Do you do anything to eliminate duplicate
addresses? For example, Access would think "500 W Main Street", "500 West
Main Street", and "500 W Main St." are all different addresses.

Having said all that, a combo box with fewer than 1,000 entries in a small
database should be no problem as long as each user's machine has plenty of
memory and the fields are indexed appropriately. 1,143 contacts in a
combo is pushing the envelope. And surely you don't expect the user to
have to scroll through all 1000+ entries just to find the contact name
wanted! I realize Access does help out if you start to type characters,
but it still seems like an excessive list. As the database gets bigger,
you'll have to consider using a technique like providing an unbound box
where the user can type the last name or part of the last name and press
Enter to pop up a list filtered only by that last name value to do a final
selection.

Just some initial thoughtsŠ

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: mrsgoudge <no_reply@yahoogroups.com>
Reply-To: <MS_Access_Professionals@yahoogroups.com>
Date: Tuesday, October 30, 2012 5:00 PM
To: <MS_Access_Professionals@yahoogroups.com>
Subject: [MS_AccessPros] Forms with many combo boxes that look up other
records

Good morning!

I have several forms with combo boxes that look up other records and am
wondering what sort of issues might be encountered down the road as the
number of records increases.

We are realtors in a small town/one county area that has no MLS. Few new
houses are built each year and the population tends to be declining. Our
population in the county excluding students is around 8500 with 4000
housing units according to the Census and 19% of those are in apartments.

The first example is the Listings Form:
1. 3 combo boxes with Address, ParcelID, HomeID each currently having 669
and less than 200/year added to each at the current rate.
2. A subform which is a continuous form with contacts--currently have
1143 and about 625 will be added during 2012. I would anticipate that in
following years it will be the same amount or less that will be added each
year since it's increasingly likely that many will be repeat buyers.

The second example would be the Tracking Form with the following combos:
1. Property Name--This looks up all listings. Currently we have 669.
Less than 200 will be added each year.
2. Address--Currently we have 669. Less than 200 will be added each year
at the current rate. This is the same as Property Name except a different
way to approach it. We generally refer/remember by Property Name but
there are a few listings that only have the address so we need both.
3. Buyers -- Approx 100 will be added each year at our current rate.
4. Unbound boxes in the header that are part of creating the filter.
There are two which duplicate #1 and #2 above.

Thanks!
Connie

------------------------------------

Yahoo! Groups Links