[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2014-12-10
8
Medium Priority
?
328 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 2000 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 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

649 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