use OnClick of combobox to refresh a subform

  • Thread starter Thread starter Sheryl
  • Start date Start date
S

Sheryl

My form has an unbound combobox 'category', and a subform
based on a query which includes a field 'category'. The
query's Criteria field forces the subform to display only
records whose category matches the value in the combox.

I am trying to write an OnClick subroutine for the combo
box so that changing the category refreshes the subform.
But I don't seem to identify the subform properly, and the
program aborts with an error. The form is 'frmRunId', the
combo box is 'CategoryBox', the subform is 'sfrmParms',
and the query is 'qryParms'. What is the correct syntax?

I have tried the following (in CategoryBox_Click); program
aborts on 2nd line:

Dim parms as Control
Set parms = Forms!sfrmParms
parms.Refresh
 
Sheryl said:
My form has an unbound combobox 'category', and a subform
based on a query which includes a field 'category'. The
query's Criteria field forces the subform to display only
records whose category matches the value in the combox.

I am trying to write an OnClick subroutine for the combo
box so that changing the category refreshes the subform.
But I don't seem to identify the subform properly, and the
program aborts with an error. The form is 'frmRunId', the
combo box is 'CategoryBox', the subform is 'sfrmParms',
and the query is 'qryParms'. What is the correct syntax?

I have tried the following (in CategoryBox_Click); program
aborts on 2nd line:

Dim parms as Control
Set parms = Forms!sfrmParms
parms.Refresh
Click to expand...

You have to work through the subform control on the main form, because
the subform itself isn't a member of the Forms collection. Also, it
would be the Requery method you'd call, not Refresh. Now, the subform
control that displays the form object named "sfrmParms" may or may not
also be named "sfrmParms". If it is, you could could write code like
this:

Private Sub CategryBox_Click()

Me!sfrmParms.Requery

End Sub

If the subform control isn't named the same as the form, you must
substitute the name of the control in the above code.
 
You might look at http://www.mvps.org/access/forms/frm0031.htm "Referring to
form and subform controls ..." by Keri Hardwick. While you're there, look
poke around the site. It is probably the most useful site on the planet for
things Access.

Also, it would seem a natural to requery the subform from the AfterUpdate
event of the combobox.

HTH
 
Hi,

I ran into an identifcal problem and the requery solution in conjunstion with the SetFocus command was not working for me. It seems the there was an intermediate step missing so I looked through the MS Access help section and found the following statement in the SetFocus section which provided the missing link.

"You can move the focus only to a visible control or form. A form and controls on a form aren't visible until the form's Load event has finished. Therefore, if you use the SetFocus method in a form's Load event to move the focus to that form, you must use the Repaint method before the SetFocus method."

So try this in your VB statement

Private Sub RunQuery_Click()

Me.Repaint
Me.SubForm.Requery


End Sub

Hope this helps.
 
Back
Top