Populate multiple query columns with multiple listbox selections

[carl.barrett]

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.

[Justin Hoffman]

<ca**********@newcastle.gov.uk> wrote in message
news:11**********************@l41g2000cwc.googlegr oups.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,205)

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.OpenReport 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?

[carl.barrett]

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]![frmReportPerpetratorCases]![txtFromDate]
EndDate = [Forms]![frmReportPerpetratorCases]![txtToDate]

For Each varItem In Me![lstPerpetrator].ItemsSelected
strSQLWherePerpetrator = strSQLWherePerpetrator &
"tblIncidentPerpetrator.PerpetratorID = " &
Me![lstPerpetrator].ItemData(varItem) & " OR "
Next varItem

strSQLWherePerpetrator = Left(strSQLWherePerpetrator,
Len(strSQLWherePerpetrator) - 4)

strSQLSelect = "SELECT tblCase.CaseReferenceNumber, tblCase.CaseNumber,
tblIncident.IncidentDate, tblIncident.IncidentType, " _
& "tblIncidentPerpetrator.PerpetratorID,
tblIncidentPerpetrator.PerpetratorTenRefNo,
tblIncidentPerpetrator.PerpetratorTitle,
tblIncidentPerpetrator.PerpetratorForename, " _
& "tblIncidentPerpetrator.PerpetratorSurname,
tblIncidentPerpetrator.PerpetratorDOB,
tblIncidentPerpetrator.PerpetratorAddress1, " _
& "tblIncidentPerpetrator.PerpetratorAddress2,
tblIncidentPerpetrator.PerpetratorAddress3,
tblIncidentPerpetrator.PerpetratorAddress4, " _
& "tblIncidentPerpetrator.PerpetratorPostCode,
tblIncidentPerpetrator.PerpetratorTenure,
tblIncidentPerpetrator.PerpetratorTenancyType " _
& "FROM (tblCase INNER JOIN tblIncident ON tblCase.CaseReferenceNumber
= tblIncident.CaseReferenceNumber) " _
& "INNER JOIN tblIncidentPerpetrator ON tblIncident.IncidentID =
tblIncidentPerpetrator.IncidentID"

strSQLWhere = " WHERE (((tblIncident.IncidentDate) >= #" &
Format$(StartDate, "dd/mmm/yyyy") & "# AND " _
& "(tblIncident.IncidentDate) <= #" & Format$(EndDate, "dd/mmm/yyyy") &
"# AND (" & strSQLWherePerpetrator & ")))"

If Len(strSQLWherePerpetrator) = 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.CreateQueryDef("qryReportPerpetratorCases", strSQL)

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

DoCmd.OpenReport "repPerpetratorCases", acPreview, "", ""

[Justin Hoffman]

<ca**********@newcastle.gov.uk> wrote in message
news:11*********************@o13g2000cwo.googlegro ups.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]![frmReportPerpetratorCases]![txtFromDate]
EndDate = [Forms]![frmReportPerpetratorCases]![txtToDate]

For Each varItem In Me![lstPerpetrator].ItemsSelected
strSQLWherePerpetrator = strSQLWherePerpetrator &
"tblIncidentPerpetrator.PerpetratorID = " &
Me![lstPerpetrator].ItemData(varItem) & " OR "
Next varItem

strSQLWherePerpetrator = Left(strSQLWherePerpetrator,
Len(strSQLWherePerpetrator) - 4)

