473,597 Members | 2,161 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Values from 4 Listboxes to a Query/Report

Hi,

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.cboFieldName 1 <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_T ype 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_Cl ick()
On Error GoTo Err_cmdRunRepor t_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.OpenRecord set("tablefield s")

strSQL = "SELECT "
j = 0
k = 0
rst.MoveFirst
'create the IN string by looping thru the listbox
For i = 0 To lstFieldNames.L istCount - 1
If lstFieldNames.S elected(i) Then
strIN = strIN & "[" & lstFieldNames.C olumn(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.L istCount - 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.C olumn(1) >= 1 And
cboFieldName1.C olumn(1) <= 7 Then
' if the field holds numeric data
strWhereIN1 = strWhereIN1 & " " &
lstFieldValues1 .Column(0, i) & ","
ElseIf cboFieldName1.C olumn(1) = 8 Then
' if the field is a date
strWhereIN1 = strWhereIN1 & "#" &
lstFieldValues1 .Column(0, i) & "#,"
ElseIf cboFieldName1.C olumn(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!cboFieldName 1 & " in (" &
Left(strWhereIN 1, 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.C olumn(1) >= 1 And cboFieldName2.C olumn(1)
<= 7 Then
' if the field holds numeric data
strWhereIN2 = strWhereIN2 & " " &
lstFieldValues2 .Column(0, i) & ","
ElseIf cboFieldName2.C olumn(1) = 8 Then
' if the field is a date
strWhereIN2 = strWhereIN2 & "#" &
lstFieldValues2 .Column(0, i) & "#,"
ElseIf cboFieldName2.C olumn(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!cboFieldName 2 & " in (" &
Left(strWhereIN 2, 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.C olumn(1) >= 1 And cboFieldName3.C olumn(1)
<= 7 Then
' if the field holds numeric data
strWhereIN3 = strWhereIN3 & " " &
lstFieldValues3 .Column(0, i) & ","
ElseIf cboFieldName3.C olumn(1) = 8 Then
' if the field is a date
strWhereIN3 = strWhereIN3 & "#" &
lstFieldValues3 .Column(0, i) & "#,"
ElseIf cboFieldName3.C olumn(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!cboFieldName 3 & " in (" &
Left(strWhereIN 3, 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.C olumn(1) >= 1 And cboFieldName4.C olumn(1)
<= 7 Then
' if the field holds numeric data
strWhereIN4 = strWhereIN4 & " " &
lstFieldValues4 .Column(0, i) & ","
ElseIf cboFieldName4.C olumn(1) = 8 Then
' if the field is a date
strWhereIN4 = strWhereIN4 & "#" &
lstFieldValues4 .Column(0, i) & "#,"
ElseIf cboFieldName4.C olumn(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!cboFieldName 4 & " in (" &
Left(strWhereIN 4, 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 "qryLocalAuthor ity"
Set qdf = MyDB.CreateQuer yDef("qryLocalA uthority", strSQL)

DoCmd.OpenRepor t "qryLocalAuthor ity", acPreview
Exit_cmdRunRepo rt_Click:
Exit Sub

Err_cmdRunRepor t_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_cmdRunRepo rt_Click
Else
MsgBox Err.Description '*** write out the error and exit
the sub
Resume Exit_cmdRunRepo rt_Click
End If

End Sub

Jan 18 '07 #1
1 2488
I would probably write a function to return a simple filter. Check the
ItemsSelected.C ount property of the listboxes and build the filter
accordingly with IN. Then when you get all the parts assembled into
the function, return the string and pass it in the Open event of the
report.
There are examples of using listboxes to filter reports at Accessweb.
www.mvps.org/access

Jan 18 '07 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
3331
by: Greg Iocco | last post by:
Simple problem, but I've checked the newsgroups and couldn't find an answer. On the main swithboard is a command button to open a report. The report I built is based off a query, and the query is now restricted by the simple "Between And ". What I want to do is pass the values of the two parameters into some textboxes in the heading of the report (so if 12/1/03 and 12/31/03 are the parameters, I can display the entered dates in the...
2
1936
by: Justin Koivisto | last post by:
Firstly, I want to thank all that have helped me out with getting grips on Access each time I've had questions. This has got to be one of the most helpful groups that I've posted to over the years. Now my problem... I have a listbox that I want to use to fill in the where clause of a query. I've been able to create a string holding the necessary WHERE clause without many problems. However, I cannot seem to reference the string inside...
2
2724
by: DC Gringo | last post by:
I have two listboxes, the first of which is an autopostback=true that allows multiple row selection. When I select multiple values (by holding down CTL) in the first one, it should query the second one. I seem unable to do this as it only sends back the first item I select whether I have the CTL key down or not. Upon the return trip, I can select another one, but it still only sends value for the last one I selected. -- MY LISTBOX --...
4
2372
by: schmidtmic | last post by:
I have a table that contains 20 checkbox fields. Each checkbox corrisponds to an Score amount. For Example: Checkbox1 is worth 10 points Checkbox2 is worth 5 points Checkbox3 is worth 2 points Checkbox4 is worth 3 points
1
1782
by: colleen1980 | last post by:
There is a form where it ask the two dates and then run a query then report. Private Sub Command36_Click() DoCmd.OpenQuery "qryResultsReport" DoCmd.OpenReport "rpt_TopTen", acPreview, "", "" end sub qryResultsReport
2
4340
by: salad | last post by:
This is a tip on how to speed up listboxes DRAMATICALLY. Persons that would benefit are those that are constantly updating the rowsource of a listbox/combobox in order to filter and sort the data and the refreshes are slow. (OT. I've often wondered why there is no .Sort or .Filter property for Combos and Listboxes.) My listboxes , and their rowsources, on my form were constantly being refreshed to filter and sort data. Ex:
4
2912
by: Phille | last post by:
Hi I have a form with a few textboxes and some listboxes with multiple values allowed. I would like to use an append query to insert everything into a table but for some reason it just gives me no values at all for the listboxes (the textboxes are ok). Is there something that could be done. Thanks in advance
4
4187
by: gazsharpe101 | last post by:
Hi, I have got 4 separate listboxes on a report and dependant on the content of the report, I wish to display a message box to inform the user of data inaccuracies that are appearing on the report. All I want it to contain is the contents of the 4 list boxes (which as I have said are filled with the correct criteria) next to each other with a message above it. I'm not sure if this can be done by combining the 4 list boxes into a combo box or...
10
3550
by: Trevor2007 | last post by:
I have a query that setting date from and date to from form values works: >=!! And <=!! but now I am trying to add another peramiter to pass in adition to above and now it doesn't work, I get a blank table when I run it and when i run the criteria just for State by Forms!! I get a blank form as well, what I'm trying to do is is set criteria of query with >=!! And <=!! And !! and when i run it I get a blank table ...
0
7967
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
7885
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
8381
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8031
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
5428
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
3882
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
3923
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2403
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
1233
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.