Frustrated trying to display Information

[Afrosheen via AccessMonster.com]

Thanks for reading this and helping me.
Here is my problem. I have two tables. Table 1 has 3 fields called Supervisor,
SupNumber, and SupPosition. It also has other fields in it, but it's the
three I'm having problems with. Table 2 has the same field names with the
information. I tried creating a relationship with One [table2] to Many
[table1]. For back round you may have one supervisor and many employees. The
key field in table 2 is set for the Supervisor. The key in table 1 is a field
called staffID used for another relation.

What I have done is set up a combo box in form 1 with the row source being
Supervisor from table 2 and then inserting the information in to table 1. I
created two text boxes so hopefully I can pull the information from table 2
and display them on the text boxes on the form. And also when I select any
Supervisor the information associated with that record will display with the
Supervisor.

I tried after update on the combo box to get the information within table 2
and nothing happens. I don't have code to show you because I'm lost as to how
to get it.

Any help would be appreciated.

Thanks

 

[John W. Vinson]

Thanks for reading this and helping me.
Here is my problem. I have two tables. Table 1 has 3 fields called Supervisor,
SupNumber, and SupPosition. It also has other fields in it, but it's the
three I'm having problems with. Table 2 has the same field names with the
information.

That would seem to be your mistake. See below.

I tried creating a relationship with One [table2] to Many
[table1]. For back round you may have one supervisor and many employees. The
key field in table 2 is set for the Supervisor. The key in table 1 is a field
called staffID used for another relation.

The ID can be multiply used.

What I have done is set up a combo box in form 1 with the row source being
Supervisor from table 2 and then inserting the information in to table 1.

If you're putting a combo box *IN THE TABLE* rather than in a form... don't.
See http://www.mvps.org/access/lookupfields.htm for a critique of what many
consider a misfeature. Combos on forms are fine, and indeed universal.

I created two text boxes so hopefully I can pull the information from table 2
and display them on the text boxes on the form. And also when I select any
Supervisor the information associated with that record will display with the
Supervisor.

The second table should contain a SupervisorID field - ONLY. Relational
databases use the "Grandmother's Pantry Principle" - "a place - ONE place! -
for everything, everything in its place". The SupNumber and SupPosition fields
should exist in Table 1, *and noplace else*.

On a Form (which you should be using for all interactions with your data) you
can use a Combo Box based on table1 to look up the supervisor information; you
can display the other fields on the form by including the other fields in the
combo box's Row Source query, and put textboxes on the form with control
sources like

=comboboxname.Column(n)

where n is the zero based index of the field that you want to see.

 

[Afrosheen via AccessMonster.com]

Thanks John for getting back to me so quickly. I'll try your suggestions at
work tomorrow and get back to you tomorrow night.

Thanks

Thanks for reading this and helping me.
Here is my problem. I have two tables. Table 1 has 3 fields called Supervisor,
SupNumber, and SupPosition. It also has other fields in it, but it's the
three I'm having problems with. Table 2 has the same field names with the
information.

That would seem to be your mistake. See below.

I tried creating a relationship with One [table2] to Many
[table1]. For back round you may have one supervisor and many employees. The
key field in table 2 is set for the Supervisor. The key in table 1 is a field
called staffID used for another relation.

The ID can be multiply used.

What I have done is set up a combo box in form 1 with the row source being
Supervisor from table 2 and then inserting the information in to table 1.

If you're putting a combo box *IN THE TABLE* rather than in a form... don't.
See http://www.mvps.org/access/lookupfields.htm for a critique of what many
consider a misfeature. Combos on forms are fine, and indeed universal.

I created two text boxes so hopefully I can pull the information from table 2
and display them on the text boxes on the form. And also when I select any
Supervisor the information associated with that record will display with the
Supervisor.

The second table should contain a SupervisorID field - ONLY. Relational
databases use the "Grandmother's Pantry Principle" - "a place - ONE place! -
for everything, everything in its place". The SupNumber and SupPosition fields
should exist in Table 1, *and noplace else*.

On a Form (which you should be using for all interactions with your data) you
can use a Combo Box based on table1 to look up the supervisor information; you
can display the other fields on the form by including the other fields in the
combo box's Row Source query, and put textboxes on the form with control
sources like

=comboboxname.Column(n)

where n is the zero based index of the field that you want to see.