Hi all, This isn't life or death
Hi all, This isn't life or death
(OP)
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, "") <> "" Then _
FilterStr = FilterStr & " AND "
If Nz(Me.SelType, "") <> "" Then _
FilterStr = FilterStr & "Payments.Type = '" & Me.SelType & "'"
Me.Filter = FilterStr
Me.FilterOn = True
End Sub
Private Sub SelType_AfterUpdate()
FilterStr = ""
If Nz(Me.SelPayee, 0) Then _
FilterStr = FilterStr & "PayeeID = " & Me.SelPayee
If Nz(Me.SelPayee, 0) And Nz(Me.SelType, "") <> "" Then _
FilterStr = FilterStr & " AND "
If Nz(Me.SelType, "") <> "" Then _
FilterStr = FilterStr & "Payments.Type = '" & Me.SelType & "'"
Me.Filter = FilterStr
Me.FilterOn = True
End Sub
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, "") <> "" Then _
FilterStr = FilterStr & " AND "
If Nz(Me.SelType, "") <> "" Then _
FilterStr = FilterStr & "Payments.Type = '" & Me.SelType & "'"
Me.Filter = FilterStr
Me.FilterOn = True
End Sub
Private Sub SelType_AfterUpdate()
FilterStr = ""
If Nz(Me.SelPayee, 0) Then _
FilterStr = FilterStr & "PayeeID = " & Me.SelPayee
If Nz(Me.SelPayee, 0) And Nz(Me.SelType, "") <> "" Then _
FilterStr = FilterStr & " AND "
If Nz(Me.SelType, "") <> "" Then _
FilterStr = FilterStr & "Payments.Type = '" & Me.SelType & "'"
Me.Filter = FilterStr
Me.FilterOn = True
End Sub
Talk To Other Members
RE: Hi all, This isn't life or death
the first if statement will run whether
A)SelPayee only has data, or
B)SelPayee And SelType has data
RE: Hi all, This isn't life or death
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
RE: Hi all, This isn't life or death
#3 SHOULD READ ...
3) put in 2nd part if 2nd control has data (that's either 2st has data or both have data)
RE: Hi all, This isn't life or death
RE: Hi all, This isn't life or death
please explain further...
making who separate? & what stepping?
RE: Hi all, This isn't life or death
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.
RE: Hi all, This isn't life or death
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.