×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Hi all, This isn't life or death

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

RE: Hi all, This isn't life or death

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

RE: Hi all, This isn't life or death

(OP)
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

RE: Hi all, This isn't life or death

(OP)
oops!

#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

try making them all separate instead of stepping.

RE: Hi all, This isn't life or death

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

please explain further...

making who separate? & what stepping?

RE: Hi all, This isn't life or death

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.
    

RE: Hi all, This isn't life or death

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close