Opening Query via Custom search form command button still asking to enter parameter value

[PoolHallJunkie]

Good Afternoon,

I created a custom search form (ActiveServicesSearch) that has 14 text search fields and 8 multi-select list boxes and I want it to pull data and open a query from a large dataset based on that criteria (if a field is left blank it should pull everything). The problem is, when I click the command button to search, it's still asking me to Enter Parameter Value for each field. I have a feeling it might be a syntax error, can someone glance at my code for the command button and see where I'm going wrong? I really appreciate any help here

Thanks


PHJ

Private Sub cmdSearch_Click()
   Dim Q As QueryDef, DB As Database

   ' Modify the Query.
   Set DB = CurrentDb()
   Set Q = DB.QueryDefs("PI Book")
   ' Modify the Query.
   Set DB = CurrentDb()
   Set Q = DB.QueryDefs("PI Book")
   Q.SQL = "SELECT [Active Services].[Service Baseline Id], [Active Services].[Client Name], [Active Services].[Location Code], [Active Services].[Address Line 1], [Active Services].[Address Line 2], " & _
 "[Active Services].[City Name], [Active Services].State, [Active Services].County, [Active Services].[Zip Code], [Active Services].[Service Code], [Active Services].SID, " & _
 "[Active Services].[Service Type], [Active Services].[Schedule Type], [Active Services].[Occurrence Type], [Active Services].quantity, [Active Services].Equipment, " & _
 "[Active Services].Size, [Active Services].Material, [Active Services].SCode, [Active Services].[Start Date], [Active Services].[End Date], [Active Services].[Event Recurrence Type], " & _
 "[Active Services].[Frequency Interval], [Active Services].M, [Active Services].T, [Active Services].W, [Active Services].[T 1], [Active Services].F, [Active Services].S, " & _
 "[Active Services].[S 1], [Active Services].Frequency, [Active Services].[Weekly Count], [Active Services].[Price UOM], [Active Services].[Price Amount], " & _
 "[Active Services].[Cost UOM], [Active Services].[Cost Amount], [Active Services].Vendor, [Active Services].[Vendor Name], [Active Services].[Parent Vendor], " & _
 "[Active Services].[ParentVendor Name], [Active Services].[Vendor Account Number], [Active Services].[V State], [Active Services].[V Zip Code], [Active Services].[Accounting Business Unit], " & _
 "[Active Services].[Vendor Business Unit], [Active Services].[MAS Library], [Active Services].[MAS Company], [Active Services].[MAS Box], [Active Services].[MAS Account], " & _
 "[Active Services].[MAS Customer Unique ID], [Active Services].[MAS Line Number], [Active Services].[is Extra Service] FROM [Active Services]" & _
 "WHERE ((([Active Services].[VID]) Like ""*"" & [Forms]![ActiveServicesSearch]![txtVID] & ""*"") AND (([Active Services].[Vendor Name]) Like ""*"" & [Forms]![ActiveServicesSearch]![txtVendorName] & ""*"") AND (([Active Services].[Parent VID]) Like ""*"" & [Forms]![ActiveServicesSearch]![txtParentVID] & ""*"") AND (([Active Services].[Parent Vendor Name]) Like ""*"" & [Forms]![ActiveServicesSearch]![txtParentVendorName] & ""*"")" & _
 " AND (([Active Services].[Client Code]) Like ""*"" & [Forms]![ActiveServicesSearch]![txtClientCode] & ""*"") AND (([Active Services].[Service Baseline ID]) Like ""*"" & [Forms]![ActiveServicesSearch]![txtServiceBaselineID] & ""*"") AND (([Active Services].[Location Code]) Like ""*"" & [Forms]![ActiveServicesSearch]![txtLocationCode] & ""*"") AND (([Active Services].[Service Code]) Like ""*"" & [Forms]![ActiveServicesSearch]![txtServiceCode] & ""*"")" & _
 " AND (([Active Services].[Address Line 1]) Like ""*"" & [Forms]![ActiveServicesSearch]![txtAddressLine1] & ""*"") AND (([Active Services].[SID]) Like ""*"" & [Forms]![ActiveServicesSearch]![txtSID] & ""*"") AND (([Active Services].[Address Line 2]) Like ""*"" & [Forms]![ActiveServicesSearch]![txtAddressLine2] & ""*"") AND (([Active Services].[City]) Like ""*"" & [Forms]![ActiveServicesSearch]![txtCity] & ""*"")" & _
 " AND (([Active Services].[County]) Like ""*"" & [Forms]![ActiveServicesSearch]![txtCounty] & ""*"") AND (([Active Services].[Zip Code]) Like ""*"" & [Forms]![ActiveServicesSearch]![txtZipCode] & ""*""));"
   'Q.Close
   ' Run the query.
   DoCmd.OpenQuery "PI Book"
End Sub

[June7]

I NEVER use dynamic parameters in query.

Review:

http://www.allenbrowne.com/ser-62.html

http://allenbrowne.com/ser-50.html