Multi Select List Box question

R

ridders

Hi

I have the following code linked to a multi select list box (LstPupils):

-----------------------------------------------------

Set ctl = Forms!SelectReview.LstPupils

For Each varitm In ctl.ItemsSelected
strPupilID = ctl.ItemData(varitm)

strsql1 = " SELECT PupilData.PupilID, PupilData.Surname, PupilData.Forename,
PupilData.Gender, " & _
" PupilData.DateOfBirth, PupilData.YearGroup, PupilData.TutorGroup,
PupilData.Active," & _
" StudentAddresses.[Parental Salutation], StudentAddresses.AddressBlock,
StudentAddresses.LetterText, Teachers.TeacherID, " & _
" Teachers!Title & ' ' & Left(Teachers!Forename,1) & ' ' &
Teachers!Surname AS TeacherName" & _
" FROM Teachers, PupilData INNER JOIN StudentAddresses ON
PupilData.PupilID = StudentAddresses.PupilID"

strWhere = "((PupilData.PupilID = '" & strPupilID & "') And
(PupilData.Active) = Yes) And (Teachers.TeacherID = GetCurrentTeacher())"
strOrder = " PupilData.Surname, PupilData.Forename"

strSQL = strsql1 & " WHERE " & strWhere & " ORDER BY " & strOrder

Next varitm

Debug.Print strSQL

MergeAllWord strSQL, "MergeTemplates"

--------------------------------------------------

The code listed just runs the last selected record.
What do I need to change to run all the selected records?

Note: The final line of code runs a mail merge function as supplied by
Albert Kallal
 
D

Dirk Goldgar

In
ridders said:
Hi

I have the following code linked to a multi select list box
(LstPupils):

-----------------------------------------------------

Set ctl = Forms!SelectReview.LstPupils

For Each varitm In ctl.ItemsSelected
strPupilID = ctl.ItemData(varitm)

strsql1 = " SELECT PupilData.PupilID, PupilData.Surname,
PupilData.Forename, PupilData.Gender, " & _
" PupilData.DateOfBirth, PupilData.YearGroup, PupilData.TutorGroup,
PupilData.Active," & _
" StudentAddresses.[Parental Salutation],
StudentAddresses.AddressBlock, StudentAddresses.LetterText,
Teachers.TeacherID, " & _ " Teachers!Title & ' ' &
Left(Teachers!Forename,1) & ' ' &
Teachers!Surname AS TeacherName" & _
" FROM Teachers, PupilData INNER JOIN StudentAddresses ON
PupilData.PupilID = StudentAddresses.PupilID"

strWhere = "((PupilData.PupilID = '" & strPupilID & "') And
(PupilData.Active) = Yes) And (Teachers.TeacherID =
GetCurrentTeacher())" strOrder = " PupilData.Surname,
PupilData.Forename"

strSQL = strsql1 & " WHERE " & strWhere & " ORDER BY " & strOrder

Next varitm

Debug.Print strSQL

MergeAllWord strSQL, "MergeTemplates"

--------------------------------------------------

The code listed just runs the last selected record.
What do I need to change to run all the selected records?

Note: The final line of code runs a mail merge function as supplied by
Albert Kallal
Click to expand...

You need to make a list of the selected PupilIDs and let your SQL
statement select all records with PupilIDs in that list. Try this:

'----- start of revised code -----
Set ctl = Forms!SelectReview.LstPupils

For Each varitm In ctl.ItemsSelected

strPupilID = strPupil & ", '" & ctl.ItemData(varitm) & "'"

Next varitm

Select Case ctl.ItemsSelected.Count
Case 0
MsgBox "No pupils selected."
Exit Sub
Case 1
strPupilID = " = " & Mid(strPupilID, 3)
Case Else
strPupilID = " In (" & Mid(strPupilID, 3) &")"
End Select

strsql1 = _
"SELECT PupilData.PupilID, PupilData.Surname, " & _
"PupilData.Forename, PupilData.Gender, PupilData.DateOfBirth, " & _
"PupilData.YearGroup, PupilData.TutorGroup, PupilData.Active, " & _
"StudentAddresses.[Parental Salutation], " & _
"StudentAddresses.AddressBlock, " & _
"StudentAddresses.LetterText, Teachers.TeacherID, " & _
"Teachers.Title & ' ' & Left(Teachers.Forename,1) " & _
"& ' ' & Teachers.Surname AS TeacherName " & _
"FROM Teachers, PupilData INNER JOIN StudentAddresses " & _
"ON PupilData.PupilID = StudentAddresses.PupilID"

strWhere = _
"((PupilData.PupilID " & strPupilID & ") And " & _
"(PupilData.Active) = Yes) " & _
"And (Teachers.TeacherID = GetCurrentTeacher())"

strOrder = "PupilData.Surname, PupilData.Forename"

strSQL = strsql1 & " WHERE " & strWhere & " ORDER BY " & strOrder

