using count in a field in a form 1

[tkemner]

I want to make a field in a form that counts records in a table that is not the record source for the table and limit the count based on a field in the current form. Any ideas out there?

 

[smedvid]

I've used the following approach...
Setup a variable that does a DCount() with the where condition equal to the field from the form. Then create a text field (unbound) on the form. Set that form field to the variable used to hold Dcount() results... Be sure to refresh as necessary... htwh... Steve Steve Medvid
"IT Consultant & Web Master"
e-Mail: Stephen_Medvid@GMACM.com

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[FoxProProgrammer]

You can use the following code to count the records.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim numrecs As Integer

Set db = CurrentDb
Set rs = db.OpenRecordset("Select * From ... Where field = ...", dbReadonly)

rs.MoveLast

numrecs = rs.RecordCount

If the data in the field that you are searching for is a string, you have to enclose it in single quotes. dz
dzaccess@yahoo.com

 

[FoxProProgrammer]

Sorry, Steve. We must have been typing at the same time. Your approach should work as well.

Best, dz
dzaccess@yahoo.com

 

[smedvid]

No problem... best to see varying approaches to solving the same simple problem...

I was an orignal FoxPro'er also with FoxBase from the old days. Haven't seen VFP since VFP 3.x though... Steve Medvid
"IT Consultant & Web Master"
e-Mail: Stephen_Medvid@GMACM.com

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[yotwiggy]

You did not give much information what version of access are you using? What level of programming can you do?