[AccessD] Puzzling query behavior

Asger Blond ab-mi at post3.tele.dk
Sun Jul 20 19:32:32 CDT 2008


Arthur,
Just strikes me:
Did you make sure that the queries for your combos (SitesByName_qs and
SitesByNumber_qs) SELECT ALL COLUMNS AVAILABLE ON YOUR FORM?
If not so, then doing a FBF with a condition on a column, which is not
present in the combo's rowsource, will not filter the combo.

Asger


-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] På vegne af Asger Blond
Sendt: 21. juli 2008 01:41
Til: 'Access Developers discussion and problem solving'
Emne: Re: [AccessD] Puzzling query behavior

Hi Arthur,

I'm baffled too. Just tested your code and it's working perfectly for me:
the combo is reflecting the current FBF-values, not the original values. I
then commented out your requerying-statement, which to my knowledge is
superfluous: it sure is - same result.
I suspect some code elsewhere in your app is resetting the combo's rowsource
to its design-time value.
If you want, send me the db offlist and I'll have a look.

Asger

-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] På vegne af Arthur Fuller
Sendt: 20. juli 2008 19:29
Til: Access Developers discussion and problem solving
Emne: [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






More information about the AccessD mailing list