Select field from table not used by form

[elbyc]

I'm working in Access 97 and am mostly a novice.
I have a form linked to a table called "Participants".
One of the fields is [Company]. I've got a control for it called
[Companytxt].

The [Company] field exists in another table called "Pipeline".
All the data in the "Participants" table is new. When I enter a new
record, I want to choose the the [Company] name from the Pipeline
table so I can set up a future relationship between the tables.

I would like to choose from the list of existing companies in the
"Pipeline" table and update the name of the company while I'm in my
form.

I've tried using a subform with a little combo box to the Pipeline
table. I don't know what code or event to write to make the results of
my selection populate the Companytxt control

 

[Larry Daugherty]

You've fallen into a trap carefully crafted by the product management
team at Microsoft to lure newbies.

That nifty thing in your table that has a combobox on it is a Lookup
Field. They are to be avoided at all costs. They will inevitably get
in your way as you try to actually do useful things with your Access
application. For further info/justification please visit
www.mvps.org/access

Then re-design your tables and relationships to accurately represent
what you want to happen.

They particularly suck in people who will be entering data directly
into the tables and trying to get Access to behave like a spreadsheet.
Don't require your users (including yourself) to enter data in tables.
Provide Forms instead.

Lookup tables are good. They are normal tables used to provide lists
from which things can be selected via comboboxes.

Lookup Fields are evil.

HTH

 

[John W. Vinson]

I'm working in Access 97 and am mostly a novice.
I have a form linked to a table called "Participants".
One of the fields is [Company]. I've got a control for it called
[Companytxt].

The [Company] field exists in another table called "Pipeline".
All the data in the "Participants" table is new. When I enter a new
record, I want to choose the the [Company] name from the Pipeline
table so I can set up a future relationship between the tables.

I would like to choose from the list of existing companies in the
"Pipeline" table and update the name of the company while I'm in my
form.

I've tried using a subform with a little combo box to the Pipeline
table. I don't know what code or event to write to make the results of
my selection populate the Companytxt control

Is the Company name unique within the Pipeline table? Is it the Primary Key of
the Pipeline table? If not, what is?

I'd suggest that the Participants table should simply have a CompanyID field
which uniquely identifies a company. This *could* be the company name, but
company names aren't really good choices for links: they aren't necessarily
unique; they can change over time (Ever hear of Enco? How about Exxon?), and
they tend to be long compared to a 4-byte long integer.

That would let you use a Form based on the Participants table, with a very
simple combo box - no subform, no code, no nothing - bound to the companyID
field.

John W. Vinson [MVP]

 

[elbyc]

You've fallen into a trap carefully crafted by the product management
team at Microsoft to lure newbies.

That nifty thing in your table that has a combobox on it is a Lookup
Field.  They are to be avoided at all costs.  They will inevitably get
in your way as you try to actually do useful things with your Access
application.  For further info/justification please visitwww.mvps.org/access

Then re-design your tables and relationships to accurately represent
what you want to happen.

They particularly suck in people who will be entering data directly
into the tables and trying to get Access to behave like a spreadsheet.
Don't require your users (including yourself) to enter data in tables.
Provide Forms instead.

Lookup tables are good.  They are normal tables used to provide lists
from which things can be selected via comboboxes.

Lookup Fields are evil.

HTH
--
-Larry-
--

I'm working in Access 97 and am mostly a novice.
I have a form linked to a table called "Participants".
One of the fields is [Company]. I've got a control for it called
[Companytxt].

The [Company] field exists in another table called "Pipeline".
All the data in the "Participants" table is new. When I enter a new
record, I want to choose the the [Company] name from the Pipeline
table so I can set up a future relationship between the tables.

I would like to choose from the list of existing companies in the
"Pipeline" table and update the name of the company while I'm in my
form.

I've tried using a subform with a little combo box to the Pipeline
table. I don't know what code or event to write to make the results of
my selection populate the Companytxt control- Hide quoted text -

- Show quoted text -

Hi - I was not aware you could do a combo box in a table, so my
wording was unclear - I assumed you could only have them in forms, and
was referring to my form.

Glad I didn't do that though, it sounds scary...

 

[elbyc]

Hi John,

The company name is unique, and you're right, it does change. Hadn't
thought of that. It is the primary key.

Your solution does not change the problem of having to match the
companyID from the Pipeline table if the combo box pulls from the
participants table, rather than from the pipeline table.

The ideal is to get the company name or id from the pipeline so I can
then relate the two tables using that field.

 

[John W. Vinson]

Hi John,

The company name is unique, and you're right, it does change. Hadn't
thought of that. It is the primary key.

Your solution does not change the problem of having to match the
companyID from the Pipeline table if the combo box pulls from the
participants table, rather than from the pipeline table.

The ideal is to get the company name or id from the pipeline so I can
then relate the two tables using that field.

Well... I guess I don't understand what you're doing.

A Combo Box takes a value from one table (its rowsource; if you want it to be
the company name you can use the Pipeline table as the rowsource) and puts it
into a field in another table (its Control Source, in this case the company
name field in the Participants table).

You wouldn't want to pull the company name from the participants table in
order to put it into the participants table! Pull the value *from where the
value is to be found*, the Pipeline table; and put it where it needs to go.

John W. Vinson [MVP]

 

[elbyc]

That was exactly what I wanted to do, but when I tried it I got blank
values in the combo box. I thought you couldn't reference a second
table so I was trying something else.

Thanks to your suggestion, I tried again and it works. Thank you very
much.