Second Parameter Query in a QueryDef

G

Guest

Hi there,

I use the following code to create a query using a multi-select list box.

Question: I want to add a second parameter, that receives its data from a
second list box on the same form. The second list box simply allows the user
to select a province (Only One). I want to incorporate both listbox values
selected as two criteria selections for the query to generate. How can i do
this using this code?

If Me.txtFormLoaded.Value = 1 Then

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String, strWhere As String
Dim i As Integer

Set db = CurrentDb

'*** create the query based on the information on the form
strSQL = "SELECT * FROM qryReportListAccreditedVetsArea "
strWhere = "Where AccreditationID IN( "
For i = 0 To lstAccreditation.ListCount - 1
If lstAccreditation.Selected(i) Then
strWhere = strWhere & lstAccreditation.Column(0, i) & ", "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ");"
strSQL = strSQL & strWhere
'MsgBox strSQL

'*** delete the previous query
db.QueryDefs.Delete "qryMyQuery"
Set qdf = db.CreateQueryDef("qryMyQuery", strSQL)

'*** open the query
DoCmd.OpenReport "rptListtAccreditedVeterinarianArea",
acViewPreview, Me.RecordSource = "qryMyQuery"
'DoCmd.OpenQuery "qryMyQuery", acNormal, acEdit
End If
'Exit_cmdRunQuery_Click:
' Exit Sub

'Err_cmdRunQuery_Click:
' If Err.Number = 3265 Then '*** if the error is the query is
missing
' Resume Next '*** then skip the delete line and
resume on the next line
' Else
' MsgBox Err.Description '*** write out the error and
exit the sub
' Resume Exit_cmdRunQuery_Click
'End If
 
D

Douglas J. Steele

If Me.txtFormLoaded.Value = 1 Then

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String, strWhere As String
Dim varSelected As Variant

Set db = CurrentDb

'*** create the query based on the information on the form
strSQL = "SELECT * FROM qryReportListAccreditedVetsArea "

strWhere = vbNullString

' Make sure they've selected at least 1 value in the listbox

If Me.lstAccreditation.ItemsSelected > 0 Then
strWhere = " AccreditationID IN ( "
For Each varSelected In Me.lstAccreditation.ItemsSelected
strWhere = strWhere & lstAccreditation.Column(0, varSelected) & ",
"
Next varSelected
strWhere = Left(strWhere, Len(strWhere) - 2) & ") AND "
End If

' Make sure they've selected at least 1 value in the province listbox

If Len(Me.lstProvince & vbNullString) > 0 Then
strWhere = strWhere & " ProvinceCD = '" & Me.lstProvince & "'"
End If

If Len(strWhere) > 0 Then
strSQL = strSQL & " WHERE " & strWhere
End If

Note that I've changed how to get the selected items from lstAccreditation:
this only looks at those that are selected, rather than at all entries. I'm
assuming that your listbox of provinces is named lstProvince, that it's set
to not allow multiselect, and that you're using text abbreviations for the
provinces (BC, AB, SK, etc.)
 
D

Duane Hookom

I wouldn't delete the querydef.

If Not IsNull(Me.lboProvince) And _
Len(strWhere) > Len("Where AccreditationID IN( ") Then
strWhere = strWhere & " And [Province]='" & Me.lboProvince & "' "
End If
strWhere = Left(strWhere, Len(strWhere) - 2) & ");"
strSQL = strSQL & strWhere
'MsgBox strSQL

'*** change SQL of the previous query
db.QueryDefs("qryMyQuery").SQL = strSQL
 
D

Douglas J. Steele

Good catch, Duane. I missed the deletion myself.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Duane Hookom said:
I wouldn't delete the querydef.

If Not IsNull(Me.lboProvince) And _
Len(strWhere) > Len("Where AccreditationID IN( ") Then
strWhere = strWhere & " And [Province]='" & Me.lboProvince & "' "
End If
strWhere = Left(strWhere, Len(strWhere) - 2) & ");"
strSQL = strSQL & strWhere
'MsgBox strSQL

'*** change SQL of the previous query
db.QueryDefs("qryMyQuery").SQL = strSQL

--
Duane Hookom
MS Access MVP

Carlee said:
Hi there,

I use the following code to create a query using a multi-select list box.

Question: I want to add a second parameter, that receives its data from
a
second list box on the same form. The second list box simply allows the
user
to select a province (Only One). I want to incorporate both listbox
values
selected as two criteria selections for the query to generate. How can i
do
this using this code?

