Combobox Problem

  • Thread starter Thread starter G deady
  • Start date Start date
G

G deady

First, let me apologize if this ends up being a double post. I posted it
earlier and it never appeared so I am assuming Windows "ate it" and am
reposting.

I have a client db for my law office. After client info is entered into
"frm_clientinfo" the db branches off into one of 4 tables depending on
whether the legal matter is a state civil(tbl_statecivilcaseinfo),
statecriminal(tbl_statecriminalcaseinfo, federalcriminal
(tbl_federalcriminalcaseinfo or federal civil matter(tbl_federalcivilcaseinfo.


The db then continues from of these 4 main tables-ie if "statecivil is
chosen then the user would choose autoaccidents, divorce, medical malpractice,
etc.

I have a client contact popup form("frm_clientcontact" bound to
"tbl_clientcontacts" that records phonecalls from a call on their case. This
form can be activated from anywhere in the db via a db since a client may
call at anytime.

Im tryng to create a combobox that will lookup the correct client when a
call is recieved but can't figure out how to have one combobox that looks up
data from 4 different tables. I can't base the combo on the clientinfo table
because a client can have more than one legal matter so the combo has to be
based on the 4 case tables in order to link the call record to the corecr
case record. I'm thinking I need sime sort of if statement Maybe a listbox
with the 4 cases and and if statement that calls a corresponding criteria
criteria in the query for the combo box. Can something like this be done? Is
there a way to call seperate records from more than 1 table in the same combo
box?
 
G deady,

Can I first of all ask a question?... Is the structure of this database
"set in concrete", or are you able to consider a review of the table
design? It would appear that you have fallen into the ol' "tables as
data" trap. It will help to solve the immediate question, and other
problems you will encounter in the future, if you are able to normalise
your design somewhat. You really should have a single Cases table, with
each case listed in this table, and a *field* in this table, not a
division between tables, to identify the Type of case (state civil,
criminal, etc.). For each of these types, it may be good to have
additional subtyping tables, for fields that only apply to one or the
other of the types, that's another question. But the core CaseInfo data
should all be in the one table.
 
To list records from more than one table, you can use a UNION query as
the RowSource of the combobox. But it might be better to restructure
your database, using the technique called "subclassing".

Instead of 0one table for clients and four totally separate tables for
different kinds of case, you'd have

- a Clients table, as now

- a Cases (or LegalMatters) table, with a many-to-one or many-to-many
relationship with Clients (can one legal matter involve more than one
client?). This table would contain all and only those fields that are
common to all kinds of case: e.g. CaseID, CaseType, CaseName,
DateCommenced, Court. (The CaseType field would indicate whether State
Civil, State Crimininal, or whatever).

- four more tables, each related one-to-one to the Cases table, and each
containing the fields unique to that particular kind of case.

This means that your combobox would only require a simple query on
Cases. When the user selected a case, the CaseID would be available, and
this could then be used to retrieve the case information from the
relevant tables.
 
John said:
To list records from more than one table, you can use a UNION query as
the RowSource of the combobox. But it might be better to restructure
your database, using the technique called "subclassing".
Click to expand...

John, Steve,

Thank you for your suggestions. Steve, I agree with the suggestion of one
cases table with common field. That is the way I originally had it set up.
Unfortunately it won't work. When I did that and then linked the individual
cases containing the non-common fields to the one cases field I exxeeded
Access's limitation on the mumber of limitations to one table. You'd be
surprised how many diffeerent type of cases there are . When you start
listing autoaccidents, divorces, custody, contracts, Insurance, Medical
Malpractice,Wills, etc., they begin to add up. There are almost 25 in the
state civil cases category alone. I quickly exceeded Access's limit of 72
relationships per table when I ran all the subtables off one main table . Not
to mention it was extremelt difficult to follow that many when all were
grouped as one.

John, I really like your idea. It is a much better solution to the
relationship limit than mine. Your solution creates all the caseids in one
tables which has been my real problem. It also solves another problem with
this particular form (the clientcontact form) which the Union query didn't.
The form is basically a phone message pad. In order for it to have any vulue
I need the clients name as well as the idnumber. (need the name to know who
called and the id to link the record to the correct client and case)The Union
query won't pull the name. With your solution I can now base this contact
form on a select query consisting of my client table and this new table.
This would allow me pull both the name and the caseid.

Many thanks.
 
G deady,

Thanks for the clarification.

In case (no pun intended) you didn't realise, John's suggestion and mine
are identical. I am not 100% clear about how you had problems with the
number of different types of cases, but once again, the problem would be
solved by normalisation of your table design.
 
Back
Top