1

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?

Community
  • 1
  • 1
jjjjjjjjjjj
  • 417
  • 8
  • 28
  • Your DCount statement shouldn't throw this error. Can you show your actual code and how you call / use it? – Andre May 03 '16 at 22:35
  • @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 May 05 '16 at 15:47
  • @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 May 05 '16 at 15:47
  • Whoops, I checked the MainTable box and it's actually a text box. Its ControlSource is RecordID. – jjjjjjjjjjj May 05 '16 at 17:23
  • I included `Option Explicit` and Christopher's answer below, and it worked. Thank you all. – jjjjjjjjjjj May 05 '16 at 20:04

1 Answers1

1

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.

Community
  • 1
  • 1
MoondogsMaDawg
  • 1,704
  • 12
  • 22
  • Ah, that did it, thanks. I defined the RecordSets as in your linked question, then used `If rs.BOF = True And rs.EOF = True Then (empty subform message) Else DCount() End If`. It seems that to test for an empty table I only need BOF *or* EOF, and not both. Is there any case where this is not true? – jjjjjjjjjjj May 05 '16 at 19:42
  • 1
    @jjjjjjjjjjj I can't think of a situation that would cause an issue. I'm pretty new to using recordsets in general, so I'm probably not much help. AFAIK after setting `rs`, it moves to Record 1 if not null, or to `BOF` and `EOF` if it is null. This article has helped me wrap my head around `rs` and may help answer your question: http://www.utteraccess.com/wiki/index.php/Recordsets_for_Beginners. I see a lot of examples using `AND` and `OR` online, I'm just not sure why they differ. – MoondogsMaDawg May 05 '16 at 20:17
  • 1
    Checking for .BOF **and** .EOF is cargo cult programming and is actually never needed. You either move forward in a recordset, or have just opened it, then use `.EOF`. You move backward, use `.BOF`. – Andre May 05 '16 at 21:42
  • @Andre Not sure how well I understood your point. Do you call `Not (.BOF And .EOF)` in [this answer](http://stackoverflow.com/a/37052403/77335) cargo cult programming? – HansUp May 08 '16 at 15:42
  • @HansUp: Um, well, yes. After opening a recordset, it either has at least one record, or not. If not, `.EOF` will always be true, so checking additionally for `.BOF` is simply unnecessary. – Andre May 09 '16 at 07:17
  • When you apply that label so broadly, then [Dim dbs As DAO.Database](http://stackoverflow.com/a/34969410/77335) is "cargo cult programming" (per your criterion) since the DAO prefix is simply unnecessary there. – HansUp May 09 '16 at 12:02