Working on a form with filtering

[sneuberg]

The ugly way to do that is just put a parameter in the forms Record Source query like I did in the attached screen shot. That's ugly as the user only gets it when the form is opened and it doesn't show the possible Years for example.

I suggest you put combo boxes in the header and populate them with the distinct values in the Record Source. You could start this by save the Record Source to a query and then use the query name in the Record Source Let say the name is qryMasterTableRecordSource. The you can make Row Source query from it like:

SELECT DISTINCT qryMasterTableRecordSource.[Academic Year]
FROM qryMasterTableRecordSource;

Which would be for the Year combo box. In the afterupdates of the combo boxes you could do your filtering. Something like:

Me.Filter = "[Academic Year] = " & Me.cboYear
Me.FilterOn = True

where cboYear would be the name of the combo box. Note that there are no single quotes in the filter as Academic Year is a number. If it were text like Academic Term then it would be like:

Me.Filter = "[Academic Term] = '" & Me.cboTerm & "'"
Me.FilterOn = True

 

[The Rev]

Ok, so for some reason, the DB grew from a few MB to over 140 MB since I uploaded it the last time. Even though I compacted it on close. So I can't upload it anymore. Weird...

Anyway...
Well, I found a bug in the classroom location form. I made a copy and fixed all of the different moving parts and called the new form "EditMasterByTerm", but if I need to reassign the classroom, the classroom number fails to populate when the location ID is selected for an existing record I want to edit. I really appreciate your help on this.

 

[sneuberg]

Ok, so for some reason, the DB grew from a few MB to over 140 MB since I uploaded it the last time. Even though I compacted it on close. So I can't upload it anymore. Weird...

It went from 1.8 MB to 140 MB? That's really not good. Unless you actual did something to justify that increase I suggest starting over with a fresh database and import your objects from this bloated thing. Did you add any large embedded images?

Anyway...
Well, I found a bug in the classroom location form. I made a copy and fixed all of the different moving parts and called the new form "EditMasterByTerm", but if I need to reassign the classroom, the classroom number fails to populate when the location ID is selected for an existing record I want to edit. I really appreciate your help on this.

I don't understand the existing Location Class Numbers form. Since two table are involved; Campus Location and Locations the normal way to do this is to put the parent tables fields (Campus Locations) in a main form and the child tables fields (Locations) in a subform. In that type of setup the Key or LocationID in this case in propagates from the main form to the subform through the subform links. I suggest you set these up like that.

 

[The Rev]

It went from 1.8 MB to 140 MB? That's really not good. Unless you actual did something to justify that increase I suggest starting over with a fresh database and import your objects from this bloated thing. Did you add any large embedded images?

It was a graphic they wanted added on all of the forms. I removed it and voila! 1.4 MB...

I don't understand the existing Location Class Numbers form. Since two table are involved; Campus Location and Locations the normal way to do this is to put the parent tables fields (Campus Locations) in a main form and the child tables fields (Locations) in a subform. In that type of setup the Key or LocationID in this case in propagates from the main form to the subform through the subform links. I suggest you set these up like that.

I'll give that a look tomorrow. Thanks again. You have been a life saver!

 

[The Rev]

Ok, I decided to go another route for the time being. Have a look at my MasterClassScheduler form. I can get every Professor and every class that is currently assigned to show in my combobox, but I actually need all of the Professors and all of the classes to show in their respective comboboxes. I'm almost there, but this one last bug is beating my brains in... :banghead:

I created a Professor named TBD to test this so it will make it easier to troubleshoot.

:banghead::banghead::banghead:

 

[sneuberg]

The Row Source for the Link Master Table.ProfID combo box has the Professors table joined with the Link Master Table table (see attached screen shot). This will limit the selections to what's in the Link Master Table. If you want all of the Professor I suggest removing the Link Master Table from this query.

What were you trying to do that made you add the Link Master Table?

 

[The Rev]

The Row Source for the Link Master Table.ProfID combo box has the Professors table joined with the Link Master Table table (see attached screen shot). This will limit the selections to what's in the Link Master Table. If you want all of the Professor I suggest removing the Link Master Table from this query.

What were you trying to do that made you add the Link Master Table?

When I remove the LMT from the query and add the ProfID from the Professor's table, the query shows the extra (unassigned) professor named TBD with PCID 999999999. However, the new ProfID combobox on the form will not show the TBD in the dropdown unless I change the control source to Professors.ProfID, and then when I try to create a new assignment (using the new record icon at the bottom of the form), and I try to select TBD from the combobox, it tells me it is bound to an autonumber field and can't be changed. Why is this so convoluted?

I seriously owe you a gift card or something!

 

[sneuberg]

I think you are asking to much of this form. I suggest you redesign the MasterClassScheduler to maintain just the Link Master Table. I suggest changing the record source of the form to just that table to make sure you don't have form control bound to the related tables. To display the data in the related tables you can either put these textboxes in a subform or use the columns of the combo box as the control sources. I've done that for the professors combo box in the attached database. What is did was:

1. Changed the name of the combo box to cboProfID as the spaces in the name you had were giving me a hard time.
   
2. Changed the Row Source of the combo box as shown in the attached qryLinksProfs.jpg. The ProfID and InstructorEmail were added
   
3. Changed the Column Count of the combo box to 9
   
4. Changed the control sources of the textboxes for the Instructor to the columns of the combo box in the form =cboProfID.Column(index) where the index is the column number starting at 0 and going to 8. For example cboProfID.Column(0) refers to the ProfID and cboProfID.Column(8) is the email address

You can see from the attachment WBU Master Class Scheduler that the AssignmentNotes is bound to the Record Source as it should be since its a field in the Link Master Table.

I didn't change the Column Widths. You may want to change this so the first width is 0 so that the ProfID isn't shown.

I suggest you do this for the rest of the form. You will probably have to use a subform to get some of the fields on the Class Location. I understand you can manipulate the properties of a subform so that is appears like it's just part of the form.