Solved Counting number of records in subform (1 Viewer)

[zebrafoot]

Hi all,

Hope this is a quickie.

I have a subform that may contain quantities of stock, or no records. I need to take an average value from this subform.

I'd like to return a count of the number of lines in the subform because if there are no records, I can't obtain an average. Doing a count of the number of records isn't working for me because if there is no record in the subform, I'm not getting "0" I'm just not getting a value. Is there a straightforward way of doing this?

Cheers,
Pete

 

[gemma-the-husky]

if your subform doesn't allow additions, and there are no records. you get a blank form - I call it undefined. Try changing the subform to allow additions and see if that fixes it. Then you should get zero, rather than an error. Then all you need is code in the beforeinsert to prevent the user actually adding a new record.

 

[theDBguy]

I'm not getting "0" I'm just not getting a value.

How exactly are you counting the records? If by "no value" you mean you're getting a Null back, then perhaps you could use the Nz() function to get a zero (0) instead of Null.

 

[zebrafoot]

Hi. Thanks both for taking time to answer my query - I very much appreciate it.

@gemma-the-husky - your suggestion works on my subform but I don't really want the user to be able to add items directly or the new record line to show, so I might try to use Nz and see how that goes.

Pete

 

[zebrafoot]

I'm not exactly sure how to use the Nz funciton. I'm trying to use it on a text box within the subform footer using something like:

=Nz([txtRecordCount], 0)

I thought this should return the value in the box txtRecordCount, or zero if that control is null. The correct value is returned if there is a subform entry or multiple entries, but no value is returned if there are no subform entries for that product. Am I using it wrong?

Pete

 

[theDBguy]

I'm not exactly sure how to use the Nz funciton. I'm trying to use it on a text box within the subform footer using something like:

=Nz([txtRecordCount], 0)

I thought this should return the value in the box txtRecordCount, or zero if that control is null. The correct value is returned if there is a subform entry or multiple entries, but no value is returned if there are no subform entries for that product. Am I using it wrong?

Pete

Hi Pete. I'll go back to my original question: How are you counting the subform records? In other words, how are you populating txtRecordCount?

 

[gemma-the-husky]

