Using SQL vs Query Grid

[Douglas]

I want to get the result of a count where I have to use an
inner join on two tables. In this example I want to
return CountOfStatus so I can tell if there are any
accounts not active before the entry will be allowed to
append to another table; ie the main table. Can you
please help me with the DoCmd.???? that would be used
here....I think. I do not want a DCount() on a query
created in the query tab. I want to do it with straight
code so it will be flexible and later I can change the
Entry table to Entry1 or Entry2 etc, depending on the
table I want to test, and just concatenate the string to
get my CountOfStatus result. Thank you very much for your
help.

SELECT Count(ChartOfAccounts.Active) AS CountOfStatus
FROM ChartOfAccounts INNER JOIN Entry ON
(ChartOfAccounts.Co = Entry.Co) AND
(ChartOfAccounts.Account = Entry.Account)
WHERE (((ChartOfAccounts.Active)=0));

 

[Albert D. Kallal]

If it is a save ed query, then you can use:

Dim rstRecs As DAO.Recordset

Set rstRecs = CurrentDb.QueryDefs("qryAccountCount").OpenRecordset

If rstRecs.RecordCount > 0 Then
MsgBox "Yup...is accounts"
else
msgbox "no accounts"
End If

rstRecs.Close
Set rstRecs = Nothing


So, the above assumes you saved the query in the query builder.

if you want to do the above code in-line, you can use:

Dim rstRecs As DAO.Recordset
dim strSql as string


strSql = "SELECT Count(ChartOfAccounts.Active) AS CountOfStatus " & _
"FROM ChartOfAccounts INNER JOIN Entry ON " & _
"(ChartOfAccounts.Co = Entry.Co) " & _
" AND " & _
"(ChartOfAccounts.Account = Entry.Account)"

set rstRecs = currentdb.OpenRecordSet(strSql)

If rstRecs.RecordCount > 0 Then
MsgBox "Yup...is accounts"
else
msgbox "no accounts"
End If

rstRecs.Close
Set rstRecs = Nothing

Remember to both close and set the reocrdset to nothing when done...

 

[Douglas]

Thank you very much. Now I get it. This will save me a
ton of time where I have had to make a new query for each
situation but now I can use variables for table names and
concatenate the variables in the string and make it much
more efficient. I really appreciate your help.