Continuous Forms--Combo Box with Variable Row Source

Here's a write-up I did for our developer wiki. I don't know if the
whole rest of the world has already figured out how to do this, but I
hadn't ever seen it implemented, and had spent a lot of time trying to
figure it out, over the years. It finally dawned on me a couple of
weeks ago how to do this.

A couple of notes:
1) This is written for a different audience than CDMA; it's written for
independent consultants who are doing development work using the
database my NGO produces. This means the voice may sound funny in this
context ("We" instead of "I") and there may be some times where I
assume some context based on our application, but this seems to be
fully usable in just about any context--I hope so, anyway.
2) Watch for line wrapping in the code.
3) I'd love to hear any feedback about this.

Jeremy
--
Jeremy Wallace
Fund for the City of New York
metrix dot fcny dot org
====
For a long time we've struggled with figuring out how to have a combo
box's row source be different for different records in a continuous
form. We think we've figured out a decent way to approach this. It
doesn't work for a datasheet view, but you can get pretty close to
mimicing this in a continuous form, so that shouldn't be too much of a
hitch.

We built this form for a client with specific needs, but it's quite
generalizable. In this case, the client has a program in which
volunteers mentor students in reading. Volunteers may team up to work
with students, alternating days. When the client takes attendance, she
wants to be able to select which student is there and, based on which
student is selected for this record, we'll fill the rowsource of a
combo box with all of the volunteers who have signed up to work with
that student. It's a pretty simple need, and one we see quite often.
But up until now, we've not seen a good solution to this problem (This
is not to say no one's come up with a solution before, just that we've
not seen any).

Here's how we did it.

The basic plan is to use two fields in place of the one combo box for
the volunteer txtVolunteer and cboVolunteer. txtVolunteer will be
disabled, and will hold a formula to display what is chosen from
cboVolunteer. cboVolunteer will only be partially visible, and will
fire its dropdown method upon gaining focus.

The form (which will be used as a subform, but can be built
independently) has five fields: Date, Student, Student Attendance,
Volunteer, and Volunteer Attendance. Actually, there are two other
fields that the user doesn't really know about. One of them is the
AttendanceID field, the PKey of the table behind most of the data on
the form. The other is that second Volunteer control, txtVolunteer, and
that's the key to how this works.

[Image here: continuous form set up to look a lot like a datasheet.
Five controls, two of which are combo boxes]

This looks fairly standard. But as soon as you click in the
cboVolunteer, you can see that something is not quite as you would
expect it to be:

[Image here: that same form with one of the combo boxes showing the
list. The list is flush left with the drop-down arrow, not the usual
flush right.]

Why is the combo box opening off to the right side like that? Because
it is so narrow, and the control displaying not cboVolunteer, but
txtVolunteer. Have a look at the form in Design View:

[Image here: that same form in design view. you can see the combo box
in question is built of two controls, a text box and a combo box that's
so narrow all you can see is the drop-down arrow.]

You can see the cboVolunteer highlighted, and the start of the formula
in txtVolunteer. Here's the full formula:

=IIf(IsNull([cboVolunteer]),"",DLookUp("SortName","tblContacts","ContactID
= " & [cboVolunteer]))

Remember to make this control Disabled and Locked.

Now all we need is two small pieces of code to make this work. This
first one sets the RowSource of cboVolunteer, when that control gets
the focus. It also immediately forces the combo to display its list fo
values.

Private Sub cboVolunteer_GotFocus()
Me!cboVolunteer.RowSource = "SELECT tblContacts.ContactID,
tblContacts.SortName" _
& " FROM tblCustom_EW_StudentVolunteerConnection " _
& " INNER JOIN tblContacts ON
tblCustom_EW_StudentVolunteerConnection.VolunteerID =
tblContacts.ContactID" _
& " WHERE " _
& " tblCustom_EW_StudentVolunteerConnection.StudentID = " &
Me.Student & " AND " _
& " tblCustom_EW_StudentVolunteerConnection.StartDate <= Date() AND
" _
& " tblCustom_EW_StudentVolunteerConnection.EndDate >= Date();"
Me.cboVolunteer.Dropdown
End Sub

This next one just move focus to the next control after a value is
selected in cboVolunteer.

Private Sub cboVolunteer_AfterUpdate()
Me.VolunteerAttendance.SetFocus
End Sub

Because txtVolunteer never receives focus (it's disabled), tabbing out
of the Student Attendance field brings focus straight to the combo box.
As soon as this happens, the RowSource for the combo is set to just
those volunteers who are associated with this student, and the lsit of
volunteers is dropped down. At this point the user can use arrow keys,
type a name, or select a name with the mouse. As soon as the mouse is
clicked or the user hits Enter or Tab, focus moves to the Volunteer
Attendance field.

Please let us know if you implement this, and give us any feedback
you've got, so we can tweak anything that needs to be tweaked in this
method.