Using Bound Control right

[Adrian]

I know this is a basic access 101 question, but for some reason I am stumped
with how to get it to work. I have access 2002.

I have 2 tables. One called mbrinfo which contains various data and has an
id (mbrid) as the primary key. The 2nd table is employers which has an
employer id (empid) as the primary key and a employer name (empname) as the
2nd column. I am creating forms for input. The employer table is empty of
data at the moment. The form will have fields for both the empid and the
empname values.

I need to be able to type in the empid and name on the form and have it
record the empid in both the mbrInfo table and the employer table while
recording the empname only in the employer table. If that empid already
exists I would like the empname to display in that field on the form. Since
I am adding the data over time selecting from a list will not always work.

I am assuming that a bound control using a combo box is the way to go but I
cannot seem to get it to work.

 

[Jerry Whittle]

Based on your description, I'm assuming that there is a one-to-many
relationship between the employers and mbrinfo tables. You need to set up
this relationship in the Relationship window with Referential Integrity
enabled.

Next You need a form based on the employers table. On it you need to have a
subform based on the mbrinfo table. When set up this way both tables will get
populated properly to get them linked.

But there is a fly in the ointment. For this to work you MUST have an entry
in the employers table before you can put in a matching record in the mbrinfo
table.

However if there is actually a many-to-many relationship between these two
tables: a mbrid can work for more than one employer and an employer can have
more than one mbrid, then what you actually need is a third bridging (or
linking or joining) table where you have the PK from each table in a record.

 

[Adrian]

I did present this as a one to may relationship but you are right it really
should be many to many. I will work with a join table and see if I can get
this sorted. I was hoping to avoid a form or subform for employers since
there were only 2 data items in that table.

 

[Risse]

I know this is a basic access 101 question, but for some reason I am
stumped
with how to get it to work. I have access 2002.

I have 2 tables. One called mbrinfo which contains various data and has
an
id (mbrid) as the primary key. The 2nd table is employers which has an
employer id (empid) as the primary key and a employer name (empname) as
the
2nd column. I am creating forms for input. The employer table is empty
of
data at the moment. The form will have fields for both the empid and the
empname values.

I need to be able to type in the empid and name on the form and have it
record the empid in both the mbrInfo table and the employer table while
recording the empname only in the employer table. If that empid already
exists I would like the empname to display in that field on the form.
Since
I am adding the data over time selecting from a list will not always work.

I am assuming that a bound control using a combo box is the way to go but
I
cannot seem to get it to work.