Listbox refresh problem?

[systemx]

Hi all,

This may be a tricky one.

I have a listbox (lstResults) with a mouse down event that opens a form. Mouseup closes the form and double click opens yet another form. This serves to allow the user to 'preview' information related to the selected listbox row - prior to opening the record in full, in an unbound form.

I have had some problems getting this to work correctly.

First my code -

This is the event code from the form containing the list box -

Private Sub lstResults_DblClick(Cancel As Integer)

DoCmd.OpenForm "frmPSWork", acNormal, , , , acWindowNormal, Me.lstResults.Value

End Sub

Private Sub lstResults_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)

On Error GoTo MouseDownErr:

DoCmd.OpenForm "frmEnqDetails"

MouseDownErr:
Exit Sub

End Sub

Private Sub lstResults_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)

DoCmd.Close acForm, "frmEnqDetails", acSaveNo

End Sub

Code from form that opens -

Private Sub Form_Open(Cancel As Integer)

On Error GoTo Err_Open:

Dim ADOrs As ADODB.Recordset
Dim strCriteria As String
Dim MyCaption As Variant

'The Search criteria is field ID equalling the enquiry number
strCriteria = "ID = " & Int(Forms!frmPSUpdate!lstResults.Value) & ""
Set ADOrs = New ADODB.Recordset
ADOrs.ActiveConnection = CurrentProject.Connection
ADOrs.Open "tblEnquiries", , adOpenKeyset, adLockOptimistic, adCmdTable

With ADOrs
'find the Record with ID equalling enquiry number
.Find strCriteria
Me.lblDescription.Caption = .Fields("Description")
.Close
End With

MyCaption = "Enquiry number " & Forms!frmPSUpdate!lstResults.Column(2) & " - Submitted on " & Forms!frmPSUpdate!lstResults.Column(4) & ", by " & Forms!frmPSUpdate!lstResults.Column(3) & "."
Me.Caption = MyCaption

Set ADOrs = Nothing

Err_Open:

End Sub

Description of problem:

The value in the list box does not seem to refresh properly. i.e. When the form is first opened, if the user clicks a row in the listbox nothing is displayed on the form that opens. If they click that row again it works. If they then click a different row, the old information is displayed. If they click the row a second time - the correct information is displayed.

Hopefully that made sense. It seems to be a problem with the lstResults.Value updating/refreshing or perhaps the timing of when the event is fired (eg fired prior to the new selection being registered).

Can anyone offer any advice on how to handle this correctly?

Thanks

Robert

 

[systemx]

Bump. I am still desperately in need of assistance with this problem.

I have attached JPGs to provide a better idea of what is happening.

As you can see in the second JPG - the form that opens is clearly displaying the ID number of the first entry in the listbox, even though the second entry is highlighted.

Additionally, when a user clicks in the empty area of the list box - the form opens and fails to close automatically.

Thanks

Robert

 

[gemma-the-husky]

probably to do with using the mousedown and mouseup events

the mousedown occurs BEFORE the click event, and the listbox updatevent, so you are popping up the info form with the wrong data.(ie the old data before the click)

if you click on a row, that row is selected, so why hot use the afterupdate event - the users wil have to close the popup form themselves, but its easier to code

 

[systemx]

Hi gemma-the-husky,

Thanks for the response. I did some tests on it earlier just using a msgbox and figured out that the problem did definately relate to the timing of when the event is called.

I have moved the event to a 'Preview' label so the mousedown event now works without issue, as the user must have selected a listbox value prior to mousedown on the label.

I guess I am frustrated that I cannot present the package exactly as I had intended - I figure there must be a sneaky way around this. If anyone has a solution, I'm all ears.

 

[systemx]

Hi all,

A solution to my problem described above is as follows. While it does not work perfectly, it does work.

A simple piece of code using a global boolean variable -

If gblEnqDesc = False Then
gblEnqDesc = True
Exit Sub
Else
gblEnqDesc = False
DoCmd.OpenForm "frmEnqDetails"
End If

This is attached to the mousedown event. When the user initially clicks on a lstbox record, the variable is false - it is then set to true and nothing happens. On the second click, it is set back to false however the form is opened.

While not a perfect solution, it does force the listbox value update prior to the form opening.

I still have the problem of the user clicking on the white area in the list box - in which case the form opens and does not close (has to be closed manually). I have tried on error coding to force it to close without luck and if I get a result I will post it here.

Thanks

Robert

 

[unclejoe]

Hi systemx,

Your code here doesn't make any sense. You mean the user has to click and hold the mouse button down, then do something with another hand than release the mouse button to close the form when done?

The user must some kind of freak with three or more hands?

Private Sub lstResults_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)

On Error GoTo MouseDownErr: 'why colon here?

DoCmd.OpenForm "frmEnqDetails"

MouseDownErr:
Exit Sub

End Sub

Private Sub lstResults_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)

DoCmd.Close acForm, "frmEnqDetails", acSaveNo

End Sub

 

[systemx]

Hi UncleJoe,

Not sure which part does not make sense?

On mousedown, the form is opened. When the user releases the mouse button, the form is closed.

The form is to preview the decscription of a customer enquiry. So the user does not need to 'do anything' with the form that opens other than read.

When the user double-clicks on the record in the listbox, an unbound form opens with all of the enquiry details.

As for the colon.... I'm not sure why I put it in. I would not normally include the colon - but I think it still works with it...have not checked to find out.

Regards

Robert

 

[unclejoe]

Hi systemx,

It’s the MouseDown and MouseUp Event of your control named “lstResults”.

Having both events running together is not feasible/advisible.

When you click the control “lstResults” on a row, the MouseDown event fires up, the “Docmd” opens your form “frmEnqDetails", once the mouse button is release, the MouseUp event fires up, the “Docmd” command closes the form “frmEnqDetails".

Remember, you have the DoubleClick event? The event of the MouseDown/Up will fire twice to open and close your form.

If you wish to use the MouseDown/Up event, create a Label or TextBox or Command Button for the user.

Openning/Closing a form to view details with the mouse event is fine but not on the ListBox mouse event.

As you have said,

The value in the list box does not seem to refresh properly. i.e. When the form is first opened, if the user clicks a row in the listbox nothing is displayed on the form that opens. If they click that row again it works. If they then click a different row, the old information is displayed. If they click the row a second time - the correct information is displayed.

This is how the MouseDown/Up event works. It doesn’t select the new record on first MouseDown event.

Hi UncleJoe,
Not sure which part does not make sense?
Robert

 

[KernelK]

Another suggestion for one of the problems you encountered earlier would be to use the openargs parameter of the docmd.openform to pass the ID number instead of using a global variable.

Also if nothing is coming up when the list is empty just put an IF lstResults.ListIndex <> -1 THEN to test if there is a selection made. The value of a listbox with no selection is -1. Not sure about your mouse down and mouse up events though.

 

[systemx]

Hi all,

Thank you both for the response and advice.

UncleJoe - I agree with everything you have pointed out. I was questioning it simply because you had said the code did not make sense - when really it was the choice of events/timing that did not make sense.

I am still on a very steep learning curve so keen to find out as much as possible to improve the way I approach problems such as this. As per my previous post - I have set up a lbl with the mousedown event until I find a way to code around this problem.

As for the mousedown/dbl click events being together - again agreed. On double click - the mousedown form opens briefly before the full enquiry form opens. I don't think this can be resolved.

KernelK - I had played with the listcount and listindex for validation purposes previously but being new to the commands had not used the -1 condition. This will definately fix the problem when the user first opens the form.

If I come up with an innovative way to get this functioning correctly - I will post the solution here. I'm a firm believer that where there's a will there is a way - but then also have to accept that access has it's limitations.

Thanks all

Robert

 

[systemx]

Ok all...a potential solution...

The mousedown and mouseup event have a variable 'Y' which specifies the vertical coordinate of the mouse cursor in twips.

I need to do some more work on this, but using the code below I am simulating the 'mouse click' event during mousedown -

'The first row of the listbox has a twip value between of 1 to 210
If Y < 211 And Y > 0 Then
'First row in listbox has been selected
Me.lstResults.ListIndex = 0
Else
'No row has been selected
Me.lstResults.ListIndex = -1
End If

