Return Count on records of related files

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to return the count of related records between 2 files without using a
saved query. ie I want to do it all in the code.

For example if I make an Update Query in the Query grid and dont save the
query but just take the SQL code and put it in the form code I will use:

DoCmd.RunSQL "UPDATE .............;" on an event in the form.

I want to do this ; but to count the records of the related files.

I tried taking the SQL code from a count query and do DoCmd.RunSQL like the
UPDATE above as:

DoCmd.RunSQL "SELECT Count(BatchInvoice.InvNum) AS CountOfInvNum
FROM tableInvoices INNER JOIN BatchInvoice ON (tableInvoices.InvNum =
BatchInvoice.InvNum);"

But this does not work.

I cant use DCount(Expression,Domain,Criteria) because the Domain would have
to be a table or a saved query, right?

How do you do this count in the code without using a saved query?

Thank you very much for your help.

Steven
 
Steven said:
I want to return the count of related records between 2 files without using a
saved query. ie I want to do it all in the code.

For example if I make an Update Query in the Query grid and dont save the
query but just take the SQL code and put it in the form code I will use:

DoCmd.RunSQL "UPDATE .............;" on an event in the form.

I want to do this ; but to count the records of the related files.

I tried taking the SQL code from a count query and do DoCmd.RunSQL like the
UPDATE above as:

DoCmd.RunSQL "SELECT Count(BatchInvoice.InvNum) AS CountOfInvNum
FROM tableInvoices INNER JOIN BatchInvoice ON (tableInvoices.InvNum =
BatchInvoice.InvNum);"

But this does not work.

I cant use DCount(Expression,Domain,Criteria) because the Domain would have
to be a table or a saved query, right?

How do you do this count in the code without using a saved query?
Click to expand...


RunSQL needs an action query, not a select query. To
retrieve data you have to open a recordset on a select
query:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL = "SELECT Count(*) FROM . . ."

Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL)
xx = rs.Fields(0)
rs.Close : Set rs = Nothing
Set db = Nothing
 
Back
Top