Query Parameters w/ List box and Combo box

[Guest]

I've been wrestling with this for quite a while and have no idea what I'm
doing wrong.

I was lucky enough to come across some code from Roger's Access website
(thanks to this newsgroup) that helped me tremendously. I created a form
that allows the user to select multiple criteria for the same field in a
query using a List Box. It works great!

However, I also have a combo box on the form that the user will select one
item from for another field. I've tried a dozen different ways to add that
criteria to the code and am not having any luck.

I need to add: where [1st Responsible] =
Forms![frm_Status_Opt]![cmbo_Initials]

Can someone please help me?

I would appreciate it greatly.

Dim MyDB As DAO.Database
Dim qdf As DAO.QueryDef
Dim i As Integer, strSQL As String
Dim strWhere As String, strIN As String
Dim flgAll As Boolean

Set MyDB = CurrentDb()

strSQL = "SELECT * FROM [tbl_Implementation Pipeline]"

'create the IN string by looping thru the listbox
For i = 0 To lst_Status.ListCount - 1
If lst_Status.Selected(i) Then
If lst_Status.Column(0, i) = "All" Then
flgAll = True
End If
strIN = strIN & "'" & lst_Status.Column(0, i) & "',"
End If
Next i

'create the WHERE string, stripping off the last comma of the IN string
strWhere = " WHERE [Status] in (" & Left(strIN, Len(strIN) - 1) & ")"

'if "All" was selected, don't add the WHERE condition
If Not flgAll Then
strSQL = strSQL & strWhere
End If

MyDB.QueryDefs.Delete "qry_Implementation_Resp_Rpt_TEST"
Set qdf = MyDB.CreateQueryDef("qry_Implementation_Resp_Rpt_TEST", strSQL)

DoCmd.OpenReport "rpt_Implementation Pipeline_Resp1", acPreview

Thanks, Rachel

 

[Guest]

Sorry, I didn't mean to post this in the "General Questions" section.

Rachel

 

[Guest]

I got it.

I added
& " And ([1st Responsible]) = [Forms]![frm_Status_Opt]![cmbo_Initials]"

to the
strWhere = " WHERE [Status] in (" & Left(strIN, Len(strIN) - 1) & ")" line.

Sorry, I didn't mean to post this in the "General Questions" section.

Rachel

I've been wrestling with this for quite a while and have no idea what I'm
doing wrong.

I was lucky enough to come across some code from Roger's Access website
(thanks to this newsgroup) that helped me tremendously. I created a form
that allows the user to select multiple criteria for the same field in a
query using a List Box. It works great!

However, I also have a combo box on the form that the user will select one
item from for another field. I've tried a dozen different ways to add that
criteria to the code and am not having any luck.

I need to add: where [1st Responsible] =
Forms![frm_Status_Opt]![cmbo_Initials]

Can someone please help me?

I would appreciate it greatly.

Dim MyDB As DAO.Database
Dim qdf As DAO.QueryDef
Dim i As Integer, strSQL As String
Dim strWhere As String, strIN As String
Dim flgAll As Boolean

Set MyDB = CurrentDb()

strSQL = "SELECT * FROM [tbl_Implementation Pipeline]"

'create the IN string by looping thru the listbox
For i = 0 To lst_Status.ListCount - 1
If lst_Status.Selected(i) Then
If lst_Status.Column(0, i) = "All" Then
flgAll = True
End If
strIN = strIN & "'" & lst_Status.Column(0, i) & "',"
End If
Next i

'create the WHERE string, stripping off the last comma of the IN string
strWhere = " WHERE [Status] in (" & Left(strIN, Len(strIN) - 1) & ")"

'if "All" was selected, don't add the WHERE condition
If Not flgAll Then
strSQL = strSQL & strWhere
End If

MyDB.QueryDefs.Delete "qry_Implementation_Resp_Rpt_TEST"
Set qdf = MyDB.CreateQueryDef("qry_Implementation_Resp_Rpt_TEST", strSQL)

DoCmd.OpenReport "rpt_Implementation Pipeline_Resp1", acPreview

Thanks, Rachel