472,798 Members | 1,313 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,798 software developers and data experts.

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
2 6299

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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: arthur-e | last post by:
Hi I'm trying to use a multiselect listbox to limit records in a report. My version at work is 97 but now at home I'm using Access2002- I know I can't go backwards ( to use this or similar code at...
2
by: Cassie Pennington | last post by:
I am trying to write various items from a multiselect list box to an SQL statement to update a report, without success. SQL only appears to accept hard-coded data or control values from a form, not...
2
by: Alan Lane | last post by:
Hello world: I'm using Access 2003. I have 2 listboxes. One is a single column. The other has two columns. I can use Dev Ashish's code (thanks Dev!) from the Access MVP Website to accumulate...
6
by: Mahesh Kumar | last post by:
Hi groups, Control name : ListboxID (lstCertification), selection mode=mutliselect. On Pageload i'm assinging string lstSplit="1/3/6/8" of the previously selected listindex id's. Now on the...
2
by: Steph | last post by:
I have created a multiselect list box control (lbx_comorb) that is populated from a datatable (dt_ptAdmission). The list box populates now problem at all. However the issue is when I load the...
2
by: ttime | last post by:
I've got a form that uses a multiselect listbox. When a user is selected from a combo box, values are populated into this listbox associated with that user. The problem is, if one person has say...
3
by: kaosyeti via AccessMonster.com | last post by:
hey... i have an unbound multiselect listbox on a form that i want to use to populate text boxes on that form. so if a user selects the 3rd item in a list of 20, how can i have that item show up...
5
by: martin DH | last post by:
Hello, The details are below, but I have a simple form (Form1) with two objects and a "search" command button. When the two objects are cascading combo boxes (the form creates the parameters for a...
1
by: asharma0001 | last post by:
Hi all, I was wondering whether somebody might be able to help me with a question I have on a MS Access Database I'm building. I have created a search form with a few multiselect listboxes....
0
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
0
linyimin
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
0
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Sept 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
5
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
0
by: lllomh | last post by:
How does React native implement an English player?
0
by: Mushico | last post by:
How to calculate date of retirement from date of birth
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.