Drop Down Box is blank

[Gismo]

Hi All,

I have a form with a drop down box and a sub form

if drop down is blank, all records must show in sub form
If Drop down has a value, sub form must be filtered

who would I accomplish this?

 

[bob fitz]

Why are you using a sub form. Why not just have your form with a combo box in the forms header. Use the combo box After Update event to apply a filter to the form.

 

[CJ_London]

not enough information to provide anything more than a basic example

have a button to activate your filter - perhaps something like this in the button click event

if isnull(cbodropdown) then subformname.form.filter="" else subformname.form.filter="somefield=" & cbodropdown
subformname.form.filteron=true

modify as required for your datatype (example assumes a number)

 

[Gismo]

Why are you using a sub form. Why not just have your form with a combo box in the forms header. Use the combo box After Update event to apply a filter to the form.

what would the filter look like?
not sure how to do this

 

[Gismo]

not enough information to provide anything more than a basic example

have a button to activate your filter - perhaps something like this in the button click event

if isnull(cbodropdown) then subformname.form.filter="" else subformname.form.filter="somefield=" & cbodropdown
subformname.form.filteron=true

modify as required for your datatype (example assumes a number)

i tried this, does not filter

#
Private Sub Registration_AfterUpdate()
If IsNull(SN) Then SparesDueSAP.Form.Filter = "" Else SparesDueSAP.Form.Filter = "SN=" & SN
SparesDueSAP.Form.FilterOn = True
End Sub#

 

[CJ_London]

presume you mean you don't get an error and SN is numeric and is the name of your combo and your field? And the combo is not bound. What is the value of SN?

 

[onur_can]

The combo box runs according to the value in the first column of the query found in the row source of the combo box. Whatever you used in the first column, you should show it as a criterion. Of course there are some exceptions. What are these exceptions? Let's say that the box that you open will filter according to the value in the 3rd column. In this case;
Name = cmbSampleBox.Column (2)
You can write.

 

[Gismo]

presume you mean you don't get an error and SN is numeric and is the name of your combo and your field? And the combo is not bound. What is the value of SN?

value of SN would be something like 8751

 

[bob fitz]

Try:

Private Sub Registration_AfterUpdate()

  If IsNull(SN) Then SparesDueSAP.Form.Filter = ""
     SparesDueSAP.Form.FilterOn = False

  Else

     SparesDueSAP.Form.Filter = "SN=" & SN
     SparesDueSAP.Form.FilterOn = True
  End If

End Sub

Or:

Private Sub Registration_AfterUpdate()

  If IsNull(SN) Then SparesDueSAP.Form.Filter = ""
     SparesDueSAP.Form.FilterOn = False
  Else

     SparesDueSAP.Form.Filter = "SN='" & SN & "'"
     SparesDueSAP.Form.FilterOn = True
  End If

End Sub

 

[bob fitz]

I have edited the code in my previous post.