Query table not updating as expected

[bg_ie]

Hi,

I have a table concerning Lab Tests which I present using a form. One
of the fields in this table is called Test Status where the user can
select the status via a combo box. Under this combo I have a label box
which shows a summary of the status for the entire table e.g. OK: 10,
NOT_OK: 1, NOT_TESTED: 5. When the combo box is changed, I issue a
function to change this label. This function does something like this
-

Private Sub UpdateLabelTestScriptStatus()
Dim recordSetTestSuiteStatus As DAO.Recordset
Set recordSetTestSuiteStatus = CurrentDb.OpenRecordset("Select *
from qryTestStatusCount")

captionText = ""

recordSetTestSuiteStatus.MoveFirst
firstWhileIteration = True
Do While Not recordSetTestSuiteStatus.EOF

If firstWhileIteration <> True Then
captionText = captionText & ", "
Else
firstWhileIteration = False
End If

captionText = captionText &
recordSetTestSuiteStatus.Fields("StatusType") & ": "
captionText = captionText &
recordSetTestSuiteStatus.Fields("CountOfStatusType")

recordSetTestSuiteStatus.MoveNext 'move to next record in
recordset
Loop

LabelTestScriptStatus.Caption = captionText

End Sub

The problem is that the line -

Set recordSetTestSuiteStatus = CurrentDb.OpenRecordset("Select * from
qryTestStatusCount")

is always returning back the same values. It seems to me that the
table concerned with the query qryTestStatusCount needs to be updated
at the begining of UpdateLabelTestScriptStatus(). If so, how do I do
this?

Thanks for your help,

Barry.

 

[Dirk Goldgar]

In

Hi,

I have a table concerning Lab Tests which I present using a form. One
of the fields in this table is called Test Status where the user can
select the status via a combo box. Under this combo I have a label box
which shows a summary of the status for the entire table e.g. OK: 10,
NOT_OK: 1, NOT_TESTED: 5. When the combo box is changed, I issue a
function to change this label. This function does something like this
-

Private Sub UpdateLabelTestScriptStatus()
Dim recordSetTestSuiteStatus As DAO.Recordset
Set recordSetTestSuiteStatus = CurrentDb.OpenRecordset("Select *
from qryTestStatusCount")

captionText = ""

recordSetTestSuiteStatus.MoveFirst
firstWhileIteration = True
Do While Not recordSetTestSuiteStatus.EOF

If firstWhileIteration <> True Then
captionText = captionText & ", "
Else
firstWhileIteration = False
End If

captionText = captionText &
recordSetTestSuiteStatus.Fields("StatusType") & ": "
captionText = captionText &
recordSetTestSuiteStatus.Fields("CountOfStatusType")

recordSetTestSuiteStatus.MoveNext 'move to next record in
recordset
Loop

LabelTestScriptStatus.Caption = captionText

End Sub

The problem is that the line -

Set recordSetTestSuiteStatus = CurrentDb.OpenRecordset("Select * from
qryTestStatusCount")

is always returning back the same values. It seems to me that the
table concerned with the query qryTestStatusCount needs to be updated
at the begining of UpdateLabelTestScriptStatus(). If so, how do I do
this?

It's not clear how the form and combo box are related to the results of
qryTestStatusCount, but it may be that you need to force the current
record to be saved before running that query. You could do that by
executing

Me.Dirty = False

after the combo box is updated, before you call
UpdateLabelTestScriptStatus().

 

[bg_ie]

In


















It's not clear how the form and combo box are related to the results of
qryTestStatusCount, but it may be that you need to force the current
record to be saved before running that query. You could do that by
executing

Me.Dirty = False

after the combo box is updated, before you call
UpdateLabelTestScriptStatus().

--
Dirk Goldgar, MS Access MVPwww.datagnostics.com

(please reply to the newsgroup)- Dölj citerad text -

- Visa citerad text -

Excellent, that was my problem thanks.