I have a form with two List Boxes (AvailableRoles and AssignedRoles). Both are unbound.
Both are populated when the form is opened by their own queries (for the second query it is "Select2"):
SELECT [qry_Role_Select1].[ID], [qry_Role_Select1].[ROLE] AS Role, [qry_Role_Select1].[ROLE_T
YPE] AS Type, [qry_Role_Select1].[ROLE_D
ESC] AS Description, [qry_Role_Select1].[ACTDEL
] FROM qry_Role_Select1 ORDER BY [ROLE_TYPE], [ROLE];
Each query is connected to a table (tbl_Roles_Temp and tbl_Roles_Temp2, respectively).
tbl_Roles_Temp table starts off with all of the available options. And, the tbl_Roles_Temp2 table starts off empty. When the user double clicks on an option in the AvailableRoles list box, the following code should execute:
Private Sub AvailableRoles_DblClick(Cancel As Integer)
'declare recordset name
Dim DB As Database
Dim role1_RST As Recordset
Dim role2_RST As Recordset
'assign table to recordset
Set DB = CurrentDb
Set role1_RST = DB.OpenRecordset("tbl_Roles_Temp", dbOpenDynaset)
Set role2_RST = DB.OpenRecordset("tbl_Roles_Temp2", dbOpenDynaset)
Dim msg As Integer
If (IsNull(Me.JobTitle2) = True) Then
msg = MsgBox("Please select a 'User Job Title/Position' first.", vbOKOnly, "Select Title/Position")
Do While (Not role1_RST.EOF)
If (role1_RST![ID] = [AssignedRoles].Column(0)) Then
If (role1_RST.EOF = True) Then
![ID] = [AssignedRoles].Column(0)
![Role] = [AssignedRoles].Column(1)
![ROLE_DESC] = [AssignedRoles].Column(3)
![ROLE_TYPE] = [AssignedRoles].Column(2)
![ActDel] = [AssignedRoles].Column(4)
.Bookmark = .LastModified
Do While (Not role2_RST.EOF)
If (role2_RST![ID] = [AssignedRoles].Column(0)) Then
What I want this to do behind the scenes is to move the option selected out of tbl_Roles_Temp (using AvailableRoles list box) and put it in to tbl_Roles_Temp2. Then, I want the two list boxes to refresh and show the item gone from AvailableRoles and now showing in AssignedRoles.
The problem is that when I double click on an option in AvailableRoles, Access completely locks and I have to force close the program and re-open it.
I actually had something very similar to this working in a database I did about a year ago. But, now I cannot get it working here. Can anyone show me what I am doing wrong? Thanks!