Requery not working

[M Joomun]

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:

Code:

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 is the code behind the form with the listbox:

Code:

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, dbOpenSnapshot)

If rst.RecordCount <> 0 Then

Me.lstDeaths.RowSource = strSQL

End If

rst.Close
Set rst = Nothing
db.Close
Set db = Nothing
strSQL = ""

End Sub

 

[M Joomun]

BTW, I've tried requerying the list box with
'Forms!frmdeathselect.lstDeaths.Requery' before I close the second form
but it has no effect.

 

[Mo]

BTW, I've tried requerying the list box with
'Forms!frmdeathselect.lstDeaths.Requery' before I close the second form
but it has no effect.

More information: If I hit 'F9', the listbox requeries as expected.
What's the Access equivalent of 'F9' - requery, refresh?

 

[John W. Vinson]

More information: If I hit 'F9', the listbox requeries as expected.
What's the Access equivalent of 'F9' - requery, refresh?

..Requery.

Perhaps you could post your actual code, and indicate the name of the Form
(and of the subform control if there's a subform involved).
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Mo]

.Requery.

Perhaps you could post your actual code, and indicate the name of the Form
(and of the subform control if there's a subform involved).

Thanks for the response. All the code is given in my original post. I
open form 1 which fires some code attached to its on_open event. The
code populates a list box (lstDeaths) with the sql string (bottom):

The user then double clicks on a row of data in the list box which looks
like this:

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

Suppose its the second row. This takes them to form 2, where they can
edit two items of data, 'StudyCauseOfDeath' and/or 'SCDinICD10'. They
then close form 2 which fires the code below and get taken back to form1:

'Forms!frmdeathselect.lstDeaths.Requery'

The data in the listbox should look like this as the number of records
that meet the criteria 'WHERE StudyCauseOfDeath Is Null "
AND SCDinICD10 Is Null' is now one less.

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

But for some reason, the requery hasn't worked. I've tried .Refresh,
..Repaint but none of these has any effect.

Form1 Code:

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, dbOpenSnapshot)

If rst.RecordCount <> 0 Then

Me.lstDeaths.RowSource = strSQL

End If

rst.Close
Set rst = Nothing
db.Close
Set db = Nothing
strSQL = ""

End Sub

 

[Bob Quintal]

.Requery.

Perhaps you could post your actual code, and indicate the name of
the Form (and of the subform control if there's a subform
involved).

Thanks for the response. All the code is given in my original
post. I open form 1 which fires some code attached to its on_open
event. The code populates a list box (lstDeaths) with the sql
string (bottom):

The user then double clicks on a row of data in the list box which
looks like this:

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

Suppose its the second row. This takes them to form 2, where they
can edit two items of data, 'StudyCauseOfDeath' and/or
'SCDinICD10'. They then close form 2 which fires the code below
and get taken back to form1:

'Forms!frmdeathselect.lstDeaths.Requery'

The data in the listbox should look like this as the number of
records that meet the criteria 'WHERE StudyCauseOfDeath Is Null "
AND SCDinICD10 Is Null' is now one less.

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

But for some reason, the requery hasn't worked. I've tried
.Refresh, .Repaint but none of these has any effect.

Form1 Code:

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, dbOpenSnapshot)

If rst.RecordCount <> 0 Then

Me.lstDeaths.RowSource = strSQL

End If

rst.Close
Set rst = Nothing
db.Close
Set db = Nothing
strSQL = ""

End Sub

You have not said where you put the line of code
'Forms!frmdeathselect.lstDeaths.Requery'
It may be executed before Access actually updates the table.

Also, it is shown above in single quotes which would convert the
statement into a comment,

 

[Mo]

You have not said where you put the line of code
'Forms!frmdeathselect.lstDeaths.Requery'
It may be executed before Access actually updates the table.

Also, it is shown above in single quotes which would convert the
statement into a comment,

It's on the close event of form 2, so:

Code:

If MsgBox("Close the form?", vbYesNo, "Exit?") = vbYes Then

DoCmd.OpenForm "frmDeathSelect"
Forms![frmdeathselect].[lstDeaths].Requery
DoCmd.Close acForm, Me.Name

End If

The single line quotes are not present in the application.

 

[Bob Quintal]

You have not said where you put the line of code
'Forms!frmdeathselect.lstDeaths.Requery'
It may be executed before Access actually updates the table.

Also, it is shown above in single quotes which would convert the
statement into a comment,

It's on the close event of form 2, so:

Code:

If MsgBox("Close the form?", vbYesNo, "Exit?") = vbYes Then

DoCmd.OpenForm "frmDeathSelect"
Forms![frmdeathselect].[lstDeaths].Requery
DoCmd.Close acForm, Me.Name

End If

The single line quotes are not present in the application.

Definitely executed before Access actually updates the table

DoCmd.Close acForm, Me.Name
DoCmd.OpenForm "frmDeathSelect"

will save the record, close the form, then open the first form.
No need to requery because you will be opening the form which will
run the query.

 

[Mo]

Definitely executed before Access actually updates the table

DoCmd.Close acForm, Me.Name
DoCmd.OpenForm "frmDeathSelect"

will save the record, close the form, then open the first form.
No need to requery because you will be opening the form which will
run the query.

Thanks very much Bob, that's done the trick!