Access 2007, Passing parameters over to a query

  • Thread starter Thread starter R50YEE
  • Start date Start date
R

R50YEE

I hope somebody can help me.
I have created a dialog box (form) which has several mutiselect list boxes,
these boxes, using the code procedure below, load strings into separate text
boxes which shows the user what they have selected. These text boxes are then
used as inputs for a parameter query. The code works fine and returns the
correct values in the textboxes ie "Xvalue" OR "Yvalue" and the dates are
shown as #date#
I used the normal Forms!Mydialogform!Mytextbox as a parameter in the
criteria box of the query. I use a separate command button on the dialog box
form to run the code
Only problem is when I run the query it returns nil values.

Private Sub Command116_Click()

'******************** Code3 Start ************************

Dim db As DAO.Database
Dim varItem As Variant
Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 As String
Dim strSQL4 As String
Dim strSQL5 As String
Dim strSQL6 As String
Set db = CurrentDb()
Set Contols1 = Me!ModelFilter
Set Contols2 = Me!FirstDateFilter
Set Contols3 = Me!SecondDateFilter
Set Contols4 = Me!OrderCountryFilter
Set Contols5 = Me!SteeringHandFilter
Set Contols6 = Me!GearboxTypeFilter
strSQL1 = ""
strSQL2 = ""
strSQL3 = ""
strSQL4 = ""
strSQL5 = ""
strSQL6 = ""
'enumerate selected items and
'concatenate to strSQL
For Each varItem In Contols1.ItemsSelected
strSQL1 = strSQL1 & Chr(34) & Contols1.ItemData(varItem) & Chr(34) &
" Or "
Next varItem

For Each varItem In Contols2.ItemsSelected
strSQL2 = strSQL2 & Contols2.ItemData(varItem) & """"
Next varItem

For Each varItem In Contols3.ItemsSelected
strSQL3 = strSQL3 & Contols3.ItemData(varItem) & """"
Next varItem

For Each varItem In Contols4.ItemsSelected
strSQL4 = strSQL4 & Chr(34) & Contols4.ItemData(varItem) & Chr(34) &
" Or "
Next varItem

For Each varItem In Contols5.ItemsSelected
strSQL5 = strSQL5 & Chr(34) & Contols5.ItemData(varItem) & Chr(34) &
" Or "
Next varItem

For Each varItem In Contols6.ItemsSelected
strSQL6 = strSQL6 & Chr(34) & Contols6.ItemData(varItem) & Chr(34) &
" Or "
Next varItem

'Trim the end of strSQL
strSQL1 = Left(strSQL1, Len(strSQL1) - 3)
strSQL2 = Left(strSQL2, Len(strSQL2) - 1)
strSQL3 = Left(strSQL3, Len(strSQL3) - 1)
strSQL4 = Left(strSQL4, Len(strSQL4) - 3)
strSQL5 = Left(strSQL5, Len(strSQL5) - 3)
strSQL6 = Left(strSQL6, Len(strSQL6) - 3)

Me!TbModelQryParm = strSQL1
Me!TbFirstDateQryParm = strSQL2
Me!TbSecondDateQryParm = strSQL3
Me!TbOrderCountryQryParm = strSQL4
Me!TbSteeringHandQryParm = strSQL5
Me!TbGearBoxTypeQryParm = strSQL6

DoCmd.OpenQuery "Result_query"
Set db = Nothing

'******************** Code3 end ************************
End Sub
 
Unfortunately, "Xvalue" OR "Yvalue" isn't a valid parameter. You need either

Field1 = "Xvalue" OR Field 1 = "Yvalue"

or

Field1 IN ("Xvalue", "Yvalue")

and neither of those can be used as a parameter. The normal approach is to
generate the SQL dynamically and change the SQL property of the query.
 
Thanks for that Douglas,
I am new to using VBA in access, i have always got by with with using macros.
I understand what you have said, but maybe you could explain why neither of
the options for valid parameters that you have suggested will work.
When I first started this new database I managed to get 2 text boxes with
dates to open a query using a Where (omiting the "Where" ) statement in an
OpenQuery macro. I then thought if I could write some code to populate
several text boxes from say, a list box, it would work the same way (but
obviously not using a macro).
One of the advantages of populating the text boxes is it shows the user what
they have picked (like when you move fields using a query wizard).
Could I still do that and generate a SQL statement dynamically ?

Thanks again.
 
Try something like:

