Access 03, What event handles all controls on a form

[tt1611]

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?

 

[The_Doc_Man]

The Form_AfterUpdate event only fires if you try to navigate away from the current record or use another method of forcing a record-save.

Personally, I would make this run only if I clicked a button, in which case you would have

Private Sub ButtonName_Click()

etc. etc. etc.

End Sub

That way, you ALWAYS know when this runs. It runs when you click the button.

 

[tt1611]

This is a good idea. As of right now, I have successfully acheived part of what I want by coding each individual control on the form. The problem i ran into when doing that is that i can only run one query at a time and i want to be able to run 2 or 3 queries based on selections within the combo boxes at the same time. Thanks Doc_Man for the thought though. I will give it a go and see how i get on.

I have a sample database here with me where they have managed to do what i need but no code is available for me to look at. I mention all that to say it is an event that can be run.

 

[gemma-the-husky]

i think doc man has a point

normally the relationship between a main form/sub form is defnied automatically, so that changes in the main form are automatically displayed in the sub form, without any intervention

Where the automatic relationship can't be constructed in this way, another way is to have the subform query dependent on certain values on your form, so that changing those values fires an after update event of the control, to requery the subform.

If you cant do that either, then the next way is to use a specific command button. - but bear in mind that until you click the button the data showed will not be correct.

Perhaps it would be a good idea in that case though, to also requery the subform in the main forms current event, also, as otherwise the subform will not be showing the correct data relative to the mainform

 

[tt1611]

Thanks for the thought Gemma. The option of a command button at this stage looks like my main option. I will try running the code from there and see what happens in the subform. As far as requerying goes, i alreayd have macros setup to requery the subform on got and lost focus so hopefully this should help. The users will have to know that selectiions made in the main form controls will only update when the "Search" button is clicked.

Thanks guys

 

[The_Doc_Man]

It is no big deal to force a refresh, requery, or repaint of the main form and all of its sub-forms underneath the same single command button. Just look up the stuff about working with sub-forms. All you need to do is find the form that is your sub-form and do the same thing to it (.refresh or .requery as appropriate) - if that is an issue and the number of records could change. On the other hand, a simple main-form .refresh might be enough depending on structural specifics.