Counting a query (1 Viewer)

[waxdart23]

In a rostering database, I am using the following to list "Stewards" who are available to work (in a venue for a performance). These are displayed in an unbound text box called lstSteward.

Private Sub cmbPerformance_AfterUpdate()
    If IsNull(cmbPerformance) Then
        lstSteward.RowSource = ""
        lstSteward.Requery
    Else
        Dim strSQL As String
        strSQL = "SELECT StewardAvailability.Perf_ID, StewardDetails.First_Name, " _
            & "StewardDetails.Last_Name, StewardAvailability.Availability FROM StewardDetails " _
            & "INNER JOIN StewardAvailability ON StewardDetails.ID = StewardAvailability.Stewards_ID " _
            & "WHERE StewardAvailability.Perf_ID = " & cmbPerformance & " AND StewardAvailability" _
            & ".Availability = TRUE;"
        lstSteward.RowSourceType = "Table/Query"
        lstSteward.RowSource = strSQL
        lstSteward.Requery
    End If
End Sub

Is there any way of counting each "Steward" available for work and displaying it on the form (maybe in a text box called "totSteward" for example).

 

[bjackson]

have you tried to create a querydef using your strSQL

that way all you need to do is use the .recordcount

 

[waxdart23]

Is it as simple as adding -

Dim strSQL As QueryDef

Where do I add this and how do I then use the .recordcount?

Thanks

 

[bjackson]

after your End if you could try something like this
Dim Recset As DAO.Recordset
dim StewardsQdf as queryDef
Dim Dbs as database
dim NoOfstewards as long
Set dbs = CurrentDb()
Set StewardsQdf = dbs.CreateQueryDef("", StrSql)
Set Recset = StewardsQdf.OpenRecordset
With Recset

if not RecSet.bof and Not RecSet.eof then
recset.movefirst
recset.movelast
NoOfStewards=.recordcount
else
NoOfStewards=0
end if

RecSet.close
set RecSet=nothing
set StewardsQdf=Nothing

i am not a programmer so their may be a better way,
this is just how i would have to do it

 

[waxdart23]

Worked a treat. Many thanks.