If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Combo box tied to a specific cell
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. |
#2
|
|||
|
|||
Combo box tied to a specific cell
On Sun, 4 Apr 2010 14:56:01 -0700, CB wrote:
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. -- John W. Vinson [MVP] |
#3
|
|||
|
|||
Combo box tied to a specific cell
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" wrote: On Sun, 4 Apr 2010 14:56:01 -0700, CB wrote: 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. -- John W. Vinson [MVP] . |
#4
|
|||
|
|||
Combo box tied to a specific cell
On Mon, 5 Apr 2010 05:45:01 -0700, CB wrote:
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! -- John W. Vinson [MVP] |
#5
|
|||
|
|||
Combo box tied to a specific cell
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. "John W. Vinson" wrote: On Mon, 5 Apr 2010 05:45:01 -0700, CB wrote: 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! -- John W. Vinson [MVP] . |
Thread Tools | |
Display Modes | |
|
|