Access error 2427: You entered an expression that has no value

1
votes

I have a form, and am counting the number of records in a subform. Sometimes the subform is empty, which I'd like to check for, but it throws

Run-time error '2427': You entered an expression that has no value

It's obvious why this error is happening, but less obvious is how to work around it. This is the code causing the error. MainTableComboBox.Value contains RecordID.

DCount("*", "[SubFormTable]", "[SubFormTable].[RecordID] = " & MainTableTextBox.Value)

I've tried the following but it still errors out.

What other ways exist to get around this error?

1
ms-accessvbams-access-2013
Your DCount statement shouldn't throw this error. Can you show your actual code and how you call / use it? – Andre
@Andre That's the actual code. For debugging purposes, I have it run inside a MsgBox() when I click a button on the form. The form is linked to the subform on RecordID and they both use different tables: the form uses Customers and the subform uses Products. A customer may or may not have any products listed. – jjjjjjjjjjj
@HansUp When I have records in the subform, it shows -1 when I click the button. When I have no records in the subform, it shows the error and doesn't output anything. – jjjjjjjjjjj
Whoops, I checked the MainTable box and it's actually a text box. Its ControlSource is RecordID. – jjjjjjjjjjj
I included Option Explicit and Christopher's answer below, and it worked. Thank you all. – jjjjjjjjjjj

1 Answers

1
votes

I think you could set a recordset equal to the subform datasource and use IF rs.EOF = True Then to see if there are records to show before running your dcount function. See this question for more information.