If Me.txtFormLoaded.Value = 1 Then

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String, strWhere As String
Dim i As Integer

Set db = CurrentDb

'*** create the query based on the information on the form
strSQL = "SELECT * FROM qryReportListAccreditedVetsArea "
strWhere = "Where AccreditationID IN( "
For i = 0 To lstAccreditation.ListCount - 1
If lstAccreditation.Selected(i) Then
strWhere = strWhere & lstAccreditation.Column(0, i) & ", "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ");"
strSQL = strSQL & strWhere
'MsgBox strSQL

'*** delete the previous query
db.QueryDefs.Delete "qryMyQuery"
Set qdf = db.CreateQueryDef("qryMyQuery", strSQL)

'*** open the query
DoCmd.OpenReport "rptListtAccreditedVeterinarianArea",
acViewPreview, Me.RecordSource = "qryMyQuery"
'DoCmd.OpenQuery "qryMyQuery", acNormal, acEdit
End If
'Exit_cmdRunQuery_Click:
' Exit Sub

'Err_cmdRunQuery_Click:
' If Err.Number = 3265 Then '*** if the error is the query is
missing
' Resume Next '*** then skip the delete line
and
resume on the next line
' Else
' MsgBox Err.Description '*** write out the error and
exit the sub
' Resume Exit_cmdRunQuery_Click
'End If
Click to expand...
Click to expand...
 
D

Duane Hookom

Well, you correctly use the Selected items which I overlooked... The MVP
tag-team approach.

--
Duane Hookom
MS Access MVP

Douglas J. Steele said:
Good catch, Duane. I missed the deletion myself.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Duane Hookom said:
I wouldn't delete the querydef.

If Not IsNull(Me.lboProvince) And _
Len(strWhere) > Len("Where AccreditationID IN( ") Then
strWhere = strWhere & " And [Province]='" & Me.lboProvince & "' "
End If
strWhere = Left(strWhere, Len(strWhere) - 2) & ");"
strSQL = strSQL & strWhere
'MsgBox strSQL

'*** change SQL of the previous query
db.QueryDefs("qryMyQuery").SQL = strSQL

--
Duane Hookom
MS Access MVP

Carlee said:
Hi there,

I use the following code to create a query using a multi-select list
box.

Question: I want to add a second parameter, that receives its data from
a
second list box on the same form. The second list box simply allows the
user
to select a province (Only One). I want to incorporate both listbox
values
selected as two criteria selections for the query to generate. How can
i do
this using this code?

If Me.txtFormLoaded.Value = 1 Then

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String, strWhere As String
Dim i As Integer

Set db = CurrentDb

'*** create the query based on the information on the form
strSQL = "SELECT * FROM qryReportListAccreditedVetsArea "
strWhere = "Where AccreditationID IN( "
For i = 0 To lstAccreditation.ListCount - 1
If lstAccreditation.Selected(i) Then
strWhere = strWhere & lstAccreditation.Column(0, i) & ",
"
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ");"
strSQL = strSQL & strWhere
'MsgBox strSQL

'*** delete the previous query
db.QueryDefs.Delete "qryMyQuery"
Set qdf = db.CreateQueryDef("qryMyQuery", strSQL)

'*** open the query
DoCmd.OpenReport "rptListtAccreditedVeterinarianArea",
acViewPreview, Me.RecordSource = "qryMyQuery"
'DoCmd.OpenQuery "qryMyQuery", acNormal, acEdit
End If
'Exit_cmdRunQuery_Click:
' Exit Sub

'Err_cmdRunQuery_Click:
' If Err.Number = 3265 Then '*** if the error is the query
is
missing
' Resume Next '*** then skip the delete line
and
resume on the next line
' Else
' MsgBox Err.Description '*** write out the error and
exit the sub
' Resume Exit_cmdRunQuery_Click
'End If
Click to expand...
Click to expand...
Click to expand...
 
E

eos

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George
 
E

eos

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George
 
E

eos

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George
 
E

eos

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George
 
E

eos

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Text field vs. Number field 7
HELP with ERROR Message 9
Simplify Code-Delete Query 3
HOW TO Simplify following code 2
Multi-Select List Box as Query Parameter 1
List box to set q parameter 1
Combox box value used in Query 1
Create Report On the Fly 1

Top