Listbox: I am stumped!

[-Mark Shelby]

I bet there is a simple solution, but I think I have stared at the problem to
long... Could someone please steer me in the right direction?

I have a form with a listbox on the lefthand side. when a selection is
clicked I want the box to display date from 3 different table into form on
the right side.

Typical example: the listbox is populated with "Account Name" on the left
and when the selected name is clicked on I want it to draw it's data from
three different table and display on same form. Exampe the name "John Smith"
is clicked on in the list box and I want the data from an "Accounts" table
(AccountID), a client table (ClientID) and a Debtor table (DebtorID).

I want the form fields to update the data in each ID field on the righthand
side when each name in the listbox is selected.

Right now the form will update 1 of the form fields but not the other two.
can someone help me out here? I am going nuts!

 

[Duane Hookom]

Much to vague to provide more than a WAG. Also please proofread your emails
as I spent too much time attempting to figure out how "display date from 3
different table" made any sense until I realized "date" was really "data"
and probably should have been "a field value".

You may be able to use DLookup() in the control sources of the text boxes.
You can reference the listbox in the Where clause of the DLookup(). Subforms
might also work by setting the Link Master/Child properties.

 

[-Mark Shelby]

Thanks for the reply. Sorry to be so vague, but I didn't want to bombard with
details! Also thanks for catching the date/data error.

I have 3 tables each with various date and a primary key. The Table names
are "debtors" with debtorID as key, "Accounts" with AccountID as key, and a
"client" table with ClientID as key.

I have a listbox on the leftside of my form with the names John Smith and
John Doe displayed alphabetically.

I have the text form fields on the right hand side of my form labeled
"Account ID" "Debtor ID" and "Client ID."

When I click on John Smith's name in the listbox I want the field values
from each table pulled and displayed in those form field with matching labels.

Similarly, when I click on John Doe's name in the list box I want the data
to change to display his DebtorID, AccountID and ClientID.

How do I accomplish this?

Some sample coding would be wonderful if possible.

 

[Duane Hookom]

You haven't described any table that has field that stores a name like "John
Doe". I'm not sure what fields which tables you want to display where and
where the Row Source of the list box is from.

 

[Dale Fye]

Mark,

You still have not given us any information about your table structure, so
whatever we provide is speculation.

The fact that John Doe is associated with more than one ID makes me think
your data structure needs to be reworked. If I were writing this
application, John Doe would only have one ID (call it PersID) value in my
entire application, and I would use that value in any table that I wanted to
track a persons ID. You could call it ClientID in the Clients table,
AccountID in the Accounts table, and DebtorID in the Debtors table, but I
would only have one ID associated with John Doe.

Given that you are stuck with your current structure, you could possibly
modify the query you use as the source for your listbox. Join whatever table
is currently filling your listbox to those other tables by the [Name] field
(or whatever links those tables) using a left join. Then bring in the
ClientID, AccountID, and DebtorID fields into the query. Then, in the Click
event of the listbox, you could fill the text boxes, something like:

Private Sub YourList_Click

me.txt_ClientID = me.YourList.column(2) '2 refers to the zero based
column in
your query
me.txt_AccountID = me.YourList.column(3)
me.txt_DebtorID = me.YourList.column(4)

End sub

HTH
Dale