Copy from list to list

[Memento]

Hello Guys,

I'm trying to copy from a listbox to a listbox on a form. The first listbox
contains hardware items (manufacturer, model, type and serial), 4 columns,
based upon a query. The second listbox is bound to a textfield
(coupled_hardware) in a users table, so I can 'couple' hardware items to
users.

However my code doesn't work as supposed to. When i doubleclick on an item,
i only see the columnheads change on the second listbox into the
manufacturer, and nothing gets added to my listbox. I'm not getting any
errors further. The code:

Private Sub lstHardware_DblClick(Cancel As Integer)
Dim strItem As Variant
Dim intCurrentRow As Integer
For intCurrentRow = 0 To lstHardware.ListCount - 1
If lstHardware.Selected(intCurrentRow) Then
strItem = lstHardware.Selected(intCurrentRow)
lstHardwareUser.AddItem (strItem)
End If
Next intCurrentRow
lstHardwareUser.RowSource = ""
lstHardwareUser.RowSource = strItem
End Sub

Thanks in advance,

Memento

 

[Steve Sanford]

You don't "copy from a listbox to a listbox". Most list box row sources are
queries, so to change what is displayed in listbox, the underlying
query/table(s) must be changed.

The AddItem method of a listbox is used to add values to the listbox row
source when the row source type is set to "Value List".

It wouldn't make sense to have the users in a value list, so the row source
must be a query/table. To change what is displayed in the listbox
"lstHardwareUser", you must change the underlying table by editing or adding
a record that meets the criteria of the query.

Remember, if the row source type is Table/Query, you don't "add" anything to
the listbox; you must add/update the underlying table. You can only add an
Item if the row source type is set to "Value List".


I was going to give you an example of the code, but I cannot figure out how
you are determining which user to assign the hardware to. If you have 100
users and each user has 3 pieces of hareware, the list box would have 300
rows.

Maybe a better way would be to have a form in single form view with the user
info on it and the assigned hardware in a subform; a listbox could be used to
assign hardware to the user.


HTH

 

[Memento]

Ok Steve, I see your point. I'll try to clarify my objectives a bit . I
have a table with hardware items and a separate table with users.

The hardware table has a field "User", so - for example - i can add a user
to a specific hardware item - example:

Hardware table
------------------
Manufacturer Model Type User
Dell Dimension 2400 PC "a user gets
filled in here - ID 236"

Users table
---------------
ID Username logonname
236 Who Am I wami

So this related back to my form. One listbox contains simply selected fields
from the hardware table. When i doubleclick an item here, I actually want to
add the userID to the selected hardware item. And when doubleclicking, I also
want that specific hardware item (and only the selected one) added to a
second listbox, so it's very easy to see which hardware items are assigned to
the user.

I hope i've clarified this one a bit. Sometimes this can get pretty complex.

With regards,

 

[Steve Sanford]

The way you explained it, you can have one user per hardware item. To add the
same type of hardware to another user, you have to add the hardware item
again. This is not the way I would set it up.

What you have is two one-to-many tables: Each user can have many hardware
items and one piece on hardware can have many users.

*View from the User table
User Hardware
----- --------------
Jon Dell Dimension 2400 PC
Jon LCD Monitor
Jon InkJet Printer

Joy Dell Dimension 2400 PC
Joy LCD Monitor
Joy HP Si5 LaserJet printer


*View from the Hardware Table
Hardware User
-------------------------- -------
Dell Dimension 2400 PC Jon
Dell Dimension 2400 PC Joy

LCD Monitor Jon
LCD Monitor Joy

InkJet Printer Jon
HP Si5 LaserJet printer Joy


I don't your mdb structure, but the tables would look something like:

Users table
---------------
User_ID - Autonumber (PK)
Username - Text
logonname - Text


Hardware table
------------------
HW_ID - Autonumber (PK)
Manufacturer - Text
Model - Text
HWType (TYPE is a reserved word - shouldn't be used for object names)


HW_USER Table (junction table)
--------------------
HWU_ID - Autonumber (PK)
User_ID_FK - (User PK from User table)
HW_ID_FK - (HW PK from Hardware table)

HTH