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

[Lisa]

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

[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

[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

[jimfortune]

Instead of Column(1), try Column(1, varItem).

James A. Fortune

[MGFoster]

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

[Lisa]

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