Record number count in VBA

[Abg]

Hello,
I have a database and I need to write a VBA code to count no of record by location.
I am new to vba. Is there any way to do this ?
I think, probably , in VBA , write a query statement, open query , count record and spit the information for record by location . Does any body has code for this ?

In advance , Thanks for help
Abg

 

[pr2-eugin]

Did you give DCount a try?

 

[Abg]

Thanks Paul . My problem is , I have a table and associated form , Table has field called "Physical Location" and I need to find number of records by physical location . So I designed a form and place a command button . When I press this button ,I need to display information about no. of record by Physical location .

 

[pr2-eugin]

So do you have a ComboBox which you will select and then click a button or is it just a button click and then the display the result?

 

[Abg]

Hi Paul , I will define constant in the VBA code as my Physical locations are fixed. i.e. I will just click the button and it then display the result.

 

[pr2-eugin]

So it would be just a matter of..

Dim totalRecords As Long, Dim constLocation As String
constLocation = "Bournemouth"
totalRecords = DCount("*", "yourTableName", "locationFieldName = '" & constLocation & "'")
Call MsgBox("The total records for " & constLocation & " is : " & totalRecords)

 

[Abg]

Thanks Paul . It worked fine. Now , I have another requirement , Is it possible to store the data displayed using msgbox into a text file ?

Thanks for help.
Ashvin

 

[pr2-eugin]

Yes, the easier way to do this would be to simply use a Query and Export the Query result, to an Excel Sheet (if you want even to text file).. All you have to do is create a TOTALS query.. Something along the Lines of..

SELECT locationFieldName, Count(locationFieldName) As CountOfLocations 
FROM yourTableName
GROUP BY locationFieldName;

Then use DoCmd.OutputTo Method to export the Query..

 

[Abg]

Hi Paul,
My location fileld name is "PhysicalLocation"
Table name is BP Card Test Traveler
I am not sure what to put here : Countoflocations ( may be a name of location,e.g. london)

Is this correct syntax , ? I am getting an error.

SELECT PhysicalLocation, Count(Physicallocation) As Countoflocations FROM [BP Card Test traveler] GROUP BY physicallocation;

 

[pr2-eugin]

What is the error, that you are getting? Which part of the SQL statement is the Error?

PS: This should be a NEW Query, using the Query Design.. Not in a Module/Form module.. Copy the Query in the SQL design view of a New query save the Query by giving it a Name, and then use the name of the Query in the DoCmd.OutputTo method..

 

[Abg]

Hi Paul ,
Yes, it works. I was adding the query in a vba module . This is why it did not work. Is it possible to add similar type of query in vba module ?

Thanks
Ashivn

 

[pr2-eugin]

You can open the stored Query using DoCmd.OpenQuery method.. Or set this to a RecordSet variable..

 

[Abg]

Thanks Paul.
Do you think ,we can use docmd.runsql with following sql query .
docmd.RunSQL SELECT [Table_name].PhysicalLocation, Count([Table_name].Physicallocation) AS Nf_Brds, [Table_name].BuildRev
FROM [Table_name]
GROUP BY [Table_name].PhysicalLocation, [Table_name].BuildRev
ORDER BY [Table_name].PhysicalLocation;

I am getting an error message (syntax errror) when i combined with docmd.run with sql from query.

Abg