Requerying a list box on an access form from another application 1

[ChrisNome]

Hi everyone

I'm having a problem with automated a simple requery procedure on a list box on an access form. The problem lies in that I'm requerying this form from a VBA module in another application, not directly from the access application.

The sub in that module performs an ADO recordset operation loading data to a table (works successfully every time) but at times does requery a list box and at times doesn't to reflect the changes in data. I can't seem to figure out why

here's the code that works at it's own wish:

*previous data changes

On Error Resume Next
Set appAcc = GetObject(, "Access.Application")
If Err.Number <> 0 Then
MsgBox "error returning to db"
Exit Sub
End If

Dim form As Access.form
Dim list As Access.Control

For Each form In appAcc.forms
If form.Name = "frmDestinationForm" Then
For Each list In form.Controls
If list.name = "lstDestination" Then
list.Requery
End If

Next

End If
Next

The instantiation never produces an error so that's not the problem. That list box sometimes requery's and sometimes doesn't to reflect the data changes and it's important that it does to the user otherwise the program will appear to be malfunctioning.

The list box's rowsource is based on a query that includes the updated table. Perhaps there's something I could do like run that with code first, or i could approach requerying the list box or manipulating access in a different way?? anything would help

thanks

 

[PHV]

Replace this:
list.Requery
with this:
list.RowSource = list.RowSource

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ChrisNome]

Hey I tried that and it appeared to work at first try, but not afterwards. I also tried to do:

list.rowsource = list.rowsource
list.requery

and

forms.setfocus
list.setfocus
list.rowsource = ""
list.requery
list.rowsource = "qryDestination"
list.requery

I kept the "qryDestination" open in access to see if i could observe the problem in action. Every time it fails, the underlying query in datasheet view doesn't reflect the changes until you click back on access. However, some times when the actions above DO work and requery the list box correctly, the same query doesn't reflect the changes either, but the list box does.

Every time data is updated the list box flashes like it's being requeried. It's almost as if the underlying query is always being requeried, but sometimes it has had enough time to make the connection to new data and other times it hasn't, by the time the list box operations are executed. As soon as the user reenters access, the query displays the right results and if i do a requery on the onEnter event of that list box it always shows the correct data. access doesn't have an access.activate though so i don't really know what to do here. it's important that the list box reflect the data change as it happens if the user has it in the background and the user will most likely continue working outside of the access application.