473,692 Members | 2,445 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Populate multiple query columns with multiple listbox selections

Hi,

I have a list box that displays 2 columns. Behind it sits a query with
five columns. These are Column1 (DOB), column2 (a concatenated string
of Surname Forname, Title), Column3 (Surname), column4 (Forename) and
column5 (title). Columns 3,4 and 5 are not shown in the list box only
the first two.

DOB Name: [Surname]&" "&[Forename]&", "&[Title]

This would show for example:

10/10/1980 Barrett Carl, Mr
11/11/1981 Smith Robert, Mr

etc..

What I want is for the selected items in the listbox to have the
corresponding data in columns 1,3,4 and 5 insterted into the query
criteria section. For example if Barrett Carl, Mr is selected I want
the 10/10/1980 entered as DOB query criteria (query column1), Barrett
entered as Surname query criteria (query column3), forename into
column4 and the title into column5, so that it displays this along one
row over the criteria section of the query. There will be multiple
entries in the criteria section depending on how many items are
selected from the listbox.

This will form the where part of an SQL statement that I will add to
the select part of the statement to create a qdf(query) from which a
report will be displayed to the user.

Hope this makes sense.

Any help would be appreciated.

Cheers.

Nov 13 '05 #1
4 5364

<ca**********@n ewcastle.gov.uk > wrote in message
news:11******** **************@ l41g2000cwc.goo glegroups.com.. .
Hi,

I have a list box that displays 2 columns. Behind it sits a query with
five columns. These are Column1 (DOB), column2 (a concatenated string
of Surname Forname, Title), Column3 (Surname), column4 (Forename) and
column5 (title). Columns 3,4 and 5 are not shown in the list box only
the first two.

DOB Name: [Surname]&" "&[Forename]&", "&[Title]

This would show for example:

10/10/1980 Barrett Carl, Mr
11/11/1981 Smith Robert, Mr

etc..

What I want is for the selected items in the listbox to have the
corresponding data in columns 1,3,4 and 5 insterted into the query
criteria section. For example if Barrett Carl, Mr is selected I want
the 10/10/1980 entered as DOB query criteria (query column1), Barrett
entered as Surname query criteria (query column3), forename into
column4 and the title into column5, so that it displays this along one
row over the criteria section of the query. There will be multiple
entries in the criteria section depending on how many items are
selected from the listbox.

This will form the where part of an SQL statement that I will add to
the select part of the statement to create a qdf(query) from which a
report will be displayed to the user.

Hope this makes sense.

Any help would be appreciated.

Cheers.


Well it does all make sense, but it's not clear what bit you need help with.
As I understand it, you have a multi-select listbox which should enable the
user to select one or more records to display. You intend to build up an
SQL string and then either create or overwrite a querydef object.
The only part I don't understand is why you put multiple criteria in for
your query. Presumably there is a table of individuals with a primary key,
so why not have a hidden column in the listbox which contains the ID? You
then loop through the listbox to build up the SQL, eg:

SELECT tblPerson.* FROM tblPerson
WHERE tblPerson.ID IN (1023,84,1396,2 05)

As another adjustment, you could consider whether a querydef is the way to
go. If you are opening a report to view, you could supply the WHERE clause
in the DoCmd.OpenRepor t bit. This is quite standard and perhaps not as
messy as fiddling with querydefs. But is there any specific bit which you
can't do?
Nov 13 '05 #2
Hi Justin,

Thanks for the hint. I've used the ID field and and it does what I
want. I don't know why I was trying to make things more difficult for
myself.

Could you elaborate a bit more on how to use the WHERE clause as the
source of the report instead of using a qdf.

Heres some of the code I have in my form that builds the SQL that is
used for the qdf that creates the query used as the source for my
report.

If I can just use the sql as the report source then that would be
great.

Cheers.

StartDate = [Forms]![frmReportPerpet ratorCases]![txtFromDate]
EndDate = [Forms]![frmReportPerpet ratorCases]![txtToDate]

For Each varItem In Me![lstPerpetrator].ItemsSelected
strSQLWherePerp etrator = strSQLWherePerp etrator &
"tblIncidentPer petrator.Perpet ratorID = " &
Me![lstPerpetrator].ItemData(varIt em) & " OR "
Next varItem

strSQLWherePerp etrator = Left(strSQLWher ePerpetrator,
Len(strSQLWhere Perpetrator) - 4)

