Selecting from the results of a Parameter Query (2003)

**iandouglas** · 2004-09-09 12:54

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

**Francois** · 2004-09-09 13:06

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

**iandouglas** · 2004-09-09 14:11

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?

**Francois** · 2004-09-09 15:03

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.

**iandouglas** · 2004-09-09 15:40

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

**Francois** · 2004-09-09 15:52

You could change the filtering code to do a find first on the recordsetclone. Have a test and see if that works.<pre>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</pre>

**iandouglas** · 2004-09-09 15:57

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.

**MarkD** · 2004-09-09 16:22

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:

<code>Private Sub cmdFilter_Click()</code>
<code> On Error GoTo Err_Handler</code>

<code> Dim sFilter As String</code>
<code> Dim sChar As String</code>

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

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

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

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

<code>End Sub</code>

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

**Francois** · 2004-09-09 17:13

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

**iandouglas** · 2004-09-09 18:24

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

**iandouglas** · 2004-09-09 18:26

& no the form isn't a sub form.

**Francois** · 2004-09-09 18:41

<P ID="edit" class=small>(Edited by Francois on 09-Sep-04 21:41. edit to correct pre tags)</P>If you have already a filter you could use :<pre>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</pre>

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

**iandouglas** · 2004-09-09 18:47

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 .........

**Francois** · 2004-09-09 18:54

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.

**iandouglas** · 2004-09-09 18:59

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?

Thread: Selecting from the results of a Parameter Query (2003)

Thread Tools