Debug.Print strSQL

MergeAllWord strSQL, "MergeTemplates"
'----- end of revised code -----

Note: I may have mucked up your SQL statement in reformatting it for
readability. I'm not sure about that SQL statement, though -- you've
got the Teachers table included in the query with no join, which is
probably going to mean a cross-product of all teachers with all
students.
 
R

ridders

Hi Dirk

This was just what I needed!
This worked with justy a couple of minor changes:

a) I added a line at the start : strPupilID ="" to clear any lists chosen
previously
b) Correction to this line so the string is identical before & after = sign:
strPupilID = strPupilID & ", '" & ctl.ItemData(varitm) & "'"

You were very observant with regards to the SQL.
In fact the Teachers table is not linked as its purpose is just to import
the TeacherID & Teacher Name of the currently logged on teacher so these are
used at the end of the mail merge letter. This would be the same signature
for all the letters produced at that time. If another teacher is logged on,
their name is appended instead.

Thanks very much for your trouble
Dirk Goldgar said:
In
ridders said:
Hi

I have the following code linked to a multi select list box
(LstPupils):

-----------------------------------------------------

Set ctl = Forms!SelectReview.LstPupils

For Each varitm In ctl.ItemsSelected
strPupilID = ctl.ItemData(varitm)

strsql1 = " SELECT PupilData.PupilID, PupilData.Surname,
PupilData.Forename, PupilData.Gender, " & _
" PupilData.DateOfBirth, PupilData.YearGroup, PupilData.TutorGroup,
PupilData.Active," & _
" StudentAddresses.[Parental Salutation],
StudentAddresses.AddressBlock, StudentAddresses.LetterText,
Teachers.TeacherID, " & _ " Teachers!Title & ' ' &
Left(Teachers!Forename,1) & ' ' &
Teachers!Surname AS TeacherName" & _
" FROM Teachers, PupilData INNER JOIN StudentAddresses ON
PupilData.PupilID = StudentAddresses.PupilID"

strWhere = "((PupilData.PupilID = '" & strPupilID & "') And
(PupilData.Active) = Yes) And (Teachers.TeacherID =
GetCurrentTeacher())" strOrder = " PupilData.Surname,
PupilData.Forename"

strSQL = strsql1 & " WHERE " & strWhere & " ORDER BY " & strOrder

Next varitm

Debug.Print strSQL

MergeAllWord strSQL, "MergeTemplates"

--------------------------------------------------

The code listed just runs the last selected record.
What do I need to change to run all the selected records?

Note: The final line of code runs a mail merge function as supplied by
Albert Kallal
Click to expand...

You need to make a list of the selected PupilIDs and let your SQL
statement select all records with PupilIDs in that list. Try this:

'----- start of revised code -----
Set ctl = Forms!SelectReview.LstPupils

For Each varitm In ctl.ItemsSelected

strPupilID = strPupil & ", '" & ctl.ItemData(varitm) & "'"

Next varitm

Select Case ctl.ItemsSelected.Count
Case 0
MsgBox "No pupils selected."
Exit Sub
Case 1
strPupilID = " = " & Mid(strPupilID, 3)
Case Else
strPupilID = " In (" & Mid(strPupilID, 3) &")"
End Select

strsql1 = _
"SELECT PupilData.PupilID, PupilData.Surname, " & _
"PupilData.Forename, PupilData.Gender, PupilData.DateOfBirth, " & _
"PupilData.YearGroup, PupilData.TutorGroup, PupilData.Active, " & _
"StudentAddresses.[Parental Salutation], " & _
"StudentAddresses.AddressBlock, " & _
"StudentAddresses.LetterText, Teachers.TeacherID, " & _
"Teachers.Title & ' ' & Left(Teachers.Forename,1) " & _
"& ' ' & Teachers.Surname AS TeacherName " & _
"FROM Teachers, PupilData INNER JOIN StudentAddresses " & _
"ON PupilData.PupilID = StudentAddresses.PupilID"

strWhere = _
"((PupilData.PupilID " & strPupilID & ") And " & _
"(PupilData.Active) = Yes) " & _
"And (Teachers.TeacherID = GetCurrentTeacher())"

strOrder = "PupilData.Surname, PupilData.Forename"

strSQL = strsql1 & " WHERE " & strWhere & " ORDER BY " & strOrder

Debug.Print strSQL

MergeAllWord strSQL, "MergeTemplates"
'----- end of revised code -----

Note: I may have mucked up your SQL statement in reformatting it for
readability. I'm not sure about that SQL statement, though -- you've
got the Teachers table included in the query with no join, which is
probably going to mean a cross-product of all teachers with all
students.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Truncated memo field in query & mail merge output 1
Truncated memo field in query & mail merge output 0
Mail Merge from Access 3
Truncated memo field in query & mail merge output 0
Printing selected reports from multi select list box 2

Top