Trouble showing correct records from Combo box

[JamieW]

I have a combo box uses it's after update value to filter a subform. this works fine but my problem is:

When the value in my combo box does not have an attached record (yet) the subform returns all records with the first combo box's value.

I would like for it to return NO records.

My other problem, that is probably linked, is that even before a combo box value is chosen and no records should be filtered, the subform is already filtered for the combo box's first value.



my code: (used the access auto combo filter)

Private Sub Combo0_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[LLineNo] = '" & Me![Combo0] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub


I basically need some code that says ' when form is opened show all records '

and ' if me!Combo0 = no records, then return nothing '


Any help would be greatly appreciated

Many Thanks, JamieW

 

[Kenln]

I don't used a linked (bound) combo box. Instead I apply a filter in the AfterUpdate based on the selection. Also if the value is Null indicating that they pressed delete and enter I remove the filter

Using an 'If Then' I test for Null or in the case below I fill the value with the text I want the user to see.

If IsNull(Me.cbo_Cust.Value) Then
     Me.cbo_Cust.Value = "Select Customer"
End If

If cbo_Cust.Value = 'Select Customer'
     ctl_MainForm.Form.FilterOn = False
Else
     sfrm_SubForm_Name.Form.Filter = "[Cust] = '" + cbo_Cust.Value + "'"
     sfrm_SubForm_Name.Form.FilterOn = True
End If

sfrm_SubForm_Name is the name of the control in the main form of the sub form, NOT the actual Sub Forms name. Which btw is usually the same but not always.

There may be a better way, however it seems to work for me.