Selecting from the results of a Parameter Query (2003)

Topic

New Reply

My clients pay me fees by standing order into several different bank accounts. When the bank statement arrives I have to check that the clients I expected to pay this month have in fact paid. The form I use is based on a parameter query so that I can enter the code for the bank account I want to deal with & I

Reply | Quote

Replies

In the query in the name field add a criteria :
Like [Enter First letter(s) of the name] & “*”

Reply | Quote

I think I have not explained myself well enough! There I am sitting looking at client records presented as a continuous form selected by a parameter query with a user input of bank account. Then I decide I only want to look at the clients thus selected but with surnames starting with say ‘P’ by clicking on a command button. It’s particularly important that its quick & simple to jump back & forth WITHIN the original selection.

So I constructed a filter which I hoped would act on the current selection and further refine it to select only clients with surnames starting with ‘P’ but without having to requery the original parameter query & reselect the bank account.. This I cannot do. Any thoughts?

Reply | Quote

Here a quick and dirty sample how to filter a continuous form by clicking on one of the buttons of the alphabet in the footer of the form.
Just open the form Namen and click on one of the buttons.
If you need help to implement it in your application, post again.

Reply | Quote

Francois
Excellent implementation! Almost perfect but because the base query is a parameter query when you click a letter it requeries the underlying parameter query & reprompts for the parameter. The very thing I’m trying to avoid! I want to say that once I’ve entered the parameter on form open thereafter select alphabetically within the parameter selection & don’t requery.

Any further thoughts?

TIA

Ian

Reply | Quote

Francois
Excellent implementation! Almost perfect but because the base query is a parameter query when you click a letter it requeries the underlying parameter query & reprompts for the parameter. The very thing I’m trying to avoid! I want to say that once I’ve entered the parameter on form open thereafter select alphabetically within the parameter selection & don’t requery.

Any further thoughts?

TIA

Ian

Reply | Quote

You could change the filtering code to do a find first on the recordsetclone. Have a test and see if that works.

Private Sub Frame14_AfterUpdate()
Dim strcriteria As String
strcriteria = "Naam Like " & Chr(34) & Chr(64 + Me.Frame14) & "*" & Chr(34)
Me.RecordsetClone.FindFirst strcriteria
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

Reply | Quote

Sadly it doesn’t. On clicking an alpha button it doesn’t requery the parameter query but neither does it do any selection. The recordset displayed does not change.

Reply | Quote

I would do this by applying filter to form. Example – simple continuous form based on Northwind Orders table, where the EmployeeID is query parameter, and CustomerID is the field to be filtered by letter entered by user:

Private Sub cmdFilter_Click()
On Error GoTo Err_Handler

Dim sFilter As String
Dim sChar As String

' Optionally leave blank to see all records (but still filtered by EmployeeID)
sFilter = vbNullString
sChar = InputBox$("Enter Letter to filter Customer ID " & _
"(leave blank for all):", "FILTER")

If Len(sChar) > 0 Then
' Must be valid character (letter):
If Asc(UCase$(sChar)) >= 65 And Asc(UCase$(sChar)) <= 90 Then
' Form already filtered by EmployeeID when opened:
sFilter = "[EmployeeID]= " & Me.EmployeeID & " AND " & _
"[CustomerID] Like " & Chr$(39) & Left$(sChar, 1) & "*" & Chr$(39)
Else
MsgBox "Invalid filter character entered.", vbExclamation, "INVALID FILTER"
End If
Else
' No character entered, display all records with EmployeeID filter only:
sFilter = "[EmployeeID]= " & Me.EmployeeID
End If

If Len(sFilter) > 0 Then
' Form FilterOn should already be true...
Me.FilterOn = True
Me.Filter = sFilter
Else
' nothing happens
End If

Exit_cmdFilter_Click:
Exit Sub
Err_Handler:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "ERROR"
Resume Exit_cmdFilter_Click

End Sub

This example uses inputbox to get filter criteria, could also use textbox, buttons, or other methods. Applying form filter did NOT cause a requery (and therefore did not invoke the parameter dialog). I wouldn’t even use the parameter query, I’d use a combo or something to apply the primary filter (the one used for parameter) & open form filtered by selected criteria.

HTH

Reply | Quote

I would do this by applying filter to form. Example – simple continuous form based on Northwind Orders table, where the EmployeeID is query parameter, and CustomerID is the field to be filtered by letter entered by user:

Private Sub cmdFilter_Click()
On Error GoTo Err_Handler

Dim sFilter As String
Dim sChar As String

' Optionally leave blank to see all records (but still filtered by EmployeeID)
sFilter = vbNullString
sChar = InputBox$("Enter Letter to filter Customer ID " & _
"(leave blank for all):", "FILTER")

