listbox - repost

  • Thread starter Thread starter Denise
  • Start date Start date
D

Denise

Hello - posted a message last week about having a listbox
that relates to table information - I got a great
response but it didn't quite cover what I need.

I have a subform [subfrmFamily] on [frmClient]. This
subform allows the user to add family members to the
client....so if the user is at record "Bob", he can go to
the subform and add "Mary" as sister and "Jane" as
daughter. They are all tied to the same family ID number
from [tblFamily]

What I need is: when I'm at record "Mary", a listbox that
shows all family members with the same FamilyID number
as "Mary" BUT, I need it to relate to the [ClientID]. In
other words, if ClientID belongs to a FamilyID, show all
ClientID's within the same FamilyID.

I hope this makes sense. Thanks in advance for help!!

Denise
 
Denise

Without some idea of your underlying data structure, it will be tough to
offer suggestions.

However, you seem to be saying that you have a table somewhere that lists,
per each FamilyID, all ClientIDs that belong to that "family". If so, I
don't understand why you'd need to relate a new ClientID to an existing
ClientID ... just connect it to the FamilyID. A simply query of all
ClientIDs where FamilyID = (whatever you determine) would show everyone
(ClientID) connect with that family.

Or am I missing something?
 
You're right about my tables, tblFamily has FamilyID with
corresponding ClientID, so that one FamilyID has several
ClientID. What I can't figure out how to do is when
looking at the client screen (which has client info,
demographics, etc.), I want a listbox to show all
ClientID's that have the same FamilyID as the client on
the screen....which means the listbox would change as you
scroll through the client records. Currently, my listbox
says show all ClientID where [ClientID] = forms!frmClient!
ClientID...how do I get the others with the same FamilyID
as the current client to show?
I really appreciate your help, my client is expecting
this tomorrow and I'm stumped!

Denise
 
Thanks for the info, I've got a good handle on where to
put the listbox, just not how to get the data to show as
I need a statement that says "show all clients that have
the same familyID as the client on the current screen",
so that my listbox would change as you scroll from client
to client.

Thanks! Any help would be greatly appreciated, I'm to
deliver the final product tomorrow and this is the only
piece I'm having a mind-blank on.

Denise
 
If I may butt in here, I think that what has everyone
confused is that you are relating ClientID to ClientID and
the experts are trying to square that with ClientID being
unique to each person in your database.

What I've gathered from your post(s) is that you have a DB
with a format similar to this:

tblFamily
FamilyID (primary key)
Otherinfo on families

tblClient
ClientID (primary key)
FamilyID (foreign key, duplicates allowed)
other info on clients

Where each ClientID can be in several families
(Grandparents, Parents, Siblings etc)
and each family can have several Clients (parents have
three children, etc)

A classic many-to-many relationship

You should also have a third table which relates the above
two tables, similar to:

tblFamilyClient
FamilyID
ClientID
....

Am I right so far?

If so, you should have a combobox which allows you to type
in or select a client and have a subform which then lists
either all the families that client is in or all the
clients who are in (one of) the same family(ies) as the
selected one. I'm not sure how you can get this to update
by just hovering the mouse over a selection but this would
be a start (unless I completely missed the boat). The
combo box would be bound to your clients table and the
subform would be bound to a query on the tblFamilyClient.

Does that help at all?
-----Original Message-----
You're right about my tables, tblFamily has FamilyID with
corresponding ClientID, so that one FamilyID has several
ClientID. What I can't figure out how to do is when
looking at the client screen (which has client info,
demographics, etc.), I want a listbox to show all
ClientID's that have the same FamilyID as the client on
the screen....which means the listbox would change as you
scroll through the client records. Currently, my listbox
says show all ClientID where [ClientID] = forms!frmClient!
ClientID...how do I get the others with the same FamilyID
as the current client to show?
I really appreciate your help, my client is expecting
this tomorrow and I'm stumped!

Denise
-----Original Message-----
Denise

Without some idea of your underlying data structure, it will be tough to
offer suggestions.

However, you seem to be saying that you have a table somewhere that lists,
per each FamilyID, all ClientIDs that belong to that "family". If so, I
don't understand why you'd need to relate a new ClientID to an existing
ClientID ... just connect it to the FamilyID. A simply query of all
ClientIDs where FamilyID = (whatever you determine) would show everyone
(ClientID) connect with that family.

Or am I missing something?

--
Good luck

Jeff Boyce
<Access MVP>

.
Click to expand...
.
Click to expand...
 
So, after changing which ClientID is being displayed, you want the listbox
that holds related ClientIDs ("family members") to be requeried, to show
THAT ClientID's related ClientIDs?

Do you have a .Requery statement for the listbox in your AfterUpdate of
however you are selecting the Client? Or in the form's OnCurrent?
 
Back
Top