Double-clicking a record in Listbox 1 moves it to Listbox 2

I have a form with two List Boxes (AvailableRoles and AssignedRoles).  Both are unbound.  

List Boxes
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_TYPE] AS Type, [qry_Role_Select1].[ROLE_DESC] 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")
    Me.JobTitle2.SetFocus
    Exit Sub
End If

role1_RST.MoveFirst
Do While (Not role1_RST.EOF)

    If (role1_RST![ID] = [AssignedRoles].Column(0)) Then
        Exit Do
    End If
    role1_RST.MoveNext
    
    If (role1_RST.EOF = True) Then

        With role1_RST
            .AddNew
            ![ID] = [AssignedRoles].Column(0)
            ![Role] = [AssignedRoles].Column(1)
            ![ROLE_DESC] = [AssignedRoles].Column(3)
            ![ROLE_TYPE] = [AssignedRoles].Column(2)
            ![ActDel] = [AssignedRoles].Column(4)
            .Update
            .Bookmark = .LastModified
        End With
    End If
Loop

role2_RST.MoveFirst
Do While (Not role2_RST.EOF)

    If (role2_RST![ID] = [AssignedRoles].Column(0)) Then
        role2_RST.Delete
    End If
    role2_RST.MoveNext
Loop

Me.AvailableRoles.Requery
Me.AssignedRoles.Requery

End Sub

Open in new window


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!
jmccloskAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
why use two tables,
add another field YesNo field to table1, name the field Selected
then use this query as the rowsource of listbox1

SELECT [qry_Role_Select1].[ID], [qry_Role_Select1].[ROLE] AS Role, [qry_Role_Select1].[ROLE_TYPE] AS Type, [qry_Role_Select1].[ROLE_DESC] AS Description, [qry_Role_Select1].[ACTDEL] FROM qry_Role_Select1 ORDER BY [ROLE_TYPE], [ROLE] Where Selected=0

use this query as the rowsource of listbox2

SELECT [qry_Role_Select1].[ID], [qry_Role_Select1].[ROLE] AS Role, [qry_Role_Select1].[ROLE_TYPE] AS Type, [qry_Role_Select1].[ROLE_DESC] AS Description, [qry_Role_Select1].[ACTDEL] FROM qry_Role_Select1 ORDER BY [ROLE_TYPE], [ROLE] Where Selected=-1


in the doubleclick event update the recored

currentdb.execute "update tbl_Roles_Temp set Selected=-1 where [ID]=" & me.listbox1

'reuqery listbox 2

me.listbox2.requery


got the idea?
0
Jeffrey CoachmanMIS LiasonCommented:
What you are asking for here is typically done with a  Many-to-many relationship.
To make things simple, MS created "Multi-valued fields".

Multivalued fields have drawbacks, but if you understand them, these types of field can be helpful.

See this great link:
https://support.office.com/en-US/Article/Guide-to-multivalued-fields-7c2fd644-3771-48e4-b6dc-6de9bebbec31?ui=en-US&rs=en-US&ad=US

But lets see if an expert can help you with this as you have stated it...
0
Jeffrey CoachmanMIS LiasonCommented:
...It looks like Rey can get you what you need, ...you can just take my post as informational and not to be considered for any points...
;-)

Jeff
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Nick67Commented:
Ah,
Screw the double-clicking.
You know the field picker when you create a listbox?
Here's the roll-you-own.

Sample attached

Rey's got the idea down cold though!
MoveEntries.mdb
0
Helen FeddemaCommented:
See my Access Archon articles on this topic (with sample databases).  #71 is for single-select listboxes, and #72 is for multi-select listboxes.  Here are the links for them:

http://www.helenfeddema.com/Files/accarch71.zip
http://www.helenfeddema.com/Files/accarch72.zip

and here is a screen shot of the form with linked multiselect listboxes:

Paired multiselect listboxes
The databases are for Access 2000, but they work with higher versions as well.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Nick67Commented:
@Helen_Feddema

That looks like the one I posted, only snazzier, with nice pictures on the buttons instead of --> and <--
:)

Nick67
0
Helen FeddemaCommented:
That is an old database -- about 14 years old, in fact!  I have a more recent article which demonstrates something different -- filtering a datasheet by selections in one or both of two listboxes.  This is the link for it:

http://www.helenfeddema.com/Files/accarch171.zip

Here is the form:

 Filtering by Listboxes
This database features the ever-useful Northwind data from long ago, before Microsoft removed it, presumably for legal reasons.
0
jmccloskAuthor Commented:
My apologies for the lag time in following up.  These were all great posts and I thank everyone for their time to respond.  The posts by Helen_Feddema seem to have the closest solutions to what I was looking for.  Some great stuff here!  Thanks again!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.