Re: List Box Madness

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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
--
-Larry-
--

"Jay" <zeugma@xxxxxxxxxxxxxx> wrote in message
news:C08FED09.A0E7%zeugma@xxxxxxxxxxxxxxxxx
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-



.

Relevant Pages

  • Re: Hobbie Cad/CNC software recommendations
    ... is that the kids come up with a design idea, Dad winds up cutting it out ... I figure once we get over the learning curve of the software then banging ... modeling program with a lot of nice features. ... I'm going to shoot for so if you want to recommend a simple one and then ...
    (rec.crafts.metalworking)
  • Re: Terminating threads in Destructor
    ... > There are numerous design flaws in the MFC sync classes, ... > hesitate to recommend them. ... I think my basic thread design is flawed, ... >>I kind of have to do that to wait for the thread to terminate. ...
    (microsoft.public.vc.mfc)
  • Re: Linking tables with MS Access functions
    ... very poor performers since they will usually force table scans vs. the use ... will recommend them when needed. ... instead of Design View. ... This Design-view restriction has been present since ...
    (microsoft.public.access.queries)
  • Re: IJ Pinball Price???
    ... new game's design style (though it may be completely different, ... I'm not a big fan of POTC, as it is way too much stop+go type game ... I would recommend waiting til some info comes out on IJ. ...
    (rec.games.pinball)