Two Listbox Problem Revisited

[April15Hater]

Hi Guys,

I'm have two listboxes: lstUnassigned and lstAssigned that (to quote LPurvis) "Assigns related records - selecting from 'Unassigned' listbox to 'Assigned' listbox entries."

The problem I'm having is when I click on one to go from unassigned to assigned, it moves it over to assigned, but it fails to take it away from the unassigned. Here's where it get's wierd. When I close and reopen the form, the listboxes requery, and it shows everything correctly. This sounds like the textbook example of leaving out me.objectname.requery, I know. The thing is I've checked my code a million times. In fact, I stepped through it twice just to be sure. I can't figure out for the life of me why this isn't updating properly. My next option is to just have it close and reopen the form everytime, but that's a really poor solution.

Any suggestions? Code posted below.

Thanks,

Joe
lstUnassigned:

SELECT tblProductionTracking.ProductionTrackingID, tblProductionTracking.ProductionID, tblProductionTracking.FunctionTrackingID, tblProductionTracking.TrackingNumber
FROM tblProductionTracking
WHERE (((tblProductionTracking.ProductionID)<>[Forms]![frmProductionStep3b]![cboProductionID]) AND ((tblProductionTracking.FunctionTrackingID)=[Forms]![frmProductionStep3b]![cboFunctionTrack]) AND ((tblProductionTracking.TrackingNumber) Not In (SELECT tblProductionTracking.TrackingNumber FROM tblProductionTracking WHERE (((tblProductionTracking.ProductionID)<>[forms]![frmProductionStep3b]![txtProductionID]) AND ((tblProductionTracking.FunctionTrackingID)=[forms]![frmProductionStep3b]![cboFunctionTrack])))));

lstassigned

SELECT tblProductionTracking.TrackingNumber, tblProductionTracking.ProductionTrackingID, tblProductionTracking.ProductionID, tblProductionTracking.FunctionTrackingID
FROM tblProductionTracking
WHERE (((tblProductionTracking.ProductionID)=[forms]![frmProductionStep3b]![txtProductionID]) AND ((tblProductionTracking.FunctionTrackingID)=[forms]![frmProductionStep3b]![cboFunctionTrack]));

Form VBA

Private Sub lstAssigned_DblClick(Cancel As Integer)
Dim rsListBox As ADODB.Recordset
Set rsListBox = New ADODB.Recordset
With rsListBox
    .ActiveConnection = CurrentProject.Connection
    .Source = "SELECT * FROM tblProductionTracking WHERE ProductionTrackingID = " & Me.lstAssigned.Column(1)
    .CursorType = adOpenDynamic
    .LockType = adLockOptimistic
    .Open
    .Delete
    .Update
    .Close
End With
Me.lstUnassigned.Requery
Me.lstAssigned.Requery
End Sub

Private Sub lstUnassigned_DblClick(Cancel As Integer)
Dim rsListBox As ADODB.Recordset
Set rsListBox = New ADODB.Recordset
With rsListBox
    .ActiveConnection = CurrentProject.Connection
    .Source = "SELECT * FROM tblProductionTracking"
    .CursorType = adOpenDynamic
    .LockType = adLockOptimistic
    .Open
    .AddNew
    !ProductionID = Me.txtProductionID.Value
    !FunctionTrackingID = Me.cboFunctionTrack.Value
    !TrackingNumber = Me.lstUnassigned.Column(3)
    .Update
    .Close
End With
Me.lstAssigned.Requery
Me.lstUnassigned.Requery
End Sub

 

[DCrake]

What I would be tempted to do is to simply have one table with all the available listbox options and a boolean flag to denote if the item has been assigned or not.

The left hand list box would use Where False and the right hand list box use Where True

As you move one from the left to the right use an update sql statement to change the flag from false to true and vice versa. Then reset the rowsource after each movement.

I have a small demo I will dig out and post in the samples database section.

David

 

[April15Hater]

Hey, that's not a bad idea. I'd really appreciate that.

 

[LPurvis]

Not to pour scorn on the suggestion, but I always voice my concerns over that scenario.
When you're using a boolean flag to indicate selection in the source list table then your immediate and permanent problem is multi-use.

