Selecting record from a table/form in one ListBox and displaying it in a ListBox in another table/form

1
votes

I'm having some trouble writing code for a button to add a selected record from one listbox(ListBoxForm2), to another listbox(ListBoxForm1). I've got a working button to remove a selected record from ListBoxForm1, which I'll include below. If anyone is able to help it would be greatly appreciated :-)

Form1 has a ListBox which reads records from Table1 (to which Table2 is inner joined) and displays them if the ID in Table1 exists in Table2. They can be selected and deleted using a button:

Private Sub RemoveMember_Callouts_Click()

Dim lngID As Long
Dim strSQL As String


If IsNull(ListBoxForm1) Then
    Exit Sub
End If

'get selected record's ID
lngID = ListBoxForm1.Value
strSQL = "DELETE * FROM [Table2] WHERE Table2ID = " & lngID
CurrentDb.Execute strSQL

'refresh the list
ListBoxForm1.Requery

End Sub

However I'm struggling when it comes to programmatically adding a selected record in Table2(Form2) to Table1. Could I do similar code to my remove click event? Does anyone have any ideas?

Thanks

1
ms-accessvbams-access-2010

1 Answers

2
votes

Before executing Delete statement. Write a statement to insert record into Table 1 from Table 2.

"INSERT INTO Table1(col1, col2) SELECT col1, col2 FROM Table2 WHERE Table2ID=" & lngID

I hope this will help