Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Star Lounger
    Join Date
    Jan 2003
    Location
    Manchester, Lancashire, England
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Selecting from the results of a Parameter Query (2003)

    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

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

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

  3. #3
    Star Lounger
    Join Date
    Jan 2003
    Location
    Manchester, Lancashire, England
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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?

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

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

  5. #5
    Star Lounger
    Join Date
    Jan 2003
    Location
    Manchester, Lancashire, England
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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>

    Francois

  7. #7
    Star Lounger
    Join Date
    Jan 2003
    Location
    Manchester, Lancashire, England
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  8. #8
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

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

    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

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

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

  10. #10
    Star Lounger
    Join Date
    Jan 2003
    Location
    Manchester, Lancashire, England
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  11. #11
    Star Lounger
    Join Date
    Jan 2003
    Location
    Manchester, Lancashire, England
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    & no the form isn't a sub form.

  12. #12
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    <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 ?
    Francois

  13. #13
    Star Lounger
    Join Date
    Jan 2003
    Location
    Manchester, Lancashire, England
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

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

  14. #14
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

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

  15. #15
    Star Lounger
    Join Date
    Jan 2003
    Location
    Manchester, Lancashire, England
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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?

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •