DCount

[T5925MS via AccessMonster.com]

I’ve been lost for days on this one. Any help is very much appreciated...

On the first form, I double click on a list box value. This action causes the
second form to open with OpenArgs:

DoCmd.OpenForm "frmFloorProgAudit", _
acNormal, , , acFormEdit, acWindowNormal, _
Me.FloorProgCriteriaID

The second form has these open events:

DoCmd.GoToRecord , , acNewRec _
Me.FloorProgCriteriaID = Me.OpenArgs

On this second form’s current event, I’m using a DCount function to count the
number of records that match the criteria of three fields:

Me.txtCountOfObservations = DCount("*", _
"tblFloorProgAudit", "[AuditID] = " & _
Me.AuditID.Value & " And " & _
"[FloorProgCriteriaID] = " & _
Me.FloorProgCriteriaID.Value & " And " & _
"[FloorProgObservationID] = " & _
Me.FloorProgObservationID.Value & " And " & _
"[AuditorID] = '" & Me.AuditorID.Value & "'")

Two of the fields are populated with default values in the form’s property
sheet. These two field’s values are recognized by the DCount function,
AuditID and AuditorID. Why does the FloorProgCriteriaID field return Null?
And more importantly, how can I code this form properly so that DCount will
correctly count the number of records that match the criteria of all three
fields on the form’s current event?

Michael

 

[Dirk Goldgar]

I’ve been lost for days on this one. Any help is very much appreciated...

On the first form, I double click on a list box value. This action causes
the
second form to open with OpenArgs:

DoCmd.OpenForm "frmFloorProgAudit", _
acNormal, , , acFormEdit, acWindowNormal, _
Me.FloorProgCriteriaID

The second form has these open events:

DoCmd.GoToRecord , , acNewRec _
Me.FloorProgCriteriaID = Me.OpenArgs

On this second form’s current event, I’m using a DCount function to count
the
number of records that match the criteria of three fields:

Me.txtCountOfObservations = DCount("*", _
"tblFloorProgAudit", "[AuditID] = " & _
Me.AuditID.Value & " And " & _
"[FloorProgCriteriaID] = " & _
Me.FloorProgCriteriaID.Value & " And " & _
"[FloorProgObservationID] = " & _
Me.FloorProgObservationID.Value & " And " & _
"[AuditorID] = '" & Me.AuditorID.Value & "'")

Two of the fields are populated with default values in the form’s property
sheet. These two field’s values are recognized by the DCount function,
AuditID and AuditorID. Why does the FloorProgCriteriaID field return Null?
And more importantly, how can I code this form properly so that DCount
will
correctly count the number of records that match the criteria of all three
fields on the form’s current event?

Michael

I imagine that, when your code in the Open event executes DoCmd.GoToRecord,
the form's Current event fires before the next line that sets the value of
Me.FloorProgCriteriaID. So when the DCount is evealuated in the Current
event, Me.FloorProgCriteriaID is still Null.

One possibility is to set the value in the Current event, if you're adding a
new record. Like this:

'------ start of example code ------
Private Sub Form_Current()

If Me.NewRecord And Not IsNull(Me.OpenArgs) Then
Me.FloorProgCriteriaID = Me.OpenArgs
End If

Me.txtCountOfObservations = DCount("*", _
"tblFloorProgAudit", "[AuditID] = " & _
Me.AuditID & " And " & _
"[FloorProgCriteriaID] = " & _
Me.FloorProgCriteriaID & " And " & _
"[FloorProgObservationID] = " & _
Me.FloorProgObservationID & " And " & _
"[AuditorID] = '" & Me.AuditorID & "'")

End Sub
'------ end of example code ------

If you do it that way, you don't need the line in the Open event setting
Me.FloorProgCriteriaID.

 

[T5925MS via AccessMonster.com]

Thanks for your reply. I put the code in exactly the way you recommended, and
now DCount does see a value. Thanks again!

I’ve been lost for days on this one. Any help is very much appreciated...

[quoted text clipped - 33 lines]

Michael

I imagine that, when your code in the Open event executes DoCmd.GoToRecord,
the form's Current event fires before the next line that sets the value of
Me.FloorProgCriteriaID. So when the DCount is evealuated in the Current
event, Me.FloorProgCriteriaID is still Null.

One possibility is to set the value in the Current event, if you're adding a
new record. Like this:

'------ start of example code ------
Private Sub Form_Current()

If Me.NewRecord And Not IsNull(Me.OpenArgs) Then
Me.FloorProgCriteriaID = Me.OpenArgs
End If

Me.txtCountOfObservations = DCount("*", _
"tblFloorProgAudit", "[AuditID] = " & _
Me.AuditID & " And " & _
"[FloorProgCriteriaID] = " & _
Me.FloorProgCriteriaID & " And " & _
"[FloorProgObservationID] = " & _
Me.FloorProgObservationID & " And " & _
"[AuditorID] = '" & Me.AuditorID & "'")

End Sub
'------ end of example code ------

If you do it that way, you don't need the line in the Open event setting
Me.FloorProgCriteriaID.