Combo box subform, don't always filter

[HelenJ]

I have a combo box that use to filter the records that are show on the sub
form, however I would like to be able to show all the records.

I have tried the following for the data source of the subform, but it
doesn't display any records when I leave the box blank.

SELECT Products.*
FROM Products

WHERE (((Products.Function)=IIf([Forms]![SuppliersFm]![functionCbo] Is
Null,(Products.Function) Like "*",[Forms]![SuppliersFm]![functionCbo]))

ORDER BY Products.Function, Products.RefNo;

does anyone know a way of doing this or can see a flaw in my code?

Many thanks

 

[Allen Browne]

Presumably functionCbo is an *unbound* combo box on the main form, and you
want to filter the subform to show only matching records if it has a value,
or to show all records if the combo is null.

Use the AfterUpdate event procedure of the combo to set the RecordSource of
the subform, like this:

Private Sub functionCbo_AfterUpdate()
Dim strWhere As String

If Not IsNull(Me.functionCbo) Then
strWhere = "WHERE (Products.[Function] = " & Me.functionCbo & ")"
End If
Me.[Sub1].Form.RecordSource = "SELECT Products.* FROM Products " &
strWhere & " ORDER BY Products.[Function], Products.RefNo;"
End Sub

Notes:
====
1. If Function is a Text field (not a Number field), you need extra quote
marks:
strWhere = "WHERE (Products.Function = """ & Me.functionCbo & """)"
Explanation:
http://allenbrowne.com/casu-17.html

2. Function is a reserved word, so it could mal-function.
The square brackets may help (as above.)
For a list of words to avoid, see:
http://allenbrowne.com/AppIssueBadWord.html#F

 

[HelenJ]

Thanks Allen that works brilliantly and I've also taken note of your comments
on my naming and adjusted that as well.

Helen

Presumably functionCbo is an *unbound* combo box on the main form, and you
want to filter the subform to show only matching records if it has a value,
or to show all records if the combo is null.

Use the AfterUpdate event procedure of the combo to set the RecordSource of
the subform, like this:

Private Sub functionCbo_AfterUpdate()
Dim strWhere As String

If Not IsNull(Me.functionCbo) Then
strWhere = "WHERE (Products.[Function] = " & Me.functionCbo & ")"
End If
Me.[Sub1].Form.RecordSource = "SELECT Products.* FROM Products " &
strWhere & " ORDER BY Products.[Function], Products.RefNo;"
End Sub

Notes:
====
1. If Function is a Text field (not a Number field), you need extra quote
marks:
strWhere = "WHERE (Products.Function = """ & Me.functionCbo & """)"
Explanation:
http://allenbrowne.com/casu-17.html

2. Function is a reserved word, so it could mal-function.
The square brackets may help (as above.)
For a list of words to avoid, see:
http://allenbrowne.com/AppIssueBadWord.html#F

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a combo box that use to filter the records that are show on the sub
form, however I would like to be able to show all the records.

I have tried the following for the data source of the subform, but it
doesn't display any records when I leave the box blank.

SELECT Products.*
FROM Products

WHERE (((Products.Function)=IIf([Forms]![SuppliersFm]![functionCbo] Is
Null,(Products.Function) Like "*",[Forms]![SuppliersFm]![functionCbo]))

ORDER BY Products.Function, Products.RefNo;

does anyone know a way of doing this or can see a flaw in my code?