March 5, 2015 at 4:27 pm
The database is essentially a student registration system, so in a grossly oversimplified overview we have three tables: Students (PK StudentID), lkupDistricts (PK DistrictNum), lkupSchools (PK DistrictNum, SchoolNum). Schools have a two field PK and the SchoolNum field is not unique, I don't know why, that's how the State Dept of Ed has done things.
I'm having problem with a combo box for selecting the school. I want to restrict it to show only schools in the current district. The problem is that it seems like Access is buffering the district number from the first record loaded.
The row source is as follows:
PARAMETERS DistrictNum Text ( 255 );
SELECT dbo_lkupSchools.SchoolNum,
dbo_lkupSchools.DistrictNum,
dbo_lkupSchools.SchoolName,
dbo_lkupSchools.SchoolCity
FROM dbo_lkupSchools
WHERE (((dbo_lkupSchools.DistrictNum)=[forms]![frmStudentsUnrestricted]![DistrictNum]))
ORDER BY dbo_lkupSchools.SchoolName;
If I change the sort or filter and reopen the form, then it buffers on the new first district number. I created a view that the combo box pulled from and also tried to pull the district number directly from the base table with a WHERE clause, no change in behavior. We're looking at around 200 districts and 2000 schools.
I'm seriously considering putting the district number in a local parameter table and updating it every time I change records. All of the users will have their own copy of the app (the back end is a hosted SQL Server), so that could be viable.
I think this should be fairly simple, I have a feeling that I'm missing something pretty basic but I just can't see it.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
March 5, 2015 at 4:44 pm
For what it's worth, I would probably build this as a stored procedure in SQL Server. Then you can do something like include the columns you're filtering on in the index. At the very least, you can see the execution plan and figure out how the SQL statement is being executed and how to fix it.
March 5, 2015 at 4:50 pm
I am considering that, I'm not yet committed to it. The one good thing is that even though this system will be used state-wide is that I can only see the two or three biggest districts using the school field, the rest of the districts will have a small enough number of students in the system that they won't really benefit from populating it.
But you never know. It's possible that I have a bunch of Type A's out there who will insist on tracking all of their entries by school.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
March 5, 2015 at 9:16 pm
You are looking to use what are called "cascading combo boxes" in Access forms. The basic concept is that you update the rowsource for the School combo box whenever the District combo box updated. To do that you need a small VBA procedure on the AfterUpdate event of the District combo box that changes the rowsource of the School combo box to match the district that has been chosen. (I assume that you are linking to the SQL Server tables, and are not using the .ADP approach.) If you need more detailed instructions on how to do that, post back, but if you search for the term "cascading combo boxes" you will find a number of examples.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
March 6, 2015 at 7:54 am
Thanks, Wendell. I'll give that a shot, that sounds like what I'm looking for. And Piet, I'm not ignoring your approach either.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
March 6, 2015 at 10:16 am
Wendell, that did it! What I needed was to add a cboSchool.requery to the On Current of the form. I had the query right, just needed to refresh it.
Thanks!
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply