what event handles all controls on a form in Microsoft Access 2003?

Question

Can some please look at the below for me and advice what i am doing wrong. I am trying to run a sql query based on data selected from comboboxes on a form, results of which are generated in a subform attached to the main form

Private Sub Form_AfterUpdate()
Dim LSQL As String
Dim cmb As ComboBox
Dim txt As TextBox
Dim chk As CheckBox

For Each Control In Me.Controls
If IsNull(cmb.Value) Then
Form_ReportSubForm.RecordSource = "datamanager"
requerysubform

Else
If IsNull(txt.Value) Then
Form_ReportSubForm.RecordSource = "datamanager"
requerysubform

Else
If chk.Value = False Then
Form_ReportSubForm.RecordSource = "datamanager"
requerysubform

Else
LSQL = "SELECT * from datamanager"
LSQL = LSQL & " WHERE engineerid = cmbengid AND membername = cmbtm AND department = cmbdept"
Form_ReportSubForm.RecordSource = LSQL
    requerysubform

    End If
    End If
    End If
    Next

End Sub

When the 3 controls on the form are updated, nothing happens in the attached subform which tells me I am probably inputting this code in the wrong event. Can you please help with this?

Answer 1

If I recall corectly if the form is unbound, that is there isn't a table or query in the forms Record Source property, then the form's AfterUpdate event doesn't get fired. Therefore you should be calling the same code from each of the three combo boxes AfterUpdate event.

And if you change a controls record source you might not need the requery. I can't recall right off hand so try it without the requery first.

And if you create your sub as a Function, you can just select the controls you want it to run on and paste =MyFunction() into the event textbox, instead of having to code it in VBA.

OnwDayWhen added the above paragraph. I would never, ever do that as that isn't done very often and isn't at all obvious what is going on when you look at the VBA.

Answer 2

Your code is a bit unclear to me, but I would use a syntax like

Form_ReportSubForm.form.RecordSource = "datamanager"

if Form_ReportSubForm is your subform object in the main form.