Good evening. I have a Professor's table with the professor's unique employee ID as my primary key on the Professor's table. I also have a "Classes" table with the unique class ID as the primary key. I have a linked master table with professors getting assigned to classes. I need to be able to select the professor's ID number from a combo box and have the name and title auto-populate from that combo box and then have another combo box populate the class name from the class ID while at the same time populating a new line on my link master table. For some reason, it just isn't adding the new line to the link master table. What am I doing wrong??:banghead:
What's it? A form with a subform? You'll probably get a faster answer to your problem if you upload your database, and explain what's suppose to happen in response to which event.
What's it? A form with a subform? You'll probably get a faster answer to your problem if you upload your database, and explain what's suppose to happen in response to which event.
Yes. I created a form and just added the two combo boxes for testing purposes. I need to have a new record get created in my link master table when I select the data for both combo boxes. I can figure out the filtering stuff later.
I think you should upload your database. It would make things go faster.
Assuming your link master table is the junction table between the Professor table and the class table, then your form should have that table, the master link table as the record source. The the bound column of the combo boxes should be the foreign keys of the Professor table and Classes table. The row sources of the combo boxes should be the Professor table and Classes table. Typically these are set up with the ID (key field) as the first column and then something like the name as the second column. The row count is set to two, the bound column to one, and Column Widths something like 0";1"
So then the combo boxes are populated with the data from their tables; professor and class. So picking a professor in the professor combo box , for example, inserts the primary key (ID) from the professor table into the master link table.
I need to know how you have this set up before I can tell you what's wrong.
I think you should upload your database. It would make things go faster.
Assuming your link master table is the junction table between the Professor table and the class table, then your form should have that table, the master link table as the record source. The the bound column of the combo boxes should be the foreign keys of the Professor table and Classes table. The row sources of the combo boxes should be the Professor table and Classes table. Typically these are set up with the ID (key field) as the first column and then something like the name as the second column. The row count is set to two, the bound column to one, and Column Widths something like 0";1"
So then the combo boxes are populated with the data from their tables; professor and class. So picking a professor in the professor combo box , for example, inserts the primary key (ID) from the professor table into the master link table.
I need to know how you have this set up before I can tell you what's wrong.
Ok, I actually got that part figured out. Was using the ProfessorID from the Professor's table. Either way, now I am a bit further along and I am trying to bind the Campus Location field to the Location Code combobox on my MasterClassScheduler table, and I don't know how to do that.
I also can't add the classrooms using the combobox to a new record using the MasterClassScheduler form because it says the join key is not in the recordset.
So, I figured out the binding for the text box. Had to change the row source to add the extra column and then bind the text box to the Column(1). Still can't figure out the classroom assignment.
I also will need to filter the classrooms based on the location I select since each location has unique classroom numbers. But I need to fix the combobox first.
Still a bit confused on how to handle the Campus Locations. Seem like the user would choose that first and then the Class Room. I thinking this through.
Still a bit confused on how to handle the Campus Locations. Seem like the user would choose that first and then the Class Room. I thinking this through.
So, when I try to add a new record, can select the Professor, class, and location just fine, but when I try to select the classroom, it tells me the "join key of table 'Locations' not in recordset"
In the version you just sent me the ClassRoomNumber combo still has the Classroomnumber as the control source. This should be UniqueClassroomID. The key should be the bound column.
In the version you just sent me the ClassRoomNumber combo still has the Classroomnumber as the control source. This should be UniqueClassroomID. The key should be the bound column.
I change the control source on the lasted version to UniqueClassroomID and I'm not getting that message. What are you doing when you see this message?
So far as the LocationCode combo box is concerned we can't use that to determine what displayed in the ClassRoomNumber combo as is bound to the LocationCode. That nice as you would like to see the locations of the classes that have already been entered. But that's determined the UniqueClassroomID.
So I suggest changing that combo to a textbox since it just to display info and add a new unbound combo to be used to select a new location. I'll try this and get back with you on the details.
I've uploaded your database with the changes I tried. Please don't adopt this as yours as I could have messed it up. Please just consider a source of explanation.
I added an additional unbound combo box named LocationCodeSelect to be used to select a campus location that would act to limit the selection in the ClassRoomNumber combo. Actually I just used to wizard to make this. It's Row Source is
Code:
SELECT [Campus Locations].LocationID, [Campus Locations].LocationCode, [Campus Locations].CampusLocation
FROM [Campus Locations]
ORDER BY [Campus Locations].[CampusLocation] DESC;
I meant the sort order to be ascending but you probably have your own preferences anyway. Since this controls the content of the ClassRoomNumber combo its after update has
Code:
Me.ClassroomNumber.Requery
The Row Source of the ClassRoomNumber combo was change to
Code:
SELECT Locations.UniqueClassroomID, Locations.ClassroomNumber, Locations.LocationID
FROM Locations
WHERE (((Locations.LocationID)=[forms]![MasterClassScheduler]![LocationCodeSelect]));
The change you should note here is in the WHERE clause which you can see depends on what is selected in the LocationCodeSelect combo.
I change the LocationCode combo to a text box which broke the CampusLocation textbox so the I change the control source of that to
CampusLocation
If you try this out first selecting a Campus location with the LocationCodeSelect (Needs a better label) you will see that the classroom have been narrowed down to that location. Once you select the class room you will see that the LocationCode and CampusLocation update accordingly.
It probably seems like the LocationCodeSelect and LocationCode should be combined. Maybe they can, but I can't see how right now. I'll sleep on it.
I've uploaded your database with the changes I tried. Please don't adopt this as yours as I could have messed it up. Please just consider a source of explanation.
I added an additional unbound combo box named LocationCodeSelect to be used to select a campus location that would act to limit the selection in the ClassRoomNumber combo. Actually I just used to wizard to make this. It's Row Source is
Code:
SELECT [Campus Locations].LocationID, [Campus Locations].LocationCode, [Campus Locations].CampusLocation
FROM [Campus Locations]
ORDER BY [Campus Locations].[CampusLocation] DESC;
I meant the sort order to be ascending but you probably have your own preferences anyway. Since this controls the content of the ClassRoomNumber combo its after update has
Code:
Me.ClassroomNumber.Requery
The Row Source of the ClassRoomNumber combo was change to
Code:
SELECT Locations.UniqueClassroomID, Locations.ClassroomNumber, Locations.LocationID
FROM Locations
WHERE (((Locations.LocationID)=[forms]![MasterClassScheduler]![LocationCodeSelect]));
The change you should note here is in the WHERE clause which you can see depends on what is selected in the LocationCodeSelect combo.
I change the LocationCode combo to a text box which broke the CampusLocation textbox so the I change the control source of that to
CampusLocation
If you try this out first selecting a Campus location with the LocationCodeSelect (Needs a better label) you will see that the classroom have been narrowed down to that location. Once you select the class room you will see that the LocationCode and CampusLocation update accordingly.
It probably seems like the LocationCodeSelect and LocationCode should be combined. Maybe they can, but I can't see how right now. I'll sleep on it.
Many thanks. I was wondering if just hiding the Location Code text box would make the form look better while still maintaining the functionality you provided above...
I've uploaded your database with the changes I tried. Please don't adopt this as yours as I could have messed it up. Please just consider a source of explanation.
I added an additional unbound combo box named LocationCodeSelect to be used to select a campus location that would act to limit the selection in the ClassRoomNumber combo. Actually I just used to wizard to make this. It's Row Source is
Code:
SELECT [Campus Locations].LocationID, [Campus Locations].LocationCode, [Campus Locations].CampusLocation
FROM [Campus Locations]
ORDER BY [Campus Locations].[CampusLocation] DESC;
I meant the sort order to be ascending but you probably have your own preferences anyway. Since this controls the content of the ClassRoomNumber combo its after update has
Code:
Me.ClassroomNumber.Requery
The Row Source of the ClassRoomNumber combo was change to
Code:
SELECT Locations.UniqueClassroomID, Locations.ClassroomNumber, Locations.LocationID
FROM Locations
WHERE (((Locations.LocationID)=[forms]![MasterClassScheduler]![LocationCodeSelect]));
The change you should note here is in the WHERE clause which you can see depends on what is selected in the LocationCodeSelect combo.
I change the LocationCode combo to a text box which broke the CampusLocation textbox so the I change the control source of that to
CampusLocation
If you try this out first selecting a Campus location with the LocationCodeSelect (Needs a better label) you will see that the classroom have been narrowed down to that location. Once you select the class room you will see that the LocationCode and CampusLocation update accordingly.
It probably seems like the LocationCodeSelect and LocationCode should be combined. Maybe they can, but I can't see how right now. I'll sleep on it.
Dude! You are my hero. Here is my finished product. I hid the Location Code text box and just use the new combobox for the display on the form. Works like a charm.
So, I want to prompt the secretary on the MasterTable form which year and term she wants to see in the history form. How can I make the form prompt her to input which year and quarter she wants to filter the data by? I've tried "Filter on Load", but I can't remember for the life of me how to make it prompt for the filter parameter...
