Moving values between list boxes.

[martinmike2]

Hello,

I am trying to create a list from values I select in another listbox.

CODE:
Option Compare Database

Private Sub cmdFour_Click()
Call removeAllFromSelected
End Sub


Private Sub cmdOne_Click()
Call addToSelected
End Sub

Private Sub cmdThree_Click()
Call removeFromSelected
End Sub

Private Sub cmdTwo_Click()
Call addAllToSelected
End Sub
Private Sub Form_Load()
Dim strSql As String
strSql = "Delete * from tblSelected"
DoCmd.SetWarnings (False)
DoCmd.RunSQL strSql
DoCmd.SetWarnings (True)
listRefresh
End Sub
Public Sub removeFromSelected()
'Ensure that primary key is the bound value of the listbox
Dim varItem As Variant
Dim varData As Variant
For Each varItem In Me.lstTwo.ItemsSelected
varData = Me.lstTwo.ItemData(varItem)
Call deleteData(varData, getPrimaryKeyType)
Next varItem
Call listRefresh
End Sub
Public Sub addToSelected()
'Ensure that primary key is the bound value of the listbox
Dim varItem As Variant
Dim varData As Variant
For Each varItem In Me.lstOne.ItemsSelected
varData = Me.lstOne.ItemData(varItem)
Call insertData(varData, getPrimaryKeyType)
Next varItem
Call listRefresh
End Sub

Public Function getPrimaryKeyType() As String
Dim rs As DAO.Recordset
Set rs = Me.lstOne.Recordset
Select Case rs.Fields(0).Type
Case 10
getPrimaryKeyType = "Text"
Case 4, 16, 9, 20, 7, 15
getPrimaryKeyType = "Number"
Case Else
MsgBox "Error with primary key. Check that primary key is bound
field of listbox"
End Select
End Function
Public Sub insertData(varData As Variant, primaryKeyType As String)
Dim strSql As String
If primaryKeyType = "Text" Then
strSql = "insert into tblSelected (strFK) values('" & varData &
"')"
Else
strSql = "insert into tblSelected (numFK) values(" & varData &
")"
End If
DoCmd.SetWarnings (False)
DoCmd.RunSQL strSql
DoCmd.SetWarnings (True)

End Sub
Public Sub addAllToSelected()
Dim lstItem As Integer
For lstItem = 0 To lstOne.ListCount - 1
lstOne.Selected(lstItem) = True
Next lstItem
addToSelected
End Sub
Public Sub removeAllFromSelected()
Dim lstItem As Integer
For lstItem = 0 To lstTwo.ListCount - 1
lstTwo.Selected(lstItem) = True
Next lstItem
removeFromSelected
End Sub

Public Sub listRefresh()
On Error Resume Next
lstOne.RowSource = lstOne.RowSource
lstTwo.RowSource = lstTwo.RowSource
End Sub
Public Sub deleteData(varData As Variant, primaryKeyType As String)
Dim strSql As String
If primaryKeyType = "Text" Then
strSql = "delete * from tblSelected where strFK = '" & varData &
"'"
Else
strSql = "delete * from tblSelected where numFK = " & varData
End If
DoCmd.SetWarnings (False)
DoCmd.RunSQL strSql
DoCmd.SetWarnings (True)
End Sub
'---------------------------------------------------------------------------------------------------------------------------------------------

Now, this fails on load, keeps asking for the value of
tblSelected.numFK.

I was thinking of maybe using a page for this particular job function
instead of a form, but I dont know what is the most efficient way to
accomplish this.

What needs to happen is this:

lstOne rowsource = "SELECT qryDETLISTER3824A.exp,
qryDETLISTER3824A.title, qryDETLISTER3824A.r_pnec,
qryDETLISTER3824A.bsc FROM qryDETLISTER3824A WHERE
qryDETLISTER3824A.bsc NOT IN (tblSelected.numFK) ORDER BY [exp]; "

lstTwo rowsource = "SELECT qryDETLISTER3824A.exp,
qryDETLISTER3824A.title, qryDETLISTER3824A.r_pnec,
qryDETLISTER3824A.bsc FROM qryDETLISTER3824A WHERE
qryDETLISTER3824A.bsc IN (tblSelected.numFK) ORDER BY [exp]; "

List1 pulls up a list of records, and List2 remains empty. Pressing
the various command buttons moves the selected values back and forth
between Listboxes.