Action after a listbox updates

[Juicejam]

I have a listbox on a form which displays data based on the value of a textbox. I requery the listbox every second or so using the timer event on the form. What I want to accomplish is that whenever the listbox changes, i.e. a new row is added to the list, a subsequent action (in my case, a beep) occurs.

I tried the afterupdate property of the listbox but that doesnt seem to work. The listbox is unbound.

ANy help would be appreciated....

[pbaldy]

That event wouldn't fire with a requery. Offhand, you could test the count before and after the requery and compare them. That would obviously miss if a row was added and another deleted at the same time.

[Juicejam]

Nice idea, and the simultaneous delete/add is not a factor as there is only one user.

Could you suggest some code as to how to do this comparison? I would limit the number of rows to about 25 for the listbox.

Thanks again.

[pbaldy]

See my code in post 3 for the count

https://www.accessforums.net/showthr...s-in-a-ListBox

Put the count before into a variable, do the Requery, then compare the count after to the variable.

[Juicejam]

ok I think I may know how to put something into a variable but how do I compare?

[pbaldy]

For what you're doing

If Before <> After Then

[Juicejam]

If Listbox.ListCount <> Listbox.ListCount then
Beep
End If

I am really not familiar with comparisons nor using the <> symbols.

[pbaldy]

Well, logically that would never work because you're comparing the value to itself. Something like:

Dim lngCountBefore As Long
Dim lngCountAfter As Long

lngCountBefore = Me.ListboxName.ListCount - IIf(Me.ListboxName.ColumnHeads, 1, 0)
Me.ListboxName.Requery
lngCountAfter = Me.ListboxName.ListCount - IIf(Me.ListboxName.ColumnHeads, 1, 0)

If lngCountBefore <> lngCountAfter Then
  MsgBox "Different count"
End If

[Juicejam]

Ah now I get it. But what is the Columnhead reference for?

[pbaldy]

It accounts for that setting of the listbox. If you are displaying column heads, your count will be off 1. In your case, it's irrelevant, but it's my standard code so I didn't edit it. You can drop that part from both lines.