Dim db As DAO.Database
DIm qdf As DAO.QueryDef
Dim varItem As Variant
Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 As String
Dim strSQL4 As String
Dim strSQL5 As String
Dim strSQL6 As String

Set db = CurrentDb()
Set Contols1 = Me!ModelFilter
Set Contols2 = Me!FirstDateFilter
Set Contols3 = Me!SecondDateFilter
Set Contols4 = Me!OrderCountryFilter
Set Contols5 = Me!SteeringHandFilter
Set Contols6 = Me!GearboxTypeFilter
strSQL1 = ""
strSQL2 = ""
strSQL3 = ""
strSQL4 = ""
strSQL5 = ""
strSQL6 = ""
'enumerate selected items and
'concatenate to strSQL
If Contols1.ItemsSelected.Count > 0 Then
For Each varItem In Contols1.ItemsSelected
strSQL1 = strSQL1 & Chr(34) & Contols1.ItemData(varItem) & _
Chr(34) & ", "
Next varItem
strSQL = "Field1 IN (" & Left(strSQL1, Len(strSQL1) - 2) & ") AND "
End If

If Contols2.ItemsSelected.Count > 0 Then
For Each varItem In Contols2.ItemsSelected
strSQL2 = strSQL2 & Contols2.ItemData(varItem) & ", "
Next varItem
strSQL = strSQL & "Field2 IN (" & _
Left(strSQL2, Len(strSQL2) - 2) & ") AND "
End If

If Contols3.ItemsSelected.Count > 0 Then
For Each varItem In Contols3.ItemsSelected
strSQL3 = strSQL3 & Contols3.ItemData(varItem) & ", "
Next varItem
strSQL = strSQL & "Field3 IN (" & _
Left(strSQL3, Len(strSQL3)-2) & ") AND "

If Contols4.ItemsSelected.Count > 0 Then
For Each varItem In Contols4.ItemsSelected
strSQL4 = strSQL4 & Chr(34) & Contols4.ItemData(varItem) & _
Chr(34) & ", "
Next varItem
strSQL = strSQL & " Field4 IN (" & _
Left(strSQL4, Len(strSQL4) - 2) & ") AND "
End If

If Contol5.ItemsSelected.Count > 0 Then
For Each varItem In Contols5.ItemsSelected
strSQL5 = strSQL5 & Chr(34) & Contols5.ItemData(varItem) & _
Chr(34) & ", "
Next varItem
strSQL = strSQL & "Field5 IN (" & _
Left(strSQL5, Len(strSQL5) - 2) & ") AND "
End If

If Contols6.ItemsSelected.Count > 0 Then
For Each varItem In Contols6.ItemsSelected
strSQL6 = strSQL6 & Chr(34) & Contols6.ItemData(varItem) & _
Chr(34) & ", "
Next varItem
strSQL = strSQL & "Field6 IN (" & _
Left(strSQL6, Len(strSQL6) - 2) & ") AND "
End If

'Trim the end of strSQL
strSQL1 = Left(strSQL1, Len(strSQL1) - 2)
strSQL2 = Left(strSQL2, Len(strSQL2) - 2)
strSQL3 = Left(strSQL3, Len(strSQL3) - 2)
strSQL4 = Left(strSQL4, Len(strSQL4) - 2)
strSQL5 = Left(strSQL5, Len(strSQL5) - 2)
strSQL6 = Left(strSQL6, Len(strSQL6) - 2)
strSQL = Left(strSQL, Len(strSQL) - 5)

Me!TbModelQryParm = strSQL1
Me!TbFirstDateQryParm = strSQL2
Me!TbSecondDateQryParm = strSQL3
Me!TbOrderCountryQryParm = strSQL4
Me!TbSteeringHandQryParm = strSQL5
Me!TbGearBoxTypeQryParm = strSQL6

strSQL = "SELECT Field1, Field2, Field3, Field4, " & _
"Field5, Field6, Field7, Field8 " & _
"FROM MyTable " & _
"WHERE " & strSQL

Set qdf = db.QueryDefs("Result_query")
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
Set db = Nothing
DoCmd.OpenQuery "Result_query"

'******************** Code3 end ************************
End Sub

Obviously you'll need to change the Field1, Field2, etc, plus use the real
SQL instead of what I mocked up.
 
Thanks again Douglas,
I am away on business for the rest of this week, so I copied the code to
notepad and I will work through it whilst I am away.
I will feed back at the end of the week.
 
Back
Top