Multiple combo boxes subform criteria

[Marnie]

I have an unbound main form (frmAssignTraining) that has 4 combo boxes that I
want to be filters for the subform data (sfrmAssignTraining). The source
query for the subform references the fields on the main form as criteria
because I need to use wildcards. Example criteria: Like
[Forms]![frmAssignTraining]![cboDepartmentFilter] & "*". I don't think that
I can use the Link Master/Child fields because of the wildcards. Some of the
combo boxes may be blank and I allow users to type a partial data in the
combo box (ie. the beginning of a department name).

I have the following code in the AfterUpdate event of each combo box:
Forms!frmAssignTraining!sfrmAssignTraining.Form.Requery

The requery is not working. To test the requery I used an invalid field
name in the criteria and did not get an error. What did I do wrong and is
there a better way to handle this?

Thanks for your input!

 

[Klatuu]

Forms!frmAssignTraining!sfrmAssignTraining.Form.Requery

Where you have sfrmAssignTraining, it should be the name of the subform
control on frmAssignTraining, not the name of the form identified in the
subform control's source object property.

 

[Marnie]

The control name on frmAssignTraining and the subform name are the same -
sfrmAssignTraining

Forms!frmAssignTraining!sfrmAssignTraining.Form.Requery

Where you have sfrmAssignTraining, it should be the name of the subform
control on frmAssignTraining, not the name of the form identified in the
subform control's source object property.

--
Dave Hargis, Microsoft Access MVP

I have an unbound main form (frmAssignTraining) that has 4 combo boxes that I
want to be filters for the subform data (sfrmAssignTraining). The source
query for the subform references the fields on the main form as criteria
because I need to use wildcards. Example criteria: Like
[Forms]![frmAssignTraining]![cboDepartmentFilter] & "*". I don't think that
I can use the Link Master/Child fields because of the wildcards. Some of the
combo boxes may be blank and I allow users to type a partial data in the
combo box (ie. the beginning of a department name).

I have the following code in the AfterUpdate event of each combo box:
Forms!frmAssignTraining!sfrmAssignTraining.Form.Requery

The requery is not working. To test the requery I used an invalid field
name in the criteria and did not get an error. What did I do wrong and is
there a better way to handle this?

Thanks for your input!

 

[Klatuu]

The names are the same? Okay. Not a good idea, but it usually only confuses
someone trying to understand your code.

In your previous post you asked about the Link Master/Child properties.
If the main form is unbound, there is nothing to link.

Is this in the Criteria row for a field in the quer?
Like [Forms]![frmAssignTraining]![cboDepartmentFilter] & "*".

I just set up a test form/subform as you describe, and it works for me.

Here is the code in my test form:

Private Sub cboTest_AfterUpdate()
Me.subLevel.Form.Requery
End Sub

The SQL of the subform's record source:
SELECT tblLevel.LevelID, tblLevel.Level FROM tblLevel WHERE
(((tblLevel.Level) Like [forms]![form5]![cbotest] & "*"));
--
Dave Hargis, Microsoft Access MVP

The control name on frmAssignTraining and the subform name are the same -
sfrmAssignTraining

Forms!frmAssignTraining!sfrmAssignTraining.Form.Requery

Where you have sfrmAssignTraining, it should be the name of the subform
control on frmAssignTraining, not the name of the form identified in the
subform control's source object property.

--
Dave Hargis, Microsoft Access MVP

I have an unbound main form (frmAssignTraining) that has 4 combo boxes that I
want to be filters for the subform data (sfrmAssignTraining). The source
query for the subform references the fields on the main form as criteria
because I need to use wildcards. Example criteria: Like
[Forms]![frmAssignTraining]![cboDepartmentFilter] & "*". I don't think that
I can use the Link Master/Child fields because of the wildcards. Some of the
combo boxes may be blank and I allow users to type a partial data in the
combo box (ie. the beginning of a department name).

I have the following code in the AfterUpdate event of each combo box:
Forms!frmAssignTraining!sfrmAssignTraining.Form.Requery

The requery is not working. To test the requery I used an invalid field
name in the criteria and did not get an error. What did I do wrong and is
there a better way to handle this?

Thanks for your input!

 

[Marnie]

I renamed the control to something different from the subform and put the sql
statement into the record source rather than the query and it worked!

Thank you very much!

The names are the same? Okay. Not a good idea, but it usually only confuses
someone trying to understand your code.

In your previous post you asked about the Link Master/Child properties.
If the main form is unbound, there is nothing to link.

Is this in the Criteria row for a field in the quer?
Like [Forms]![frmAssignTraining]![cboDepartmentFilter] & "*".

I just set up a test form/subform as you describe, and it works for me.

Here is the code in my test form:

Private Sub cboTest_AfterUpdate()
Me.subLevel.Form.Requery
End Sub

The SQL of the subform's record source:
SELECT tblLevel.LevelID, tblLevel.Level FROM tblLevel WHERE
(((tblLevel.Level) Like [forms]![form5]![cbotest] & "*"));
--
Dave Hargis, Microsoft Access MVP

The control name on frmAssignTraining and the subform name are the same -
sfrmAssignTraining

Forms!frmAssignTraining!sfrmAssignTraining.Form.Requery

Where you have sfrmAssignTraining, it should be the name of the subform
control on frmAssignTraining, not the name of the form identified in the
subform control's source object property.

--
Dave Hargis, Microsoft Access MVP


:

I have an unbound main form (frmAssignTraining) that has 4 combo boxes that I
want to be filters for the subform data (sfrmAssignTraining). The source
query for the subform references the fields on the main form as criteria
because I need to use wildcards. Example criteria: Like
[Forms]![frmAssignTraining]![cboDepartmentFilter] & "*". I don't think that
I can use the Link Master/Child fields because of the wildcards. Some of the
combo boxes may be blank and I allow users to type a partial data in the
combo box (ie. the beginning of a department name).

I have the following code in the AfterUpdate event of each combo box:
Forms!frmAssignTraining!sfrmAssignTraining.Form.Requery

The requery is not working. To test the requery I used an invalid field
name in the criteria and did not get an error. What did I do wrong and is
there a better way to handle this?

Thanks for your input!