Full Version: Form Design Help, Please: How Do I Get My Form To Display Names Instead Of Keys?
UtterAccess Forums > Microsoft® Access > Access Forms
I am having considerable trouble designing a data entry form that both functions correctly and is user-friendly. Attached are items that will familiarize you with my design. Proprietary information has been blocked out in red, but this should not inhibit the reader’s understanding.
My database serves a training department, and the form I am designing is connected with two tables: 1) tblSessions (stores data general to each training session, such as date, time, instructor, etc.); and 2) tblAssessments (stores data about each participant of the class, such as their test scores). The latter, tblAssessments, is interfaced via a datasheet-style subform (please refer to Form.jpg—the subform is located under the “Trainee Information” label). An important detail about the form is all fields with combo boxes in the “Session Information” area, as well as TraineeID in the subform have a foreign key in the bound column, and the name of the person/entity in unbound columns.
Here’s my main problem in a nutshell: This is a nightmare for the poor data entry person, because after all is selected in combo boxes, the user is left with a screen filled with ID numbers—mostly foreign keys. The user is able to see the name in the combo box list when a person or entity is being selected, but there is no easy way to double-check one’s work afterward.
I am a fairly new Access 2007 user, so it would be of extreme help to me if someone could explain, starting with broad steps, what exact design changes I need to make to the form, tables, or both, to allow the user to select names from the combo boxes, and see names displayed on the form when finished. When in reference to a person, LastName, FirstName format would be ideal.
Here’s a smaller matter: Referring to Form.jpg, notice please that the first six digits of the SessionID value equals the CourseID value. SessionIDs are numbered sequentially, so I would like to get Access to auto-populate the next available SessionID for a given CourseID, once CourseID is entered. How is this accomplished?
Thank you so much in advance!!!
the column width of each combo box defines what data is shown when you drop down:
The first non zero # is what is displayed
The zero hides the first column
Hope that helps,
Thank you, Kevin, but I am concerned with what appears after the selection is made. When the user enters data with these controls, names do appear on the dropdown boxes, to the right of the key field value. The bound column is the key field, and names are displayed in unbound columns. Thus, it is possible to see the name of the person or facility while selecting. My concern is with what happens after this. Once a selection is made, the key value is displayed, as this is what has been entered into the table. The key fields include CourseID, InstructorID, AltInstructorID, FacilityID, and TraineeID (in the subform). Once all of these items are entered, each displays an ID number, not something meaningful like a name. If the user were to check his or her work, it would be necessary to click on each individual dropdown arrow to see which person, course, or facility is highlighted on the list. My desired goal is to change this situation such that names are shown on the form. For example, if John Doe were taking a training course with my company, I would like the user to select him from a dropdown, and see "Doe, John" displayed in the form control after the selection is made (even if the key value is what actually gets entered into the table). Presently, if I were to do this, I would see only the key value (his employee number) in the control after selection. I do not know what design element I need to change in order to accomplish this--do I need to change the form or its controls, or the fields in the actual tables? In what way do they need changed?
Thanks again!
Jack Cowley
If your form has Combo boxes for CourseID, InstructorID, etc. then the users will see the text equivalent of the saved Primary Key. Only the table will have the PK's and your users should never see the tables, but forms where you can show them the text values and not the PK's.... It will not be necessary for the user to select the combo boxes as they will show the Text as you move from record to record. Try it and I think you will see what I mean.
If you currently see the PK then you need to hide that column (in the combo box) and then the user will see the Text value of the next column.....
I hope this is making sense!
Eureka! I just tried what both of you were trying to tell me, and it worked. Thank you both so much!!!
Jack Cowley
You are most welcome and we are glad the we could help you!! If you create your combo's using the Wizard Access will automatically hide the PK and show as many columns as you like, when you open the combo. After a selection is made with a combo on the one of the selected columns will be shown, not all of them.
ontinued success with your database...
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.