I have the following code that runs my report generator. The user
selects a table from a combo box, then whatever fields they want from a
list box. (This part all works and the report runs fine). There is then
a combo box they can select a field from (eg CompanyID etc) and then
the list box below that contains the values (eg Microsoft, Novell etc).
These are all multi-select list boxes. Now I can get the code to work
if the user selects a table, fields, and values up to the first value
list box, (but they can't leave this blank and just select a table and
fields), so I need to add some kind of 'If Me.cboFieldName1 <Null
Then' execute whatnot if not then please skip everything and just run
the report.
But there are then 3 more combos and list boxes, So I need to do the
following:
if cboFieldName1 is not null then
run this code
if not skip the next three and run report
if cboFieldName2 is not null then
run this code
if not skip the next two and run report
if cboFieldName3 is not null then
run this code
if not skip the next one and run report
if cboFieldName4 is not null then
run this code
if not skip to running the report
But i'm not sure exactly what to write to get it to work. So some help
there would be great.
So at the moment the code below makes the user use all 4 refining list
boxes. But then spits out the following error when run report is
clicked - Syntax error (missing operator) in query expression 'Year in
('2006') WHERE Membership_Type in ('Facilities') WHERE
subMembership_Type in (Eastern Stand Coporate Suite')'.
it has read the values selected (from 3 not all 4)
So in summary:
Works selecting a table and fields and values (1 listbox only - have to
remove the creating the WITH IN string sections of code for list boxes
2 - 4 for this to work) In addition the user must not leave the one
working list box empty.
So I need a If statement to allow for an empty list box/s
situation.
The form itself works for 4 list boxes until the run report is clicked
and the missing syntax error appears.
The error is somewhere in the below code, but i'm hitting a
brickwall.
All help is greatly appreciated.
Private Sub cmdRunReport_Click()>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
On Error GoTo Err_cmdRunReport_Click
Dim MyDB As DAO.Database
Dim qdf As QueryDef
Dim rst As DAO.Recordset, rst2 As DAO.Recordset
Dim i As Integer, j As Integer, k As Integer, strSQL As String
Dim strFieldList As String, strIN As String, strWhereIN1 As String,
strWhereIN2 As String, strWhereIN3 As String, strWhereIN4 As String
Dim strWhere1 As String, strWhere2 As String, strWhere3 As String,
strWhere4 As String
Dim flgAll As Boolean
Set MyDB = CurrentDb()
Set rst = MyDB.OpenRecordset("tablefields")
strSQL = "SELECT "
j = 0
k = 0
rst.MoveFirst
'create the IN string by looping thru the listbox
For i = 0 To lstFieldNames.ListCount - 1
If lstFieldNames.Selected(i) Then
strIN = strIN & "[" & lstFieldNames.Column(0, i) & "] as
Field" & k & ","
rst.Edit
rst!indexx = k
rst.Update
rst.MoveNext
k = k + 1
Else
rst.Edit
rst!indexx = Null
rst.Update
rst.MoveNext
End If
j = j + 1
Next i
For i = k To lstFieldNames.ListCount - 1
strIN = strIN & "null as Field" & i & ","
Next i
' stripoff the last comma of the IN string
strFieldList = Left(strIN, Len(strIN) - 1)
strSQL = strSQL & strFieldList & " FROM " & Me!cboTable
'MsgBox strSQL
' creates the -WhereIN- string by looping thru the listbox
For i = 0 To lstFieldValues1.ListCount - 1
If lstFieldValues1.Selected(i) Then
If lstFieldValues1.Column(0, i) = "All" Then
flgAll = True
End If
' checks data type of field for delimiting
If cboFieldName1.Column(1) >= 1 And
cboFieldName1.Column(1) <= 7 Then
' if the field holds numeric data
strWhereIN1 = strWhereIN1 & " " &
lstFieldValues1.Column(0, i) & ","
ElseIf cboFieldName1.Column(1) = 8 Then
' if the field is a date
strWhereIN1 = strWhereIN1 & "#" &
lstFieldValues1.Column(0, i) & "#,"
ElseIf cboFieldName1.Column(1) = 10 Then
'if the field is text
strWhereIN1 = strWhereIN1 & "'" &
lstFieldValues1.Column(0, i) & "',"
End If
End If
Next i
' creates the -WHERE- string, stripping off the last comma of
the -IN- string
strWhere1 = " WHERE " & Me!cboFieldName1 & " in (" &
Left(strWhereIN1, Len(strWhereIN1) - 1) & ")"
'MsgBox strWhere1
' if "All" was selected then don't add the -WHERE- condition
If Not flgAll Then
strSQL = strSQL & strWhere1
End If
'MsgBox strSQLS
' creates the -WhereIN- string by looping thru the listbox
For i = 0 To lstFieldValues2.ListCount - 1
If lstFieldValues2.Selected(i) Then
If lstFieldValues2.Column(0, i) = "All" Then
flgAll = True
End If
' checks data type of field for delimiting
If cboFieldName2.Column(1) >= 1 And cboFieldName2.Column(1)
<= 7 Then
' if the field holds numeric data
strWhereIN2 = strWhereIN2 & " " &
lstFieldValues2.Column(0, i) & ","
ElseIf cboFieldName2.Column(1) = 8 Then
' if the field is a date
strWhereIN2 = strWhereIN2 & "#" &
lstFieldValues2.Column(0, i) & "#,"
ElseIf cboFieldName2.Column(1) = 10 Then
'if the field is text
strWhereIN2 = strWhereIN2 & "'" &
lstFieldValues2.Column(0, i) & "',"
End If
End If
Next i
' creates the -WHERE- string, stripping off the last comma of the
-IN- string
strWhere2 = " WHERE " & Me!cboFieldName2 & " in (" &
Left(strWhereIN2, Len(strWhereIN2) - 1) & ")"
'MsgBox strWhere2
' if "All" was selected then don't add the -WHERE- condition
If Not flgAll Then
strSQL = strSQL & strWhere2
End If
'MsgBox strSQL
' creates the -WhereIN- string by looping thru the listbox
For i = 0 To lstFieldValues3.ListCount - 1
If lstFieldValues3.Selected(i) Then
If lstFieldValues3.Column(0, i) = "All" Then
flgAll = True
End If
' checks data type of field for delimiting
If cboFieldName3.Column(1) >= 1 And cboFieldName3.Column(1)
<= 7 Then
' if the field holds numeric data
strWhereIN3 = strWhereIN3 & " " &
lstFieldValues3.Column(0, i) & ","
ElseIf cboFieldName3.Column(1) = 8 Then
' if the field is a date
strWhereIN3 = strWhereIN3 & "#" &
lstFieldValues3.Column(0, i) & "#,"
ElseIf cboFieldName3.Column(1) = 10 Then
'if the field is text
strWhereIN3 = strWhereIN3 & "'" &
lstFieldValues3.Column(0, i) & "',"
End If
End If
Next i
' creates the -WHERE- string, stripping off the last comma of the
-IN- string
strWhere1 = " WHERE " & Me!cboFieldName3 & " in (" &
Left(strWhereIN3, Len(strWhereIN3) - 1) & ")"
'MsgBox strWhere3
' if "All" was selected then don't add the -WHERE- condition
If Not flgAll Then
strSQL = strSQL & strWhere3
End If
'MsgBox strSQL
' creates the -WhereIN- string by looping thru the listbox
For i = 0 To lstFieldValues4.ListCount - 1
If lstFieldValues4.Selected(i) Then
If lstFieldValues4.Column(0, i) = "All" Then
flgAll = True
End If
' checks data type of field for delimiting
If cboFieldName4.Column(1) >= 1 And cboFieldName4.Column(1)
<= 7 Then
' if the field holds numeric data
strWhereIN4 = strWhereIN4 & " " &
lstFieldValues4.Column(0, i) & ","
ElseIf cboFieldName4.Column(1) = 8 Then
' if the field is a date
strWhereIN4 = strWhereIN4 & "#" &
lstFieldValues4.Column(0, i) & "#,"
ElseIf cboFieldName4.Column(1) = 10 Then
'if the field is text
strWhereIN4 = strWhereIN4 & "'" &
lstFieldValues4.Column(0, i) & "',"
End If
End If
Next i
' creates the -WHERE- string, stripping off the last comma of the
-IN- string
strWhere4 = " WHERE " & Me!cboFieldName4 & " in (" &
Left(strWhereIN4, Len(strWhereIN4) - 1) & ")"
'MsgBox strWhere4
' if "All" was selected then don't add the -WHERE- condition
If Not flgAll Then
strSQL = strSQL & strWhere4
End If
'MsgBox strSQL
MyDB.QueryDefs.Delete "qryLocalAuthority"
Set qdf = MyDB.CreateQueryDef("qryLocalAuthority", strSQL)
DoCmd.OpenReport "qryLocalAuthority", acPreview
Exit_cmdRunReport_Click:
Exit Sub
Err_cmdRunReport_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_cmdRunReport_Click
Else
MsgBox Err.Description '*** write out the error and exit
the sub
Resume Exit_cmdRunReport_Click
End If
End Sub
