467,894 Members | 1,585 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,894 developers. It's quick & easy.

Multiselect Listbox

I have a simple multiselect listbox with a rowsorce of MemberID, MemberName,
SendLetter. SendLetter is a Yes/No field. What is the code to set SendLetter to
Yes when the user selects MemberName? I want to do this as the selections are
being made not after-the-fact after all selections are made.

Thanks!

Sally
Nov 13 '05 #1
  • viewed: 5986
Share:
2 Replies

Hi Sally,
I'm hoping my reply will answer all of the posts that you have sent today
.... :-)

I like to use a multi-select list box to create a list of selections, then
use the In() operator to use this list as the criteria for the "query".

I say "query" because I am actually building a RecordSource for a
datasheet-style subform contained right in the same form as the listbox. As
items are progressively selected from the listbox , the subform instantly
refreshes to display them. Depending on how many records you select, this
can be very handy indeed.

My listbox also contains an "(All)" value, which can be selected to display
all records without any criteria.
See http://www.mvps.org/access/forms/frm0043.htm for the details on how to
do that, if you like.

This is the Row Source for the listbox that employs this method:

SELECT DISTINCTROW tblMembers.MemberID, tblMembers.MemberName FROM
tblMembers UNION Select "(All)" as null , "(All)" as Bogus From tblMembers;
----------------------------------------------------------------------------
---

And here is the code (tested and seems to be working fine) for the lisbox's
AfterUpdate event
************************************************** **************
Private Sub lstMembers_AfterUpdate()

'"Add All to List" Courtesy: Dev Ashish
' http://www.mvps.org/access/forms/frm0043.htm

Dim MyDB As DAO.Database
Set MyDB = CurrentDb

Dim ctl As Control
Set ctl = Me.lstMembers

Dim varItm As Variant
Dim strSelected As String

Dim MySQL As String
Dim whr As String

Dim Msg As String
Dim CR As String
CR = vbCrLf

'--------------------------Step
One -----------------------------------------------------
'Build an SQL string for use as the subform's Record Source, to show the
user which
'records have been selected from the listbox.

'Add a "safety" step which prevents the user from combining "All" with any
other criteria.
If ctl.ItemsSelected.Count > 1 And ctl.Selected(0) = True Then
Msg = ""
Msg = Msg & "You cannot select '(All)' along with " & CR
Msg = Msg & "any other criteria."
MsgBox (Msg)
ctl.Selected(0) = False
End If

'Build the list of selected MemberID's
For Each varItm In ctl.ItemsSelected

If Len(strSelected) > 0 Then
strSelected = strSelected & ", " & ctl.ItemData(varItm)
Else
strSelected = ctl.ItemData(varItm)
End If

Next varItm

'Debug.Print strSelected

MySQL = ""
MySQL = MySQL & "SELECT tblMembers.* FROM tblMembers "

whr = "" 'Clear the string variable

If Len(strSelected) > 0 And InStr(1, strSelected, "All") = 0 Then

whr = whr & "WHERE ((tblMembers.MemberID)In("
whr = whr & strSelected & ")"
whr = whr & ") "
End If

MySQL = MySQL & whr
MySQL = MySQL & "; "
'Debug.Print MySQL

Me.sbfMembers.Form.RecordSource = MySQL

'------------------------------ Step 2 ------------------------------
'Reset ALL "SendLetter" values to False.
'(We'll set the selected records to True in step 3)

MySQL = "" 'Reset the variable for re-use
MySQL = MySQL & "UPDATE tblMembers SET tblMembers.SendLetter = False "
MySQL = MySQL & "WHERE (((tblMembers.SendLetter)=True));"

MyDB.Execute MySQL, dbFailOnError

'------------------------------ Step 3 ------------------------------
'Set the selected values to "True"

MySQL = "" 'Reset the variable for re-use
MySQL = MySQL & "UPDATE tblMembers SET tblMembers.SendLetter = True "
MySQL = MySQL & whr 'Same WHERE statement created ealier. :-)
MySQL = MySQL & ";"

MyDB.Execute MySQL, dbFailOnError

Set MyDB = Nothing
Set ctl = Nothing

Me.Refresh

End Sub

************************************************** **************
--
HTH,
Don
=============================
Use My*****@Telus.Net for e-mail
Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)

I'm an Access97 user, so all posted code
samples are also Access97- based
unless otherwise noted.

Do Until SinksIn = True
File/Save, <slam fingers in desk drawer>
Loop

================================

"Sally" <sm*****@earthlink.net> wrote in message
news:vl****************@newsread2.news.atl.earthli nk.net...
I have a simple multiselect listbox with a rowsorce of MemberID, MemberName, SendLetter. SendLetter is a Yes/No field. What is the code to set SendLetter to Yes when the user selects MemberName? I want to do this as the selections are being made not after-the-fact after all selections are made.

Thanks!

Sally

