Solved

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

Posted on 2014-12-10
8
264 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 119

Expert Comment

by:Rey Obrero
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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now