[AccessD] Puzzling query behavior

Arthur:

My idea would be to take a different approach, which may not be helpful.
But instead of using filter by form, I'd modify the RecordSource of the form
based on the filters the user wants and Requery the form - same with the
combo boxes.   

Does this go back to your pre-hurricane problem or is this a new issue?


Rocky Smolin
Beach Access Software
858-259-4334
www.e-z-mrp.com
www.bchacc.com
 
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller
Sent: Sunday, July 20, 2008 10:29 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] Puzzling query behavior

I have some code used to do Filter By Form. It code from ADH. It makes a
copy of the form in question, adding a QBF suffx to it. You a button on your
orignal form to open the QBF form, which then lets build up a filter
expression and passes it back to your original form.

So far, so good. The problem is that there are a pair of combo boxes on the
original form which let the user move throw the records using the seach
combos.

Here's the problem. Although there is a new filter on the form, these combos
still relect the whole recordset. I have tried various things without
success. It's very puzzling.

<code>
    With Me
        If .Filter <> "" Then
            Dim strSQL1 As String, strSQL2 As String
            strSQL1 = "SELECT * FROM SitesByName_qs WHERE " & .Filter
            strSQL2 = "SELECT * FROM SitesByNumber_qs WHERE " & .Filter
            Debug.Print strSQL1
            Debug.Print strSQL2
            .SiteByName_finder_cbo.RowSource = strSQL1
            Debug.Print "Row source is now: " &
.SiteByName_finder_cbo.RowSource
            .SiteNumber_finder_cbo.RowSource = strSQL2
            Debug.Print "Row source is Now: " &
.SiteNumber_finder_cbo.RowSource
            .SiteByName_finder_cbo.Requery
            .SiteNumber_finder_cbo.Requery
        End If
        .Requery
    End With
-- in the debug I see what I expect, such as SELECT * FROM SitesByName_qs
WHERE (([SiteCity] LIKE "Toronto*")) SELECT * FROM SitesByNumber_qs WHERE
(([SiteCity] LIKE "Toronto*")) Row source is now: SELECT * FROM
SitesByName_qs WHERE (([SiteCity] LIKE
"Toronto*"))
Row source is Now: SELECT * FROM SitesByNumber_qs WHERE (([SiteCity] LIKE
"Toronto*"))
<code>

I'm baffled. I can't see what I'm doing wrong, but I inspect the two combos
rowsource they are back to their original values

Ideas?
Thanks,
Arthur
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com