RecordCount of recordset for specific records.

[Gibson]

Is it possible to find the recordcount of a recordset that contains a
specific field. I mean if a recordset lists multiple records for 5
different invoices can I, through code, get the number of records for
invoice number 2?

Thanks

 

[PC Datasheet]

DCount("*","TblInvoice","[InvoiceNumber] = 2")

 

[Gibson]

Thanks for the quick response. One additional question though. The
recordset I am looking into, what you refer to in you code as "TblInvoice"
is actually a DAO recordset I created from a query with the following:
Set qdf = db.QueryDefs(stDocName)
Set prm = qdf.Parameters(0)
'Set parameter value
prm = strFile
'Execute QueryDef to produce a recordset
Set rs = qdf.OpenRecordset

Is there a way to reference the recordset in place of the "TblInvoice" in
your code? If not, could I set up two criteria in your code, one for "File"
and the second for "InvoiceNumber"?
I tried a few variations with no luck.

Thanks again for the help. I am keeping a file that illustrates and explains
any sample code or suggestions I receive through my various postings.
Hopefully one day I will learn this well enough to not be such a frequent
"poster" or even make some suggestions to others.

DCount("*","TblInvoice","[InvoiceNumber] = 2")


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Is it possible to find the recordcount of a recordset that contains a
specific field. I mean if a recordset lists multiple records for 5
different invoices can I, through code, get the number of records for
invoice number 2?

Thanks

 

[PC Datasheet]

You can't reference the recordset but you can reference the query you built
the recordset from. Just substitute the name of the query for TblInvoice.
BTW, all the quotes are necessary!

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Thanks for the quick response. One additional question though. The
recordset I am looking into, what you refer to in you code as "TblInvoice"
is actually a DAO recordset I created from a query with the following:
Set qdf = db.QueryDefs(stDocName)
Set prm = qdf.Parameters(0)
'Set parameter value
prm = strFile
'Execute QueryDef to produce a recordset
Set rs = qdf.OpenRecordset

Is there a way to reference the recordset in place of the "TblInvoice" in
your code? If not, could I set up two criteria in your code, one for
"File" and the second for "InvoiceNumber"?
I tried a few variations with no luck.

Thanks again for the help. I am keeping a file that illustrates and
explains any sample code or suggestions I receive through my various
postings. Hopefully one day I will learn this well enough to not be such a
frequent "poster" or even make some suggestions to others.

DCount("*","TblInvoice","[InvoiceNumber] = 2")


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Is it possible to find the recordcount of a recordset that contains a
specific field. I mean if a recordset lists multiple records for 5
different invoices can I, through code, get the number of records for
invoice number 2?

Thanks

 

[Amy Blankenship]

You should be able to apply a filter to the recordsetclone and count the
records in that.

HTH;

Amy

Thanks for the quick response. One additional question though. The
recordset I am looking into, what you refer to in you code as "TblInvoice"
is actually a DAO recordset I created from a query with the following:
Set qdf = db.QueryDefs(stDocName)
Set prm = qdf.Parameters(0)
'Set parameter value
prm = strFile
'Execute QueryDef to produce a recordset
Set rs = qdf.OpenRecordset

Is there a way to reference the recordset in place of the "TblInvoice" in
your code? If not, could I set up two criteria in your code, one for
"File" and the second for "InvoiceNumber"?
I tried a few variations with no luck.

Thanks again for the help. I am keeping a file that illustrates and
explains any sample code or suggestions I receive through my various
postings. Hopefully one day I will learn this well enough to not be such a
frequent "poster" or even make some suggestions to others.

DCount("*","TblInvoice","[InvoiceNumber] = 2")


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Is it possible to find the recordcount of a recordset that contains a
specific field. I mean if a recordset lists multiple records for 5
different invoices can I, through code, get the number of records for
invoice number 2?

Thanks

 

[Marshall Barton]

Is it possible to find the recordcount of a recordset that contains a
specific field. I mean if a recordset lists multiple records for 5
different invoices can I, through code, get the number of records for
invoice number 2?

I think you want something like:

rs.Filter = "InvNum = 2"
Set rsx = rs.OpenRecordset
rsx.MoveLast
numrecs = rsx.RecordCount
rsx.Close: Set rsx = Nothing