R
RFrechette
Gary Walter was a big help with this problem in 12/06. I have a followup
though. I use this code on a form to select 2 parameters for a report. (A
Resp. person and either an Active, Pending, or Complete report).
Here's the code I ended up with from Gary:
Private Sub cmd_OK_Click()
'On Error GoTo Err_cmd_OK_Click
Dim MyDB As DAO.Database
Dim qdf As DAO.QueryDef
Dim i As Integer, ii As Integer, strSQL As String
Dim strWhere As String, strIN As String, strIN1 As String
Dim flgAll As Boolean
Set MyDB = CurrentDb()
strSQL = "SELECT * FROM [tbl_Implementation Pipeline]"
'Status - 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
'Resp - create the IN string by looping thru the listbox
For ii = 0 To lst_Resp.ListCount - 1
If lst_Resp.Selected(ii) Then
If lst_Resp.Column(0, ii) = "All" Then
flgAll = True
End If
strIN1 = strIN1 & "'" & lst_Resp.Column(0, ii) & "',"
End If
Next ii
'create the WHERE string, stripping off the last comma of the IN string
strWhere = " WHERE [Status] in (" & Left(strIN, Len(strIN) - 1) & ")" & _
" And [1st Responsible] in (" & Left(strIN1, Len(strIN1) - 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"
Set qdf = MyDB.CreateQueryDef("qry_Implementation_Resp_Rpt", strSQL)
DoCmd.OpenReport "rpt_Implementation Pipeline_Resp1", acPreview
DoCmd.Close acForm, "frm_Status_Opt", acSaveNo
Exit_cmd_OK_Click:
Exit Sub
Err_cmd_OK_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
ElseIf Err.Number = 5 Then
MsgBox "You must make a selection"
Resume Exit_cmd_OK_Click
Else
MsgBox Err.Description '*** write out the error and exit the sub
Resume Exit_cmd_OK_Click
End If
End Sub
The database that I use this code in is synchronized with 3 replicas. It
works great in the master. It turns out the people who use the replicas have
never tried this. When they try to use the form on the replicas they get an
error stating "Run-time error 3452. You cannot make changes to the design of
the database at this replica." It brings them to this line in the code:
MyDB.QueryDefs.Delete "qry_Implementation_Resp_Rpt"
Set qdf = MyDB.CreateQueryDef("qry_Implementation_Resp_Rpt", strSQL)
Do you have any idea how I can rectify this? They need to be able to have
the form work like the Master in how it selects the parameters for the query
behind the report.
I really appreciate any help I can get on this problem.
---------------------------
Below is the messages that went back and forth between Gary and myself.
Gary Walter" wrote:
not meaning to "complicate," but here be some skeleton code where you are
looping only through *selected* rows of listbox, plus checking that have at
least something selected (adapted from Helen Fedemma code)
http://www.helenfeddema.com/access.htm
Dim ctl As Access.ListBox
Dim strItem As String
Dim varItem As Variant
Set ctl = Me!lstxxx
'Check that at least one item has been selected
If ctl.ItemsSelected.Count = 0 Then
MsgBox "Please select at least one item"
ctl.SetFocus
Exit Sub
End If
'Iterate through collection of items selected in the listbox
For Each varItem In ctl.ItemsSelected
'here concern is col=0 of selected row=varItem
strItem = Nz(ctl.Column(0, varItem))
Debug.Print "Selected item: " & strItem
'collect your strIN/check for "All"
Next varItem
:
A "quick-and-dirty" method I use for concatenating multiple criteria is to
se "(1=1)" when an early criteria evaluates to "All."
(**untested code**)
On Error GoTo ErrorHandler
Dim MyDB As DAO.Database
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]"
'*** first ListBox***
'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
Exit For
Else
strIN = strIN & "'" & lst_Status.Column(0, i) & "',"
End If
End If
Next i
If flgAll = True Then
strWhere = " WHERE (1=1)"
Else
'strip off the last comma of the IN string
strWhere = " WHERE ([Status] IN (" _
& Left(strIN, Len(strIN) - 1) & "))"
End If
'*** second ListBox (still checking column 0?)***
flgAll = False
strIn = ""
'create the IN string by looping thru the listbox
For i = 0 To lst_Resp.ListCount - 1
If lst_Resp.Selected(i) Then
If lst_Resp.Column(0, i) = "All" Then
flgAll = True
Exit For
Else
strIN = strIN & "'" & lst_Resp.Column(0, i) & "',"
End If
End If
Next i
If flgAll = True Then
'don't add to strWhere
Else
'strip off the last comma of the IN string
strWhere = strWhere & " AND ([1st Responsible] IN (" _
& Left(strIN, Len(strIN) - 1) & "))"
End If
strSQL = strSQL & strWhere
Debug.Print strSQL 'check Immediate Window if things go wrong
MyDB.QueryDefs("qry_Implementation_Resp_Rpt").SQL = strSQL
ErrorHandlerExit:
If Not MyDB Is Nothing Then Set MyDB = Nothing
Exit Sub
ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " &
Err.Description
Resume ErrorHandlerExit
End Sub
:
I have a form with a list box and a combo box that allows me to gather the
parameters for my report. They are working fine (w/ the help of this forum).
However, I now need to change the combo box to a list box to allow for
multiple selections for that field as well.
I will be changing the [1st Responsible] combo box field to a list box
called lst_Resp. My other list box is called lst_Status.
I tried it a dozen different ways and almost had it working. (Although I'm
sure I went the very long way around.) Then I couldn't seem to get the Where
string to work.
Hopefully, I explained that clearly enough. Any help would be greatly
appreciated.
My code is below.
Thanks, Rachel
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) &
")" &
_
" And ([1st Responsible]) =
[Forms]![frm_Status_Opt]![cmbo_Initials]"
'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"
Set qdf = MyDB.CreateQueryDef("qry_Implementation_Resp_Rpt", strSQL)
though. I use this code on a form to select 2 parameters for a report. (A
Resp. person and either an Active, Pending, or Complete report).
Here's the code I ended up with from Gary:
Private Sub cmd_OK_Click()
'On Error GoTo Err_cmd_OK_Click
Dim MyDB As DAO.Database
Dim qdf As DAO.QueryDef
Dim i As Integer, ii As Integer, strSQL As String
Dim strWhere As String, strIN As String, strIN1 As String
Dim flgAll As Boolean
Set MyDB = CurrentDb()
strSQL = "SELECT * FROM [tbl_Implementation Pipeline]"
'Status - 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
'Resp - create the IN string by looping thru the listbox
For ii = 0 To lst_Resp.ListCount - 1
If lst_Resp.Selected(ii) Then
If lst_Resp.Column(0, ii) = "All" Then
flgAll = True
End If
strIN1 = strIN1 & "'" & lst_Resp.Column(0, ii) & "',"
End If
Next ii
'create the WHERE string, stripping off the last comma of the IN string
strWhere = " WHERE [Status] in (" & Left(strIN, Len(strIN) - 1) & ")" & _
" And [1st Responsible] in (" & Left(strIN1, Len(strIN1) - 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"
Set qdf = MyDB.CreateQueryDef("qry_Implementation_Resp_Rpt", strSQL)
DoCmd.OpenReport "rpt_Implementation Pipeline_Resp1", acPreview
DoCmd.Close acForm, "frm_Status_Opt", acSaveNo
Exit_cmd_OK_Click:
Exit Sub
Err_cmd_OK_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
ElseIf Err.Number = 5 Then
MsgBox "You must make a selection"
Resume Exit_cmd_OK_Click
Else
MsgBox Err.Description '*** write out the error and exit the sub
Resume Exit_cmd_OK_Click
End If
End Sub
The database that I use this code in is synchronized with 3 replicas. It
works great in the master. It turns out the people who use the replicas have
never tried this. When they try to use the form on the replicas they get an
error stating "Run-time error 3452. You cannot make changes to the design of
the database at this replica." It brings them to this line in the code:
MyDB.QueryDefs.Delete "qry_Implementation_Resp_Rpt"
Set qdf = MyDB.CreateQueryDef("qry_Implementation_Resp_Rpt", strSQL)
Do you have any idea how I can rectify this? They need to be able to have
the form work like the Master in how it selects the parameters for the query
behind the report.
I really appreciate any help I can get on this problem.
---------------------------
Below is the messages that went back and forth between Gary and myself.
Gary Walter" wrote:
not meaning to "complicate," but here be some skeleton code where you are
looping only through *selected* rows of listbox, plus checking that have at
least something selected (adapted from Helen Fedemma code)
http://www.helenfeddema.com/access.htm
Dim ctl As Access.ListBox
Dim strItem As String
Dim varItem As Variant
Set ctl = Me!lstxxx
'Check that at least one item has been selected
If ctl.ItemsSelected.Count = 0 Then
MsgBox "Please select at least one item"
ctl.SetFocus
Exit Sub
End If
'Iterate through collection of items selected in the listbox
For Each varItem In ctl.ItemsSelected
'here concern is col=0 of selected row=varItem
strItem = Nz(ctl.Column(0, varItem))
Debug.Print "Selected item: " & strItem
'collect your strIN/check for "All"
Next varItem
:
A "quick-and-dirty" method I use for concatenating multiple criteria is to
se "(1=1)" when an early criteria evaluates to "All."
(**untested code**)
On Error GoTo ErrorHandler
Dim MyDB As DAO.Database
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]"
'*** first ListBox***
'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
Exit For
Else
strIN = strIN & "'" & lst_Status.Column(0, i) & "',"
End If
End If
Next i
If flgAll = True Then
strWhere = " WHERE (1=1)"
Else
'strip off the last comma of the IN string
strWhere = " WHERE ([Status] IN (" _
& Left(strIN, Len(strIN) - 1) & "))"
End If
'*** second ListBox (still checking column 0?)***
flgAll = False
strIn = ""
'create the IN string by looping thru the listbox
For i = 0 To lst_Resp.ListCount - 1
If lst_Resp.Selected(i) Then
If lst_Resp.Column(0, i) = "All" Then
flgAll = True
Exit For
Else
strIN = strIN & "'" & lst_Resp.Column(0, i) & "',"
End If
End If
Next i
If flgAll = True Then
'don't add to strWhere
Else
'strip off the last comma of the IN string
strWhere = strWhere & " AND ([1st Responsible] IN (" _
& Left(strIN, Len(strIN) - 1) & "))"
End If
strSQL = strSQL & strWhere
Debug.Print strSQL 'check Immediate Window if things go wrong
MyDB.QueryDefs("qry_Implementation_Resp_Rpt").SQL = strSQL
ErrorHandlerExit:
If Not MyDB Is Nothing Then Set MyDB = Nothing
Exit Sub
ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " &
Err.Description
Resume ErrorHandlerExit
End Sub
:
I have a form with a list box and a combo box that allows me to gather the
parameters for my report. They are working fine (w/ the help of this forum).
However, I now need to change the combo box to a list box to allow for
multiple selections for that field as well.
I will be changing the [1st Responsible] combo box field to a list box
called lst_Resp. My other list box is called lst_Status.
I tried it a dozen different ways and almost had it working. (Although I'm
sure I went the very long way around.) Then I couldn't seem to get the Where
string to work.
Hopefully, I explained that clearly enough. Any help would be greatly
appreciated.
My code is below.
Thanks, Rachel
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) &
")" &
_
" And ([1st Responsible]) =
[Forms]![frm_Status_Opt]![cmbo_Initials]"
'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"
Set qdf = MyDB.CreateQueryDef("qry_Implementation_Resp_Rpt", strSQL)