inconsistent listbox iteration via code

[xentaur]

Hi Gurus

Background:
I designed and maintain a case-noting system for my organisation. It is set up so that a shortcut copies the front-end to the users temp directory (only 1 meg so 2-5 seconds at best) and then launches. Backend is MySQL.

Users enter search criteria on the main form (FrmMain) and a list of possible matches is generated in the first listbox (List1). Users then select their preferred client with a click in List1 which then returns a list of case notes in date/time descending order in the second listbox (List2).

As users add case-notes, the form executes a List2.Requery, then iterates with a for/next loop to locate the matching primary key for the newly added case-note, and (ideally) highlights that same case-note to identify the case note currently displayed with its details in List2.

Here are the strange things:
If I am in the database as an administrator, the code that iterates to locate the newly added case note works. If, however, I am in it as a user (and the only user at that, as the front end is in my own temp directory) then it doesn't work - no error, it just doesn't work.

Me.List2.Requery
Me.Refresh
For x = 1 To Me.List2.ListCount
Me.List2.SetFocus
        If Int(Me.List2.ItemData(x)) = y Then
        Me.List2.Selected(x) = True
        x = Me.List2.ListCount
        End If
Next x

Wierd also that I need to refer to the itemdata as an integer in order for it to match my primary key value in 'y' as both are numerical values.

Unsure exactly what I'm missing here. I've even tried planting a 'DoEvents' at various points within the iteration loop to return control to the OS but with no success.

I'm hoping it's a simple fix but I'm stumped as to why it would work in administrator mode and not in user mode.

Thanks guys (non gender-specific guys, of course!)

Xentaur.

 

[genomon]

This probably isn't the problem, but just FYI the Int function is usually used to return the integer portion of a decimal; the coercive function CInt will convert data types...

Outside of a dog, a book is man's best friend. Inside of a dog it's too dark to read.

 

[xentaur]

Thanks for the response, genomon.

Both the itemdata and y in the above code are integer values with no decimal point.

Still, that doesn't solve my more pressing problem. Anyone else got any thoughts as to why I'm getting different results based upon how I'm logged into the database?

Thanks in advance.

 

[PHV]

Anyway:
For x = [!]0[/!] To Me!List2.ListCount [!]- 1[/!]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[xentaur]

Thanks PHV,

If memory serves, x as 0 highlighted the column heading in list2, which I don't need. The problem isn't the scope of the set in list 2 but the fact that it wont iterate through the loop to highlight the most recently added case note when I'm logged in as a user. It will iterate properly when I'm logged in as administrator.

I'm on the verge of rewriting the code so that it wont even highlight the most recent entry, so as to avoid confusion for the user (those guys!). This is really frustrating.

Anybody??

Cheers

 

[xentaur]

Resolved:

The problem seemed to lie with the delay between saving the record at the front end and the back end then returning a primary key - MySQL generates an auto-increment number at saverecord, not at newrecord.

I didn't realise that I had a null pkey value running through the loop above because out of frustration I msgboxed the pkey value BUT the msgbox was waiting for a pkey value to return from the back end before showing itself, however if the code just ran through it didn't wait for pkey to populate. My solution was a while loop until a value for pkey appeared:

DoCmd.RunCommand acCmdSaveRecord

Do While Me.pkey = Null
DoEvents
Loop

Me.List1.Requery
Me.Refresh
DoEvents
disev = 0
Dim x As Integer
For x = Me.List1.ListCount - 1 To 1 Step -1
If CLng(Me.List1.ItemData(x)) = Me.pkey Then Me.List1 = Me.List1.ItemData(x): x = 1
Next x

Cheers

 

[xentaur]

oops,

obviously, for List1 above read List2.

Cheers