strSQLSelect = "SELECT tblCase.CaseRef erenceNumber, tblCase.CaseNum ber,
tblIncident.Inc identDate, tblIncident.Inc identType, " _
& "tblIncidentPer petrator.Perpet ratorID,
tblIncidentPerp etrator.Perpetr atorTenRefNo,
tblIncidentPerp etrator.Perpetr atorTitle,
tblIncidentPerp etrator.Perpetr atorForename, " _
& "tblIncidentPer petrator.Perpet ratorSurname,
tblIncidentPerp etrator.Perpetr atorDOB,
tblIncidentPerp etrator.Perpetr atorAddress1, " _
& "tblIncidentPer petrator.Perpet ratorAddress2,
tblIncidentPerp etrator.Perpetr atorAddress3,
tblIncidentPerp etrator.Perpetr atorAddress4, " _
& "tblIncidentPer petrator.Perpet ratorPostCode,
tblIncidentPerp etrator.Perpetr atorTenure,
tblIncidentPerp etrator.Perpetr atorTenancyType " _
& "FROM (tblCase INNER JOIN tblIncident ON tblCase.CaseRef erenceNumber
= tblIncident.Cas eReferenceNumbe r) " _
& "INNER JOIN tblIncidentPerp etrator ON tblIncident.Inc identID =
tblIncidentPerp etrator.Inciden tID"

strSQLWhere = " WHERE (((tblIncident. IncidentDate) >= #" &
Format$(StartDa te, "dd/mmm/yyyy") & "# AND " _
& "(tblIncident.I ncidentDate) <= #" & Format$(EndDate , "dd/mmm/yyyy") &
"# AND (" & strSQLWherePerp etrator & ")))"

If Len(strSQLWhere Perpetrator) = 0 Then
MsgBox "You must select an Incident Type from the list. Please
try again."
lstPerpetrator. SetFocus
Exit Sub
End If

strSQL = strSQLSelect + strSQLWhere

Set qdf = dbs.CreateQuery Def("qryReportP erpetratorCases ", strSQL)

Me.Visible = False
[Forms]![frmMainMenu].Visible = False

DoCmd.OpenRepor t "repPerpetrator Cases", acPreview, "", ""

Nov 13 '05 #3

<ca**********@n ewcastle.gov.uk > wrote in message
news:11******** *************@o 13g2000cwo.goog legroups.com...
Hi Justin,

Thanks for the hint. I've used the ID field and and it does what I
want. I don't know why I was trying to make things more difficult for
myself.

Could you elaborate a bit more on how to use the WHERE clause as the
source of the report instead of using a qdf.

Heres some of the code I have in my form that builds the SQL that is
used for the qdf that creates the query used as the source for my
report.

If I can just use the sql as the report source then that would be
great.

Cheers.

StartDate = [Forms]![frmReportPerpet ratorCases]![txtFromDate]
EndDate = [Forms]![frmReportPerpet ratorCases]![txtToDate]

For Each varItem In Me![lstPerpetrator].ItemsSelected
strSQLWherePerp etrator = strSQLWherePerp etrator &
"tblIncidentPer petrator.Perpet ratorID = " &
Me![lstPerpetrator].ItemData(varIt em) & " OR "
Next varItem

strSQLWherePerp etrator = Left(strSQLWher ePerpetrator,
Len(strSQLWhere Perpetrator) - 4)

strSQLSelect = "SELECT tblCase.CaseRef erenceNumber, tblCase.CaseNum ber,
tblIncident.Inc identDate, tblIncident.Inc identType, " _
& "tblIncidentPer petrator.Perpet ratorID,
tblIncidentPerp etrator.Perpetr atorTenRefNo,
tblIncidentPerp etrator.Perpetr atorTitle,
tblIncidentPerp etrator.Perpetr atorForename, " _
& "tblIncidentPer petrator.Perpet ratorSurname,
tblIncidentPerp etrator.Perpetr atorDOB,
tblIncidentPerp etrator.Perpetr atorAddress1, " _
& "tblIncidentPer petrator.Perpet ratorAddress2,
tblIncidentPerp etrator.Perpetr atorAddress3,
tblIncidentPerp etrator.Perpetr atorAddress4, " _
& "tblIncidentPer petrator.Perpet ratorPostCode,
tblIncidentPerp etrator.Perpetr atorTenure,
tblIncidentPerp etrator.Perpetr atorTenancyType " _
& "FROM (tblCase INNER JOIN tblIncident ON tblCase.CaseRef erenceNumber
= tblIncident.Cas eReferenceNumbe r) " _
& "INNER JOIN tblIncidentPerp etrator ON tblIncident.Inc identID =
tblIncidentPerp etrator.Inciden tID"

