Solved

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

Posted on 2014-12-10
8
281 Views
Last Modified: 2014-12-23
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!
0
Comment
Question by:jmcclosk
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40492079
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40492092
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40492107
...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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 26

Expert Comment

by:Nick67
ID: 40492540
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
 
LVL 31

Accepted Solution

by:
Helen_Feddema earned 500 total points
ID: 40497062
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
 
LVL 26

Expert Comment

by:Nick67
ID: 40497072
@Helen_Feddema

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

Nick67
0
 
LVL 31

Assisted Solution

by:Helen_Feddema
Helen_Feddema earned 500 total points
ID: 40500616
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
 

Author Closing Comment

by:jmcclosk
ID: 40514787
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

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question