Unanswered: combo subform filter that selects all records
hello everyone, I have a subform question:
I used the wizard to add a combo button to my form that will filter the results shown in my subform. Here's the code (I changed the '=' sign to 'Like'):
Private Sub Combo39_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ItemNum] Like '" & Me![Combo39] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
My question is, how do I get the subform to show all the records now? I used the idea of adding an extra row to my 'combo39' row source. Here's the SQL:
SELECT dbo_vwItems.ItemNum, dbo_vwItems.Description FROM dbo_vwItems UNION select distinct null, "ALL" FROM dbo_vwItems
ORDER BY dbo_vwItems.ItemNum;
I wanted to add a line to the code to say if the ItemNum choice is 'ALL', then show all the records, but I couldn't work out how to do it. After trying out lots of combinations of single quotes, double quotes, %, *, inside and outside brackets etc I'm stuck. I'm sure it must be possible somehow but don't know how...
By the way, this form looks up records from a linked SQL database I don't know if that makes a difference.