List Box Multiple Select

  • Thread starter Thread starter Mark
  • Start date Start date
M

Mark

I've created a List Box containing names that allows the
user the make multiple selections and then click
a 'Finish' button. The idea is that only the selections
will be transferred into a table. Any ideas on how I can
do this?

Many thanks

Mark
 
Thanks for the reply. So where do I find the database?

-----Original Message-----
Take a look at my SelectRecords sample db on
http://www.mccallie.org/midschl/index.htm. You should be able to get some
ideas from it.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

I've created a List Box containing names that allows the
user the make multiple selections and then click
a 'Finish' button. The idea is that only the selections
will be transferred into a table. Any ideas on how I can
do this?

Many thanks

Mark
Click to expand...

.
Click to expand...
 
PMFJI, but I suspect Sandra had a small fumble in cutting and pasting the
URL... go to:

http://www.daiglenet.com/MSAccess.htm


HTH

Fred Boer


Mark said:
Thanks for the reply. So where do I find the database?

-----Original Message-----
Take a look at my SelectRecords sample db on
http://www.mccallie.org/midschl/index.htm. You should be able to get some
ideas from it.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

I've created a List Box containing names that allows the
user the make multiple selections and then click
a 'Finish' button. The idea is that only the selections
will be transferred into a table. Any ideas on how I can
do this?

Many thanks

Mark
Click to expand...

.
Click to expand...
Click to expand...
 
Darn it! I can't believe how often I cut and paste the wrong URL!!! Thanks
Fred!!

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.


Fred said:
PMFJI, but I suspect Sandra had a small fumble in cutting and pasting
the URL... go to:

http://www.daiglenet.com/MSAccess.htm


HTH

Fred Boer


Mark said:
Thanks for the reply. So where do I find the database?

-----Original Message-----
Take a look at my SelectRecords sample db on
http://www.mccallie.org/midschl/index.htm. You should be able to get some
ideas from it.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.


Mark wrote:
I've created a List Box containing names that allows the
user the make multiple selections and then click
a 'Finish' button. The idea is that only the selections
will be transferred into a table. Any ideas on how I can
do this?

Many thanks

Mark

.
Click to expand...
Click to expand...
Click to expand...
 
LOL - Be sure to tell them who sent you if you decide to enroll anyone -
maybe they'll give me referral credit towards my tuition . . . (right after
pigs start flying over the moon!).

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Many thanks! It was a good trick to get me to search the
entire web-site though.

-----Original Message-----
PMFJI, but I suspect Sandra had a small fumble in cutting and
pasting the URL... go to:

http://www.daiglenet.com/MSAccess.htm


HTH

Fred Boer


Mark said:
Thanks for the reply. So where do I find the database?


-----Original Message-----
Take a look at my SelectRecords sample db on
http://www.mccallie.org/midschl/index.htm. You should be able to
get some ideas from it.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this
newsgroup.


Mark wrote:
I've created a List Box containing names that allows the
user the make multiple selections and then click
a 'Finish' button. The idea is that only the selections
will be transferred into a table. Any ideas on how I can
do this?

Many thanks

Mark

.
Click to expand...


.
Click to expand...
Click to expand...
 
Sandra,

Was looking for something exactly as you have described
here. It was exactly what I wanted.

One more item if I may, I was studying this so I can learn
from it. I was trying to make a "Select All" choice here
but cannot seem to get the thing right. Your buttons allow
multiple selections and that's great. How can I get a
Select All to do the same thing? I'm still learning
recordsets and cannot seem to get this working.

Thanks!
PC
-----Original Message-----
LOL - Be sure to tell them who sent you if you decide to enroll anyone -
maybe they'll give me referral credit towards my tuition . . . (right after
pigs start flying over the moon!).

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Many thanks! It was a good trick to get me to search the
entire web-site though.

-----Original Message-----
PMFJI, but I suspect Sandra had a small fumble in cutting and
pasting the URL... go to:

http://www.daiglenet.com/MSAccess.htm


HTH

Fred Boer


Thanks for the reply. So where do I find the database?


-----Original Message-----
Take a look at my SelectRecords sample db on
http://www.mccallie.org/midschl/index.htm. You should be able to
get some ideas from it.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this
newsgroup.


Mark wrote:
I've created a List Box containing names that allows the
user the make multiple selections and then click
a 'Finish' button. The idea is that only the selections
will be transferred into a table. Any ideas on how I can
do this?

