Hi all, This isn't life or death

[rafe]

Hi all,

This isn't life or death but it’s annoying me & this kind of thing usually indicates a bad design so...

I have a continuous form linked to a multi-table query. All query data are bound to controls in the detail section. All detail fields are disabled - this form just shows history, no updating. In the header section I have two unbound controls that I’m trying to feed to the form's Me.Filter (as per code below). Filter works dandy. However…

The annoying thing is that the last detail line shows "AutoNumber" it's a primary key field (DocID) of one tables of the query. If I disable the Me.AllowAdditions field the “AutoNumber” goes away but the unbound header controls will sometimes blank themselves. I.e. filter works just as expected, based upon the entered data in the header controls, but the controls set themselves to NULL (sometimes) after the filter.

Option Compare Database
Option Explicit

Dim FilterStr As String

Private Sub Form_Load()
    Me.SelPayee = ""
    Me.SelType = ""
    FilterStr = ""
    Me.Filter = FilterStr
    Me.FilterOn = True
End Sub

Private Sub SelPayee_AfterUpdate()
    FilterStr = ""
    If Nz(Me.SelPayee, 0) Then _
        FilterStr = FilterStr & "PayeeID = " & Me.SelPayee
    If Nz(Me.SelPayee, 0) And Nz(Me.SelType, &quot;&quot;) <> &quot;&quot; Then _
        FilterStr = FilterStr &amp; &quot; AND &quot;
    If Nz(Me.SelType, &quot;&quot;) <> &quot;&quot; Then _
        FilterStr = FilterStr &amp; &quot;Payments.Type = '&quot; &amp; Me.SelType &amp; &quot;'&quot;
    Me.Filter = FilterStr
    Me.FilterOn = True
End Sub

Private Sub SelType_AfterUpdate()
    FilterStr = &quot;&quot;
    If Nz(Me.SelPayee, 0) Then _
        FilterStr = FilterStr &amp; &quot;PayeeID = &quot; &amp; Me.SelPayee
    If Nz(Me.SelPayee, 0) And Nz(Me.SelType, &quot;&quot;) <> &quot;&quot; Then _
        FilterStr = FilterStr &amp; &quot; AND &quot;
    If Nz(Me.SelType, &quot;&quot;) <> &quot;&quot; Then _
        FilterStr = FilterStr &amp; &quot;Payments.Type = '&quot; &amp; Me.SelType &amp; &quot;'&quot;
    Me.Filter = FilterStr
    Me.FilterOn = True
End Sub

 

[Psychpt]

correct me if i'm wrong, it doesen't seem like you're testing properly.
the first if statement will run whether
A)SelPayee only has data, or
B)SelPayee And SelType has data

 

[rafe]

yes, but that's the intended effect.

i'm not limiting the select order & i'm allowing blanks in either field.

it's a string build-up of the filter clause so...

1) put in 1st part if 1st control has data (that's either 1st has data or both have data)
2) put in " AND " if both controls have data
3) put in 2nd part if 2nd control has data (that's either 1st has data or both have data)

sorry if i wasn't being clear

 

[rafe]

oops!

#3 SHOULD READ ...

3) put in 2nd part if 2nd control has data (that's either 2st has data or both have data)

 

[Psychpt]

try making them all separate instead of stepping.

 

[rafe]

sorry it's before noon & i'm a wee bit slow intil the coffee kicks in.

please explain further...

making who separate? & what stepping?

 

[Psychpt]

if you try running the filter more than once(i.e. change one of the comboboxes more than once), FilterStr
will keep adding to itself it will never clear itself unless you unload and reload the form.

you need a way of clearing it and starting over.

 

[rafe]

i've tested & the filter gets flushed & restarted ok... that's not the issue.

it's the pesky "AutoNumber" as the last line on the continuous form that I'm trying to get rid of. If I set the AllowAdditions = False then the two header controls blank themselves sometimes & that's even more annoying.