Solved Record count using lookup table (1 Viewer)

[Peter Peterson]

Hi - I have read #post-1637811 a number of times and modified my code but still can't get the result I need/expect.
I have a client table "tblClients" and a table "tblStatus" to track the status my clients are through a process, e.g. field [Status] (Lead, Appointment Booked, Appointment Held, Closed).
I have a field in tblClients [ClientStatus_ID] linked to tblStatus [StatusID].
I want to display on a form the number of clients at each stage of the process, i.e. how many clients are at the Lead stage, the Appointment Booked state, etc.
I followed @arnelgp code but I must have some misunderstanding of what I'm doing

Private Sub updateClientProcessNumbers()
Dim lngCount As Long
Dim varClientStatus As Variant

lngCount = DCount("*", "tblClients", "ClientStatus_ID=" & DLookup("Status", "tblStatus", "Status='Lead'"))
Me.cmdNumLead.Caption = CStr(lngCount)
End Sub


I end up an Run-time Error 2471 - The expression you entered as a query parameter produced this error: 'Lead'

Any help will be most welcome
Thanks Peter

 

[The_Doc_Man]

I'm not sure what you are looking up.

Let's split this up. You have a DLookup( "Status", "tblStatus", "Status='Lead'" ). What status did you expect it to give you OTHER than 'Lead', and therefore why did you bother to look it up?

Then In your DCount, you are saying to count the records with "ClientStatus_ID=Lead" (because what else COULD the DLookup return but that?) But in that context, did you mean that the status ID would actually be "Lead"? The problem is that the implied SQL for that DCount sees the expression "ClientStatus_ID=Lead" - but it can't know what Lead means in that context. It probably sees the word Lead as a field name.

The right way to do this MIGHT be:

lngCount = DCount( "*", "tblClients", "ClientStatus_ID='Lead'" )

To better understand what happened here, check the quoting. But the truth is, I had to guess at what you were doing because you have what is essentially a tautology in that DLookup.

 

[arnelgp]

can you open the Status table and loop through each record:

Private Sub updateClientProcessNumbers()

   Dim lngCount As Long
   Dim db As DAO.Database
   Dim rs As DAO.Recordset

   Set db = CurrentDb
   Set rs = db.OpenRecordset("tblStatus", dbOpenSnapshot, dbReadOnly)
   With rs
        If Not (.BOF And .EOF) Then
            .MoveFirst
        End If
        Do Until .EOF
            'arnelgp
            'Change !StatusID for the correct Long Integer field in tblStatus
            lngCount = DCount("1", "tblClients", "ClientStatus_ID = " & !StatusID)
          
            Select Case !status
            Case "Lead"
                Me.cmdNumLead.Caption = CStr(lngCount)
            Case "Appointment Booked"
                Me.cmdNumAppBooked.Caption = CStr(lngCount)
            Case "Appointment Held"
                Me.cmdNumAppHeld.Caption = CStr(lngCount)
            Case "Closed"
                Me.cmdNumClosed.Caption = CStr(lngnum)
            End Select
          
            .MoveNext
        Loop
        .Close
    End With
    Set rs = Nothing
    Set db = Nothing

End Sub

 

[theDBguy]

Hi. Have you considered simply using a Totals query displayed as a subform on your form?

Just a thought...

 

[Peter Peterson]

Thankyou all for replying so quickly.
I originally had the 'Status' as a lookup in the Clients table, and read somewhere on this forum to avoid such practice and replace my Lookup lists with tables, which is now challenging my knowledge and skills - but enjoying the learning.
I agree with everything you wrote @The_Doc_Man and fiddled with my code to check the result. I am trying to compare "Lead" against ClientStatus_ID which is a Number - never going to work!

I managed to find my way there: lngCount = DCount("ClientStatus_ID", "tblClients", "ClientStatus_ID=1")

I have simply repeated the line of code for each Status type.

However, I am going to review @arnelgp code with the view to loop my code.

Thank you also to @theDBguy for your alternative solution.

I'm sure I'll be back soon with more questions.

Cheers Peter