Cool combo box functionality desired--changing sort order and highlighting

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Arnold - 08 Mar 2006 10:59 GMT

Greetings Gurus,

In a mainform's header, I have a combobox named comboStudents. The
rowsource for this combobox is:

SELECT -999 As StudentID, "<Add New Student>" As FullName, "aaa" As
LastName, "x" As FirstName From qryStudents UNION SELECT StudentID,
FullName, LastName, FirstName FROM qryStudents
ORDER BY LastName;

which was from Wayne Gillespie and works great to allow teachers to
open an 'add new student form' by clicking on Add New Student item in
the combobox.

I would like to add the following functionality to the combobox:

1. have a button outside the combobox that, when clicked or toggled,
switches the listing of the students' names in the combobox to be
lastname, then firstname, so teachers who prefer to look up students by
their last names could do so.

2. include a checkmark column or some kind of highlighting of the
students whose records have been updated for that day. For instance,
this whole database is a 'progress reports' database in which daily
notes on each student's behavior and performance are recorded. If a
teacher enters some notes, then closes out of the dbase, then returns
to the dbase that day or the following day, I'd like for the combobox
to somehow display a mark for all the students who have been addressed
during the last instance or day the database was used (or mark the
students who haven't been reported on yet). This would help the
teachers jump to the students who have not been reported on.

Are these possible?

The mainform is unbound; for instance, the Recorde Source is SELECT *
FROM Students WHERE StudentID=7;

Thanks a lot.

Arnold

Reply to this Message

Anthony England - 08 Mar 2006 12:14 GMT

> Greetings Gurus,
>
[quoted text clipped - 36 lines]
>
> Arnold

You say the main form is unbound, but to me it looks like it is bound - but
you have an unbound combo box on it, the purpose of which is to jump to the
highlighted record (or add a new record).

Both of the tasks you ask about could be achieved by changing the RowSource
for the combo box. By looking at the select statement, I would guess that
FullName is a calculated field in the qryStudents but I can't know this for
sure. The button just needs to re-write the SQL for the combo.

The second task also needs a bit more info, but assuming there was a related
table called StudentNotes then the following query would add an extra column
showing how many notes you had made about the student that day:

SELECT StudentID, FullName, LastName, FirstName,
(SELECT COUNT(*) FROM StudentNotes
WHERE Students.StudentID=StudentNotes.StudentID AND
DateOfNote>=Date()) AS NotesToday
FROM Students

Whilst you may be currently happy with your combo box, there are limits for
this control, and you can achieve far more exiting things if the FindStudent
functionality was done with something other than a simple combobox. For
example, if pressing a button opens a search form where you type in "Arnold"
this would be able to say show you a list of people with either a firstname
or lastname of Arnold. The list of students could show those with no notes
highlighted in red - these sorts of things are not possible with a combo.

Reply to this Message

Arnold - 09 Mar 2006 02:00 GMT

Thanks Anthony for the reply. Interesting ideas.

I successfully re-wrote the SQL, and instead of using a command button,
I used 2 labels: Sort by Last Name, and Sort by First Name.

When a user clicks the Sort by Last Name label, the event procedure is:

Me.lblSortbyFirstName.Visible = True
Me.lblSortbyLastName.Visible = False
Me!ComboStudents.RowSource = "SELECT -999 As StudentID, '<Add New
Student>' As FullName, 'aaa' As LastName, 'x' As FirstName From
qryStudents UNION SELECT StudentID, FullName, LastName, FirstName FROM
qryStudents ORDER BY LastName;"

When a user clicks the Sort by First Name label, the code is:

Me.lblSortbyLastName.Visible = True
Me.lblSortbyFirstName.Visible = False
Me!ComboStudents.RowSource = "SELECT -999 As StudentID, '<Add New
Student>' As SortbyFirstName, 'aaa' As FirstName, 'x' As LastName From
qryStudents2 UNION SELECT StudentID, SortbyFirstName, FirstName,
LastName FROM qryStudents2 ORDER BY FirstName;"

By default, the main form opens with the combo box sorted by the
students' first names.
So, I now have qryStudents and qryStudents2, which have the calculated
fields FullName and SortbyFirstName, respectively.

This works--but I don't know how to implement the second part of your
post. I want the 'NotesToday' column containing counts or other marks
to reside in the combo in the main form's header. The rowsources of
the combo box are now 2 different queries, neither of which contain the
'Progress' table (you typed StudentNotes--good guess). How can this
'count' code be included in the rowsource select statements or
underlying 2 queries?

Thanks,
Arnold

Reply to this Message

Anthony England - 09 Mar 2006 07:37 GMT

> Thanks Anthony for the reply. Interesting ideas.
>
[quoted text clipped - 34 lines]
> Thanks,
> Arnold

Hi Arnold
You seem to have done the first bit pretty much exactly as I was hinting at
without me needing to write down the full solution in code. I thought the
second bit would have been easier as there was an example SQL statement -
albeit with the wrong table/field names.
Now I don't yet the SQL for qryStudents, nor do I know the structure of the
Progress table, but since your 2 statements both use qryStudents, let's
change this base query so the extra column will be avialable for any
subsequent queries.
To avoid complications with the UNION bit and just to test this out, create
a new query - 'qryTest'. Assuming there is a field Progress.StudentID which
links to Students.StudentID, and there is a field in this table DateOfNote
(remember never to use reserved words such as 'Date' for a field name) then
the form of the query should be something like:

SELECT StudentID, FullName, LastName, FirstName,
(SELECT COUNT(*) FROM Progress
WHERE Students.StudentID=Progress.StudentID AND
DateOfNote=Date()) AS NotesToday
FROM Students

This assumes the DateTime field only contains a date (no time portion).
Once you can get qryTest to show you each student with a count of the notes
they have in the progress table, then you can alter qryStudents to include
this column. By the way, this sort of query, uses one SELECT statement
embedded in another and is called a subquery - just in case you need to look
up references for this technique.

Reply to this Message

Arnold - 09 Mar 2006 22:30 GMT

Anthony,

Incredible. Thanks a lot; it worked.

Reply to this Message

Arnold - 09 Mar 2006 22:31 GMT

Anthony,

Incredible. Thanks a lot; it worked.

Reply to this Message

Arnold - 09 Mar 2006 22:40 GMT

However, now there are 2 instances of <Add New Student> at the top of
the combo box, one that has 0 under the NotesToday, and the other that
has 1 under the NotesToday. Do you know why this may be? Another
alteration of the SQL needed?

The <Add New Student> list item is generated in the select statement in
my first post. The rest of the kids are listed fine.

Thanks.

Reply to this Message

Anthony England - 13 Mar 2006 15:11 GMT

> However, now there are 2 instances of <Add New Student> at the top of
> the combo box, one that has 0 under the NotesToday, and the other that
[quoted text clipped - 5 lines]
>
> Thanks.

Hi
Sorry I've been away from my desk.
If this is still not working, then post the whole SQL you currently have and
I can tweak it for you.

Reply to this Message