recomendations

[Fipp]

I have a database that contains information on football games. There are many
different games from many different seasons, teams, opponents.

I will eventually have a fairly large number of different reports and forms
that will want to be viewed but filtered by the above three catagories.
Usually all of the reports at any given period of time while running the
reports will want to be viewed off the same criteria as the one before. (i
don't think you would want to have to fill out the parameter manually for
each report or you will be entering redundent data as the parameter report
after report.)

I was thinking of having a form with 3 multi select list boxes, [seasonlst]
, [teamlst] , [opponentlst] and having each form filter information based of
the values entered in each box.

Is this the best way to do it or is there a better way?

 

[pietlinden]

I have a database that contains information on football games. There are many
different games from many different seasons, teams, opponents.

I will eventually have a fairly large number of different reports and forms
that will want to be viewed but filtered by the above three catagories.
Usually all of the reports at any given period of time while running the
reports will want to be viewed off the same criteria as the one before. (i
don't think you would want to have to fill out the parameter manually for
each report or you will be entering redundent data as the parameter report
after report.)

I was thinking of having a form with 3 multi select list boxes, [seasonlst]
, [teamlst] , [opponentlst] and having each form filter information based of
the values entered in each box.

Is this the best way to do it or is there a better way?

It's one way. You can use the code at www.mvps.org/access to filter
the contents of the report based on the items selected in the
listbox(es).

 

[Fipp]

Thanks for your reply and help. I am now trying to go about it as explained
on the website mvps.org. I am trying to understand it all and I am not sure
on a few things, deffinitely not on what the code should be a private sub of
and how it puts the code into the hidden control? Where is the Hidden control
in the example code?

Here is what I have.

I have a form called "reportfilterfrm" on that form I will start with my
first list box named [teamlb]

the query that I would like to put the where clause into is
"statscalculationsqry" with the bound field of [team] ([team] is a text field)

I don't currently have a hidden control to which the where clause is
manually assigned and I am not sure where that is in the code below?

Here is how I adjusted the codes using my names?

'******************** Code Start ************************
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Set frm = Form!reportfilterfrm
Set ctl = frm!teamlb
strSQL = "Select * from statscalculationsqry where [team]="
'Assuming text [team] is the bound field in lb
'enumerate selected items and
'concatenate to strSQL
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & " OR [EmpID]="
Next varItem

'Trim the end of strSQL
strSQL=left$(strSQL,len(strSQL)-12))
'******************** Code end ************************

Here is the code that is listed on the MVPS website:

'******************** Code Start ************************
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Set frm = Form!frmMyForm
Set ctl = frm!lbMultiSelectListbox
strSQL = "Select * from Employees where [EmpID]="
'Assuming long [EmpID] is the bound field in lb
'enumerate selected items and
'concatenate to strSQL
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & " OR [EmpID]="
Next varItem

'Trim the end of strSQL
strSQL=left$(strSQL,len(strSQL)-12))
'******************** Code end ************************