Many thanks

Mark

.



.
Click to expand...
Click to expand...

.
Click to expand...
 
Hi PC,

Good question - this is really pretty easy to do - there are (at least) two
ways to do this. The first is to loop through the contents of the listbox
and add records to the recordset (similar to the way used to add a single
record). This is fine if the number of items in the listbox tends to be low.
A more efficient way of adding a larger number of records is to use an
append query. Both methods are illustrated in the following two event
procedures.

As a bonus I threw in a DeleteAll procedure - this one uses a delete query.

Note that the query method uses almost the same query as used for the
rowsource of lstAvailable.


'**** Loop the listbox method
'--------------------------------------------------
Private Sub cmdAddAll_Click()
Dim varItem As Variant
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim inti As Integer
Dim intStart As Integer
Set db = CurrentDb()
'*** If column headings are used then
' the first row of data is row 1, otherwise
' the first row of data is row 0.
If Me.lstAvailable.ColumnHeads Then
intStart = 1
Else
intStart = 0
End If
Set rst = db.OpenRecordset("Select * from tblPersonClasses where
PersonNbr=-1")
For inti = intStart To Me.lstAvailable.ListCount - 1
With rst
.AddNew
.Fields("PersonNbr") = Me.PersonNbr
.Fields("Classid") = Me.lstAvailable.ItemData(inti)
.Update
End With
Next inti
rst.Close
Set rst = Nothing
Set db = Nothing
Me.lstAvailable.Requery
Me.lstSelected.Requery
End Sub

'**** Append Query
'--------------------------------------------------

Private Sub cmdAddAll2_Click()
Dim strSQL As String
Dim db As DAO.Database
strSQL = "INSERT INTO tblPersonClasses (ClassId, PersonNbr) " _
& "SELECT c.ClassId, " & Me.PersonNbr & " as Expr1 " _
& "FROM tblclasses AS c " _
& "LEFT JOIN (" _
& "SELECT tblPersonClasses.PersonNbr, " _
& "tblPersonClasses.ClassId FROM tblPersonClasses " _
& "WHERE tblPersonClasses.PersonNbr=" & Me.PersonNbr & ") AS pc " _
& "ON c.ClassId = pc.ClassId WHERE pc.ClassId Is Null;"
Set db = CurrentDb()
'Debug.Print strSQL
db.Execute strSQL
Me.lstAvailable.Requery
Me.lstSelected.Requery
Set db = Nothing
End Sub

Private Sub cmdAddOne_Click()
Dim varItem As Variant
Dim rst As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb()
Set rst = db.OpenRecordset("Select * from tblPersonClasses where
PersonNbr=-1")
For Each varItem In Me.lstAvailable.ItemsSelected
With rst
.AddNew
.Fields("PersonNbr") = Me.PersonNbr
.Fields("Classid") = Me.lstAvailable.ItemData(varItem)
.Update
End With
Next varItem
rst.Close
Set rst = Nothing
Set db = Nothing
Me.lstAvailable.Requery
Me.lstSelected.Requery

End Sub

'**** Delete Query
'--------------------------------------------------
Private Sub cmdDeleteAll_Click()
Dim strSQL As String
Dim db As DAO.Database
strSQL = "Delete * from tblPersonClasses " _
& "Where PersonNbr=" & Me.PersonNbr & ";"
Set db = CurrentDb()
Debug.Print strSQL
db.Execute strSQL
Me.lstAvailable.Requery
Me.lstSelected.Requery
Set db = Nothing
End Sub
 
Sandra, for the link, I'm having some difficulties that
maybe you can resolve.

Having a bit of a problem. I cannot seem to duplicate what
is going on in the listboxes. I have fought for hours to
get mine to emulate this, but no success.

I looked into your query AvailClasses. While on the
surface it appears easy, when I tried the same thing, I
only get a portion of the numbers in my table. Kinda
making me nuts. Anyway, looking at the query via SQL I see
this;

SELECT c.ClassId, c.ClassName, pc.ClassId
FROM tblclasses AS c LEFT JOIN [SELECT
tblPersonClasses.PersonNbr, tblPersonClasses.ClassId FROM
tblPersonClasses WHERE tblPersonClasses.PersonNbr=[forms]!
[frmPersonClasses]![personnbr]]. AS pc ON c.ClassId =
pc.ClassId
WHERE (((pc.ClassId) Is Null));

What I can't seem to understand is where the internal
[SELECT tblPersonClasses....]" came from. I think I
understand what it does as if you run the query, it asks
for a parameter input which is being drawn from the form.
That's cool and that's what I need to. I just cannot get
it to work. Access pumps out a "error in FROM clause" and
I can't seem to get it fixed.

Like I said, my situation is virtually identical to your
sample db. What you have done, I need to do here too.
Being a newbie, I cannot figure this one out.

Can you assist?

Thanks,
Pat
 
Hi Pat,

That's one of the gotchas of using a nested query - basically this is a
query inside of query. Most people would save the inside query under a
separate name and then reference the saved query from the outside query. I
like nested queries for a variety of reasons but this is a problem that is
caused when Access modifies the query and changes the delimiters of the
nested query from parentheses to brackets followed by a period. Then when
brackets occur within the nested query, Jet doesn't like it and balks. Nice
huh?

To fix it, all you have to do is change the delimiters of the nested query
from [ ]. to ( )

SELECT c.ClassId, c.ClassName, pc.ClassId
FROM tblclasses AS c LEFT JOIN (SELECT
tblPersonClasses.PersonNbr, tblPersonClasses.ClassId FROM
tblPersonClasses WHERE tblPersonClasses.PersonNbr=[forms]!
[frmPersonClasses]![personnbr]) AS pc ON c.ClassId =
pc.ClassId
WHERE (((pc.ClassId) Is Null));



--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Sandra, for the link, I'm having some difficulties that
maybe you can resolve.

Having a bit of a problem. I cannot seem to duplicate what
is going on in the listboxes. I have fought for hours to
get mine to emulate this, but no success.

I looked into your query AvailClasses. While on the
surface it appears easy, when I tried the same thing, I
only get a portion of the numbers in my table. Kinda
making me nuts. Anyway, looking at the query via SQL I see
this;

SELECT c.ClassId, c.ClassName, pc.ClassId
FROM tblclasses AS c LEFT JOIN [SELECT
tblPersonClasses.PersonNbr, tblPersonClasses.ClassId FROM
tblPersonClasses WHERE tblPersonClasses.PersonNbr=[forms]!
[frmPersonClasses]![personnbr]]. AS pc ON c.ClassId =
pc.ClassId
WHERE (((pc.ClassId) Is Null));

What I can't seem to understand is where the internal
[SELECT tblPersonClasses....]" came from. I think I
understand what it does as if you run the query, it asks
for a parameter input which is being drawn from the form.
That's cool and that's what I need to. I just cannot get
it to work. Access pumps out a "error in FROM clause" and
I can't seem to get it fixed.

Like I said, my situation is virtually identical to your
sample db. What you have done, I need to do here too.
Being a newbie, I cannot figure this one out.

Can you assist?

Thanks,
Pat
-----Original Message-----
Sandra,

Was looking for something exactly as you have described
here. It was exactly what I wanted.

One more item if I may, I was studying this so I can learn
from it. I was trying to make a "Select All" choice here
but cannot seem to get the thing right. Your buttons allow
multiple selections and that's great. How can I get a
Select All to do the same thing? I'm still learning
recordsets and cannot seem to get this working.

Thanks!
PC
-----Original Message-----
LOL - Be sure to tell them who sent you if you decide to enroll
anyone - maybe they'll give me referral credit towards my tuition .
. . (right after pigs start flying over the moon!).

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.


Mark wrote:
Many thanks! It was a good trick to get me to search the
entire web-site though.


-----Original Message-----
PMFJI, but I suspect Sandra had a small fumble in cutting and
pasting the URL... go to:

http://www.daiglenet.com/MSAccess.htm


HTH

Fred Boer


Thanks for the reply. So where do I find the database?


-----Original Message-----
Take a look at my SelectRecords sample db on
http://www.mccallie.org/midschl/index.htm. You should be able to
get some ideas from it.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this
newsgroup.


Mark wrote:
I've created a List Box containing names that allows the
user the make multiple selections and then click
a 'Finish' button. The idea is that only the selections
will be transferred into a table. Any ideas on how I can
do this?

Many thanks

Mark

.



.

.
Click to expand...
.
Click to expand...
Click to expand...
 
Back
Top