By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,422 Members | 1,615 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,422 IT Pros & Developers. It's quick & easy.

Trouble with Simple Multi Select Listbox in Access 2003 (not so simple for me)

P: n/a
Hello,

I am new to using recordsets, and i am completly stuck with this one.
I am trying to use a multi select list box to write records to a table.
Something in my code is causing the same record to be written over and
over, for example if I have 4 items selected, it writes the same item 4
times in my table.

Here is my code.

Private Sub OrderRxs_Click()

Dim db As DAO.Database
Dim rstOrder As DAO.Recordset
Dim varItem As Variant

OrderGroupNumber = DMax("[OrderGroupID]", "[RefillOrders_Info]") +
1

If ListBox.ItemsSelected.Count = 0 Then
MsgBox "You must select Rx Numbers to Order.", vbExclamation +
vbOKOnly
Exit Sub
Else
bytChoice1 = MsgBox("Order " & ListBox.ItemsSelected.Count & "
Rx(s) for Paitent?", vbInformation + vbYesNo)
If bytChoice1 = vbNo Then
Exit Sub
End If
If bytChoice1 = vbYes Then
Set db = CurrentDb
Set rstOrder = db.OpenRecordset("RefillOrders_Info")

With rstOrder
For Each varItem In ListBox.ItemsSelected
.AddNew
![OrderGroupID] = OrderGroupNumber
![Patient Code] = ListBox.Column(1)
![Pat Last Name] = ListBox.Column(2)
![Pat First Nme] = ListBox.Column(3)
![EmployeeID] = lngCurrentEmpID
.Update
Next varItem
End With
End If
End If

rstOrder.Close
Set rstOrder = Nothing
db.Close
Set db = Nothing
End Sub

I would love some help with my mess!!!

thanks,
Lisa

Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Br
In news:11*********************@g14g2000cwa.googlegro ups.com,
Lisa <li*******@aol.com> said:
Hello,

I am new to using recordsets, and i am completly stuck with this one.
I am trying to use a multi select list box to write records to a
table. Something in my code is causing the same record to be written
over and over, for example if I have 4 items selected, it writes the
same item 4 times in my table.

Here is my code.

Private Sub OrderRxs_Click()

Dim db As DAO.Database
Dim rstOrder As DAO.Recordset
Dim varItem As Variant

OrderGroupNumber = DMax("[OrderGroupID]", "[RefillOrders_Info]") +
1

If ListBox.ItemsSelected.Count = 0 Then
MsgBox "You must select Rx Numbers to Order.", vbExclamation +
vbOKOnly
Exit Sub
Else
bytChoice1 = MsgBox("Order " & ListBox.ItemsSelected.Count & "
Rx(s) for Paitent?", vbInformation + vbYesNo)
If bytChoice1 = vbNo Then
Exit Sub
End If
If bytChoice1 = vbYes Then
Set db = CurrentDb
Set rstOrder = db.OpenRecordset("RefillOrders_Info")

With rstOrder
For Each varItem In ListBox.ItemsSelected
.AddNew
![OrderGroupID] = OrderGroupNumber
![Patient Code] = ListBox.Column(1)
![Pat Last Name] = ListBox.Column(2)
![Pat First Nme] = ListBox.Column(3)
![EmployeeID] = lngCurrentEmpID
.Update
Next varItem
End With
End If
End If

rstOrder.Close
Set rstOrder = Nothing
db.Close
Set db = Nothing
End Sub

I would love some help with my mess!!!

thanks,
Lisa

Nov 13 '05 #2

P: n/a
Br
In news:11*********************@g14g2000cwa.googlegro ups.com,
Lisa <li*******@aol.com> said:
<>
With rstOrder
For Each varItem In ListBox.ItemsSelected
.AddNew
![OrderGroupID] = OrderGroupNumber
![Patient Code] = ListBox.Column(1)
![Pat Last Name] = ListBox.Column(2)
![Pat First Nme] = ListBox.Column(3)
![EmployeeID] = lngCurrentEmpID
.Update
Next varItem
End With
End If
End If


You're not referencing the selected item correctly.

eg.

![Patient Code] = ListBox.Column(1)

Should be..

![Patient Code] = ListBox.Column(1, varItem)

What you are doing is getting the bound value only which is the current
selected record.

Check out the Access help for "ItemsSelected" and check the Example,
it's all there :)

Br@Dley
Nov 13 '05 #3

P: n/a
Instead of Column(1), try Column(1, varItem).

James A. Fortune

Nov 13 '05 #4

P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You have to indicate which row in the ListBox the data comes from. You
used varItem. I usually use a variable named "row."

Dim row Variant
For Each row In ListBox.ItemsSelected
.AddNew
![OrderGroupID] = OrderGroupNumber
![Patient Code] = ListBox.Column(1,row)
![Pat Last Name] = ListBox.Column(2,row)
![Pat First Nme] = ListBox.Column(3,row)
![EmployeeID] = lngCurrentEmpID
.Update
Next row
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQmhd2YechKqOuFEgEQJiFACg3qVL0f0FZL2F4nV2HlCBcS BiNmsAn0JU
XkKJe9ordHI7iAGNIlFZaNEZ
=wzy9
-----END PGP SIGNATURE-----
Lisa wrote:
Hello,

I am new to using recordsets, and i am completly stuck with this one.
I am trying to use a multi select list box to write records to a table.
Something in my code is causing the same record to be written over and
over, for example if I have 4 items selected, it writes the same item 4
times in my table.

Here is my code.

Private Sub OrderRxs_Click()

Dim db As DAO.Database
Dim rstOrder As DAO.Recordset
Dim varItem As Variant

OrderGroupNumber = DMax("[OrderGroupID]", "[RefillOrders_Info]") +
1

If ListBox.ItemsSelected.Count = 0 Then
MsgBox "You must select Rx Numbers to Order.", vbExclamation +
vbOKOnly
Exit Sub
Else
bytChoice1 = MsgBox("Order " & ListBox.ItemsSelected.Count & "
Rx(s) for Paitent?", vbInformation + vbYesNo)
If bytChoice1 = vbNo Then
Exit Sub
End If
If bytChoice1 = vbYes Then
Set db = CurrentDb
Set rstOrder = db.OpenRecordset("RefillOrders_Info")

With rstOrder
For Each varItem In ListBox.ItemsSelected
.AddNew
![OrderGroupID] = OrderGroupNumber
![Patient Code] = ListBox.Column(1)
![Pat Last Name] = ListBox.Column(2)
![Pat First Nme] = ListBox.Column(3)
![EmployeeID] = lngCurrentEmpID
.Update
Next varItem
End With
End If
End If

rstOrder.Close
Set rstOrder = Nothing
db.Close
Set db = Nothing
End Sub

I would love some help with my mess!!!

thanks,
Lisa

Nov 13 '05 #5

P: n/a
I came to the right place for help! That worked perfectly. If I could
wrap my arms around you all I would give you a big hug :)

Thanks so much for your expertise!
Lisa

Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.