Filtering a continuous form

[sbrookebounds]

Wow, I feel like I'm loosing my mind at the moment! I have a basic continuous form in Access 07. It has the following fields:
Serial #, Part #, Model #
I want to put combo boxes in the header to filter the form. I have a combo box for each field. I named them:
SerialSearch, PartSearch, ModelSearch


The combo boxes are based on tables to automatically update when a new serial, part, model # is added. So, I got this far, so now I have a continuous form with three combo boxes in the header. I can select a value from the combo boxes, but I have no idea how get these to filter the records. I would like to be able to enter values into multiple combo boxes and have it filter, or when the combo boxes are blank, I would like it to show all records. Help please!! Thank you!!

[weekend00]

put the code in the events:

Private Sub DoFilter()
    dim fStr as string
    fstr="true "
    if trim(nz(SerialSearch,""))<>"" then fstr=fstr+ "and [serial #]='" & searialSearch & "' "
    if trim(nz(PartSearch,""))<>"" then fstr=fstr+ "and [part #]='" & PartSearch & "' "
    if trim(nz(ModelSearch,""))<>"" then fstr=fstr+ "and [Model #]='" & ModelSearch & "' "
    me.filter=fstr
    me.filteron=true
End Sub
Private Sub SerialSearch_after_update()
     DoFilter
End sub
Private Sub PartSearch_after_update()
     DoFilter
End sub
Private Sub ModellSearch_after_update()
     DoFilter
End sub

above codes works for all three fields are text. if some fields are number, please modify accordingly.

[sbrookebounds]

Should I make a filter button to put the first code on? I see that the rest are on the comboboxes that I already have, but not sure where to put the first code.

[sbrookebounds]

Oh nevermid, I see what you did. Thank you for your help I will try this out today!

[sbrookebounds]

Okay, I tried it and the Model number search works great but the Part number and Serial number searches will not work. I can send you my database if that would help. thanks

[weekend00]

sorry, I missed a space before the "and " for part # and serial #:

Private Sub DoFilter()
    dim fStr as string
    fstr="true "
    if trim(nz(SerialSearch,""))<>"" then fstr=fstr+ "and [serial #]='" & searialSearch & "' "
    if trim(nz(PartSearch,""))<>"" then fstr=fstr+ " and [part #]='" & PartSearch & "' "
    if trim(nz(ModelSearch,""))<>"" then fstr=fstr+ " and [Model #]='" & ModelSearch & "' "
    me.filter=fstr
    me.filteron=true
End Sub

[sbrookebounds]

Still not working, I have attached my database, if you get a chance could you please give it a look? Thank you in advance. It will open to the correct form.

[sbrookebounds]

I'm assuming there is no solution to this problem?

[weekend00]

Sorry, I can not open accdb file. I use only Access 2003

[tyracg]

I think you just need to add a space before the first "and" also

sorry, I missed a space before the "and " for part # and serial #:

Private Sub DoFilter()
    dim fStr as string
    fstr="true "
    if trim(nz(SerialSearch,""))<>"" then fstr=fstr+ "and [serial #]='" & searialSearch & "' "
    if trim(nz(PartSearch,""))<>"" then fstr=fstr+ " and [part #]='" & PartSearch & "' "
    if trim(nz(ModelSearch,""))<>"" then fstr=fstr+ " and [Model #]='" & ModelSearch & "' "
    me.filter=fstr
    me.filteron=true
End Sub

[okahn]

Remove ON from the last line to leave it:
me.filter=true