strSQLSelect = "SELECT tblCase.CaseReferenceNumber, tblCase.CaseNumber,
tblIncident.IncidentDate, tblIncident.IncidentType, " _
& "tblIncidentPerpetrator.PerpetratorID,
tblIncidentPerpetrator.PerpetratorTenRefNo,
tblIncidentPerpetrator.PerpetratorTitle,
tblIncidentPerpetrator.PerpetratorForename, " _
& "tblIncidentPerpetrator.PerpetratorSurname,
tblIncidentPerpetrator.PerpetratorDOB,
tblIncidentPerpetrator.PerpetratorAddress1, " _
& "tblIncidentPerpetrator.PerpetratorAddress2,
tblIncidentPerpetrator.PerpetratorAddress3,
tblIncidentPerpetrator.PerpetratorAddress4, " _
& "tblIncidentPerpetrator.PerpetratorPostCode,
tblIncidentPerpetrator.PerpetratorTenure,
tblIncidentPerpetrator.PerpetratorTenancyType " _
& "FROM (tblCase INNER JOIN tblIncident ON tblCase.CaseReferenceNumber
= tblIncident.CaseReferenceNumber) " _
& "INNER JOIN tblIncidentPerpetrator ON tblIncident.IncidentID =
tblIncidentPerpetrator.IncidentID"

strSQLWhere = " WHERE (((tblIncident.IncidentDate) >= #" &
Format$(StartDate, "dd/mmm/yyyy") & "# AND " _
& "(tblIncident.IncidentDate) <= #" & Format$(EndDate, "dd/mmm/yyyy") &
"# AND (" & strSQLWherePerpetrator & ")))"

If Len(strSQLWherePerpetrator) = 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.CreateQueryDef("qryReportPerpetratorCases", strSQL)

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

DoCmd.OpenReport "repPerpetratorCases", 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,1325) AND PerpDate BETWEEN #01/01/2005#
AND #01/31/2005#"

DoCmd.OpenReport "repPerpetratorCases", 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.

[Justin Hoffman]

<ca**********@newcastle.gov.uk> wrote in message
news:11*********************@o13g2000cwo.googlegro ups.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]![frmReportPerpetratorCases]![txtFromDate]
EndDate = [Forms]![frmReportPerpetratorCases]![txtToDate]

For Each varItem In Me![lstPerpetrator].ItemsSelected
strSQLWherePerpetrator = strSQLWherePerpetrator &
"tblIncidentPerpetrator.PerpetratorID = " &
Me![lstPerpetrator].ItemData(varItem) & " OR "
Next varItem

strSQLWherePerpetrator = Left(strSQLWherePerpetrator,
Len(strSQLWherePerpetrator) - 4)

strSQLSelect = "SELECT tblCase.CaseReferenceNumber, tblCase.CaseNumber,
tblIncident.IncidentDate, tblIncident.IncidentType, " _
& "tblIncidentPerpetrator.PerpetratorID,
tblIncidentPerpetrator.PerpetratorTenRefNo,
tblIncidentPerpetrator.PerpetratorTitle,
tblIncidentPerpetrator.PerpetratorForename, " _
& "tblIncidentPerpetrator.PerpetratorSurname,
tblIncidentPerpetrator.PerpetratorDOB,
tblIncidentPerpetrator.PerpetratorAddress1, " _
& "tblIncidentPerpetrator.PerpetratorAddress2,
tblIncidentPerpetrator.PerpetratorAddress3,
tblIncidentPerpetrator.PerpetratorAddress4, " _
& "tblIncidentPerpetrator.PerpetratorPostCode,
tblIncidentPerpetrator.PerpetratorTenure,
tblIncidentPerpetrator.PerpetratorTenancyType " _
& "FROM (tblCase INNER JOIN tblIncident ON tblCase.CaseReferenceNumber
= tblIncident.CaseReferenceNumber) " _
& "INNER JOIN tblIncidentPerpetrator ON tblIncident.IncidentID =
tblIncidentPerpetrator.IncidentID"

strSQLWhere = " WHERE (((tblIncident.IncidentDate) >= #" &
Format$(StartDate, "dd/mmm/yyyy") & "# AND " _
& "(tblIncident.IncidentDate) <= #" & Format$(EndDate, "dd/mmm/yyyy") &
"# AND (" & strSQLWherePerpetrator & ")))"

If Len(strSQLWherePerpetrator) = 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.CreateQueryDef("qryReportPerpetratorCases", strSQL)

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

DoCmd.OpenReport "repPerpetratorCases", 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,1325) AND PerpDate BETWEEN #01/01/2005#
AND #01/31/2005#"

DoCmd.OpenReport "repPerpetratorCases", 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.