If Len(sChar) > 0 Then
' Must be valid character (letter):
If Asc(UCase$(sChar)) >= 65 And Asc(UCase$(sChar)) <= 90 Then
' Form already filtered by EmployeeID when opened:
sFilter = "[EmployeeID]= " & Me.EmployeeID & " AND " & _
"[CustomerID] Like " & Chr$(39) & Left$(sChar, 1) & "*" & Chr$(39)
Else
MsgBox "Invalid filter character entered.", vbExclamation, "INVALID FILTER"
End If
Else
' No character entered, display all records with EmployeeID filter only:
sFilter = "[EmployeeID]= " & Me.EmployeeID
End If

If Len(sFilter) > 0 Then
' Form FilterOn should already be true...
Me.FilterOn = True
Me.Filter = sFilter
Else
' nothing happens
End If

Exit_cmdFilter_Click:
Exit Sub
Err_Handler:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "ERROR"
Resume Exit_cmdFilter_Click

End Sub

This example uses inputbox to get filter criteria, could also use textbox, buttons, or other methods. Applying form filter did NOT cause a requery (and therefore did not invoke the parameter dialog). I wouldn’t even use the parameter query, I’d use a combo or something to apply the primary filter (the one used for parameter) & open form filtered by selected criteria.

HTH

Reply | Quote

Is your continuous form a subform ? Then you have to set the filter on the form in the subform control.

Reply | Quote

Combining Francois’s ideas & interface which I like with Marks suggestion I tried to mod Francois’s code to : –

Private Sub Frame14_AfterUpdate()
Dim strcriteria As String
strcriteria = “[OurBank]= ” & Me.OurBank & ” AND ” & _
“Surname Like ” & Chr(34) & Chr(64 + Me.Frame14) & “*” & Chr(34)

Me.Filter = strcriteria
Me.FilterOn = True
End Sub

But it didn’t do what I expected! The parameter query was still requeried. Can anyone help to suggest why & how to tweak it?

TIA

Reply | Quote

(Edited by Francois on 09-Sep-04 21:41. edit to correct pre tags)

If you have already a filter you could use :

Private Sub Frame14_AfterUpdate()
Dim strcriteria As String
If Me.Filter = "" Then
   strcriteria = Me.Filter & " AND Surname Like " & Chr(34) & Chr(64 + Me.Frame14) & "*" & Chr(34)
Else
   strcriteria = "Surname Like " & Chr(34) & Chr(64 + Me.Frame14) & "*" & Chr(34)
End If
Me.Filter = strcriteria
Me.FilterOn = True
End Sub

Where is Me.OurBank coming from ? from the record source or from an unbound textbox in the form header or footer ?

Reply | Quote

OurBank is the field in the original query that the parameter prompts for. It’s the field I’m trying to say – hold that constant & now filter the surnames for those that start with ………

Reply | Quote

That’s the problem. You can’t do a filtering in such a manner. Compare the bank in the field with the bank in the field. That have no sense.
The bank should already be filtered in the query.
So returning to my first code without any bank comparing should work.

Reply | Quote

But it doesn’t. Your original code works perfectly EXCEPT that it precipitates a requery of the parameter query which I don’t want to happen! Any other thoughts?

Reply | Quote

I don’t understand why setting a filter would trigger a requery.
Can you attach a stripped version of your db ? (without sensitive data)

Reply | Quote

I don’t understand why setting a filter would trigger a requery.
Can you attach a stripped version of your db ? (without sensitive data)

Reply | Quote

Ok, I was working on my pc with access 2000. I had no problem.
Just tried the db I send you on a pc with Access 2003 and I have the same problem, asking for the parameter.
This is an Access 2003 problem. I’ll try to find a solution.

Reply | Quote

I’ve just spent a frustrating 30 mins trying to strip the db down under the 100K limited zipped & failed – 209K is as far as it will go. I’m pleased I don’t have to

Reply | Quote

Sorry for having waste your time with the stripping of the db.
I have a workaround.
I build a form frmParameter with one textbox asking the parameter and a button to open the form.
In the query I set a criteria to >[forms]![frmparameter]![text0]
With this system the parameter is not asked every time you select an alpha character.
Do you think you can modify your db with this workaround ?
In attachment a sample db, just open the form frmParameter

Reply | Quote

Francois

Yes thanks a lot it, now works just as I wanted with the really neat alphabetic selector you provided. Thanks once again!

Ian

Reply | Quote

Francois

Yes thanks a lot it, now works just as I wanted with the really neat alphabetic selector you provided. Thanks once again!

Ian

Reply | Quote

Sorry for having waste your time with the stripping of the db.
I have a workaround.
I build a form frmParameter with one textbox asking the parameter and a button to open the form.
In the query I set a criteria to >[forms]![frmparameter]![text0]
With this system the parameter is not asked every time you select an alpha character.
Do you think you can modify your db with this workaround ?
In attachment a sample db, just open the form frmParameter

