listbox re-repost

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

Denise

Sorry gang, I must not be making myself clear (not enough
coffee)

I have tblClient, which store individual's data.
I have tblFamily key being [FamilyID], where you choose
[ClientID] from tblClient to 'make a family'.

When scrolling through client records, I need a listbox
on the frmClient which shows all members of their
family. I need some sort of expression that says "if
ClientID is part of a FamilyID, show all ClientID's
belonging to that same FamilyID.

This way, if at "Bob" record, I can enter that "Bob" is
parent and "Mary" is daughter.....then when I go
to "Mary" record, I'll see that "Bob" is already
entered. Again, when at record containing [ClientID], if
[ClientID] is part of a [FamilyID], show all [ClientID]
within the same [FamilyID]. Subsequently, if [ClientID]
is not part of [FamilyID], it will show nothing.

I hope this helps - I thought it would be a simple
expression behind the query of the listbox, but I am
stumped and on a tomorrow deadline.

THANKS THANKS THANKS!

Denise
 
So you have a tblFamily that is used to show the clients that are members of
each family. Can a client be a member of more than one family? If not, and
assuming that there is a control on your form that holds the value of
ClientID (I assume that it's named txtClientID for the purpose of this
suggestion), set the row source of the list box control to the following
generic (change names as needed for your setup) SQL statement:

SELECT ClientID, ClientName FROM tblClient INNER JOIN tblFamily
ON tblClient.ClientID = tblFamily.ClientID
WHERE tblFamily.FamilyID =
DLookup("FamilyID", "tblFamily", "ClientID=" & [txtClientID])
ORDER BY ClientName;

Then, put code similar to this on the form's OnCurrent event:

Private Sub Form_Current()
Me.ListBoxName.Requery
End Sub
 
Thanks for the sql code, but it's not working. To
clarify, I am using a subform "subfrmFamily" which has a
client id of "ClientID", the field to capture that data
is in fact txtClientID. The subform is embedded on form
frmClient with ClientID as the link.
When I enter the sql statement then try to run the query,
its says that "ClientID could refer to more than one
table in my query", but I don't know which one I should
be referring to.
I can't change the name of ClientID in either table at
this point.
I really appreciate your help - this is my last thing to
do before delivery....of course.
Denise


-----Original Message-----
So you have a tblFamily that is used to show the clients that are members of
each family. Can a client be a member of more than one family? If not, and
assuming that there is a control on your form that holds the value of
ClientID (I assume that it's named txtClientID for the purpose of this
suggestion), set the row source of the list box control to the following
generic (change names as needed for your setup) SQL statement:

SELECT ClientID, ClientName FROM tblClient INNER JOIN tblFamily
ON tblClient.ClientID = tblFamily.ClientID
WHERE tblFamily.FamilyID =
DLookup("FamilyID", "tblFamily", "ClientID=" & [txtClientID])
ORDER BY ClientName;

Then, put code similar to this on the form's OnCurrent event:

Private Sub Form_Current()
Me.ListBoxName.Requery
End Sub


--

Ken Snell
<MS ACCESS MVP>

Sorry gang, I must not be making myself clear (not enough
coffee)

I have tblClient, which store individual's data.
I have tblFamily key being [FamilyID], where you choose
[ClientID] from tblClient to 'make a family'.

When scrolling through client records, I need a listbox
on the frmClient which shows all members of their
family. I need some sort of expression that says "if
ClientID is part of a FamilyID, show all ClientID's
belonging to that same FamilyID.

This way, if at "Bob" record, I can enter that "Bob" is
parent and "Mary" is daughter.....then when I go
to "Mary" record, I'll see that "Bob" is already
entered. Again, when at record containing [ClientID], if
[ClientID] is part of a [FamilyID], show all [ClientID]
within the same [FamilyID]. Subsequently, if [ClientID]
is not part of [FamilyID], it will show nothing.

I hope this helps - I thought it would be a simple
expression behind the query of the listbox, but I am
stumped and on a tomorrow deadline.

THANKS THANKS THANKS!

Denise
Click to expand...


.
Click to expand...
 
Sorry --

SELECT tblClient.ClientID, tblClient.ClientName
FROM tblClient INNER JOIN tblFamily
ON tblClient.ClientID = tblFamily.ClientID
WHERE tblFamily.FamilyID =
DLookup("FamilyID", "tblFamily", "ClientID=" & [txtClientID])
ORDER BY ClientName;



--

Ken Snell
<MS ACCESS MVP>

Denise said:
Thanks for the sql code, but it's not working. To
clarify, I am using a subform "subfrmFamily" which has a
client id of "ClientID", the field to capture that data
is in fact txtClientID. The subform is embedded on form
frmClient with ClientID as the link.
When I enter the sql statement then try to run the query,
its says that "ClientID could refer to more than one
table in my query", but I don't know which one I should
be referring to.
I can't change the name of ClientID in either table at
this point.
I really appreciate your help - this is my last thing to
do before delivery....of course.
Denise


-----Original Message-----
So you have a tblFamily that is used to show the clients that are members of
each family. Can a client be a member of more than one family? If not, and
assuming that there is a control on your form that holds the value of
ClientID (I assume that it's named txtClientID for the purpose of this
suggestion), set the row source of the list box control to the following
generic (change names as needed for your setup) SQL statement:

SELECT ClientID, ClientName FROM tblClient INNER JOIN tblFamily
ON tblClient.ClientID = tblFamily.ClientID
WHERE tblFamily.FamilyID =
DLookup("FamilyID", "tblFamily", "ClientID=" & [txtClientID])
ORDER BY ClientName;

Then, put code similar to this on the form's OnCurrent event:

Private Sub Form_Current()
Me.ListBoxName.Requery
End Sub


--

Ken Snell
<MS ACCESS MVP>

Sorry gang, I must not be making myself clear (not enough
coffee)

I have tblClient, which store individual's data.
I have tblFamily key being [FamilyID], where you choose
[ClientID] from tblClient to 'make a family'.

When scrolling through client records, I need a listbox
on the frmClient which shows all members of their
family. I need some sort of expression that says "if
ClientID is part of a FamilyID, show all ClientID's
belonging to that same FamilyID.

This way, if at "Bob" record, I can enter that "Bob" is
parent and "Mary" is daughter.....then when I go
to "Mary" record, I'll see that "Bob" is already
entered. Again, when at record containing [ClientID], if
[ClientID] is part of a [FamilyID], show all [ClientID]
within the same [FamilyID]. Subsequently, if [ClientID]
is not part of [FamilyID], it will show nothing.

I hope this helps - I thought it would be a simple
expression behind the query of the listbox, but I am
stumped and on a tomorrow deadline.

THANKS THANKS THANKS!

Denise
Click to expand...


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