This succesfully avoids any the problems I mentioned - except as UncleJoe pointed out the doubleclick and mousedown events that should not be used together.

Of course, you need to code the twips for every row in the listbox.

Just thought I would share this...it may not be the right solution but it may have a use to someone in the future.

Cheers

Rob

EDIT: Oh yeah, if anyone has code for determining the position of a listbox scrollbar...please let me know! Thanks

 

[systemx]

Success! From what I can see, this works without any bugs or problems with one exception - I still have the 'Mousedown' and 'Doubleclick' event on the listbox.

The result for the user is that when they double click to open the record, there is a brief flash as the popup form is opened and disappears. Feedback from users who have tested my application has indicated that this is not a problem (although I am sure someone will complain at some stage).

Firstly, a massive thank you to Steven Lebans. Without his example custom scrollbar I would not have got this working! Found in this thread -

http://www.access-programmers.co.uk/forums/showthread.php?t=77189&highlight=scrollbar

Here's how I got it working -

Private Sub lstResults_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)

On Error GoTo MouseDownErr

'If user clicks right or middle mouse button, do nothing (optional)
If Button = acRightButton Or Button = acMiddleButton Then
Exit Sub
End If

'If user clicks the left mouse button
If Button = acLeftButton Then

Dim MyRow

'Text35 control is a control in Steven Lebans custom scroll bar
'If null, user has not used the scroll bar
'If less than 3833, user has not scrolled past the first 13 records in the lstbox
Importantly, my listbox is sized to show 13 records

If IsNull(Me.Text35) Or Me.Text35 = "" Or Me.Text35 < 3833 Then

MyRow = 0

Select Case Y
'Depending on location of mouse in twips, get the listindex value
Case 1 To 210
MyRow = MyRow
Case 211 To 420
MyRow = MyRow + 1
Case 421 To 630
MyRow = MyRow + 2
Case 631 To 840
MyRow = MyRow + 3
Case 841 To 1050
MyRow = MyRow + 4
Case 1051 To 1260
MyRow = MyRow + 5
Case 1261 To 1470
MyRow = MyRow + 6
Case 1471 To 1680
MyRow = MyRow + 7
Case 1681 To 1890
MyRow = MyRow + 8
Case 1891 To 2100
MyRow = MyRow + 9
Case 2101 To 2310
MyRow = MyRow + 10
Case 2311 To 2520
MyRow = MyRow + 11
Case 2521 To 2730
MyRow = MyRow + 12
Case Else
MyRow = -1
End Select

'Set list index value accordingly (acts like user clicking on listbox row)
Me.lstResults.ListIndex = MyRow

'Else, the user has used the scrollbar
Else
Dim MyLoc As Integer
'The following will indicate how many rows the user has scrolled down
MyLoc = Int(Me.Text35.Value - 2381) / 121
Select Case Y
'Same as previous, but in reverse
Case 1 To 210
MyRow = MyLoc - 12
Case 211 To 420
MyRow = MyLoc - 11
Case 421 To 630
MyRow = MyLoc - 10
Case 631 To 840
MyRow = MyLoc - 9
Case 841 To 1050
MyRow = MyLoc - 8
Case 1051 To 1260
MyRow = MyLoc - 7
Case 1261 To 1470
MyRow = MyLoc - 6
Case 1471 To 1680
MyRow = MyLoc - 5
Case 1681 To 1890
MyRow = MyLoc - 4
Case 1891 To 2100
MyRow = MyLoc - 3
Case 2101 To 2310
MyRow = MyLoc - 2
Case 2311 To 2520
MyRow = MyLoc - 1
Case 2521 To 2730
MyRow = MyLoc
Case Else
MyRow = -1
End Select
Me.lstResults.ListIndex = MyRow
End If


If Me.lstResults.ListIndex = -1 Then
Exit Sub
Else
DoCmd.OpenForm "frmEnqDetails"
End If

Else
Exit Sub
End If

MouseDownErr:
Exit Sub
End Sub

The code still needs some tidying up I admit. But for now, it works. I also need to figure how to hide the default Access scroll bar on the listbox. If this is not possible - then this has been fruitless!

Hope this method helps someone, somewhere.