In the example which, I believe, you're looking at - the issue was that a choice of selections can be made per parent record. So there is at least one foreign key at work. So we're not just dealing with a case of whether an item is selected at all or not - but whether they're selected for a given parent record.
(For example one parent record could have selected the first half of options and another parent record select the second half. If we just checked items off in the source table as selected then that would indicate them all as selected and hence nothing left to select).

So naturally the inclusion of a parent affects the concept.

Equally if you're selecting items, perhaps as a user chooses the documents they want to print out, then this is often suggested to be handled by the checkbox next to a selection option - and just update them all back to false when you're done.
But that's when multi-user issues come in. You're assuming (hoping) that no other user is currently making selections at the same time. Otherwise one is selecting or unselecting items that another user is potentially doing the opposite (and seeing the confusing effects of the other user's actions).
That's why a local selection table which stores Foreign Keys (and optionsally a "selected" boolean field) maintains that multi-user option. (Assuming that you're not sharing a front end too! :-s)

As for your problem with your current implementation, it's hard to offer a suggestion without a) spending an inordinate amount of time figuring out what your objects are in the code above or b) seeing the app itself.

FWIW I guess you've implemented quite a lot of this yourself? (As I don't recall making any use of recordsets in the standard example).
<shrug>

Cheers.

 

[DCrake]

I have just posted an example in the sample databases that handles the issue of parent records. There are tow forms one with and one without a parent record. With regard to multi user conflicts the base selection table would be a local table especially for the problem you discussed. I have found that with any forms I have distributed both single and multi user no issues arose. The only issue was syncronising the local table to the linked table. The local table needs to be a clone of the linked table so when the form is opened it checks to see if it needs updating.

David

 

[April15Hater]

Hi Leigh,

I actually was on the right track to begin with, then I saw your example, and it got me the rest of the way...so I thought. But the recordsets are the leftovers of the original code and basically subsidize your example's append and delete queries. That wouldn't cause a problem, would it?

I do see why it's a bad idea to do that, and I had actually begun to write a post that questions exactly what you said, but you beat me to it.

Being pragmatic, I've given up the idea that I'm going to be able to keep the BE 100% confidential. We try not to let it get out for the sake of it not getting into the hands of outside vendors, contractors, etc.. So I'm just going to not post it directly on the internet. That being said, I can send you the whole thing if you PM your email.

Thanks!

 

[Banana]

Just to answer Leigh's last question about recordset; I did made generous use of (for lack of better term) "transferring listboxes" and in my various evolution, I found recordsets to be easier to work with.

But as Leigh alluded to, it's important to consider what is being modeled. In most cases where I implement such tool, it's usually to model a many-many relationship. I *think* this is what April15Hater is doing as well. However, it's usually easiest to work directly with listbox's recordset. It's so much tidier to me.

