Solved: Record Count

[kbsudhir]

Hi all,

I want to execute a query on the click of the button of the access form.
I do not want the records generated by te query but I need just the count of the records.

How should carry out this..????

:help :help

Thanks
Sudhir

[CreganTur]

Instead of running a query, you can use the DCount function to get a count of records in a table. Search for DCount in Access VBA Help to get some good info on using this function.

[kbsudhir]

I have done this in manner as shown below


Dim cn As ADODB.Connection, rs As ADODB.Recordset
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\My Documents\Team\TeamData.mdb;"

Set rs = New ADODB.Recordset

rs.Open "Select TrainingPlanner.TrainingName From TrainingPlanner Where (((TrainingPlanner.SchDate) = ' & Forms!UserForm!List41 & ') AND ((TrainingPlanner.SchTimings) = ' & Forms!UserForm!List43 & ') AND ((TrainingPlanner.SchMonth) = ' & Forms!UserForm!List31 & ') AND ((TrainingPlanner.Team) = ' & Forms!UserForm!List46 & '))", cn, adOpenStatic, adLockReadOnly


Sometimes it works but there are instances where I get the following error

[Run-time error '-2147467259(80004005)':
The database has been places in a state by user 'Admin' on machine
"xyz" that prevents it from being opned or locked]

in line:

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\My Documents\Team\TeamData.mdb;"


Please let me know what should I do to overcome this error.

All the code is written on the button click of the access form which is in the same database which I am trying to open.

Thanks
Sudhir

[CreganTur]

Okay, didn't realize you were using an ADO connection. That makes things easier.

ADO has a built in function called RecordCount that you can use. So, after you open the connection to your record source, you can then use rs.RecordCount to get the total number of records that have been pulled.


Sometimes it works but there are instances where I get the following error

[Run-time error '-2147467259(80004005)':
The database has been places in a state by user 'Admin' on machine
"xyz" that prevents it from being opned or locked]

That error means that you're trying to access an already open recordset. Generally it is encountered when either:

you're trying to connect to an Access database that is already open
The same ADO connection is already open and was not closed properly last time

[kbsudhir]

Here is the query which I am using


SELECT TrainingPlanner.TrainingName FROM TrainingPlanner WHERE (((TrainingPlanner.SchDate)= ' & Forms!UserForm!List41 & ') AND ((TrainingPlanner.SchTimings)= ' & Forms!UserForm!List43 & ') AND ((TrainingPlanner.Team)= ' & Forms!UserForm!List46 & ') AND ((TrainingPlanner.SchMonth)= ' & Forms!UserForm!List31 & '))

The problem is query is not pulling any records. Hence the record count is zero "0"

But when I write the same query in access queries & give the same parameters manually its pulling 2records but when i select teh same parameters from the listboxes then no records are being pulled.

I am not able to understand why its happening..???

Any views on this..????

Sudhir

[CreganTur]

I am not able to understand why its happening..???

Any views on this..????

It's because you're not concatenating your VBA with your SQL correctly. You have to replace all of your single quotes with double quotes. Also, you then have to add specific symbols to tell SQL what data your VBA values are. If the value is numeric then you don't use a symbol. If it's a String, then you must add in single quotes: '" & "'
If you're using Dates, then you must use pound signs: #" & "#

In the code below, I set your schDate as String (using single quotes)- if you need it to be Data data type, then change the single quotes to pound signs.



"SELECT TrainingPlanner.TrainingName FROM TrainingPlanner WHERE" _
& " (((TrainingPlanner.SchDate)= '" & Forms!UserForm!List41 _
& "') AND ((TrainingPlanner.SchTimings)= '" & Forms!UserForm!List43 _
& "') AND ((TrainingPlanner.Team)= '" & Forms!UserForm!List46 _
& "') AND ((TrainingPlanner.SchMonth)= '" & Forms!UserForm!List31 & "'))"

[kbsudhir]

Thanks Rabdy, taht was very helpful & also cleared my concept of using ",' & # while writing the query in VBA.

Once again thanks very much.
:bow: :bow: :bow:

Sudhir