Reply | Quote

Must you actually filter the form, or would a means of navigating to the first item in the “Ps”, for instance” be enough? I’m attaching a sample that allows you to do that. It was built in 2002 and is in 2000 format, but it should run in 2003 if you replace the reference to the Access 10.0 object library.

Reply | Quote

Charlotte

Thanks for your suggestion. With Francois’s help I got his elegant alphabetic selector to work with the advice he gave above.

Ian

Reply | Quote

Charlotte

Thanks for your suggestion. With Francois’s help I got his elegant alphabetic selector to work with the advice he gave above.

Ian

Reply | Quote

Must you actually filter the form, or would a means of navigating to the first item in the “Ps”, for instance” be enough? I’m attaching a sample that allows you to do that. It was built in 2002 and is in 2000 format, but it should run in 2003 if you replace the reference to the Access 10.0 object library.

Reply | Quote

I’ve just spent a frustrating 30 mins trying to strip the db down under the 100K limited zipped & failed – 209K is as far as it will go. I’m pleased I don’t have to

Reply | Quote

Ok, I was working on my pc with access 2000. I had no problem.
Just tried the db I send you on a pc with Access 2003 and I have the same problem, asking for the parameter.
This is an Access 2003 problem. I’ll try to find a solution.

Reply | Quote

But it doesn’t. Your original code works perfectly EXCEPT that it precipitates a requery of the parameter query which I don’t want to happen! Any other thoughts?

Reply | Quote

That’s the problem. You can’t do a filtering in such a manner. Compare the bank in the field with the bank in the field. That have no sense.
The bank should already be filtered in the query.
So returning to my first code without any bank comparing should work.

Reply | Quote

OurBank is the field in the original query that the parameter prompts for. It’s the field I’m trying to say – hold that constant & now filter the surnames for those that start with ………

Reply | Quote

(Edited by Francois on 09-Sep-04 21:41. edit to correct pre tags)

If you have already a filter you could use :

Private Sub Frame14_AfterUpdate()
Dim strcriteria As String
If Me.Filter = "" Then
   strcriteria = Me.Filter & " AND Surname Like " & Chr(34) & Chr(64 + Me.Frame14) & "*" & Chr(34)
Else
   strcriteria = "Surname Like " & Chr(34) & Chr(64 + Me.Frame14) & "*" & Chr(34)
End If
Me.Filter = strcriteria
Me.FilterOn = True
End Sub

Where is Me.OurBank coming from ? from the record source or from an unbound textbox in the form header or footer ?

Reply | Quote

Combining Francois’s ideas & interface which I like with Marks suggestion I tried to mod Francois’s code to : –

Private Sub Frame14_AfterUpdate()
Dim strcriteria As String
strcriteria = “[OurBank]= ” & Me.OurBank & ” AND ” & _
“Surname Like ” & Chr(34) & Chr(64 + Me.Frame14) & “*” & Chr(34)

Me.Filter = strcriteria
Me.FilterOn = True
End Sub

But it didn’t do what I expected! The parameter query was still requeried. Can anyone help to suggest why & how to tweak it?

TIA

Reply | Quote

& no the form isn’t a sub form.

Reply | Quote

& no the form isn’t a sub form.

Reply | Quote

Is your continuous form a subform ? Then you have to set the filter on the form in the subform control.

Reply | Quote

Sadly it doesn’t. On clicking an alpha button it doesn’t requery the parameter query but neither does it do any selection. The recordset displayed does not change.

Reply | Quote

You could change the filtering code to do a find first on the recordsetclone. Have a test and see if that works.

Private Sub Frame14_AfterUpdate()
Dim strcriteria As String
strcriteria = "Naam Like " & Chr(34) & Chr(64 + Me.Frame14) & "*" & Chr(34)
Me.RecordsetClone.FindFirst strcriteria
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

Reply | Quote

Here a quick and dirty sample how to filter a continuous form by clicking on one of the buttons of the alphabet in the footer of the form.
Just open the form Namen and click on one of the buttons.
If you need help to implement it in your application, post again.

Reply | Quote

I think I have not explained myself well enough! There I am sitting looking at client records presented as a continuous form selected by a parameter query with a user input of bank account. Then I decide I only want to look at the clients thus selected but with surnames starting with say ‘P’ by clicking on a command button. It’s particularly important that its quick & simple to jump back & forth WITHIN the original selection.

So I constructed a filter which I hoped would act on the current selection and further refine it to select only clients with surnames starting with ‘P’ but without having to requery the original parameter query & reselect the bank account.. This I cannot do. Any thoughts?

Reply | Quote

In the query in the name field add a criteria :
Like [Enter First letter(s) of the name] & “*”

Reply | Quote