getting an accurate record count

  • Thread starter Thread starter deeann
  • Start date Start date
D

deeann

I am trying to flag all "open" instances on a form where a
person can select from a combo box whether a period is
open, closed or future. There can only be one open period
at a time, but my code does not refect the current count
especially when a user makes changes.


Private Sub Combo6_BeforeUpdate(Cancel As Integer)
Dim db As Database, rs As Recordset
Dim strquery As String
Set db = CurrentDb
strquery = "select count (GL_PERIODS.STATUS_ID) as
Count FROM GL_PERIODS WHERE (GL_PERIODS.STATUS_ID = 1);"
Set rs = db.OpenRecordset(strquery, dbOpenDynaset)
rs.MoveLast

If rs!count > 1 Then MsgBox "err" & rs!count
rs.Requery

End Sub

The underlying table looks like this:

Period_id Period_Alias Status_id
0104 Jan 04 2
0204 Feb 04 2
0304 Mar 04 1
0404 Apr 04 3

where status is 1 == open, 2= closed and 3 = future.
 
Deeann,

There are three approaches that I can think of to this. One is to save
the record first, so that the record currently being edited is included
in the count. Another would be to run an update query, if the user is
trying to set a record to 'open', to reset the status of any existing
'open' status record. Or thirdly, you could only apply the "test" if
the user is trying to change the record to "open" status, like this...

Private Sub Combo6_BeforeUpdate(Cancel As Integer)
Dim db As Database
Dim rs As DAO.Recordset
Dim strquery As String
If Me.Combo6 = 1 Then
Set db = CurrentDb
strquery = "SELECT count(GL_PERIODS.STATUS_ID) As MyCount FROM
GL_PERIODS WHERE (GL_PERIODS.STATUS_ID = 1);"
Set rs = db.OpenRecordset(strquery, dbOpenDynaset)
rs.MoveLast
If rs!MyCount = 1 Then
MsgBox "You already have a record with open status"
Cancel = True
End If
rs.Close
Set rs = Nothing
Set db = Nothing
End If
End Sub
 
Back
Top