Requery not working (1 Viewer)

[majhl]

I have a form with a list box. The rowsource of this listbox is set to a bit of sql that is run when the form is opened:

SELECT BatchDate, Count(BatchDate) AS [No Of Deaths] 
FROM tblMR515Deaths 
WHERE StudyCauseOfDeath Is Null 
AND SCDinICD10 Is Null 
GROUP BY BatchDate

The data in the listbox might look like this (with column headings):

'Batch Date'------'No of Deaths'
01/12/2009------65
02/12/2009------4


Users then double-click on a row in the listbox which takes them to a second form where they can navigate through records and add additional details. The two pieces of data they edit can edit are 'StudyCauseOfDeath' and 'SCDinICD10'. So if they select the second row in the listbox, go to the second form and edit one record, the 'No Of Deaths' column in the listbox when they get back to it, should read:

'Batch Date'------'No of Deaths'
'02/12/2009'------3

This is the part I'm having trouble with. I've tried re-querying the form with the listbox, refreshing it, repainting it. I've tried doing the requery/refresh/repaint in different events (onActivate, OnOpen etc) or before I close the second form and move back to the first, but nothing I do seems to have any effect with what's displayed in the listbox.

Anyone know what I'm doing wrong?

Below's the code behind the form with the listbox:

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
 
Set db = CurrentDb
 
strSQL = "SELECT BatchDate, Count(BatchDate) AS [No Of Deaths] "
strSQL = strSQL & "FROM tblMR515Deaths "
strSQL = strSQL & "WHERE StudyCauseOfDeath Is Null "
strSQL = strSQL & "AND SCDinICD10 Is Null "
strSQL = strSQL & "GROUP BY BatchDate "
 
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
 
If rst.RecordCount <> 0 Then
 
Me.lstDeaths.RowSource = strSQL
 
End If
 
rst.Close
Set rst = Nothing
db.Close
Set db = Nothing
strSQL = ""
 
End Sub

 

[Mr. B]

Try requerying the Listbox not the form.

If the listbox is in another form, you will need to use the full reference to the object:
Forms!NameOfYourForm.NameOfListbox.Requery

 

[majhl]

Try requerying the Listbox not the form.

If the listbox is in another form, you will need to use the full reference to the object:
Forms!NameOfYourForm.NameOfListbox.Requery

Thanks for the reply.

That was one of the things I tried first. It doesn't work for some reason.

 

[boblarson]

By the way code tags on the forum here are using the word CODE not VBA and use SQUARE BRACKETS [ ] not < > like HTML.

So you use

Code

and

/Code

within [ ]

 

[majhl]

By the way code tags on the forum here are using the word CODE not VBA and use SQUARE BRACKETS [ ] not < > like HTML.

So you use

Code

and

/Code

within [ ]

Thanks for the tips!