Full Version: Both Names, One Control
UtterAccess Forums > Microsoft® Access > Access Forms
Greetings. I have a n00b question I'm hoping someone will be able to answer. Before I ask, let me briefly explain what the Access Database I'm designing will be used for.
ssentially, it keeps track of the tutorial system at the college I work for. When we receive a request for a tutor, we enter basic information for the tutee (student receiving the services, we call them Student Learners) such as their name, address, instructor, status, etc. When we hire a new tutor, we do the same thing in a separate table.
Then, when we assign a tutor to a tutee/Student Learner, we go back into the database and select the tutor's name from a drop down menu. The tutor's Primary Key is linked to a Tutor field in the Student Learner table. The drop down menu is a control for the Tutor field.
Now, when I'm designing the form, I get an annoyance. I open the drop down menu, select the tutor's name, and then move on. However, a meaningless number is replaced by the tutor's name because Access only displays the field that the drop-down is bound to.
Rather than showing the Primary Key (AutoNumber) the tutor was assigned, I'd like to be able to view the Tutor's Name in the drop-down menu, even when it was closed. I'm sure this is an issue others have run into as well?
For starters, can this be done?
I was thinking that I could set up a query (I'd have to do this anyways, because I only want the drop-down menu to include tutor's that meet certain criteria, i.e. those that are still active tutors and are not carried over from the previous semester) to do this. The only thing that would show would be a: Name: [FirstName] & " " & [LastName] column, and the query would NOT show the AutoNumber Primary Key or the First & Last Name fields.
So... would this work? The drop-down menu would get it's information from the query I described above.
However, that obviously wouldn't maintain the relationship between the Tutee/Student Learner and the Tutor. I'd have to define a relationship between my Tutors table and my Tutors query (that I described above), correct? If so -- and this is such a simple, embarrassing question - what would I link together? Obviously the Primary Key (AutoNumber) of the Tutor Table... but then link it to what from the query?
Your time, consideration, and help are GREATLY appreciated! Thanks!
ComboBox RowSource
SELECT TutorID, FirstName & " " & LastName as TutorName
FROM TutorsTable
ComboBox Properties:
Column Count: 2
Bound Column: 0
Column Widths: 0";2"
Worked great, thank you very much!
do have one other question, but I know this is probably just wishful thinking. Still, I'll ask anyways.
If/when a tutor indicates that they no longer wish to take new Student Learners (most commonly at the end of the semester), I'd like to make it so we go in and change their status to "inactive." What I'd like to happen at that point, then, would be to stop having their names show up on the drop down menus. That is, if they are no longer taking new Student Learners, then we'll never assign a Student Learner to them so their name shouldn't show up on the combo box on the Student Learner form.
Oknow this can be done by setting criteria in the query so only those with an "Active" status show up. However, this presents a problem in our form. Let's say that John Doe (Tutor) was assigned to Billy Bob (Student Learner). Later, we changed John Doe's (T) status to inactive. If we go back to the form for Billy Bob (SL), the tutor's name in the combo box is wrong because John Doe was made inactive.
So, is there anyway that I can stop making inactive tutor's show up in combo box's on forms, but not replace that value when we bring up the form of a pre-existing Student Learner? Like I said, wishful thinking, right?
Thanks again!
I think I read this somewhere, but i've never tried it.
The gist of it was to create two controls. One unbound combo box with the required query behind it and one text box bound to the 'Tutors' field.
You then place the text box over the combo box so it covers all but the select arrow at the right hand end.
In the 'after update' event of the combo, set the value of the text box to that of the combo.
This means that the text box will always show the value recorded in the underlying table, but it looks as though it's a combo box with a filtered list.
HAs I say, i've never tried it, but I don't see in principle why it wouldn't work.
Let me know if you have any success!
I would solve this problem by using one form to enter records
and another form to view already entered records. But the
smoke and mirrors thing suggested by Locky sounds like fun.
Do you have Active /Inactive field in your Tutor table?
Have you tried to filter your tutor control in your new student learner's form for Active?
That way only Active Tutors will appear for assignment.
Locky, I haven't tried it yet. However, in principal, it does seem like it would work. I'll let you know what happens if/when I try it (probably sometime tomorrow morning).
ce, thanks for the advice. Earlier, I had thought there was some reason this wouldn't work... but the more I think about it, the more I realize that it would make perfect sense to do it like that. The way our old/current database (the one I'm rebuilding) is set up, that method wouldn't work (because of the design of the forms), but the point of me redesigning the database is to make it more user-friendly and up-to-date - so yes, that is a definite possibility.
Cynthia, I do have a Status field in my Tutor table (which is actually linked to a TutorStatus table, which contains the Active and Inactive statuses). What I'm doing is designing a query so that (a)only active tutor's come up in the combo box and (b)their first and last names are in one field as opposed to two. The problem is that, when I pull up the form for an older Student Learner, a new tutor (the next in the table) shows up in the combo box instead of the one we originally assigned. In cases where we haven't reassigned a tutor, I want the original tutor's name to appear in the Combo Box for pre-existing assignments, but not show up in the drop-down menu when I add new Student Learners. Picky, aren't I? laugh.gif
Thanks for your help, everyone. I really appreciate it.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.