Record Count in Main Switchboard Form.

[Corneliu]

I am trying to list the number of shows on the main SwitchBoard Form. Everytime I try to do a count and than list it in a List Box or Text Box, nothing comes up.

I tried it thru a query as well and still nothing.
When I run the query it returns the number of shows, but when I am in the main switchboard, there is no number in the list box or text box.

Anyone know how to get a total number of records from a table and list it in the main switchboard form? If a query is needed, I already have one that returns the proper number, just wont display it on the main switchboard.

THANK YOU...

 

[BNPMike]

Why would you use a list box for one value? Does your Switchboard form have a count query as it's record source?

 

[kevinf2349]

I would set up a function within the forms module like this:

Private Function Countrecs() As Integer
Static lngX As Long
Dim rst As Recordset
Dim strSQL As String
On Error GoTo Whoops
strSQL = "SELECT * from tblContact where tblContact.[Town] = " & Me!TownID & ";"
Set rst = CurrentDb.OpenRecordset(strSQL)
If rst.RecordCount <> 0 Then
rst.MoveLast
End If
Countrecs = rst.RecordCount
Exit_it:
Exit Function
Whoops:
Resume Exit_it
End Function

Then, in the Control.Source for the text box on the form type:

=Countrecs()

That should then return a count of the number of records in the query.

Hope this helps

 

[adamroof]

I have the same thing, but its not really a switchboard, but will work the same. OnCurrent put in code below, it doesnt matter what form you put it on as long as you reference the right table!

Me.txtAdam.Value = DCount("*", "Incident", "ISStaffID = 1 AND StatusID < 6")
Me.txtBryce.Value = DCount("*", "Incident", "ISStaffID = 2 AND StatusID < 6")
Me.txtLynsey.Value = DCount("*", "Incident", "ISStaffID = 3 AND StatusID < 6")
Me.txtMike.Value = DCount("*", "Incident", "ISStaffID = 4 AND StatusID < 6")

in a nutshell, its like this....

me.(controlname on form).value = DCOUNT("All Fields From table Incident" Where Staff Number = 1 And Status of incident is not complete") My tables state the status is a value from 1 to 7, and the staff is assigned a number.