Filtering records in a form Access 2003

[dawnd3]

Hi there, I have a data entry form for projects. I want to be able to filter the form so that it only shows open projects. So I went into the underlying query and took care of that. But, I want to be able to still look up closed projects on occasion. They are still showing in my lookup combo box so how do I get them to open when I select them in the look up box?

Thank you,

Dawn

 

[PHV]

So I went into the underlying query
Why not simply use the Filter and FilterOn properties ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dawnd3]

Can you tell me how to do that?

Thanks,

Dawn

 

[MajP]

lets assume your field is a yes no field and it is called 'projectClosed'. You could have procedures that shows all, shows closed, shows open. You just call them when you want to change the forms filter. Something like

public sub showClosedOnly()
me.filter = "projectClosed = FALSE"
me.filterOn = TRUE
end sub

public sub showOpenOnly()
me.filter = "projectClosed = TRUE"
me.filterOn = TRUE
end sub

Public sub showAll()
me.filter = ""
me.filterOn = FALSE
end sub

If it is based on a text field
me.Filter = "projectClosed = 'Closed'"

In your case if you are not filtering your listbox also, then unfilter when your search can not find the record.

 

[dawnd3]

Majp. Thanks for your response, I just need a little more hand holding. Where do I put this code? In the OnChange of the "projectclosed" field? Or in an event on the form? Somewhere else? And am I creating this field for the user to select "all open" or "all closed" etc? So it should be a combobox?

Thanks for your help!!! I am not totally clueless, but nearly so.

Dawn

 

[MajP]

Lots of ways you could do this. How exactly do you want it to work?
1) Do you want to leave all choices in the combo and if it can not find it in the filtered list then unfilter the records?
2) Do you want to have the ability for the user to apply a filer: All, Open Projects, Closed Projects.
3) Do you want to filter the combo lookup as well?

I was originally suggesting that you had an option group or command buttons on your form where you can filter the choices.

If it was me I would have. I would have an option group at the top of my form where I can select
[] All Projects
[] Open Projects
[] Closed Projects

When you select a choice I would filter both the form and the rowsource of the lookup combo. That way if I select "closed Projects" it would change my combo to show only closed projects and my form records only include closed projects. In that case I think I would skip the filter because even though you can filter a combo box the code is a little tricky. I would make instead three queries for my combo, and three for my form
qryComboAll, qryComboOpen, qryComboClose
qryAll, qryOpen, qryClosed

So the code would be something like.

Private Sub optFilter_AfterUpdate()
Select Case optFilter
Case 1
Me.cmboLookup.RowSource = "qryCmboClosed"
Me.RecordSource = "qryClosed"
Case 2
Me.cmboLookup.RowSource = "qryCmboOpen"
Me.RecordSource = "qryOpen"
Case 3
Me.cmboLookup.RowSource = "qryCmboAll"
Me.RecordSource = "qryAll"
End Select
End Sub

Now it is in synch. My lookup and my form have the same filter, and the user determine which they want to look at.