Count cascading combo records 1

[Steven811]

Hi

I have a simple form with 4 cascading combo boxes that are sourced through queries and work fine.

To help the user I want to display a record count as a record from each combo is selected. I searched the archives and using my own initiative came up with something that doesn't work, so now I'm stuck.

In the control source of an unbound text box I inserted the following:

=Count([me.mycbo(2)])

It returns an [#error] message.

Can some one help.

Steven811

 

[PHV]

Have you tried this ?
=Me!mycbo.ListCount

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Steven811]

I get the #Name? error message.

Tried the following:

=Me!mycbo(1).ListCount

then

=Me!mycbo.IDfield.ListCount

All came up with the same error message.

 

[PHV]

Is the TextBox in the same form as the combo ?
Try to use the Expression Builder to get the syntax of the combo name in the loaded forms.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Steven811]

I have checked with the expression builder and the name is correct. I'm unsure whether I have to use

mycbo.(columnnumber)

or

mycbo.fieldname

 

[PHV]

Use mycbo.ListCount

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[RoyVidar]

Neither, just as PHV suggested

[tt]Me!NameOfCombo.Listcount[/tt]

will count the number of "rows" in the combo.

Or are you after the recordcount of the form? Then perhhaps

[tt]msgbox me.recordsetclone.recordcount[/tt]

or

[tt]dim rs as dao.recordset
set rs=me.recordsetclone
rs.movelast
msgbox rs.recordcount[/tt]

I seem to recall to questions by you, where you haven't told us what worked or not. It is encouraged, read one of the faq's in PHV's signature to get to know how to get the most out of the membership.

Roy-Vidar

 

[Steven811]

My apologies if I've not provided feedback on every entry. I do try but, must confess that if I've resolved a stubborn problem it's opened a floodgate that has taken my attention as I move on to the next issue on my db.

I'm very grateful for the support within the forum and it's the least I can do.

Consider me a reformed character.

 

[Steven811]

The parent form has 4 cascading combo boxes and the user selects a record from the 1st cbo, this provides the criteria for the 2nd cbo etc. To help the user I wanted to add a simple total number of records display of the record set count. I was using an unbound text box to do this.

This would update as the cbo records are selected/changed.

The records are based around:

cboRep
cboCustomer
cboSite
cboContact

The cbo boxes are sourced through queries and they populate combo boxes and sub forms. I can get a record count on the subforms through using [=count(fieldname)] no problem. The 2nd cbo does not populate a sub form, this is the 1st record set I wish to count.

When I use [Me!mycbo.ListCount] I receive the [#Name?] error message. This I thought was due to there not being a fieldname to reference.

 

[PHV]

Follow the Expression builder (...) when in the Control source property of the TextBox, find the form on the left pane, the combo on the middle one and the ListCount on the right pane.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TheAceMan1]

How are ya Steven811 . . . . .

First . . . to be sure of the [blue]proper name to use for a control on a form[/blue], just check the [purple]Name Property[/purple] (its at the top in the [purple]Other Tab[/purple]). When referring to the control, this is it!. You can even change it if you like to something more suitable.

Now . . . according to your needs your calculated control requires updating on combobox selections, so some event has to be used.

In the code module for the form, copy/paste the following code ([blue]you[/blue] check/substitute proper names in [purple]purple[/purple]):

[blue]Public Sub cbRecordCount()
   Dim n As Long, cbName As String, cnt As Long
   
   For n = 1 To 4
      cbName = Choose(n, "[purple][b]cboRep[/b][/purple]", "[purple][b]cboCustomer[/b][/purple]", "[purple][b]cboSite[/b][/purple]", "[purple][b]cboContact[/b][/purple]")
      
      If Me(cbName).ListIndex <> -1 Then
         cnt = cnt + Me(cbName).ListCount
      End If
   Next
   
   Me![purple][b]YourCalculatedControlName[/b][/purple] = cnt
   
End Sub[/blue]

Next . . . in the [blue]After Update[/blue] event of the comboboxes and the [blue]On Load[/blue] of the form, copy/paste the following code:

[blue]   Call cbRecordCount[/blue]

Finally . . . remove what you have in the [blue]Control Source[/blue] of the [blue]calculated control[/blue].

Thats it . . . . give it a whirl and let me know . . . .

See Ya! . . . . . .

 

[Steven811]

Hi TheAceMan1

I'm doing fine ta,

Your suggestion works really well. With your assistance I would like to modify it a little.

At the moment the calculated control sums the record sets as I work my way through the combo boxes and what I would like to be able to do is have it display only the number of records in the selected record set.

For example:

cboRep count 4, calculated control shows 4, then select rep and move on to the related customers for that rep and
cboCust count 12, calculated control shows 16 (wish to show 12) and so on through cbosite and cbocontact.

Thanks

Steven811

 

[TheAceMan1]

OK Steven811 . . . . .

[ol][li]Remove [blue]all[/blue] previusly given code. . .[/li]
[li]Then . . . in the [blue]After Update[/blue] of the comboboxes of interest, ([blue]you[/blue] copy/paste the following code):

[blue]Me!YourCalculatedControlName = Me![purple][b]ComboBoxName[/b][/purple].ListCount[/blue]

[/li][/ol]

See Ya! . . . . . .

 

[Steven811]

Thanks, works a treat.

I've used the list counting code elsewhere on my db too.

V grateful.