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 on form only saves ID and not value
I've created a combo box on a form that a user can select from a set of staff
names referenced in a table of staff names. However, when I select my staff names from the combo box, what it stores in the table is just the ID, not the staff name - so when I create a report all I have in my staff name field is the ID, not the staff name. I can't figure out what I'm doing wrong. Please help. |
#2
|
|||
|
|||
Combo box on form only saves ID and not value
Actually, it sounds like you are doing it exactly right.
1. Some inexperienced users might tell you to open the table in design view, highlight your StaffID field, and then change the Display Control property from Textbox to combo or list, and then change the RowSource to your Staffs table, but they would be wrong. Of course, you can do this, and when you open your table, you will see the staff names rather than the ID, but it can be confusing when you think you are going to get an ID and actually see a name. 2. The proper way to do this is to link you staff table to the other table in your reports Record Source. Link the two tables on the StaffID field, and then add the StaffName field to the query. Then change the control source of the control where you want to see the name to the StaffName field. -- HTH Dale Don''t forget to rate the post if it was helpful! email address is invalid Please reply to newsgroup only. "Sashacat7" wrote: I've created a combo box on a form that a user can select from a set of staff names referenced in a table of staff names. However, when I select my staff names from the combo box, what it stores in the table is just the ID, not the staff name - so when I create a report all I have in my staff name field is the ID, not the staff name. I can't figure out what I'm doing wrong. Please help. |
#3
|
|||
|
|||
Combo box on form only saves ID and not value
Thanks, although can you give me some detail about how I complete #2?
Queries are an area where I'm very weak (plus I'm sketchy on terminology.) what I just tried was going into the field on my report where Staffname should show up, going into PropertiesControl SourceExpression Builder Then I added + table + staffname + staffname + value Which didn't work (probably for obvious reasons to someone who actually knows what they are doing.) Thanks for helping "Dale Fye" wrote: Actually, it sounds like you are doing it exactly right. 1. Some inexperienced users might tell you to open the table in design view, highlight your StaffID field, and then change the Display Control property from Textbox to combo or list, and then change the RowSource to your Staffs table, but they would be wrong. Of course, you can do this, and when you open your table, you will see the staff names rather than the ID, but it can be confusing when you think you are going to get an ID and actually see a name. 2. The proper way to do this is to link you staff table to the other table in your reports Record Source. Link the two tables on the StaffID field, and then add the StaffName field to the query. Then change the control source of the control where you want to see the name to the StaffName field. -- HTH Dale Don''t forget to rate the post if it was helpful! email address is invalid Please reply to newsgroup only. "Sashacat7" wrote: I've created a combo box on a form that a user can select from a set of staff names referenced in a table of staff names. However, when I select my staff names from the combo box, what it stores in the table is just the ID, not the staff name - so when I create a report all I have in my staff name field is the ID, not the staff name. I can't figure out what I'm doing wrong. Please help. |
#4
|
|||
|
|||
Combo box on form only saves ID and not value
Actually, you need to:
1. Open the report in design view. 2. Select the report (square in the upper left of the design) 3. In the reports properties, on the data tab, Record Source property, click the "..." to open the query that the report is designed from. If you are currently just using a table, it will ask you if you want to create a query based on the table (say yes). 4. In the query grid, add the Staff table (that contains the StaffID, StaffName, and other fields that pertain to the staff). and join the Staff table to the other table on the StaffID field. Then save the query and close it. You should now see the name of the saved query in the Record Source property of the forms property sheet. If you don't, scroll through the tables and queries until you find the one you just saved and select it. 5. Now, go to the Control Source on your report and select the StaffName field from the list of fields that are available. That should do it. When you run your report, instead of seeing the ID number that is associated with the name, you should now see the name. -- HTH Dale Don''t forget to rate the post if it was helpful! email address is invalid Please reply to newsgroup only. "Sashacat7" wrote: Thanks, although can you give me some detail about how I complete #2? Queries are an area where I'm very weak (plus I'm sketchy on terminology.) what I just tried was going into the field on my report where Staffname should show up, going into PropertiesControl SourceExpression Builder Then I added + table + staffname + staffname + value Which didn't work (probably for obvious reasons to someone who actually knows what they are doing.) Thanks for helping "Dale Fye" wrote: Actually, it sounds like you are doing it exactly right. 1. Some inexperienced users might tell you to open the table in design view, highlight your StaffID field, and then change the Display Control property from Textbox to combo or list, and then change the RowSource to your Staffs table, but they would be wrong. Of course, you can do this, and when you open your table, you will see the staff names rather than the ID, but it can be confusing when you think you are going to get an ID and actually see a name. 2. The proper way to do this is to link you staff table to the other table in your reports Record Source. Link the two tables on the StaffID field, and then add the StaffName field to the query. Then change the control source of the control where you want to see the name to the StaffName field. -- HTH Dale Don''t forget to rate the post if it was helpful! email address is invalid Please reply to newsgroup only. "Sashacat7" wrote: I've created a combo box on a form that a user can select from a set of staff names referenced in a table of staff names. However, when I select my staff names from the combo box, what it stores in the table is just the ID, not the staff name - so when I create a report all I have in my staff name field is the ID, not the staff name. I can't figure out what I'm doing wrong. Please help. |
#5
|
|||
|
|||
Combo box on form only saves ID and not value
Ok, I got as far as this "and join the Staff
table to the other table on the StaffID field. " Before I got lost again. I should have mentioned earlier I'm using Access 2007, in case that makes a difference. Thanks! "Dale Fye" wrote: Actually, you need to: 1. Open the report in design view. 2. Select the report (square in the upper left of the design) 3. In the reports properties, on the data tab, Record Source property, click the "..." to open the query that the report is designed from. If you are currently just using a table, it will ask you if you want to create a query based on the table (say yes). 4. In the query grid, add the Staff table (that contains the StaffID, StaffName, and other fields that pertain to the staff). and join the Staff table to the other table on the StaffID field. Then save the query and close it. You should now see the name of the saved query in the Record Source property of the forms property sheet. If you don't, scroll through the tables and queries until you find the one you just saved and select it. 5. Now, go to the Control Source on your report and select the StaffName field from the list of fields that are available. That should do it. When you run your report, instead of seeing the ID number that is associated with the name, you should now see the name. -- HTH Dale Don''t forget to rate the post if it was helpful! email address is invalid Please reply to newsgroup only. "Sashacat7" wrote: Thanks, although can you give me some detail about how I complete #2? Queries are an area where I'm very weak (plus I'm sketchy on terminology.) what I just tried was going into the field on my report where Staffname should show up, going into PropertiesControl SourceExpression Builder Then I added + table + staffname + staffname + value Which didn't work (probably for obvious reasons to someone who actually knows what they are doing.) Thanks for helping "Dale Fye" wrote: Actually, it sounds like you are doing it exactly right. 1. Some inexperienced users might tell you to open the table in design view, highlight your StaffID field, and then change the Display Control property from Textbox to combo or list, and then change the RowSource to your Staffs table, but they would be wrong. Of course, you can do this, and when you open your table, you will see the staff names rather than the ID, but it can be confusing when you think you are going to get an ID and actually see a name. 2. The proper way to do this is to link you staff table to the other table in your reports Record Source. Link the two tables on the StaffID field, and then add the StaffName field to the query. Then change the control source of the control where you want to see the name to the StaffName field. -- HTH Dale Don''t forget to rate the post if it was helpful! email address is invalid Please reply to newsgroup only. "Sashacat7" wrote: I've created a combo box on a form that a user can select from a set of staff names referenced in a table of staff names. However, when I select my staff names from the combo box, what it stores in the table is just the ID, not the staff name - so when I create a report all I have in my staff name field is the ID, not the staff name. I can't figure out what I'm doing wrong. Please help. |
#6
|
|||
|
|||
Combo box on form only saves ID and not value
2007 doesn't matter.
Actually, reading back to your initial post, you mention [ID] and "staff name field". So, what was your original query/table for the report, there should be a field that currently displays the [ID] with each staff member. Is that just [ID] or is it [StaffID], or [StaffName], or ???? In the Staff table, what are your fields? See questions above. It may be that you need to join the ID fields, not [StaffID]. On the other hand, if your problem is that you don't know how to join the fields in the query grid, then Click and hold on the ID (StaffID) field in one table, and drag from that table to the ID (StaffID) field in the other table. If neither of those solves the problem, then provide me with the names of the tables and their fields and I'll try to explain better with your actual table structure. -- HTH Dale Don''t forget to rate the post if it was helpful! email address is invalid Please reply to newsgroup only. "Sashacat7" wrote: Ok, I got as far as this "and join the Staff table to the other table on the StaffID field. " Before I got lost again. I should have mentioned earlier I'm using Access 2007, in case that makes a difference. Thanks! "Dale Fye" wrote: Actually, you need to: 1. Open the report in design view. 2. Select the report (square in the upper left of the design) 3. In the reports properties, on the data tab, Record Source property, click the "..." to open the query that the report is designed from. If you are currently just using a table, it will ask you if you want to create a query based on the table (say yes). 4. In the query grid, add the Staff table (that contains the StaffID, StaffName, and other fields that pertain to the staff). and join the Staff table to the other table on the StaffID field. Then save the query and close it. You should now see the name of the saved query in the Record Source property of the forms property sheet. If you don't, scroll through the tables and queries until you find the one you just saved and select it. 5. Now, go to the Control Source on your report and select the StaffName field from the list of fields that are available. That should do it. When you run your report, instead of seeing the ID number that is associated with the name, you should now see the name. -- HTH Dale Don''t forget to rate the post if it was helpful! email address is invalid Please reply to newsgroup only. "Sashacat7" wrote: Thanks, although can you give me some detail about how I complete #2? Queries are an area where I'm very weak (plus I'm sketchy on terminology.) what I just tried was going into the field on my report where Staffname should show up, going into PropertiesControl SourceExpression Builder Then I added + table + staffname + staffname + value Which didn't work (probably for obvious reasons to someone who actually knows what they are doing.) Thanks for helping "Dale Fye" wrote: Actually, it sounds like you are doing it exactly right. 1. Some inexperienced users might tell you to open the table in design view, highlight your StaffID field, and then change the Display Control property from Textbox to combo or list, and then change the RowSource to your Staffs table, but they would be wrong. Of course, you can do this, and when you open your table, you will see the staff names rather than the ID, but it can be confusing when you think you are going to get an ID and actually see a name. 2. The proper way to do this is to link you staff table to the other table in your reports Record Source. Link the two tables on the StaffID field, and then add the StaffName field to the query. Then change the control source of the control where you want to see the name to the StaffName field. -- HTH Dale Don''t forget to rate the post if it was helpful! email address is invalid Please reply to newsgroup only. "Sashacat7" wrote: I've created a combo box on a form that a user can select from a set of staff names referenced in a table of staff names. However, when I select my staff names from the combo box, what it stores in the table is just the ID, not the staff name - so when I create a report all I have in my staff name field is the ID, not the staff name. I can't figure out what I'm doing wrong. Please help. |
#7
|
|||
|
|||
Combo box on form only saves ID and not value
I have two tables "Stores" and "Staff Names"
All my forms and reports were originally built on the Stores table. I've decided to try and ease some data entry tasks by adding a combo box that references a new table called Staff Names. The Staff Names table has two fields: ID and Staff Names. Prior to the addition of the new combo box, we just typed in the staff name as a data entry field for that record. The report currently returns the value from either Staff 1 or Staff 2 in the Stores table. |
#8
|
|||
|
|||
Combo box on form only saves ID and not value
OK,
So you have fields [Staff 1] and [Staff 2] in the [Stores] table. In the form for entering store info, you now have two combo boxes (I would name these cbo_Staff_1 and cbo_Staff_2), which both reference the [Staff Names] table as their Row Source. Back to the Query grid. You will need to have the [Stores] table, and two copies of the [Staff Names] table (when you add the second staff names table, it will probably read [Staff Names_1]) in your query grid. Click and hold on the ID field in [Staff Names] and drag onto the [Staff 1] field in the [Stores] table. Then click and hold on the ID field in the [Staff Names_1] table and drag that connection to the [Staff 2] field in the [Stores] table. Right click on each of the lines connecting [Stores] to the two staff names tables and select the "Join Properties" option. Once that dialog has come up, click on the radio button that says something like: "Include all records from 'Stores' and only those records from 'Staff Names' where the joined fields are equal. Then click OK and repeat for the link to the other staff names table. If there are not any fields listed in the grid at the bottom of the page, double click on the * at the top of the [Stores] table, that tells Access to include all the fields from the Stores table in the query. Next, double click on the [Staff Names] field in the [Staff Names] table (you should now see this in the grid). Repeat this for the other [Staff Names_1] table. Run this query (click the excaimation point in the ribbon bar) to make sure you have all the fields you need for your report (along with the two names). Now save the query and go back to the design view of the report. Select the [Staff 1] field, open the properties window, and then select the [Staff Names] field as the control source for that field. Do the same for the [Staff 2] field, but select the 2nd version of the [Staff Names]. Hopefully, you will now see the names of the staff, rather than their ID values in the report. -- HTH Dale Don''t forget to rate the post if it was helpful! email address is invalid Please reply to newsgroup only. "Sashacat7" wrote: I have two tables "Stores" and "Staff Names" All my forms and reports were originally built on the Stores table. I've decided to try and ease some data entry tasks by adding a combo box that references a new table called Staff Names. The Staff Names table has two fields: ID and Staff Names. Prior to the addition of the new combo box, we just typed in the staff name as a data entry field for that record. The report currently returns the value from either Staff 1 or Staff 2 in the Stores table. |
#9
|
|||
|
|||
Combo box on form only saves ID and not value
I got this part "Next, double
click on the [Staff Names] field in the [Staff Names] table (you should now see this in the grid). Repeat this for the other [Staff Names_1] table." I have the stores fields listed down on the grid. I went back up to my staff names tables and double clicked on the Staff Names fields and they showed up in the next column of the grid What I have is: stores* staff names staff names stores staff names staff names_1 When I attempt to run the query, it gives me an error message of "Type Mismatch in Expression." |
#10
|
|||
|
|||
Combo box on form only saves ID and not value
According to Access, the problem is that I've got a mismatch in the field
types. The ID field and auto number and the staff name field is a text. |
|
Thread Tools | |
Display Modes | |
|
|