Combo box to display information from two tables

[Trickle]

Hi All

I've got a combo box on a Client Support Plan form to display information from a client Home Assessment table, eg

Row Source:SELECT [HomeAssessment].[ClientNumber], [HomeAssessment].[ClientLastName], [HomeAssessment].[ClientFirstName], [HomeAssessment].[StaffMember], [HomeAssessment].[PersonalGoal1], [HomeAssessment].[PersonalGoal2], [HomeAssessment].[PersonalGoal3], [HomeAssessment].[PersonalGoal4], [HomeAssessment].[PersonalGoal5], [HomeAssessment].[PersonalGoal6] FROM HomeAssessment ORDER BY [PatientLastName];

On Change Code:
Private Sub Combo356_Change()
Me.ClientFirstName = Me.Combo356.Column(2)
Me.ClientLastName = Me.Combo356.Column(1)
Me.PersonalGoal1 = Me.Combo356.Column(4)
Me.PersonalGoal2 = Me.Combo356.Column(5)
Me.PersonalGoal3 = Me.Combo356.Column(6)
Me.PersonalGoal4 = Me.Combo356.Column(7)


Me.PersonalGoal5 = Me.Combo356.Column(8)
Me.PersonalGoal6 = Me.Combo356.Column(9)
Me.StaffMember = Me.Combo356.Column(3)
[Visit Requirement].SetFocus
End Sub


etc....

This works fine but I would also like the same combo box to display the client's address information, which is stored in a separate Referrals table. Is this possible?

Many thanks in advance for all and any help.

Trickle

[ranman256]

The combo is only meant to show 1 item to pick, the client, or a State, etc.
if you are showing every personal goal and the address for every client , then you are doing something wrong.

You want a 'client' combo box, to show only client names, addr

then after that is chosen, you want a 'goal' combo, to pick ONLY the person in the cboClient box's goals
select goals where [personID] = " & cboClient

[pbaldy]

Sure; add the other table to the row source query, and join on the appropriate field(s), presumably client number.

[Trickle]

Many thanks for your replies. I am only getting the personal goals for a single client, which is what I want. I'll have a go at adding the Referral table to the row source query.

[Trickle]

This worked perfectly - many thanks

[pbaldy]

No problemo.