strSQLWhere = " WHERE (((tblIncident. IncidentDate) >= #" &
Format$(StartDa te, "dd/mmm/yyyy") & "# AND " _
& "(tblIncident.I ncidentDate) <= #" & Format$(EndDate , "dd/mmm/yyyy") &
"# AND (" & strSQLWherePerp etrator & ")))"

If Len(strSQLWhere Perpetrator) = 0 Then
MsgBox "You must select an Incident Type from the list. Please
try again."
lstPerpetrator. SetFocus
Exit Sub
End If

strSQL = strSQLSelect + strSQLWhere

Set qdf = dbs.CreateQuery Def("qryReportP erpetratorCases ", strSQL)

Me.Visible = False
[Forms]![frmMainMenu].Visible = False

DoCmd.OpenRepor t "repPerpetrator Cases", acPreview, "", ""

Set the report's datasource as above, but with no where clause at all.
Then, field names not accurate, but try something like:

strWhere = "PerpID IN (104,109,207,13 25) AND PerpDate BETWEEN #01/01/2005#
AND #01/31/2005#"

DoCmd.OpenRepor t "repPerpetrator Cases", acViewPreview, , strWhere

Just make sure, they cannot simply open the report without specifiying this
otherwise they could inadvertently print out all records. If this is a
danger, there are ways around it depending on which version of Access.

PS Although I see you took my hint for using the ID, you decided to use the
OR syntax instead of an IN. OR clauses can really slow things down and
although you would think that the query ... WHERE PerpID=1 OR PerpID=2 OR
PerpID=3 might be the same as WHERE PerpID IN (1,2,3) the speed can differ
greatly.


Nov 13 '05 #4

<ca**********@n ewcastle.gov.uk > wrote in message
news:11******** *************@o 13g2000cwo.goog legroups.com...
Hi Justin,

Thanks for the hint. I've used the ID field and and it does what I
want. I don't know why I was trying to make things more difficult for
myself.

Could you elaborate a bit more on how to use the WHERE clause as the
source of the report instead of using a qdf.

Heres some of the code I have in my form that builds the SQL that is
used for the qdf that creates the query used as the source for my
report.

If I can just use the sql as the report source then that would be
great.

Cheers.

StartDate = [Forms]![frmReportPerpet ratorCases]![txtFromDate]
EndDate = [Forms]![frmReportPerpet ratorCases]![txtToDate]

For Each varItem In Me![lstPerpetrator].ItemsSelected
strSQLWherePerp etrator = strSQLWherePerp etrator &
"tblIncidentPer petrator.Perpet ratorID = " &
Me![lstPerpetrator].ItemData(varIt em) & " OR "
Next varItem

strSQLWherePerp etrator = Left(strSQLWher ePerpetrator,
Len(strSQLWhere Perpetrator) - 4)

strSQLSelect = "SELECT tblCase.CaseRef erenceNumber, tblCase.CaseNum ber,
tblIncident.Inc identDate, tblIncident.Inc identType, " _
& "tblIncidentPer petrator.Perpet ratorID,
tblIncidentPerp etrator.Perpetr atorTenRefNo,
tblIncidentPerp etrator.Perpetr atorTitle,
tblIncidentPerp etrator.Perpetr atorForename, " _
& "tblIncidentPer petrator.Perpet ratorSurname,
tblIncidentPerp etrator.Perpetr atorDOB,
tblIncidentPerp etrator.Perpetr atorAddress1, " _
& "tblIncidentPer petrator.Perpet ratorAddress2,
tblIncidentPerp etrator.Perpetr atorAddress3,
tblIncidentPerp etrator.Perpetr atorAddress4, " _
& "tblIncidentPer petrator.Perpet ratorPostCode,
tblIncidentPerp etrator.Perpetr atorTenure,
tblIncidentPerp etrator.Perpetr atorTenancyType " _
& "FROM (tblCase INNER JOIN tblIncident ON tblCase.CaseRef erenceNumber
= tblIncident.Cas eReferenceNumbe r) " _
& "INNER JOIN tblIncidentPerp etrator ON tblIncident.Inc identID =
tblIncidentPerp etrator.Inciden tID"

strSQLWhere = " WHERE (((tblIncident. IncidentDate) >= #" &
Format$(StartDa te, "dd/mmm/yyyy") & "# AND " _
& "(tblIncident.I ncidentDate) <= #" & Format$(EndDate , "dd/mmm/yyyy") &
"# AND (" & strSQLWherePerp etrator & ")))"

If Len(strSQLWhere Perpetrator) = 0 Then
MsgBox "You must select an Incident Type from the list. Please
try again."
lstPerpetrator. SetFocus
Exit Sub
End If

