Combo box tied to a specific cell

[CB]

Hello everyone.
I have a table that I am using for my combo box list. It is called Name. I
want users to be able to select a name and have it populate the name column
of a second table, but I only want it to populate the name column for row 1
in my table. I want to have another name combo box populate the name column
for row 2 of my review table.

I have been successful in having the combo box display my list of names from
the Name table, but I can't get it to populate a specific row. Despite
playing with the Control and using usbforms, it always wants to add the name
value to a new row. Please bear in mind that I don't know VB.

Title of hero or heroine will be awarded for your assistance. Thank you.

 

[John W. Vinson]

Hello everyone.
I have a table that I am using for my combo box list. It is called Name. I
want users to be able to select a name and have it populate the name column
of a second table, but I only want it to populate the name column for row 1
in my table. I want to have another name combo box populate the name column
for row 2 of my review table.

I have been successful in having the combo box display my list of names from
the Name table, but I can't get it to populate a specific row. Despite
playing with the Control and using usbforms, it always wants to add the name
value to a new row. Please bear in mind that I don't know VB.

Title of hero or heroine will be awarded for your assistance. Thank you.

Well... *several* problems here.

For one, Name is a reserved word. A table has a Name property; a field has a
Name property; a form or a control on a form have... well, you know. If you
use Name as the Name of a field in the table, Access will get confused.

Secondly, a combo box is not a datatype. You can't store data "in a combo
box". It is a tool that gets data from one spot (its Row Source) and stores
that data into a different spot (its Control Source).

Thirdly, you're assuming incorrectly that tables have defined "rows". A table
isn't a spreadsheet. Different rows in the review table are different -
unrelated, unconnected! - *records* in a table. There's nothing to prevent the
two different records from having different value of the Name (or, better, the
CustomerName or ClientName or some other kind of name) field. You won't need a
separate combo box to do so.

Could you explain what tables you have, how they're related (if at all), and
in what field (or fields) you're trying to store the value from the combo box?
It would also help to describe the combo's RowSource.

 

[CB]

Thank you for replying and setting me straight.

Here is what I have:
Review Table
PK - ID
FK - PersonID
Review Type
Start Date
End Date
Percent Complete
FK - StatusID
There are only a set number of review types (there are a lot of them, but
still a set number). Each will have only one person assigned, one status,
ect., which is why I said one row.
Status Table
PK - ID
StatName
Activation date
Termination date

Person Table
PK - ID
EmpName
Team name
Activation date
Termination date
I have created a one to many relationship from the review table to the
person and status tables.

I have one form for each review type. I wanted people to be able to go that
review type form and fill it out for that review type. My control source for
the Person Name combo box is the PersonID and the row source is SELECT
Person.EmpName FROM Person RIGHT JOIN Review ON Person.EmpID=Review.ID; .

I have this sinking feeling that I will have to restructure my tables, but
if I create a table for each review type, it will be a lot of work and I had
hoped that I could just tie the Person combo box to the name field for a
specific reviewID.

Thank you for your time.

 

[John W. Vinson]

Thank you for replying and setting me straight.

Here is what I have:
Review Table
PK - ID
FK - PersonID
Review Type
Start Date
End Date
Percent Complete
FK - StatusID
There are only a set number of review types (there are a lot of them, but
still a set number). Each will have only one person assigned, one status,
ect., which is why I said one row.
Status Table
PK - ID
StatName
Activation date
Termination date

Person Table
PK - ID
EmpName
Team name
Activation date
Termination date
I have created a one to many relationship from the review table to the
person and status tables.

I have one form for each review type. I wanted people to be able to go that
review type form and fill it out for that review type. My control source for
the Person Name combo box is the PersonID and the row source is SELECT
Person.EmpName FROM Person RIGHT JOIN Review ON Person.EmpID=Review.ID; .

I have this sinking feeling that I will have to restructure my tables, but
if I create a table for each review type, it will be a lot of work and I had
hoped that I could just tie the Person combo box to the name field for a
specific reviewID.

Well, you certainly do NOT want to "store data in tablenames" by having one
table per review type!!!

I guess I don't understand the issue. How do the review types differ from one
another? Why do you need a whole separate *FORM* for different types of
review? Where is the data from these (different, customized???) forms stored?

I'm concerned that you've made the very common mistake of starting your design
with the forms and are "backfitting" the tables to them. What real-life
"entities" - persons, things, or events - are encountered in the course of a
review? Are you perhaps assuming that a review asking 38 questions will need a
table with 38 fields for the answers? If so... consider instead a one to many
relationship to a questions table one *record* per question, and an answers
table with one record per question per review. More info please!

 

[CB]

I did not design the backend before I started working on the forms. I do
plan on having a questions table and an answers table, but I am trying to
figure this problem out before I do more with them.

The general idea was to have a table that captured status information about
the different reviews, another one to use as a lookup table for the status, a
second table to be the lookup for the person's name, a third table to be the
lookup for the questions and a forth table to capture the responses. One
person will work on a review and there will be one status for each review,
but there will be many questions and answers tied to one review. Once all of
the reviews are done, the information will be studied and the database
refreshed for the next round (why it is being refrehsed is a bone of
contention and not my choice).

My problem is trying to get my form to work with my backend structure. When
a person fills out the name filed on Review Form A, I need that information
to be tied to Review ID 1 and I can't make that happen. I thought I could do
a select statement somehwere, but that doesn't seem to work.