Filtering a form (A2k)

Topic

New Reply

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?

Reply | Quote

Replies

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.

Reply | Quote

Okay. I now get

[indent]

---

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);’.

---

[/indent]

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);”

Reply | Quote

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

Reply | Quote

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…)

Thanks Hans It works perfectly.
Still oh-so-much to learn.

Reply | Quote

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

Reply | Quote

If you get the same syntax error, you haven’t modified the code

Reply | Quote

Hans,
The code is *definitely* modified.
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:

Forms!sfrmS1_Awards.FilterOn = True

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

Reply | Quote

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?

Reply | Quote

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!

Reply | Quote

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.

Reply | Quote

Maybe it doesn’t have to be? I’m guessing that’s the case…I can’t remember my reasoning when I first thought of it. If I remember, I’ll post back, but until then, I’ll just leave it unbound, and have the forms on my tabs bound to their respective tables. Now, putting yourself in my shoes, would you rename your forms to “frm” vice “sfrm” based upon the fact that they’re not going to be linked to the main form (frmS1), however they will be nested in the tab control?

Reply | Quote

I’d use sfrm as prefix whenever a form is intended to be placed on another form, whether it is linked to the main form or not

Reply | Quote

Okay, thanks!

Reply | Quote

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

Reply | Quote

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]

Reply | Quote

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 …)

Reply | Quote

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 worth

Reply | Quote