transfer listbox data

[alex]

Using Access ’03…

I’m attempting to create a paired listbox and am having some
difficulty with the code.

This is the code that I’m placing behind the onclick event of the
button that transfers one record from the lbsource to the
lbdestination.

I’m getting a Compile error: Variable not defined, however, on
(itm). I don’t understand if I need to define it somewhere.
Apparently I do.

Should it be something like:
Dim itm as …

Here’s the code:

Dim rsSource As DAO.Recordset
Dim rsDestination As DAO.Recordset
Dim theBug As String

If Me.lbSource.ItemsSelected.Count = 0 Then
Beep
Exit Sub
End If

Set rsSource = CurrentDb.OpenRecordset("SourceTable", _
dbOpenDynaset)
Set rsDestination = _
CurrentDb.OpenRecordset("DestinationTable", _
dbOpenDynaset)

‘the following code produces a compile error on “itm”
For Each itm In Me.lbSource.ItemsSelected
theBug = Me.lbSource.ItemData(0)

rsDestination.AddNew
rsDestination!Description = Me.lbSource.ItemData(itm)
rsDestination.Update

rsSource.FindFirst "Description = '" & _
Me.lbSource.ItemData(itm) & "'"
rsSource.Edit
rsSource.Delete

Next itm

Me.lbSource.Requery
Me.lbDestination.Requery

‘end of code

Can anyone tell me what I’m doing wrong?
Thanks,
alex

 

[Steve Sanford]

Yes, you need to declare "itm" as a variant type variable. Try this:

Dim rsSource As DAO.Recordset
Dim rsDestination As DAO.Recordset
Dim theBug As String

Dim itm As Variant '<<<< ADD This



HTH

 

[alex]

Yes, you need to declare "itm" as a variant type variable. Try this:

Dim rsSource As DAO.Recordset
Dim rsDestination As DAO.Recordset
Dim theBug As String

Dim itm As Variant   '<<<< ADD This

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


















- Show quoted text -

Steve, that worked perfectly. Thanks.
Can you tell from the code above how to set the focus back to the
source listbox so that the next value is automatically selected. I
can set the focus, but a value is not highlighted.
alex

 

[Steve Sanford]

Alex,

When you requery the list box, it should unselect everything.

try this:

Dim rsSource As DAO.Recordset
Dim rsDestination As DAO.Recordset
Dim theBug As String

<snip>

Me.lbDestination.Requery
Me.lbSource.Requery

Me.lbSource.setfocus
Me.lbSource.Selected(0) = True

‘end of code


I think this should select the first row of the listbox.


HTH

 

[ÂÒÊÀ¸¡Éú]

ΪʲôûÓÐÖÐÎÄ£¿£¿£¿

Yes, you need to declare "itm" as a variant type variable. Try this:

Dim rsSource As DAO.Recordset
Dim rsDestination As DAO.Recordset
Dim theBug As String

Dim itm As Variant '<<<< ADD This



HTH