List box selection and requery

[Guest]

Hi all

I have an MS Access form with the following objects

ListBox with visible set to false, populated by a query and on timer event
set to requery at 5000 milliseconds.
A button which makes the ListBox visible and deselects any selected items
A button which sets ListBox visible back to false.

Code snippet for button one:

Private Sub btnAssignSingle_Click()

Dim i As Integer

Me!lstEmptySlotsSingle.Visible = True

For i = 0 To lstEmptySlotsSingle.ListCount - 1
Me.lstEmptySlotsSingle.Selected(i) = False
Next

End Sub

Code snippet for button two as follows

Private Sub btnCancel_Click()

Me!lstEmptySlotsSingle.Visible = False

End Sub

Code snippet for ListBox on timer as follows

Private Sub Form_Timer()

Me!lstEmptySlotsSingle.Requery

End Sub

My problem : well button one makes the list box visible as required. My
user selects an item ..... then decides not to proceed with the action on
that selection and clicks button two. When the user clicks button one again
the list box becomes visible, the previously selected item is de-selected as
required by the code ...... BUT ..... at the next on timer event when the
list box is requeried the previously de-selected item becomes selected again!!

Where am i going wrong with this. I'd appreciate any help..... and hope I
have explained it properly.

WinXP and MS Office 2000

Regards

Michael Bond

 

[Guest]

It is the requery.

You can avoid this problem by altering the the cancel button and the timer
event.

1) Make sure that nothing is selected when you press cancel

Private Sub btnCancel_Click()
Dim i As Integer

Me!lstEmptySlotsSingle.Visible = False
For i = 0 To lstEmptySlotsSingle.ListCount - 1
Me.lstEmptySlotsSingle.Selected(i) = False
Next

End Sub

2) take care that the timer event remembers it's state. If nothing was
selected before, deselect everything.

Private Sub Form_Timer()
Dim i As Integer

If lstEmptySlotsSingle.ItemsSelected.Count = 0 Then
Me!lstEmptySlotsSingle.Requery
For i = 0 To lstEmptySlotsSingle.ListCount - 1
Me.lstEmptySlotsSingle.Selected(i) = False
Next
Else
Me!lstEmptySlotsSingle.Requery
End If
End Sub


Kind Regards
Damiaan

 

[Guest]

Damiann

thanks for your reply ..... I had considered your method but choose not to
use it as it has a effect i need to avoid.

User clicks button one which makes the list box visible and then selects an
item in the list box.

The operation does not end at this point. Having selected an item they have
several other options, all controlled by various other buttons etc. which
must be performed on the selection they choose. Using this method, If they
fail to complete those opperations in the 5 seconds set in the requery "on
timer" event the selection they have made will be deseleted and the other
operations will resul;t in an error.... erm....some of my potential users are
not keyboard wizards and i don't want to give them more problems than they
already have.

i could set the on timer event greater than 5 seconds but then run the risk
that another user on another PC will already have made an alteration to
thesame record they have chosen, with all sorts of consequenses

Sorry I should of course have explained that other operations were to be
performed on the selection. I need to be sure that the deselection only
applies in the moment that the list box is made visible again and not during
the course of the "on timer" event. Am I making sense here....?

Any other ideas ... or input from other users?

Regards

Michael Bond

 

[Guest]

Dear Michael

It seems indeed very complicated. Maybe other users might help.

If i understand you well it should work with my example.
That is where the line
If lstEmptySlotsSingle.ItemsSelected.Count = 0 Then
comes in.
This check should make sure the behavior is the same as you proposed.

 

[John Nurick]

PMFJI, but two things come to mind:

1) Why not do away with the timer event and instead requery the listbox
in the button_click event before making it visible?

2) To prevent other users updating the record in question you should
think about locking it - e.g. by editing it in a bound form or by using
recordset operations...

 

[Guest]

John

Thanks for this ..... but I think I'm at fault in my explanation of what I'm
doing with my objects.

The database is accessible by multi-users. The list box contains a list of
shift hours available to be worked on overtime. User A may have it open,
select a slot they want to work and then close it (the opening and closing is
in fact "visible true" and "visible false"). The on timer event requery is
required to deal with the possibility that user B eleswhere also has the list
box open and is not aware that one of the slots he/she is viewing has already
been selected and taken by User A. I'm afraid the "politics" of the situation
is that the Users must, at all times, be presented with a full list of the
slots that are available and that locking records would only serve to give
the impression that slots were available which were not.

The updating of the table containing the slots is achieved through recordset
operations with one of the action buttons on the form after the slot is
chosen by the User. It's ensuring they always see the correct list of
available slots that is causing my problems.

Any further ideas? .... anyone?

Regards

Michael Bond

 

[Guest]

For info to any one interested ........

A solution was eventually forthcoming from Roy-Vidar via module and vba
forum on www.access-programmers.co.uk as follows

Remove the "for i = 0 ........
.........next......" section of code and then add the following code to the
btnCancel

Me!lstEmptySlotsSingle.SetFocus
Me!lstEmptySlotsSingle.listIndex = -1

Regards

Michael Bond

 

[Andreas]

How many items in the listbox?
If you are requerying every 5 seconds (which presumably happens
regardless of visibility of listbox), your systems administrator will do
unspeakable things to you when he/she finds out ;-)

May be just do a requery when the listbox is made visible, or at least
toggle the TimerInterval on making it visible/invisible.

Regards,
Andreas

 

[John Nurick]

As I understand it the process from your user's point of view is

1) click a button
2) select a time slot from the listbox that appears
3) select relevant options using various other controls
4) click another button to actually book that slot.

During the whole of this process you have the listbox requerying itself
approximately every 5 seconds in order to ensure that the user always
sees an up-to-date list of available slots.

I feel this design is fundamentally flawed because it doesn't update the
underlying table until step 4. This means that User A can select time
slot X and start on step 3; meanwhile users B and C can do the same; and
whichever gets to step 4 first gets the time slot while the others get
frustrated.

IMHO it would be much better to lock time slot X as soon as user A
selects it, and release it if user A cancels the operation or changes
his/her mind.

 

[Guest]

Andreas

thanks .... you are on the ball

list box contains about 250 to 300 items at the start of the process and
gradually after choices made by user goes down to about 100.

The form, as you suggest, does have an "if visible" clause on the "on timer"
event. additionally the databse is not in frequent or continuous use so
network traffic effect is hardly measureable ......

anyway ..... what are system administrators for but to complain (and if you
are one then I've just lost a good source of advice/help in the future lol)

Thanks for the advice

Michael

p.s. a lot of water has gone under the bridge since first posting this and I
now know why the listbox was behaving the ways it was ...... it was bound to
a column which was not indexed and did not have "unique values" thus causing
the listindex value to become very confused in some operations. The final and
ultimate soultion was to bind the list box to the primary index column
instead, even though I did not want that to appear in the list box.

 

[Guest]

John

I have to agree with your assessment. My expertise has always been in Excel
and VBA and this is my first dabble in Access. I am now encountering exactly
the problem you anticipate .... so dare I be bold and ask what is the code to
lock the record which I would guess I attach to the "on click" event of the
list box.

Appreciate the interest your showing in helping me achieve my aim