How are you actually trying to get the number of lines, then? From a text box on the subform?
(which is what @theDBguy just asked, I see.

If so, you just can't do that, as if you have no records, and your form doesn't allow you to add any, everything on the subform is undefined. It's not even null. It's not available to be used at all, I believe

You could do a dcount. That would give you a zero, but it probably won't autorefresh.

itemcount = nz(dcount("*","subformquery"),0)

 

[zebrafoot]

@theDBguy apologies, didn't read your response carefully enough.

I'm using a text box with the control source =count("[InventoryID]"). As I said, this works where there are entries in the subform, but not where there are none. If there were a simple way of testing for the subform being empty, that would work for me.

Pete

 

[theDBguy]

@theDBguy apologies, didn't read your response carefully enough.

I'm using a text box with the control source =count("[InventoryID]"). As I said, this works where there are entries in the subform, but not where there are none. If there were a simple way of testing for the subform being empty, that would work for me.

Pete

Okay, in that case, as @gemma-the-husky said, you can replace this:

=Nz([txtRecordCount],0)

with something like this:

=DCount("*", "SubformSourceTableOrQueryName", "LinkedChildFieldName=" & Nz([LinkedMasterChildFieldName],0))

Hope that makes sense...

 

[zebrafoot]

Okay, in that case, as @gemma-the-husky said, you can replace this:

=Nz([txtRecordCount],0)

with something like this:

=DCount("*", "SubformSourceTableOrQueryName", "LinkedChildFieldName=" & Nz([LinkedMasterChildFieldName],0))

Hope that makes sense...

Hi,

Thanks once again for your input. I partially understand this, I think.

the source table is called tblInventory. I'd like to count all the inventory entries with a given product ID, but I'm not sure how to write this in code. I've tried:

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

but that gives me a count of all inventory items, rather than those matching the product ID I want.

 

[arnelgp]

you can also Count the number of Records in the subform
by adding code to the Subform's Current Event:

private sub form_current()
dim i as long
with me.recordsetclone
if not (.bof and .eof) then
.movefirst
end if
do until .eof
i = i + 1
.movenext
loop
end with
'replace txt with the name of textbox in Main form
me.parent!txt = i
end sub

 

[zebrafoot]

you can also Count the number of Records in the subform
by adding code to the Subform's Current Event:

private sub form_current()
dim i as long
with me.recordsetclone
if not (.bof and .eof) then
.movefirst
end if
do until .eof
i = i + 1
.movenext
loop
end with
'replace txt with the name of textbox in Main form
me.parent!txt = i
end sub

Hello and thank you.

This works insofar as it gives a count of the records in the subform if there are records. But it doesn't seem to return a value when n = 0 (or null, or something other than null, as described above!).

 

[arnelgp]

just tested it and it does.

Private Sub Form_Current()
Me.Parent!txtMain = Me.RecordsetClone.RecordCount
End Sub

 

[zebrafoot]

Thank you for the reply.

It's not working on mine. I get the inventory count on the main form if the count is not zero. If I go directly to a product with no inventory, the text box is empty. If I go to a product with no stock from a product with stock, the text box retains the value from the previous record.

 

[arnelgp]

hee is a demo, add/edit/delete something to the subform.

 

[zebrafoot]

Thank you. I really don't want to sound obtuse, but I can't get this to work quite as I'd like. If I understand this at all, I think this section should work on its own:

Dim i As Long
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone

With rs
If Not (.BOF And .EOF) Then
.MoveLast
.MoveFirst
i = .RecordCount
End If

End With
Set rs = Nothing

Me.Parent!txtStockCount = i

I think the problem I have is that I only have a subform to count if there are inventory items for the productID I'm looking at, so the code is not counting anything. If I allow additions to the subform, as Gemma suggested, above, the form does at least return a 0 value, but then I have an empty line in my subform, which I specifically didn't want!

 

[arnelgp]

so you must AllowAdditions = Yes for it to work.
if you don't want any addition of records, add code to BeforeInsert event of the form:

private sub form_beforeInsert(cancel as integer)
cancel = true
end sub

 

[zebrafoot]

so you must AllowAdditions = Yes for it to work.
if you don't want any addition of records, add code to BeforeInsert event of the form:

private sub form_beforeInsert(cancel as integer)
cancel = true
end sub

Thank you

 

[gemma-the-husky]

With rs
If Not (.BOF And .EOF) Then
.MoveLast
.MoveFirst
i = .RecordCount
End If

would need to be this

With rs
If Not (.BOF And .EOF) Then
.MoveLast
.MoveFirst
end if
i = .RecordCount
End with

the movefirst and movelast just makes sure you have all the records, and then i gets set to the number of records. So if there's no records, i doesn;t get set, although I think you would get 0 anyway as a default value for i,.
I would also make sure you have option explicit set at the top.


I can only repeat - if you have a form with no records, and you have allow additions set to false, then I term it undefined. Effectively the form objects just don't exist, and therefore cannot be read. I think that must be what happens. You don't even get a null.

 

[zebrafoot]

With rs
If Not (.BOF And .EOF) Then
.MoveLast
.MoveFirst
i = .RecordCount
End If

would need to be this

With rs
If Not (.BOF And .EOF) Then
.MoveLast
.MoveFirst
end if
i = .RecordCount
End with

the movefirst and movelast just makes sure you have all the records, and then i gets set to the number of records. So if there's no records, i doesn;t get set, although I think you would get 0 anyway as a default value for i,.
I would also make sure you have option explicit set at the top.


I can only repeat - if you have a form with no records, and you have allow additions set to false, then I term it undefined. Effectively the form objects just don't exist, and therefore cannot be read. I think that must be what happens. You don't even get a null.

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