strSQL = strSQLSelect + strSQLWhere

Set qdf = dbs.CreateQuery Def("qryReportP erpetratorCases ", strSQL)

Me.Visible = False
[Forms]![frmMainMenu].Visible = False

DoCmd.OpenRepor t "repPerpetrator Cases", acPreview, "", ""


Possible repost:

Set the report's datasource as above, but with no where clause at all.
Then, field names not accurate, but try something like:

strWhere = "PerpID IN (104,109,207,13 25) AND PerpDate BETWEEN #01/01/2005#
AND #01/31/2005#"

DoCmd.OpenRepor t "repPerpetrator Cases", acViewPreview, , strWhere

Just make sure, they cannot simply open the report without specifiying this
otherwise they could inadvertently print out all records. If this is a
danger, there are ways around it depending on which version of Access.

PS Although I see you took my hint for using the ID, you decided to use the
OR syntax instead of an IN. OR clauses can really slow things down and
although you would think that the query ... WHERE PerpID=1 OR PerpID=2 OR
PerpID=3 might be the same as WHERE PerpID IN (1,2,3) the speed can differ
greatly.

Nov 13 '05 #5

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

Similar topics

1
3556
by: theoryboy | last post by:
I'm trying to implement multiple selection functionality in a Tix Hlist using a control-click. I've bound control-click to a function that uses selection_set to add to the selection, but it doesn't seem to work. Only the last clicked item appears selected (highlighted) in the display and the return from info_selection only ever contains the last 2 items, rather than all the items I have control-clicked. It's odd, because if I set up the...
2
6570
by: Jen F. | last post by:
I have inherited a medical database in which there are multiple values stored in a single field (ie. "Current Conditions" field might contain 1-20 different conditions, separated by comma (ie. "Heart Disease,Hyper Tyroid,Cancer" etc. I would like to search via combo box for any one or more than one value in this field, ie-what patients have Heart Disease or Cancer by selecting these disorders off the combo box list. Any help would be...
4
2856
by: Alan Lane | last post by:
Hello world: I'm including both code and examples of query output. I appologize if that makes this message longer than it should be. Anyway, I need to change the query below into a pivot table query. I'm having trouble doing it. Help! Here is my code so far: Sub OldRegionQuery()
0
3506
by: starace | last post by:
I have designed a form that has 5 different list boxes where the selections within each are used as criteria in building a dynamic query. Some boxes are set for multiple selections but these list boxes do not necessarily need to have a selection made to be used in the dynamic query. In essence the form can have selections made in all or none of its list boxes to form the dynamic query I am looking to get some feedback in reference to...
6
4885
by: Joe | last post by:
I have 2 multi-list boxes, 1 displays course categories based on a table called CATEGORIES. This table has 2 fields CATEGORY_ID, CATEGORY_NAME The other multi-list box displays courses based on a table called COURSES. This table has 2 fields CATEGORY_ID, COURSE_NAME. The CATEGORY_ID is a FK in COURSES and a PK in CATEGORIES. I want to populate the course list box based on any category(s)
0
2045
by: bill yeager | last post by:
Duray, it helps in regards to knowing how to get the items that were selected in the lisbox, but I'm going in reverse --- after I get the items from the db that were selected, I'd like to be able to highlight them in the listbox within the datagrid control......... Thanks, Bill... >-----Original Message-----
1
2766
by: Marisa | last post by:
Hi. I am new to C#, and I am having trouble getting the multiple selection of listbox to work. I have an asp.net web form, and I need two listboxes which are filled with an access table. I have to be able to select multiple items, and fill a datagrid with the results. I can fill 2 listboxes and a datagrid, but not with multiple selections. So far, I have no code behind, I have completed it with just using the characteristics of the...
0
1392
by: Gunnar Hurtig | last post by:
Hi All I am relatively new to Tkinter and am putting a wraparound to the ATNF ASAP program. In one part I present several long lists in list boxes for selection. My code will remember the multiple selections made but I woul also like to highlight and maintain the highlighted items as I make additional selections. I can maintain the highlighted selections from listbox to listbox, but can't seem to figure out how to maintain the selected...
12
4032
by: micarl | last post by:
How would i print a report based on criteria selected from several Combo Boxes as well as multiple Multi Select List Boxes, that are located on the same form? I can get one Multi List Box, just not several, to report using this code i found - Private Sub cmdPreview_Click()
0
8610
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
8968
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
8810
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
8810
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
5821
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
4325
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
4559
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2977
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
3
1961
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.