Multiselect Listbox

[Sally]

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

[Don Leverton]

Re: Multiselect Listbox


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.Memb erID, tblMembers.Memb erName 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_Afte rUpdate()

'"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.ItemsSelect ed.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.ItemsSelect ed

If Len(strSelected ) > 0 Then
strSelected = strSelected & ", " & ctl.ItemData(va rItm)
Else
strSelected = ctl.ItemData(va rItm)
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.Me mberID)In("
whr = whr & strSelected & ")"
whr = whr & ") "
End If

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

Me.sbfMembers.F orm.RecordSourc e = 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.Send Letter = False "
MySQL = MySQL & "WHERE (((tblMembers.S endLetter)=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.Send Letter = 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.Name@Telus.N et 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" <smiller@earthl ink.net> wrote in message
news:vlFEc.2699 $yy1.953@newsre ad2.news.atl.ea rthlink.net...[color=blue]
> I have a simple multiselect listbox with a rowsorce of MemberID,[/color]
MemberName,[color=blue]
> SendLetter. SendLetter is a Yes/No field. What is the code to set[/color]
SendLetter to[color=blue]
> Yes when the user selects MemberName? I want to do this as the selections[/color]
are[color=blue]
> being made not after-the-fact after all selections are made.
>
> Thanks!
>
> Sally
>
>[/color]

[Sally]

Re: Multiselect Listbox

Don,

Thank you very, very much!!

I really appreciate your help.

Sally


"Don Leverton" <leveriteNoJunk Mail@telusplane t.net> wrote in message
news:crKEc.5856 7$E84.37925@edt nps89...[color=blue]
>
> 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.Memb erID, tblMembers.Memb erName 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_Afte rUpdate()
>
> '"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.ItemsSelect ed.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.ItemsSelect ed
>
> If Len(strSelected ) > 0 Then
> strSelected = strSelected & ", " & ctl.ItemData(va rItm)
> Else
> strSelected = ctl.ItemData(va rItm)
> 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.Me mberID)In("
> whr = whr & strSelected & ")"
> whr = whr & ") "
> End If
>
> MySQL = MySQL & whr
> MySQL = MySQL & "; "
> 'Debug.Print MySQL
>
> Me.sbfMembers.F orm.RecordSourc e = 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.Send Letter = False "
> MySQL = MySQL & "WHERE (((tblMembers.S endLetter)=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.Send Letter = 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.Name@Telus.N et 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" <smiller@earthl ink.net> wrote in message
> news:vlFEc.2699 $yy1.953@newsre ad2.news.atl.ea rthlink.net...[color=green]
> > I have a simple multiselect listbox with a rowsorce of MemberID,[/color]
> MemberName,[color=green]
> > SendLetter. SendLetter is a Yes/No field. What is the code to set[/color]
> SendLetter to[color=green]
> > Yes when the user selects MemberName? I want to do this as the selections[/color]
> are[color=green]
> > being made not after-the-fact after all selections are made.
> >
> > Thanks!
> >
> > Sally
> >
> >[/color]
>
>[/color]