I am using this dcount function in my access vba code

TotAanwezig = DCount("tblPersoneel.ID", "Query2", "[TrID] +1 = " & Me.TrainingID & " +1 AND [Toegang] = " & Me.ListToegang & "")

The thing is, I would like to removed the query from access (Query2) and just use a vba variable. The sql would be something like:

varCount = "SELECT Count(*) AS CountRows, tblPersoneel.ID, tblPersoneel.Achternaam, tblTrgRegDetails.PersID, tblPersoneel.Toegang, tblTrgRegDetails.ID, tblTrgRegDetails.TrID " & _
"FROM tblTrgRegDetails RIGHT JOIN tblPersoneel ON tblTrgRegDetails.PersID = tblPersoneel.ID " & _
"WHERE [TrID] +1 = " & Me.TrainingID & " +1 AND [Toegang] = " & Me.ListToegang & ";"


How do I assing the value of CountRows to the form field TotAanwezig?

Do I need to use recordsets or cant it be done easier? Thanks in advance!

The domain function provides a shortcut. The alternative is to use DAO or ADO to open a recordset and read the resulting record.