controls won't requery

[jnoody]

My problem is that sometimes the listbox (lstCountries) below updates/requeries after either the add_Click() or delete_Click() functions, and sometimes not. The data in tblCountries does change everytime, but the contents of the listbox don't always. I can't find any rhyme or reason to it. Can anyone tell from my code what I am doing wrong? Thanks ahead of time.

Private Sub add_Click()
On Error GoTo Err_add_Click
Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset
rs.Open "tblCountries", getConn(), adOpenDynamic, adLockOptimistic
With rs
.AddNew
![chrName] = Me.txtNewCountry
.Update
End With

rs.Close
Set rs = Nothing


Exit_add_Click:
Me.lstCountries.Requery
Refresh
Exit Sub

Err_add_Click:
MsgBox Err.Description
Resume Exit_add_Click

End Sub
Private Sub delete_Click()
On Error GoTo Err_delete_Click
Dim rs As ADODB.Recordset
Dim strSql As String
Dim country As Variant
Dim selCount As Integer

Set rs = New ADODB.Recordset
strSql = "SELECT * FROM tblCountries WHERE "
selCount = 0
For Each country In Me.lstCountries.ItemsSelected
If Not selCount = 0 Then
strSql = strSql & " AND "
End If
strSql = strSql & "idsCountryID=" & Me.lstCountries.ItemData(country)
selCount = selCount + 1
Next

If selCount > 0 Then
rs.Open strSql, getConn(), adOpenDynamic, adLockOptimistic
rs.MoveFirst
While Not rs.EOF
rs.delete
rs.MoveNext
Wend
rs.Close
Set rs = Nothing
End If

Exit_delete_Click:
Me.lstCountries.Requery
Refresh
Exit Sub

Err_delete_Click:
MsgBox Err.Description
Resume Exit_delete_Click

End Sub

 

[jkl0]

You might have to set the focus to the list box before the requery.

Me.lstCountries.SetFocus

Hope this helps.

 

[PHV]

Another way is to replace this:
Me.lstCountries.Requery
with this:
Dim strTemp As String
strTemp = Me!lstCountries.Rowsource
Me!lstCountries.Rowsource = ""
DoEvents
Me!lstCountries.Rowsource = strTemp

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

 

[jnoody]

I tried both of those and neither worked. The listbox is still not always updating.

I don't know if this is relevant, but I should mention that both controls are unbound and that the rowsource for the listbox is tblCountries.

Any other ideas?

 

[jnoody]

I have been thinking about it and I am convinced that it is a timing issue. Access is updating the form before it finishes making changes to the table. I am very distressed...

 

[TheAceMan1]

How are ya jnoody . . .

Requery of listboxes not working is not uncommon. The following should work:

[blue]   Me.lstCountries.[purple][b]RowSource[/b][/purple] = Me.lstCountries.[purple][b]RowSource[/b][/purple][/blue]

See Ya! . . . . . .

 

[jnoody]

Thanks, but it's still not working. The listbox requeries correctly about 25% of the time.

 

[Grey0ne]

make a constant requery with delay?
.Requery
.RequeryDelay = 500

 

[jnoody]

Do I enter that code as is? Are we saying that my form is going to constantly be requerying?

 

[TheAceMan1]

jnoody . . .

Then I'd say you have some kind of corruption!

Try deleting and reinstantiating the listbox (don't forget to copy any code in the listbox events first!).

See Ya! . . . . . .

 

[jnoody]

Tried recreating all of the boxes and code and still have the same symptoms.

 

[jnoody]

I recreated the entire DB with one table called tblCountries with a primary key autonumber called idsCountry and a text called chrName. Then I created a form called frmCountries with a textbox called txtNewCountry, a command button called cmdNewCountry, and a listbox called lstCountries with tblCountries as its Row Source. I tried all the code talked about in this thread for just cmdNewCountry and I still have exactly the same symptoms. Sometimes the box refreshes when I add a new country and sometimes it doesn't, but the data always ends up in the table.

I also tried using DoEvents before requerying.

Does anyone know the solution to this problem. Perhaps Access and VBA are just not capable of this function?

 

[PHV]

I think it's an ADO delayed write issue.
Have you considered to play with DAO instead ?
Or perhaps straight SQL, like this:
Private Sub add_Click()
Dim strTemp As String
CurrentDb.Execute "INSERT INTO tblCountries(chrName) VALUES ('" & Me!txtNewCountry & "')"
DoEvents
strTemp = Me!lstCountries.Rowsource
Me!lstCountries.Rowsource = ""
DoEvents
Me!lstCountries.Rowsource = strTemp
End Sub

Private Sub delete_Click()
Dim strSql As String
Dim country As Variant
Dim selCount As Integer
Dim strTemp As String
strSql = "DELETE FROM tblCountries WHERE "
selCount = 0
For Each country In Me!lstCountries.ItemsSelected
If selCount <> 0 Then strSql = strSql & " AND "
strSql = strSql & "idsCountryID=" & Me!lstCountries.ItemData(country)
selCount = selCount + 1
Next
If selCount > 0 Then
CurrentDb.Execute strSql
End If
DoEvents
strTemp = Me!lstCountries.Rowsource
Me!lstCountries.Rowsource = ""
DoEvents
Me!lstCountries.Rowsource = strTemp
End Sub

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

 

[jnoody]

Thanks PH. That worked. I am more inclined towards SQL anyway. I was using ADO because everything I read said I should know it. If ADO is so screwed up with VBA, why is it glorified so?