Changing form's RecordSource but keeping its Filter

[inso18]

Hi All, I have a small problem:

My form functions in a way of having 2 possible underling quiries (with exactly the same fields and metadata) being changed from one to another when needed by the user. The problem is, I can't keep the filter as it was with the previous underlying query. There's some error occurring in the proccess of trying to keep the old filter and having it with the new underling query:

After this code, which changes form's underling query from one into another,

Dim strFilter as String
strFilter = Me.Form.Filter
Me.Form.RecordSouce = "Query1"
Me.Requery
Me.Form.Filter = strFilter
Me.Form.FilterOn = True

Form shows nothing at all.

Anyone is familiar with this kind of thing?
Thanks a bunch.

 

[Remou]

It is likely that the filter includes the name of the first table of query. You could use Replace to swap names.

 

[inso18]

Do you mean the first field of query? It doesn't. Filter coutains [City_Id] and the first field of the query is [Distribution_Id], which the filter doesn't contain. The first field of underling table of the query is also [Distribution_Id].

How exactly can I use Replace, and why if I apply filter in a seperate command, after changing the RecordSource it does work, and if I apply it in the same command with RecordSource change, just like in the code above, it doesn't?

Thanks.

 

[Remou]

I meant first table OR query.
For example:
[tt]Query1.City_Id=1[/tt]

I still think that there may be an error in the filter string, because when I test a similar setup, the filter works.

 

[TheAceMan1]

. . . Also when you write to the [blue]RecordSource[/blue] of a form it [blue]automatically requires[/blue]. So [purple]Me.Requery[/purple] isn't necessary (nothing to do with the problem though).

This could be easily pinned down if you post both queries!

See Ya! . . . . . .

 

[inso18]

Well the queries are a bit complicated, and I'm not sure whether you'll be into going through them, are you sure quieies are the problem?

Table: Distribution
Id_Distribution, Autonumber {Index}
Id_Volunteer, Number (Child Related to Volunteers)
Id_Customer, Number (Child Related to Customers)
Id,Delivery, Number (Child Related to Delivery)
Amount, Number
Remarks, Memo

Table: Customers
Id_Customer, Autonumber {Index}
Id_City, Number (Child Related to City)
Lname, Text
Fname, Text

Table: City
Id_City, Autonumber {Index}
CityName, Text

Table: Volunteers
Id_Volunteer, Autonumber {Index}
Full_Name, Text

Table: Delivery
Id_Delivery, Autonumber {Index}
Date_Delivery, Date/Time
Id_Type_Delivery, Number (Child Related to Type_Delivery)

Table: Type_Delivery
Id_Type_Delivery, Autonumber {Index}
TypeDeliveryName, Text

forDistributionEdit:

SELECT Distribution.Id_Distribution, Distribution.Id_Delivery, Distribution.Amount, IIf(IsNull([amount]),1,[amount]) AS Amount1, Distribution.Id_Customer, [TypeDeliveryName] & ' - ' & [Date_Delivery] AS Expr2, City.Id_City, City.CityName, Distribution.Id_Volunteer, [Lname] & ' ' & [Fname] AS Expr1, Distribution.Remarks, Type_Delivery.Id_Type_Delivery
FROM Type_Delivery INNER JOIN (Delivery INNER JOIN ((City INNER JOIN Customers ON City.Id_City = Customers.Id_City) INNER JOIN Distribution ON Customers.Id_Customer = Distribution.Id_Customer) ON Delivery.Id_Delivery = Distribution.Id_Delivery) ON Type_Delivery.Id_Type_Delivery = Delivery.Id_Type_Delivery
ORDER BY Distribution.Id_Distribution;

forDistributionEditExpanded:

SELECT T.Id_Distribution, T.Id_Delivery, T.Amount1, [TypeDeliveryName] & ' - ' & [Date_Delivery] AS Expr2, T.Id_City, T.CityName, T.Id_Customer, T.Id_Volunteer, T.Remarks, Type_Delivery.Id_Type_Delivery
FROM tblCount AS C, Type_Delivery INNER JOIN ((forDistributionEdit AS T INNER JOIN Customers ON T.Id_Customer = Customers.Id_Customer) INNER JOIN Delivery ON T.Id_Delivery = Delivery.Id_Delivery) ON Type_Delivery.Id_Type_Delivery = Delivery.Id_Type_Delivery
WHERE (((C.Count)<=[T].[Amount1]));

Here's the VBA code that changes RecordSources:

