Hi Everyone

I have been trying to learn how to create a Dynamic query using a Listbox

I am using the following on a Command Button to enable me to filter the results based on the Division or Diivisions Selected from the Listbox:-

Private Sub cmdReturnResults_Click()

'Each method or property should have there own occurance and incremented in number depending on the type.
Dim MyResults As clsDynamicQuery
Set MyResults = New clsDynamicQuery

On Error GoTo handler

With MyResults
.QueryName = "qDivWorkOrders" 'The name of the query that your subform is built on

'.ListBoxDetails(Me.DivisionListBox, "DivID", "NUMERIC").Occurance = 1 'if the listbox is bound to a text datatype then use "Numeric" as the last argument

Me.qDivWorkOrders.Form.RecordSource = .NewFormRecordSource ' pass the new recordsource to the form
m_ReportWhereClause = .FilterReport ' Pass the where clause to a module level delaration to filter reports see sub:ReportOpen_Click
End With

handler:
If Err.Number = 94 Then ' This error checking is necessary when using numeric controls with the property [TextboxNumeric]
Resume Next
End If

Set MyResults = Nothing
End Sub


qDivWorkOrders is the name of the query that the Subform is based on

When I run the code I get the following error:-

Compile Error
User-defined type not found

Can anyone help?

Regards

Mike

Have you checked out my example in the code archive?

Hi William

I have added a Listbox called lstDivisions
A query to display the results called query1 and a Command button with this Code:-

Option Compare Database
Option Explicit
Dim vWhereDiv As Variant

Private Sub cmdReturnResults_Click()
Dim db As Database
Dim qd As QueryDef
Dim vWhere As Variant

Set db = CurrentDb()

On Error Resume Next
db.QueryDefs.Delete "query1"
On Error GoTo 0

vWhere = Null
vWhere = vWhere & vWhereDiv


If Nz(vWhere, "") = "" Then
MsgBox "There are no search criteria selected." & vbCrLf & vbCrLf & _
"Search Cancelled.", vbInformation, "Search Canceled."
Else
Set qd = db.CreateQueryDef("query1", "SELECT * FROM tWorkOrders WHERE " & _
Mid(vWhere, 6))

db.Close
Set db = Nothing

DoCmd.OpenQuery "query1", acViewNormal, acReadOnly
End If

End Sub

Private Sub lstDivisions_AfterUpdate()
On Error GoTo AfterUpdate_Error
Dim varItem As Variant
Dim strTempItem As String

For Each varItem In Me.ActiveControl.ItemsSelected
strTempItem = strTempItem & " [Div]=" & Me.ActiveControl.ItemData(varItem) & " Or "
Next

strTempItem = "(" & Left(strTempItem, Len(strTempItem) - 4) & ")"

vWhereDiv = Null

vWhereDiv = vWhereDiv & " AND " & strTempItem

AfterUpdate_Error_Exit:
Exit Sub

AfterUpdate_Error:
If Err.Number = 5 Then
vWhereDiv = Null
Resume AfterUpdate_Error_Exit
Else
MsgBox Err.Number & " - " & Err.Description
Resume AfterUpdate_Error_Exit
End If
End Sub

When I select an item from the List and run it I get the error:- There are no search Criteria selected ??

Can you see where I am going wrong??

Your help appreciated

Regards

Mike

Is this a type you have defined in your database somewhere?

clsDynamicQuery

It's this line, isn't it?

vWhere = vWhere & vWhereDiv

Where does vWhereDiv get a non-Null value?

Hi Frank

Many thanks for your response but I have moved away from that Code and now trying to make
the Code suplied by William's link to work

Thanks

Mike