Show names in Lookup Field instead of IDs?

[Guest]

Hi,
I've found great help on here before, so I hope someone can offer something
this time as well. Bear with me, as this is a bit of a complicated question,
and I'm rather a beginner:

Here goes: I have a table (let's just call it Table1), which stores
investment accounts. Each account is linked to a specific Client in a Clients
table, through the ClientID field. Each account is also linked to lookup
tables for AccountType, InvestmentType, and Company, through their respective
ID fields.

So the fields of Table1 are as follows:
AccountID (primary key)
ClientID
AccountTypeID
InvestmentTypeID
CompanyID
Account Number (a text field filled by user input)

All this is well and good. I have a form (Form1) which is used to fill this
table, and the form uses Lookup fields for each item (except for AccountID
and Account Number), so the user can select the correct value for each field.
This table works fine.

HOWEVER (here comes the complicated part),

I also have Table2, which contains specific investments made in each
account, which is linked to Table1 through the AccountID field.

Here are the fields for Table2:
InvestmentID (primary key)
AccountID (links to Table1)
InvestmentDate (filled by user)
InvestmentAmount(filled by user)
CurrentDate (filled by user)
CurrentAmount (filled by user)
Etc. (a few other user-filled fields)

Here is the problem:
I need to have another user form to enter in each individual investment.
This form is linked to Form1 through the AccountID field, so when you click a
button on Form1, Form2 opens displaying the correct account, and then the
user can enter in the rest of the information.

However, I want the AccountID field on Form2 to display ALL of the
information from Table1. I've tried creating a Lookup field that shows all
the fields, but it only shows the number value in the underlying tables, and
NOT the names. For example, it DOES show up with the correct account in the
AccountID field on the form, but it shows up as a series of numbers such as
315 5 1 7 100005
instead of the text values that I want to display instead, such as:
John Brown Mutual Fund Non-Compounding 100005

Since these are all separate fields that I am combining into one Lookup
field, how do I do this, since the underlying fields are not Lookup fields?
Do I need to create some kind of query under this field? I've tried
everything I can think of, but it still just shows the numbers. Please let me
know if anything is not clear or needs more explanation.
Thanks for any help anyone can give!
Rose.

 

[George Nicholson]

1) Create a stored Select query that returns all InvestmentTypeIDs and
InvestmentTypes
2) Create a one-line high listbox or combo box on your form and use that
query as the RowSource
3) The control should be bound to the ID value (BoundColumn: 1)
but only display the name (i.e., Set ColumnWidths to: 0"; 2" or
something similar)
4) Set Locked = True and/or Enabled = False for that control if it's just
for the display of existing information.

Repeat for ClientID, CompanyID, AccountTypeID, etc.

You now have queries readily available whenever you may need to make similar
"translations" in the future (and you will...).

HTH,

 

[Guest]

Thanks George. However, what I am looking for is for the ONE combo box to show

"ClientName, AccountName, InvestmentName, CompanyName, AccountNumber"

so that the user can select which account to update for a specific client
(clients can have multiple Accounts which can have multiple Investments and
Companies, so I need it to show ALL of the fields).
Right now the combo box works, by setting the row-source to
"Client ID; AccountID; InvestmentID; CompanyID; AccountNumber"
BUT it shows all the ID NUMBERS, and not the names attached to the IDs, like
I need it to.

Any ideas? Let me know if you need more information.
Thanks!
Rose.

 

[Guest]

I FIGURED IT OUT!!!!

Yayyyy!! It was your query idea that helped me figure it out!

In case anyone else has the same question, here is what I did:

I created a new query linking "Table1" (through the AccountID) to the name
fields in each of the lookup tables. (And to First and Last name in the
Clients table.)
Then I used this query as the RecordSource for the lookup combo box in my
form, and concantenated all of the fields, one after the other (hiding the
AccountID field), into the second column of my combo box, using &", "& and
&": "& where needed.

I haven't used the form yet, but so far it seems to be just what I needed! I
can now select the Account (or have it show up automatically if the user
comes from the Create Account form), and have all of names show up, even when
the list loses focus.

Thanks for your idea George!
Rose.