Creating a Query in code.

[Thomas]

I am attempting to create a query using vb code.
I have the function created that correctly creates the sql text but I don't
know how to create a query using that sql text.
I have seen other examples of code to do this but they all use Dim db as
Database and Dim qdf as QueryDef.
When I try to use these I keep getting an error stating "Compile error:
User-defined type not defined".
I am running Access 2002 sp3.
The following is the code I am using in the form:

Option Compare Database

Private Sub cmd_OpenReport_Click()

Dim qryCreation As String
Dim db As Database
Dim qdf As QueryDef

qryCreation = BuildWhereString()
MsgBox qryCreation, vbInformation, "Query"

'Deleted the previous query.
Set db = CurrentDb
db.QueryDefs.Delete "qry_Agent_SelectedAgentInfo"
Set qdf = db.CreateQueryDef("qry_Agent_SelectedAgentInfo", qryCreation)

'Open the query
DoCmd.OpenQuery "qry_Agent_SelectedAgentInfo", acViewNormal, acEdit

End Sub 'cmd_OpenReport_Click()

Function BuildWhereString() As String
On Error Resume Next

Dim strWhere As String
Dim qrySQL As String
Dim varItemSel As Variant

qrySQL = "SELECT qry_AllAgent_OverallStatistics.*, * " & vbCrLf
qrySQL = qrySQL & "FROM qry_AllAgent_OverallStatistics " & vbCrLf
qrySQL = qrySQL & "WHERE
(((qry_AllAgent_OverallStatistics.AgentDisplayName) In ("

' ... build "Location" criterion expression
If HowManySelectedInListBox(Me.lst_AgentList) <> 0 Then
For Each varItemSel In Me.lst_AgentList.ItemsSelected
strWhere = strWhere & "'" & _
Me.lst_AgentList.ItemData(varItemSel) & "', "
Next varItemSel
strWhere = Left(strWhere, Len(strWhere) - Len(", "))
End If

BuildWhereString = qrySQL & strWhere & ")))" & vbCrLf & "WITH
OWNERACCESS OPTION;"



End Function 'BuildWhereString() As String

Function HowManySelectedInListBox(xlstListBox As ListBox) As Long
' *** THIS FUNCTION RETURNS THE NUMBER OF ITEMS SELECTED IN A LISTBOX.

Dim xlngSelected As Long
Dim xvarSelected As Variant

On Error Resume Next

xlngSelected = 0

For Each xvarSelected In xlstListBox.ItemsSelected
xlngSelected = xlngSelected + 1
Next xvarSelected

HowManySelectedInListBox = xlngSelected
Err.Clear
End Function 'HowManySelectedInListBox(xlstListBox As ListBox) As Long

 

[Thomas]

I figured it out.
I needed to add the reference "Microsoft DAO 3.6 Object Library".
After doing so my code now works.