Filter records based on user input 1

[annie52]

My main form displays requisition information and has several linked subforms in a tab control. I want an unbound text box on the main form to allow the user to enter the requisition number they need to view. Then, a command button to click on that sets the filter to that requisition number. A second command button releases the filter (i.e., allows the user to view all records).

I know this isn't too difficult because I never had any formal Access training and I managed to create a similar form several years ago. I haven't used Access for quite some time and just cannot remember how I did this before. I hope some kind soul will get me jump started again.

 

[TheAceMan1]

How are ya annie52 . . .

[blue]To enable the filter:
   Me.Filter = "[RequisitionNumberFieldName] = " & Me![UnboundTextboxName]
   Me.FilterOn = True

To disable the filter:
   Me.FilterOn = False
   Me.Filter = Null[/blue]

[blue]Your Thoughts? . . .[/blue]

BTW ... [blue]Welcome to Tek-Tips[/blue]. Be sure to have a look at one of the links at the bottom of my post.

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[annie52]

Sorry for the delay - I got pulled away for awhile.

Please clarify for me. I used your code in the On Click event of my command button but get Run-time error 2001 (You canceled the previous operation). Am I putting it in the wrong place?

 

[TheAceMan1]

annie52 . . .

I gave you the code for the [blue]On Click[/blue] event of both buttons!

The [blue]Filter[/blue] button should be ([blue]you![/blue] substitute proper names in [purple]purple[/purple]):

[blue]   Me.Filter = "[[purple][b]RequisitionNumberFieldName[/b][/purple]] = " & Me![[purple][b]UnboundTextboxName[/b][/purple]]
   Me.FilterOn = True[/blue]

The [blue]Release[/blue] button should be:

[blue]   Me.FilterOn = False
   Me.Filter = Null[/blue]

[blue]Your Thoughts? . . .[/blue]




See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[annie52]

Hi TheAceMan1. I know you sent me code for both buttons and I used it. (Thank you.) However, my problem is still the same. Here's what behind my filtering button:
=========================
Private Sub cmdEnterJD_Click()
On Error GoTo Err_cmdEnterJD_Click

Me.Filter = "[JD] = " & Me![EnterJD]
Me.FilterOn = True

Exit_cmdEnterJD_Click:
Exit Sub

Err_cmdEnterJD_Click:
MsgBox "Error #" & Err.Number & " " & Err.Description
Resume Exit_cmdEnterJD_Click

End Sub
=====================
When I step through the code, it errors out at:
Me.Filter = "[JD] = " & Me![EnterJD]

Note: JD is a field name on my main form and EnterJD is the name of my unbound text field which is also on the main form.
======================
Error 94 - Invalid Use of Null displays when I attempt to turn off the filter:

Private Sub cmdAllReqns_Click()
On Error GoTo Err_cmdAllReqns_Click

Me.FilterOn = False
Me.Filter = Null

Exit_cmdAllReqns_Click:
Exit Sub

Err_cmdAllReqns_Click:
MsgBox "Error #" & Err.Number & " " & Err.Description
Resume Exit_cmdAllReqns_Click

End Sub
======================
I appreciate your feedback.

 

[PHV]

it errors out
Which error message ?

Error 94 - Invalid Use of Null
Replace this:
Me.Filter = Null
with this:
Me.Filter = ""

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

 

[annie52]

Hi, PHV. I mentioned the error in my 4/9/09 post:
Run-time error 2001 (You canceled the previous operation).

I did replace the Null with "" a few minutes ago. Of course, you're right, it resolved that button's problem.

 

[PHV]

Is JD defined as numeric in the form's RecordSource ?
If not, I'd try this:

Private Sub cmdEnterJD_Click()
Me.FilterOn = False
Me.Filter = "[JD]='" & Me![EnterJD] & "'"
Me.FilterOn = True
End Sub

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

 

[annie52]

PHV - You're my hero today! It works great now. Thanks!

 

[TheAceMan1]

annie52 . . .

Sorry for getting back a little to late . At least you have resolution ... and thank you [blue]PHV![/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]