I have a form (frmEnvDocs) (with no record source associated) that has a List Box (named EnvDocs) with row source shown below.

SELECT DISTINCT tblTypeEnvProcess.TypeEnvProcess FROM tblTypeEnvProcess UNION select "All" from tblTypeEnvProcess;

It also has a command button on it. The button opens a date-prompter form named frmNumberEnvDocsApprvdDatePrompter.

The date prompter form has two unbound fields on it - one for Start Date and one for End Date. This form also has a command button that the user clicks once they have selected the start and end dates. The OnClick event procedure for this command button executes the VBA Code shown below.

**************

Private Sub cmdOpenQuery_Click()
On Error GoTo Err_cmdOpenQuery_Click
Dim MyDB As DAO.Database
Dim qdef As DAO.QueryDef
Dim i As Integer
Dim strSQL As String
Dim strWhere As String
Dim strIN As String
Dim flgSelectAll As Boolean
Dim varItem As Variant

Set MyDB = CurrentDb()

strSQL = "SELECT * FROM qryNumberofEnvDocsApprvd"

'Build the IN string by looping through the listbox
'EnvDocs is the name of the unbounded List Box with row source:
'SELECT DISTINCT tblTypeEnvProcess.TypeEnvProcess FROM tblTypeEnvProcess UNION select "All" from tblTypeEnvProcess;
For i = 0 To EnvDocs.ListCount - 1
If EnvDocs.Selected(i) Then
If EnvDocs.Column(0, i) = "All" Then
flgSelectAll = True
End If
strIN = strIN & "'" & EnvDocs.Column(0, i) & "',"
End If
Next i

'Create the WHERE string, and strip off the last comma of the IN string
strWhere = " WHERE [TypeEnvProcess] in (" & Left(strIN, Len(strIN) - 1) & ")"

'If "All" was selected in the listbox, don't add the WHERE condition
If Not flgSelectAll Then
strSQL = strSQL & strWhere
End If

MyDB.QueryDefs.Delete "qryNumberofEnvDocsApprvd2"
Set qdef = MyDB.CreateQueryDef("qryNumberofEnvDocsApprvd2", strSQL)

'Open the query, built using the IN clause to set the criteria
DoCmd.OpenQuery "qryNumberofEnvDocsApprvd2", acViewNormal

'Clear listbox selection after running query
For Each varItem In Me.EnvDocs.ItemsSelected
Me.EnvDocs.Selected(varItem) = False
Next varItem

Exit_cmdOpenQuery_Click:
Exit Sub

Err_cmdOpenQuery_Click:

If Err.Number = 5 Then
MsgBox "You must make a selection(s) from the list", , "Selection Required !"
Resume Exit_cmdOpenQuery_Click
Else
'Write out the error and exit the sub
MsgBox Err.Description
Resume Exit_cmdOpenQuery_Click
End If

End Sub

************
The query qryNumberofEnvDocsApprvd is made up of the primary table (Projects) and five other related tables (tblRoutes, tblCounties, tblDistricts, tblEnvDocs, and tblTypeEnvProcess). The query selects all approved environmental documents (DateApproved field in table tblEnvDoc) within a particular time period (the Date Prompter mentioned above). The time period is the criteria shown below on the DateApproved field in the query.

Between [Forms]![frmNumberEnvDocsApprvdRptDatePrompter]![txtStartDate] And [Forms]![frmNumberEnvDocsApprvdRptDatePrompter]![txtEndDate]

If I run the query directly (without using any forms), it asks me for the start and end dates and then runs the query just fine. However, when I use the forms with the VBA code above, I get a compile error (variable not defined) and it opens the VBA code in question and highlights the term EnvDocs in the following line of code: For i = 0 To EnvDocs.ListCount - 1

I suspect I have something in the code out of order but can't see the forest for the trees. Can anyone offer any help. Thanks in advance for your time.

if it is a multiselect listbox you need to use items selected like this to test the count of items selected

yourlistbox.ItemsSelected.Count > 0

Where would I insert that in the code? Thanks agian.

it isnt an INSERT into your existing code it s a REPLACEMENT example.

you have these lines...



i am saying it should be something like this.

try retyping this line:

For i = 0 To EnvDocs.ListCount - 1

but after the word "To " type Me., the intellisense will prompt you for the correct object name.

To Bob G. I tried your replacement code but got the same compile error message.

To arnelgp. Did you mean like this:

For i = 0 To Me.EnvDocs.ListCount - 1

If so, I get the compile error message "Method or Data Member Not Found" and it highlights the term ".EnvDocs" in the line of code. I may have type it incorrectly.

then EnvDocs is not the name of the listbox

I have attached a sample database for y'all to look at. If you run the query without a selected range of approval dates, then it runs fine. However, when you run the query with the range of approval dates, you get a compile error message. I borrowed the VBA code from the internet at http://www.databasedev.co.UK/query_using_listbox.html and modified it to fit our situation. However, in their VBA code string strSQL = "SELECT * FROM ___________", they were using a table where I am using a query. Any other help is greatly appreciated. Thanks in advance.

the answer is that you have to use the FORM qualifier as you are calling the listbox that resides on one form from the onlick event of the another form

heres the fix:

Many thanks to arnelgp and Bob G for spending their valuable time to help me out. The fix arnelgp provided works great, and now I have a new twist to the challenge. I would like to have two other command buttons on the form frmEnvDocs that would perform a print/preview of a report with and without a selected range of approval dates. The report in question is rptApprvdEnvDoc.

I have attached a revised copy of the sample database for your reference. I sincerely appreciate any help with this added feature. Thanks in advance.

No one interested enough to answer? Thanks in advance for your valuable time.