Help on RunTime Error '2427'

[CBG2112]

I need to count the number of records in a subform once a selection is made from a combobox. If there’s no record present, I would like to record it as a 0. I’ve added Text22 in the subform’s footer as =NZ([CountOfBATCH_ID],”0”). When I try to run the code, I receive a “Run-time error ‘2427’: You entered an expression that has no value."

I have on the change event of the combobox as

Dim db As DAO.Database
      Set db = CurrentDb
      Dim rst As DAO.Recordset
      Set rst = db.OpenRecordset("tblSHS")
      rst.AddNew
      rst![BATCH_ID] = [BATCH_ID]
      rst![DReview] = [Text70]
      rst![DError] = Forms![Batch Detail]![qryRError subform1]![Text22]
      rst!StatusTx = [cStatus].Text
      rst!StatusDt = Now()
      rst!StatusUpdateUser = xUserName
      rst.Update
      rst.Close
      Set db = Nothing

The code debugs on rst![DError] = Forms![Batch Detail]![qryRError subform1]![Text22]

 

[boblarson]

1. when you have an NZ function on a count you would give it a 0 without quotes.

2. Why not just get the count from the recordset itself?

Example:

Dim lngCount As Long
Dim rst As DAO.Recordset
 
Set rst =  Forms!MainFormHere.SubformControlHere.Form.RecordsetClone
 
lng = rst.RecordCount

 

[boblarson]

Oh, forgot to include my standard subform info:

In this:

Forms!MainFormHere.SubformControlHere.Form.RecordsetClone

The SubformControlHere refers to the control on the main form which HOUSES the subform and not the subform name itself unless it shares the name with the subform exactly.

The .Form. part stays exactly as is because that tells Access we want something from the subform and not the subform control.