To help illustrate, suppose I had a a database where I wanted to assign employees to certain team. A team can have many employees, and an employee can be on more than one teams (actually, the last statement isn't strictly necessary for this to work, but it will become clear further on).

Therefore, the form's source should be derived from Team table. The Unassigned listbox, to use April15Hater's convention, should be from Employees table (e.g. listing all Employees in the firm). Thus the second listbox's source should be the junction table joining Employee to Team. April15Hater already has done this with his Assigned Listbox's rowsource (the WHERE criteria filtering to show only what was assigned for this particular record).

Now to add an employee to the team, I would just reference the listbox's recordset and avoid the whole requery mess.

Pseudo-code: (I can never remember the correct collection to refer for multiple selection for a listbox, so that may have to be double checked)

Private Sub AddNew_Click()

Dim rs As DAO.Recordset
Dim var as Variant

Set rs = Me.lstAssigned.Recordset

With rs
  For Each var in Me.lstUnAssigned.ItemSelected
    .Addnew
    .Fields(TeamKey) = Me.TeamKey
    .Fields(EmployeeKey) = var
    .Update
  Next
End With

End Sub

Now if we only wanted to assign one employee to exactly one team, we'd just add one more line after the update loop to requery the UnAssigned listbox, which should have its criteria set to exclude the employees already selected.

For unassigning, we just reverse the process, deleting the row from the lstAssigned's recordset (which happens to be the junction table).

I hope this helps some...

PS: To be clear, for some reasons, I've never considered using the transferring listboxes for cases of merely making selection (say to select which reports I want to print) as I'd just content myself with a multi-select listbox. Transferring listbox would be an unnecessary work in that scenario (unless the client of course specifically wants this, then who am I to argue?)

 

[gemma-the-husky]

i do exactly the same as DC - I have left and right arrow/buttons to add/subtract all selected items

as long as the queries for the left and right list boxes are correct, i cant see how it wont work.

the other thing is it looks slick, and works well as far as users are concerned

 

[DCrake]

Thanks Gemma, I'm glad somebody's on my side. If you look at Access's own wizards they use this functionality, so who are we to mock Microsoft (tee hee). Using multi select is fine but making sure you have the correct items before you transfer over can be cumbersome, especially if you have long lists.

David

 

[LPurvis]

Lots to reply to.

David's
>> The local table needs to be a clone of the linked table so when the form is opened it checks to see if it needs updating.
Copying the table locally is one option. Though that's usually reserved for client server applications where repetative data transfer of a list is considered wasteful of precious resources. It can go the other way of course, copying an entire table over every time is a hit compared to locally holding FK's that are the PK's that have been selected.

April15Hater's
>> That wouldn't cause a problem, would it?
Nope.
>> I can send you the whole thing if you PM your email.
While I don't mind looking at a file if it's going to reasonably help obtain the solution quicker than an endless back and forth in the thread, receiving it via email kinda stops anyone else joining in so is a bit trickier. A temporary online location might be an idea? (That's why I keep my examples under my own domain rather than in an examples forum - I can pull them or update them at will).

Banana's
>> I've never considered using the transferring listboxes for cases of merely making selection
I wouldn't look to it either for the most part. It was mainly a commentary on the concept of the local table which holds a boolean field for "selection".
Apart from that - I don't have much to say. The example I've already offered is there and not much different from what's being said I suspect.
(The reason I made the List Select example MDB a couple of years ago was to lessen the extent to which I had to talk about it anymore ;-).

Gemma's
>> as long as the queries for the left and right list boxes are correct, i cant see how it wont work.
Yes I think everyone has the same UI in mind. (Whatever the implementation, standard or otherwise). It's just there is some problem with the queries that April15Hater's has currently implemented.

Cheers.

 

[April15Hater]

I originally had a junction table that was the identical structure as the parent table. But I was having other problems getting the parent and junction table to union, and I was having probs getting the junction to update the parent accurately, so I decided to simplify things and take it out.

 

[gemma-the-husky]

having said that i re-looked at my code

in some cases i do actually have a third table, but this is really to manage many to many links

eg - one use of this i have is to manage depots - we have about 15 depots, but not all can accept inter-depot transfers - so we have a link table to hold the permitted links

in this case in the left list we have (for a given depot) unlinked depots, and in the right pane (linked depots) and the arrow buttons, append/remove the selected items to the jjunction table, and refresh the lists

another use of this (this time without the junction table) is to select loads for delivery to add to a particular route.

On the other hand I do agree with Leigh, that it is possible that users doing the same operation simultaneously could interfere with one another, (eg selecting invoices for payment) and where this is critical, I use some locking, or flagging mechanism to prevent multiple users undertaking the same activity.


code - heres the code i use on the add button for manage the linked depots

at the time i wrote it i wasnt very familiar at all with using sql directly, but was comfortable with queries and recordsets - so i added the selected records into a temp table, and then used a stored query to update these to the main database - now i would have done all this directly with an sql statement, without using the temp table

so now i would do this

Private Sub btnAdd_Click()
   Dim ctl As control
   dim sqlstrg as string
   Dim varItm As Variant

[COLOR="Red"]'we have clicked one or more items in the unselected list[/COLOR]
    msgoff

    Set ctl = Me!lstUnlinked
    
    For Each varItm In lstUnlinked.ItemsSelected

[COLOR="red"]'the base depot is the one wew are using - either a field on the form, or a global var, or whatever[/COLOR]
       'the basedepot = Me!txtdepotid

[COLOR="red"]'and the linked depot is the item in the list box[/COLOR]
       'the linkeddepot = ctl.ItemData(varItm)  'the bound column

[COLOR="Green"]'get this string correct[/COLOR]
       sqlstrg = "insert Into etc"

[COLOR="Red"]'and execute it[/COLOR]
       currentdb.execute sqlstrg 

    Next varItm
    

[COLOR="red"]'now requery the two list boxes[/COLOR]
    lstUnlinked.Requery
    lstLinked.Requery
    
    Set ctl = Nothing
 End Sub

 

[Banana]

April15Hater, how is the form, unassigned listbox and assigned listbox related to tables then?

As I've pointed out, it only really make sense when you have a many-many relationship (or at least one-many relationship in case where you want to assign only one entity to one group, though I've not actually implemented that myself). The assigned listbox, IMO, simply has to be derived from the junction table that lists all keys for each potential entity with the group key as the criteria.

 

[April15Hater]

Leigh, I'm going to post it to our ftp site, I just have to get an account made so I can upload it.

Gemma- now i would have done all this directly with an sql statement, without using the temp table
That's what I think I'm trying to do. The temp table was my first instinct (and I was rather proud of my creativity), but on the whole found it problematic; although that could have been a result of me being an amateur.

Banana-
Not sure I understand your question, but I'll take a stab- Basicall the rowsource of the listboxes query the tables. The lstUnassigned uses a NOT IN clause to remove any of the values shown in lstAssigned.

 

[April15Hater]

I could have sworn i posted this, but I don't see it, oh well, here it is:

ftp://ftp.cceservices.com
user-access
pass-temp09

Security should give Admin full rights.

The form in question is frmProductionStep3b. It can only be opened with an openarg usually passed from frmProductionStep3a. If you select Solomon, Joe for the Contractor in frmStep3a, some test values should be entered in there.

 

[April15Hater]

I got it!! Sigh...*headdesk* As it turns out there was a residual line of code on my form open that updates the rowsource. So all along my SQL was right, but VBA was changing it without me knowing.

What I don't understand is when I went back to edit the rowsource in design view it was unchanged and didn't reflect the extraneous line of code. Doesn't that normally update itself when you go back to look at it in design view?

 

[LPurvis]

Glad you found it.

I'd actually downloaded your mdbs, relinked and upon opening the form had source errors from an unrelated control and then code requiring a value to be passed in OpenArgs.
No time for battling with such things - it got left again while I continued with work. :-s
Sorry, but that will always be the case unless the example is straight forward to get in to for diagnosis. (I look at example MDBs to save time in threads, whereas some folks feel it's wrong to even examine files - as that's what clients pay them for... Personally I'm all for being efficient. It's time that my clients pay me for. ;-)

As to your issue here, are you saying you were surprised that the rowsource in design view was as you'd had it last? That the code re-assignment hadn't changed anything? (Thats as it should be, runtime property assignments persist during runtime. It's possible to get some common ones to persist, such as Filter, Orderby etc. but in general not. Bug oddities aside

Cheers.

 

[April15Hater]

I completely understand, and I'm grateful you would even consider looking at it for me.

As for the reassignment, the only reason I'm surprised is because I come from the school of Excel VBA where the code generally changes things for good. I was just ignorant to the fact that runtime assignments persist during runtime like you explained.

With regard to the source errors, I think that was another issue in the scheme of things. Just out of curiosity, was it txtProductionID that was causing the hangup because oddly enough it doesn't do it on my end, whcih is why I just now caught it. The openargs I knew about and mentioned that it had to be opened using Step3a.

That being said, it feels good knowing that I figured it out by myself. For the most part, I try to figure things out for myself so that I learn better. In fact, the PM I sent was more about trying to understand your example than get an answer for mine (even though I was on the completely wrong track). The diagnosing process really helps build up self esteem and trust in my programming skills, especially on brain rackers like this one.

Thanks again for your help guys!

Joe