Using DCount to populate record "X of Y" box

[Kurt]

I am trying to populate a record “X of Y†unbound text box (called
“RecordCountâ€) on a subform. The code is in the On Current event of the
subform. The relevant portion is:

Me![RecordCount] = "" & (recClone.AbsolutePosition + 1) & " of " & _
DCount("PG_ID", "tblProgramInformation", PC_ID =
Forms!frmPrimaryContacts!PC_ID)

So, when the user selects a PC_ID from a combo box on the
*main*form(frmPrimaryContacts), I would like the unbound text box on the
subform to update to show the total number of records in
tblProgramInformation with the matching PC_ID (e.g., “1 of 5â€).

But the code always returns a DCount of “16,†regardless of what PC_ID is
selected on the main form. (16 is the total number of records currently in
tblProgramInformation.) The recClone.AbsolutePosition is correct, but the
total count is not.

What I am doing wrong? Thank you. – Kurt (Full code below.)

###

Private Sub Form_Current()

Dim recClone As Object
Dim intNewRecord As Integer

' If this is a "New Record" then Exit the procedure

intNewRecord = IsNull(Me![PG_ID])

If intNewRecord Then
Me![RecordCount] = "New"
Exit Sub
Else
‘
End If

' Make a clone of the recordset underlying the form so
' we can move around without affecting the form's recordset
Set recClone = Me.RecordsetClone

Me![RecordCount] = "" & (recClone.AbsolutePosition + 1) & " of " & _
DCount("PG_ID", "tblProgramInformation", PC_ID =
Forms!frmPrimaryContacts!PC_ID)

recClone.Close

Exit_Form_Current:
Exit Sub
…

End Sub

 

[John Vinson]

But the code always returns a DCount of “16,” regardless of what PC_ID is
selected on the main form. (16 is the total number of records currently in
tblProgramInformation.) The recClone.AbsolutePosition is correct, but the
total count is not.

The third argument of DCount needs to be a *text string* containing a
valid WHERE clause.

Either use recClone.RecordCount, or

Me![RecordCount] = "" & (recClone.AbsolutePosition + 1) & " of " & _
DCount("PG_ID", "tblProgramInformation", "[PC_ID] = " & _
Forms!frmPrimaryContacts!PC_ID)


John W. Vinson[MVP]