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
