List Box Madness

  • Thread starter Thread starter Jay
  • Start date Start date
J

Jay

The telephone girls at work use a call log database. The main form where
call details are input *should* be pretty simple. The first item is a
pulldown list of customers. The second is a list box for customer location,
so that if customer x has 3 offices, the correct location can be chosen.
However, this is where it gets a bit mad. Each company has its own location
table (with only up to about 6 or 7 records in at the most). And the
location list box is actually a list box for each table on top of each
other! (so that what looks like one in design view is actually 20 all in the
same place), I've looked at the VBA and there appears to be code with code
making a particular list box visible (& the others not visible) if that
company is selected in the first 'company' field !?

I can see he has attempted to do it another way as there is a defunct
company~location table.

So my question is, what's the simplest way of doing what he was originally
trying to do? I presume one table with companies & locations linked to a
queried control of some sort.

Any help appreciated.


-Jay-
 
It sounds like someone once had a better schema going than the current
one. I recommend going back to the earlier one. My guess would be
that the later design happened because someone didn't understand how
to dynamically change the company criteria in a query. That's how
they came up with the idea of multiple company/location tables.

There is an old adage in the design of database systems that goes
something like this: "Get the tables done correctly and everything
else will be simple".

The first step in the actual design process is to resolve the entities
in play in your application and assure that each entity has a table.
All entities of a given type belong in that one table (you should
never have multiple tables referring to the same kind of thing)..

The primary key of the Company table should appear in the
CompanyLocation table as a Foreign Key. Then, in the Relationships
window, draw a line from tblcompany.primaryKey to
tblCompanyLocation.foreignKey by what ever names you have given them.
Double click the link and establish the relationship as one-to-many
between Company and CompanyLocation. Turn Referential Integrity on
and enable cascading deletes. Once you have done that you'll only be
able to create a CompanyLocation record when you have a corresponding
Company record; you won't be able to create orphan locations. Also,
if you delete a company you will also delete all of its locations.

Form design:

I recommend a Find Company: combobox in the header of your form. Tell
the wizard you want to go to a particular record.

To display your CompanyLocations I'd use a listbox whose recordsourse
is a query on tblCompanyLocation. Using the QBE grid on the query for
the listbox: in the criteria line for the ForeignKey refer to the
form's value of the company's primary key.

HTH
 
Many thanks for your advice Larry. I've got the new cascading combo's set up
now. I did go with a combo for the Company & a list box for the location.
When you suggested - " I'd use a listbox whose recordsourse is a query on
tblCompanyLocation." Did you mean a SELECT statement or an *actual* query,
as I've just used the following:

SELECT DISTINCTROW [Company~Location].LOCATION FROM [Company~Location] WHERE
((([Company~Location].COMPANY) Like forms!StaffForm!CompanyComBox));

Rather than creating an actual query, if you know what I mean. Is the above
still actually classed as a query even if it's not a Database Object Query?

As regards referential integrity etc. I'm still a newbie to Access so it's
definitely on my TO DO List :-) (The DB I've 'adopted' currently has *no*
primary keys set!)

Cheers,

-Jay-
 
Every table *should* have a primary key. It may not be life and death
to very limited applications with small datasets but so it anyway. To
easily accomplish it, just add a new field with a datatype of
Autonumber as the first field in the design of each table. When you
close the design, a value will be assigned to each existing record.
Thereafter a new value will be assigned as you create each new record.

Most SQL statements are Queries. The way you did it was the way I'd
recommend. The queries that show up in the Database|Query window are
"Named" queries; a SQL statement that's been given a name.

Keep plugging with your leaarning.

--
-Larry-
--

Jay said:
Many thanks for your advice Larry. I've got the new cascading combo's set up
now. I did go with a combo for the Company & a list box for the location.
When you suggested - " I'd use a listbox whose recordsourse is a query on
tblCompanyLocation." Did you mean a SELECT statement or an *actual* query,
as I've just used the following:

SELECT DISTINCTROW [Company~Location].LOCATION FROM [Company~Location] WHERE
((([Company~Location].COMPANY) Like forms!StaffForm!CompanyComBox));

Rather than creating an actual query, if you know what I mean. Is the above
still actually classed as a query even if it's not a Database Object Query?

As regards referential integrity etc. I'm still a newbie to Access so it's
definitely on my TO DO List :-) (The DB I've 'adopted' currently has *no*
primary keys set!)

Cheers,

-Jay-

It sounds like someone once had a better schema going than the current
one. I recommend going back to the earlier one. My guess would be
that the later design happened because someone didn't understand how
to dynamically change the company criteria in a query. That's how
they came up with the idea of multiple company/location tables.

There is an old adage in the design of database systems that goes
something like this: "Get the tables done correctly and everything
else will be simple".

The first step in the actual design process is to resolve the entities
in play in your application and assure that each entity has a table.
All entities of a given type belong in that one table (you should
never have multiple tables referring to the same kind of thing)..

The primary key of the Company table should appear in the
CompanyLocation table as a Foreign Key. Then, in the Relationships
window, draw a line from tblcompany.primaryKey to
tblCompanyLocation.foreignKey by what ever names you have given them.
Double click the link and establish the relationship as one-to-many
between Company and CompanyLocation. Turn Referential Integrity on
and enable cascading deletes. Once you have done that you'll only be
able to create a CompanyLocation record when you have a corresponding
Company record; you won't be able to create orphan locations. Also,
if you delete a company you will also delete all of its locations.

Form design:

I recommend a Find Company: combobox in the header of your form. Tell
the wizard you want to go to a particular record.

To display your CompanyLocations I'd use a listbox whose recordsourse
is a query on tblCompanyLocation. Using the QBE grid on the query for
the listbox: in the criteria line for the ForeignKey refer to the
form's value of the company's primary key.

HTH
Click to expand...
Click to expand...
 
Back
Top