Solved Counting number of records in subform (1 Viewer)

[theDBguy]

Thank you. I hear you regarding the not even getting a null.

That's why I gave you a sample DCount() expression that doesn't rely on the subform. We need to fix it, if it's not working for you.

= DCount("*", "[tblInventory]", "[InventoryID] =" & Nz("[tblInventory]![ProductID]", 0))

Try it this way:

=DCount("*", "tblInventory", "[InventoryID]=" & Nz([ProductID],0))

Are you sure the ProductID is related to the InventoryID?

 

[zebrafoot]

That's why I gave you a sample DCount() expression that doesn't rely on the subform. We need to fix it, if it's not working for you.

Try it this way:

=DCount("*", "tblInventory", "[InventoryID]=" & Nz([ProductID],0))

Are you sure the ProductID is related to the InventoryID?

Hi. Apologies if my lack of experience is making this a more painful experience than it should be!

ProductID is a foreign key within tblInventory.

This code appears to work:

=DCount("*","[tblInventory]","[ProductID] =" & Nz([ProductID],0))

(although only if the Allow Additions property of the subform is set to Yes).

Thank you all once again for your assistance with this.

Pete

 

[theDBguy]

(although only if the Allow Additions property of the subform is set to Yes).

Hi. Glad to hear you got it sorted out. Although, that approach shouldn't matter whether the subform allows additions or not. In fact, it shouldn't even matter whether you have a subform or not. Good luck!

 

[arnelgp]

dcount() will not work if you have a Select query with joins as recordsource, eg:

recordsource: select id, productCode, t2.productname from table1 left join tblproduct as t2 on table1.productCode = t2.productCode.

 

[zebrafoot]

Hi. Glad to hear you got it sorted out. Although, that approach shouldn't matter whether the subform allows additions or not. In fact, it shouldn't even matter whether you have a subform or not. Good luck!

Ah, yes of course, I can put the text box in the parent form and set the control source to the code above. It doesn't need to be in the footer of the subform. It works!

 

[theDBguy]

Ah, yes of course, I can put the text box in the parent form and set the control source to the code above. It doesn't need to be in the footer of the subform. It works!

Congratulations!