query from a listbox/button on a form

[MarcSHU]

As I searched the Internet, I came accross a thread on this site which was incredibly helpful (I am miles beyond where I was) Anyway, I can not find that thread now that I have become a member. I know it was in this forum.

Bottom line: They had examples of this code. I have it in but I keep getting a 424Object Required error. Could someone please take a look at this and see what is wrong. I have DAO 3.6 enabled. I have tried it with both DAO and ADO enabled and get the same error.


Private Sub Command18_Click()
Dim dbs As DAO.Database 'CurrentDb
Dim qdf As DAO.QueryDef 'Used to save the query that's created for the report


On Error GoTo ErrHandler

Dim strSQL As String
Dim strYourSQLSelectFromStatement As String
Dim strSQLWhere As String
Dim varItem As Variant

strYourSQLSelectFromStatement = "SELECT [Hardware Table].[BARCODE], [Hardware Table].[Rack #], [Hardware Table].[Description], [Hardware Table].[Item], [Hardware Table].[Maker], [Hardware Table].[Model# (HW) Version# (SW)], [Hardware Table].[Serial #], [Hardware Table].[Purpose/Remarks], [Hardware Table].[Host name], [Hardware Table].[IP address], [Hardware Table].[DNS ENTRY REQ'D?], [Hardware Table].[CPU Type], [Hardware Table].[CPU Speed], [Hardware Table].[#CPUs], [Hardware Table].[HD], [Hardware Table].[RAM], [Hardware Table].[Location], [Hardware Table].[In Production], [Hardware Table].[Accounted For], [Hardware Table].[Date Accounted For]"
strSQLWhere = "vbNullString"

If (descriptions.ItemsSelected.Count = 0) Then Exit Sub
strSQLWhere = "Where"
For Each varItem In descriptions.ItemsSelected
strSQLWhere = strSQLWhere & Description.Column(0, varItem) & "' OR "
Next varItem

strSQLWhere = Left(strSQLWhere, Len(strSQLWhere) - 4) 'Get rid of last OR

strSQL = strYourSQLSelectFromStatement & strSQLWhere

Set dbs = CurrentDb
dbs.QueryDefs.Delete "descriptions"
Set qdf = dbs.CreateQueryDef("descriptions&quot
strSQL = vbNullString
DoEvents

ExitProcedure:
Exit Sub

ErrHandler:

If (Err.Number = 3265) Then 'IFT, tryed to delete a query that did not exist.

Resume Next

Else

MsgBox Err.Number & Err.Description
Resume ExitProcedure
End If

End Sub

 

[MarcSHU]

Okay, I think I am making headway..... Here is what I have now. BUT, now when I try to open the query that my button builds, It asks me to enter a parameter for each field in my query. It also shows the table "w" in the query. (There is no table "w" in my db)

Private Sub Command18_Click()
Dim dbs As DAO.Database 'CurrentDb
Dim qdf As DAO.QueryDef 'Used to save the query that's created for the report


On Error GoTo ErrHandler

Dim strSQL As String
Dim strYourSQLSelectFromStatement As String
Dim strSQLWhere As String
Dim varItem As Variant

strYourSQLSelectFromStatement = "SELECT [Hardware Table].BARCODE, [Hardware Table].[Rack #], [Hardware Table].Description, [Hardware Table].Item, [Hardware Table].Maker, [Hardware Table].[Model# (HW) Version# (SW)], [Hardware Table].[Serial #], [Hardware Table].[Purpose/Remarks], [Hardware Table].[Host name], [Hardware Table].[IP address], [Hardware Table].[DNS ENTRY REQ'D?], [Hardware Table].[CPU Type], [Hardware Table].[CPU Speed], [Hardware Table].[#CPUs], [Hardware Table].HD, [Hardware Table].RAM, [Hardware Table].Location, [Hardware Table].[In Production], [Hardware Table].[Accounted For], [Hardware Table].[Date Accounted For] FROM [Hardware Table]"
strSQLWhere = "where"

If (descriptions.ItemsSelected.Count = 0) Then
MsgBox "You did not select anything from the list" _
, vbExclamation, "Nothing to find!"
Exit Sub
End If

For Each varItem In descriptions.ItemsSelected
strSLQWhere = strSQLWhere & descriptions.Column(0, varItem) & "OR"

Next varItem

strSQLWhere = Left(strSQLWhere, Len(strSQLWhere) - 4) 'Get rid of last OR

strSQL = strYourSQLSelectFromStatement & strSQLWhere

Set dbs = CurrentDb
dbs.QueryDefs.Delete "descriptions"
Set qdf = dbs.CreateQueryDef("descriptions", strSQL)
strSQL = vbNullString
DoEvents

ExitProcedure:
Exit Sub

ErrHandler:

If (Err.Number = 3265) Then 'IFT, tryed to delete a query that did not exist.

Resume Next

Else

MsgBox Err.Number & Err.Description
Resume ExitProcedure
End If

End Sub