Nov 13 '05 #2
Don,

Thank you very, very much!!

I really appreciate your help.

Sally
"Don Leverton" <le****************@telusplanet.net> wrote in message
news:crKEc.58567$E84.37925@edtnps89...

Hi Sally,
I'm hoping my reply will answer all of the posts that you have sent today
... :-)

I like to use a multi-select list box to create a list of selections, then
use the In() operator to use this list as the criteria for the "query".

I say "query" because I am actually building a RecordSource for a
datasheet-style subform contained right in the same form as the listbox. As
items are progressively selected from the listbox , the subform instantly
refreshes to display them. Depending on how many records you select, this
can be very handy indeed.

My listbox also contains an "(All)" value, which can be selected to display
all records without any criteria.
See http://www.mvps.org/access/forms/frm0043.htm for the details on how to
do that, if you like.

This is the Row Source for the listbox that employs this method:

SELECT DISTINCTROW tblMembers.MemberID, tblMembers.MemberName FROM
tblMembers UNION Select "(All)" as null , "(All)" as Bogus From tblMembers;
----------------------------------------------------------------------------
---

And here is the code (tested and seems to be working fine) for the lisbox's
AfterUpdate event
************************************************** **************
Private Sub lstMembers_AfterUpdate()

'"Add All to List" Courtesy: Dev Ashish
' http://www.mvps.org/access/forms/frm0043.htm

Dim MyDB As DAO.Database
Set MyDB = CurrentDb

Dim ctl As Control
Set ctl = Me.lstMembers

Dim varItm As Variant
Dim strSelected As String

Dim MySQL As String
Dim whr As String

Dim Msg As String
Dim CR As String
CR = vbCrLf

'--------------------------Step
One -----------------------------------------------------
'Build an SQL string for use as the subform's Record Source, to show the
user which
'records have been selected from the listbox.

'Add a "safety" step which prevents the user from combining "All" with any
other criteria.
If ctl.ItemsSelected.Count > 1 And ctl.Selected(0) = True Then
Msg = ""
Msg = Msg & "You cannot select '(All)' along with " & CR
Msg = Msg & "any other criteria."
MsgBox (Msg)
ctl.Selected(0) = False
End If

'Build the list of selected MemberID's
For Each varItm In ctl.ItemsSelected

If Len(strSelected) > 0 Then
strSelected = strSelected & ", " & ctl.ItemData(varItm)
Else
strSelected = ctl.ItemData(varItm)
End If

Next varItm

'Debug.Print strSelected

MySQL = ""
MySQL = MySQL & "SELECT tblMembers.* FROM tblMembers "

whr = "" 'Clear the string variable

If Len(strSelected) > 0 And InStr(1, strSelected, "All") = 0 Then

whr = whr & "WHERE ((tblMembers.MemberID)In("
whr = whr & strSelected & ")"
whr = whr & ") "
End If

MySQL = MySQL & whr
MySQL = MySQL & "; "
'Debug.Print MySQL

Me.sbfMembers.Form.RecordSource = MySQL

'------------------------------ Step 2 ------------------------------
'Reset ALL "SendLetter" values to False.
'(We'll set the selected records to True in step 3)

MySQL = "" 'Reset the variable for re-use
MySQL = MySQL & "UPDATE tblMembers SET tblMembers.SendLetter = False "
MySQL = MySQL & "WHERE (((tblMembers.SendLetter)=True));"

MyDB.Execute MySQL, dbFailOnError

'------------------------------ Step 3 ------------------------------
'Set the selected values to "True"

MySQL = "" 'Reset the variable for re-use
MySQL = MySQL & "UPDATE tblMembers SET tblMembers.SendLetter = True "
MySQL = MySQL & whr 'Same WHERE statement created ealier. :-)
MySQL = MySQL & ";"

MyDB.Execute MySQL, dbFailOnError

Set MyDB = Nothing
Set ctl = Nothing

Me.Refresh

End Sub

************************************************** **************
--
HTH,
Don
=============================
Use My*****@Telus.Net for e-mail
Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)

I'm an Access97 user, so all posted code
samples are also Access97- based
unless otherwise noted.

Do Until SinksIn = True
File/Save, <slam fingers in desk drawer>
Loop

================================

"Sally" <sm*****@earthlink.net> wrote in message
news:vl****************@newsread2.news.atl.earthli nk.net...
I have a simple multiselect listbox with a rowsorce of MemberID,

MemberName,
SendLetter. SendLetter is a Yes/No field. What is the code to set

SendLetter to
Yes when the user selects MemberName? I want to do this as the selections

are
being made not after-the-fact after all selections are made.

Thanks!

Sally


Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by arthur-e | last post: by
2 posts views Thread by Cassie Pennington | last post: by
6 posts views Thread by Mahesh Kumar | last post: by
2 posts views Thread by Steph | last post: by
3 posts views Thread by kaosyeti via AccessMonster.com | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.