Private Sub cmdView_Click()
    Me.FilterOn = False
    Select Case Me.Form.RecordSource
    Case "forDistributionEdit"
        Me.Form.RecordSource = "forDistributionEditExpanded"
         GoTo 1
    Case "forDistributionEditExpanded"
        Me.Form.RecordSource = "forDistributionEdit"
    End Select
1
    DoEvents
    Call subApplyFilter
End Sub

Private Sub subApplyFilter()
    Dim frm As Form
    Dim ctl As Control
    Dim strFilter As String
    Set frm = Forms!DistributionEdit
    'checking all controls
    For Each ctl In frm.Controls
        'goes through filter combos
        If Left(ctl.Name, 4) = "cbo1" Then
            If Not IsNull(ctl.Value) Then
                strFilter = strFilter & " And " & frm.RecordSource & "." & _
                Right(ctl.Name, Len(ctl.Name) - 4) & " = " & ctl.Value
            End If
        'goes through filter toggle
        ElseIf ctl.Name = "tgl1Remarks" Then
            If ctl.Value = True Then _
            strFilter = strFilter & " And " & frm.RecordSource & "." & "Remarks Is Not Null"
        End If
    Next ctl
    'if exists, cleans first "And"
    If InStr(strFilter, " And") = 1 Then _
        strFilter = Right(strFilter, Len(strFilter) - 5)
    'assigns filter
    Me.Filter = strFilter
    Me.FilterOn = True
End Sub

Hope I won't be banned for this, heh

 

[Remou]

As far as I can see, you are applying the filter twice. You are including the RecordSource in the filter, it is not necessary. Why not just build the SQL to include the criteria, rather than using a filter?

Private Sub cmdView_Click()
    strWhere = ApplyFilter
    Select Case Me.Form.RecordSource
    Case "forDistributionEdit"
        strSQL="Select * From forDistributionEditExpanded Where " & strWhere
    Case "forDistributionEditExpanded"
       strSQL="Select * From forDistributionEdit Where " & strWhere
    End Select
    Me.Form.RecordSource = strWhere
End Sub

Function ApplyFilter()
    Dim frm As Form
    Dim ctl As Control
    Dim strFilter As String
    Set frm = Forms!DistributionEdit
    'checking all controls
    For Each ctl In frm.Controls
        'goes through filter combos
        If Left(ctl.Name, 4) = "cbo1" Then
            If Not IsNull(ctl.Value) Then
                strFilter = strFilter & " And " & _
                Right(ctl.Name, Len(ctl.Name) - 4) & " = " & ctl.Value
            End If
        'goes through filter toggle
        ElseIf ctl.Name = "tgl1Remarks" Then
            If ctl.Value = True Then _
            strFilter = strFilter & " And " & "Remarks Is Not Null"
        End If
    Next ctl
    'if exists, cleans first "And"
    If InStr(strFilter, " And") = 1 Then _
        strFilter = Right(strFilter, Len(strFilter) - 5)
    'assigns filter
    ApplyFilter = strFilter
End Function

Are you sure that none of the filter fields requires a delimiter?

By the way, GoTo is generally deprecated, there was a recent post on the topic.

 

[inso18]

Remou,
First, thanks.

As far as I can see, you are applying the filter twice.

Can you show where?

You are including the RecordSource in the filter, it is not necessary.

I'm including RecordSource as part of the filter string for example: forDistributionEdit.Id_Customer = 111
How can I build filter string without using the name of the RecordSource? (using just Id_Customer = 111 doesn't work)

Why not just build the SQL to include the criteria, rather than using a filter?

There might be lots of cretierias, as there are 5 combo boxes for filter conditions, there are a lot of possibilities for the criteria.

Are you sure that none of the filter fields requires a delimiter?

They all require '=', that's it.

By the way, GoTo is generally deprecated, there was a recent post on the topic

You're right. It's probably better to use conditions, more readable and block-build-like, thanks, I'll read the post you've mentioned.

 

[Remou]

Ok, I was not seeing very far in regards to applying the filter twice.

Id_Customer = 111 works for me in Access 2000 and this example

http://msdn2.microsoft.com/en-us/library/aa195885(office.11).aspx

from Microsoft does not include the table and/or query name. You will need the name of the table if you have included a field with the same name from two different tables, but in that case it is best to use an alias for one or both of the fields.

There is no reason why you should not have several criteria (

http://www2.merriam-webster.com/cgi-bin/mwdictsn?va=criteria

) in an SQL statement, if something works as a filter, it will work as a Where statement.

 

[inso18]

You are right, SQL statmenet can be made in the same way filter can be made. However there is a whole mess of quotes and double quotes for me there, as it is SQL + VBA and I do not fully understand when to use what. But that is of topic I guess.
Thanks for the help.