Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filtering a form (A2k)

    Okay. I have a form (sfrmS1_Awards) with a list box on it (lstAwards) and a toggle button (tglViewAwards). This form is used for the processing of awards. If the award has been approved, a date is entered in a field (AWARD_SIGN_DATE). The code for my toggle button does two things, (I'd like for it to do three).

    Private Sub tglAwardsViewed_Click()
    If Me.tglAwardsViewd = True Then
    Me.tglAwardsViewed.Caption = "Now Viewing Completed Awards"
    Forms!sfrmS1_Awards!lstAwards.RowSource = (Lengthy sql statement here, I've removed it, however it works. Basically, if the award has been signed, it shows in the list box)
    Else
    Me.tglAwardsViewed.Caption = "Now Viewing Pending Awards"
    Forms!sfrmS1_Awards!lstAwards.RowSource = (Lengthy sql statement here, I've removed it, however it works. Basically, if the award has NOT been signed, it shows in the list box)
    End If
    Me!lstAwards.Requery
    End Sub


    So you can tell the things my toggle button does when it is clicked. Is it possible to set the form's filter equal to the filters I am applying to the list box? My end goal is to keep users from using the mouse wheel/pgup/down keys to cycle through records. Is what I"m doing possible or am I stuck with using the cycle property?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filtering a form (A2k)

    I'm sorry, I fail to understand the connection between "Is it possible to set the form's filter equal to the filters I am applying to the list box?" and "My end goal is to keep users from using the mouse wheel/pgup/down keys to cycle through records."

    The answer to the question is yes, if you know how to set the row source of the list box in code, you can set the Filter property of the form in code too, and set FilterOn to True.

    To disable the mouse wheel, see MouseWheelOnOff. To disable PgUp/PgDn: see ACC2000: How to Disable PAGE UP and PAGE DOWN Keys in a Form.

  3. #3
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering a form (A2k)

    I forgot to mention - I'm trying to stay away from the MouseHook.dll method. I know it's possible, and I have it as a last resort, but I'd like my users to be able to scroll through the records that are show on the list box, however they'd like. I suppose it's a VBA nightmare to make the current record on the form = the one highlighted in the listbox. I know its not too hard to make the form = the one selected on the listbox, but vice versa seems tricky
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Filtering a form (A2k)

    Jeremy

    I am thinking an alternative to design here..

    Could you have a tabbed set on your subform? One for Now Viewing Completed Awards and another Now Viewing Pending Awards the toggle button could switch the visibility on and off of the particular option you choose on the toggle button. This takes away the three way option you describe from the toggle button. My <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15> worth
    Jerry

  5. #5
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering a form (A2k)

    Okay. I now get

    <hr>Run-time error '3075'

    Syntax error. in query expression 'SELECT tbl16Awards.SSN, tbl16Awards.LNAME, tbl16Awards.FNAME, tbl16Awards.UNIT, tbl16Awards.AWARD_TYPE, tbl16Awards.AWARD_SIGNED_DATE FROM tbl16Awards WHERE ((tbl16Awards.AWARD_SIGNED_DATE) Is Not Null);'.<hr>

    When I debug, it highlights my line:
    Forms!sfrmS1_Awards.Filter = "SELECT tbl16Awards.SSN, tbl16Awards.LNAME, tbl16Awards.FNAME, tbl16Awards.UNIT, tbl16Awards.AWARD_TYPE, tbl16Awards.AWARD_SIGNED_DATE FROM tbl16Awards WHERE ((tbl16Awards.AWARD_SIGNED_DATE) Is Not Null);"
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Filtering a form (A2k)

    The current record is the one currently selected, whatever means you use to select it. As far as I know, the mouse scroll is going to affect records, not items in a listbox, so are you trying to keep the listbox selected record in sync with the current record? You can certainly do that, but it creates an awful lot of activity behind the form if the user is merrily spinning the scrollwheel up and down. [headache]
    Charlotte

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filtering a form (A2k)

    I didn't mean that you should set the Filter property of the form to an SQL statement! The Filter property represents the WHERE clause (without the word WHERE itself), so try

    Forms!sfrmS1_Awards.Filter = "tbl16Awards.AWARD_SIGNED_DATE Is Not Null"
    Forms!sfrmS1_Awards.FilterOn = True

  8. #8
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering a form (A2k)

    First, Charlotte, your question is exactly what I'm trying to do (though a secondary desire. Now it's a primary) Thank you! (Though now I need to find out how to do it...)

    <img src=/S/woops.gif border=0 alt=woops width=58 height=36> Thanks Hans <img src=/S/grin.gif border=0 alt=grin width=15 height=15> It works perfectly.
    Still oh-so-much to learn.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filtering a form (A2k)

    You can select the appropriate item in the list box in the On Current event of the form. Depending on the exact way your form works, you may need to avoid this triggering an endless loop (form selects item in list box selects record in form selects ...)

  10. #10
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering a form (A2k)

    Okay. I understand the endless loop. However, since I thanked you without testing, I've run into another stopping point. I failed to realize when I tested that I had Cycle = Current Record, instead of All Records. Now, when I go from design view to form view, I get the same Syntax error message, (not the run-time, just the syntax error.)
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filtering a form (A2k)

    If you get the same syntax error, you haven't modified the code <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

  12. #12
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering a form (A2k)

    Hans,
    The code is *definitely* modified. <img src=/S/yep.gif border=0 alt=yep width=15 height=15> <img src=/S/yep.gif border=0 alt=yep width=15 height=15> <img src=/S/yep.gif border=0 alt=yep width=15 height=15>
    Now, if I open the form, it is fine, however after I click the button, I get the run-time error, with the same message, although I've since removed superfluous parentheses. The error I get still shows those parentheses. It also highlights the line:

    <span style="background-color: #FFFF00; color: #000000; font-weight: bold">Forms!sfrmS1_Awards.FilterOn = True</span hi>

    I really hope this isn't a case of corruption. This is the first form I've started to make operational, the rest are just set up as design (no data attached yet).
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filtering a form (A2k)

    The prefix sfrm seems to indicate that this is a subform. Subforms are not part of the Forms collection, only main forms.

    Is it a subform of the form from which the code is run? If so, try

    Me.sfrmS1_Awards.Filter = "..."
    Me.sfrmS1_Awards.FilterOn = True

    You must use the name of the subform as a control on the main form. This is not necessarily the same as the name of the subform in the database window. To find the control name, open the main form in design view and click once on the subform (not twice, for that will select something in the subform). The control name is displayed in the caption of the Properties window, and in the Name property.

    Perhaps you're setting the filter elsewhere too?

  14. #14
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering a form (A2k)

    <img src=/S/yep.gif border=0 alt=yep width=15 height=15> it is a subform, however it hasn't been attached to anything. It will eventually be set on a tab, but this form is special, in that it doesn't relate to the main table at all, it's just on the same tab collection because the same office that deals with the other tabs deals with this one. The form's filter must have been set when I had the entire SQL statement in it, and until I clicked the button, it was never changed to what you suggested. All I did was clear the Filter from the properties window and it worked fine.

    Back to your additional food for thought, here is how my forms are set up (there are more, however these are the two main types as far as referencing data/relationships goes):

    frmS1 <- the main form for the office. this form's control source is my main table, tblPersonnel. There are no objects on this form that display data. This is simply a container for my tab control. The tab controls will each display a subform assigned to each specific work section within the office.

    sfrmS1_Awards <- used for tracking awards/editing/printing. The only time this refers to tblPersonnel is when adding a new record to tbl16Awards. The data is pulled from tblPersonnel, then a new record is created in tbl16Awards.

    sfrmS1_IndividualInfo <- the "meat and potatoes" of the database, this subform will have a list box and tab control on it, each tab control displaying groups of fields related to the person selected in the list box. All of these tabs will hold subforms linked to tblpersonnel.


    Any info/suggestions on design would be greatly appreciated!
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filtering a form (A2k)

    Perhaps I misunderstand your desciption, but I don't understand why frmS1 is bound to tblPersonnel if it doesn't display any data. I think I would place the contents of sfrmS1_IndividualInfo directly in frmS1 instead of in a subform.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •