Linked Combo Box Not Updating

[unhinged]

G'day,

I have a student details form, fmvqStudent (based on qStudentDetails),
with a tab control. The tab control for attendance shows two combo
boxes: cbxCurrentClassID (Number) and cbxShowPeriod (Date). The former
is bound to qStudentDetails.CurrentClassID.

There is also a subform, sfmtAttendance, which is based on the
tAttendances table and linked by qStudentDetails.StudentID =
tAttendance.StudentID and qStudentDetails.CurrentClassID =
tAttendance.ClassID.

I am trying to have a user select a class from one combo box and have
every related attendance record show on the subform. The footer of the
subform has calculated controls showing the total hours attended, total
hours required and the percentage of attendance. The attendance data is
recorded fortnightly for some classes and monthly for others, so I want
to be able to filter the subform to only display those records matching
the value in cbxShowPeriod.

That part, after much experimentation, now works. Yay me.

What is not working is the value list attaching to cbxShowPeriod. When
I first open the form fmvqStudent and navigate to the Attendance tab, I
have a class displayed in cbxCurrentClassID and the values in
cbxShowPeriod are correct. But I do not see any of the related records
until I click on a command button, cmdShowAllPeriods, which has the
following code:

Me.sfmtAttendance.Form.FilterOn = False
[cbxShowPeriod] = Null
Me.sfmtAttendance.Form.Requery


I'm thinking there's an OnLoad event that needs to happen to show the
related data, but any suggestions on this point are welcome.

However, what I am really worried about is that when I select a
different class from cbxCurrentClassID, the value list populating
cbxShowPeriod becomes a NULL list and the subform recordset does not
update. But if I close the form and re-open it, the cbxShowPeriod value
list is correctly populated with the values from the new value in
cbxCurrentClassID.

Here's the AfterUpdate event code from cbxCurrentClassID:

Me.sfmtAttendance.Form.Requery
[cbxShowPeriod] = Null
Me.cbxShowPeriod.RowSource = "SELECT DISTINCT
tAttendance.WeekCommencingDate FROM qStudentDetails INNER JOIN
tAttendance ON (qStudentDetails.CurrentClassID = tAttendance.ClassID)
AND (qStudentDetails.StudentID = tAttendance.StudentID) WHERE
(((tAttendance.ClassID)=[cbxCurrentClassID]));"


The AfterUpdate event code for cbxShowPeriod is:

Dim strInput As Variant
Dim strFilter As String
' store value selected by user in combo box
strInput = Nz([cbxShowPeriod], 0)
' Build criteria string
strFilter = BuildCriteria("WeekCommencingDate", dbDate, strInput)
Me.sfmtAttendance.Form.Filter = strFilter
Me.sfmtAttendance.Form.FilterOn = True


I'm not sure what else is needed. I know that the system basically
works because of the behaviour described above, but I am really
struggling to get the instant update part working.

Thanks in Advance,

Daniel.

 

[pietlinden]

maybe these will help...
http://www.mvps.org/access/forms/frm0025.htm
http://www.mvps.org/access/forms/frm0031.htm

probably the way you are referencing the controls is wrong, but then
that's understandable given the complexity of the subform/tabbbed
control stuff. I can't do it without referring to the refs above
because I can never get the syntax right.

 

[unhinged]

Thanks, but having tried all of the variations for referencing controls
as per the suggested instructions, I am still encountering the lack of
update issue.

I am puzzled, because the SQL in the after update event for the
cbxCurrentClassID box is the same as the SQL in the RowSource property
of the cbxShowPeriod box.

If the query is the same, why does the list show the correct values
when the form is opened but not repopulate when the value in the combo
box updates? If it's an update issue, how is my code above incorrect?
Is it something to do with setting the cbxShowPeriod box to NULL?

 

[unhinged]

Update:

I have changed the cbxShowPeriod control from a combo box to a list
control so that multiple dates can be selected. A bit of learning was
involved to build the filter string from multiple values, but the
filtering of the subform is working just fine.

HOWEVER:

The values in the list box (renamed to lboxShowPeriod) are still not
updating when changing the value in cbxCurrentClassID.

The observed behaviour is that the list box contains the correct values
when the form is opened and I tab to the Attendance page.
cbxCurrentClassID shows HAIR101 (from a list of HAIR101, HAIR102 and
HAIR103) and lboxShowPeriod displays the five dates for which I have
entered attendance data. Changing the value in cbxCurrentClassID from
HAIR101 to HAIR102 results in a zero-length list of dates in
lboxShowPeriod. Changing the value in cbxCurrentClassID back to HAIR101
populates lboxShowPeriod with the appropriate values.

Changing cbxCurrentClassID to HAIR102 and entering design mode, then
display mode, populates lboxShowPeriod with the one date for which I
have entered attendance data. Changing cbxCurrentClassID to HAIR101
results in a zero-length list in lboxShowPeriod.


Please, if anyone can understand what is happening here then reply to
this post. I'm losing even more of my hair trying to solve this one.

Cheers,

Daniel.