Solved

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

Posted on 2014-12-10
8
300 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

687 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