number of records?

[cyberpac9]

i have a query that i've used on some forms that returns a list of records. however, i have some users who don't want the entire list...they'd just like a form that, after entering their parameters, returns the number of records (sort of like a count)....how would i accomplish that using vba?

i've done similar things using PHP but not sure how to do this using VBA. i was thinking something like (using my own terms not actual VBA code):
stSQL = "complex sql string"
result = execute (stSQL)
cntResult = countOfRecords (result)

is there any way to do anything like this?

 

[gromit]

Hi -

If it is an action query you can do something like is shown in the following thread:
http://www.access-programmers.co.uk/forums/showthread.php?t=98643

If it is a select query, then you would need to assign the results to a recordset and use the RecordCount property.

hth,

- g

 

[cyberpac9]

Hi -

If it is an action query you can do something like is shown in the following thread:
http://www.access-programmers.co.uk/forums/showthread.php?t=98643

If it is a select query, then you would need to assign the results to a recordset and use the RecordCount property.

hth,

- g

i'll give that a look and see if that helps....thanks!

 

[DB7]

cyberpac9, all you need is a DCount() result.
Not need to run a query or open a recordset.
Look up DCount in VBA help.

Dim intCount As Integer
Dim strCriteria As string
strCriteria = "txtCountry Like 'A*'"

intCount = DCount("pkID","tblCountry",strCriteria)

 

[cyberpac9]

cyberpac9, all you need is a DCount() result.
Not need to run a query or open a recordset.
Look up DCount in VBA help.

Dim intCount As Integer
Dim strCriteria As string
strCriteria = "txtCountry Like 'A*'"

intCount = DCount("pkID","tblCountry",strCriteria)

i would definitely like to use the dcount (seems very simple and straight forward....now, here's my only problem (i think)...like i said before, i have another form that is based on a query and now some users just want the number and not the list...here is what the query looks like in a simplified version:

SELECT t1.id, t1.name, t2.deptname, t3.id, t3.trainer 
FROM t1 INNER JOIN t2 ON t1.id = t2.t1id 
WHERE EXISTS (SELECT * FROM t2 WHERE t2.id = t3.t2id)

basically, the code from the original form is complex with multiple tables with a couple INNER JOINS and an EXISTS (SUB-SELECT)...can something like this be put into a DCount()?

your example is pretty straightforward but using a simple select statement...i need something a little more complicated....can this be done?

 

[DB7]

If you save the Query in the database, then yes.
Let's say you call it "qryt1", then your DCount would look like...
=DCount("id","qry1").

 

[cyberpac9]

If you save the Query in the database, then yes.
Let's say you call it "qryt1", then your DCount would look like...
=DCount("id","qry1").

perfect....i'll be giving that a try this afternoon....thanks!

 

[cyberpac9]

If you save the Query in the database, then yes.
Let's say you call it "qryt1", then your DCount would look like...
=DCount("id","qry1").

that works great....now, instead of having it call a query that is saved (qry1) how do i get it to work with a query string in VBA?

i have: stSQL = "My long query".
then, intCount = DCount("participant_id", "stSQL") but this will not work...i guess i need it to process stSQL before DCount can use stSQL...

(note: my query is a part of the VBA because i will use dynamic criteria in the query - date, trainer, class.)

 

[cyberpac9]

using the code like i have above i get "Run-time error 208: Ivalid object name 'stSQL'"

i've declared stSQL as a string at the top, stored my query in stSQL...how is it invalid?

 

[Bat17]

you dont want quotes around stSQL - it is a variable
Not sure your SQL string will work in Dcount but wotrth a try,

peter

 

[cyberpac9]

you dont want quotes around stSQL - it is a variable
Not sure your SQL string will work in Dcount but wotrth a try,

peter

yeah, i've tried removing the quotes...doing so give me a "Runt-time error '30020': Cannot find column 'C1'"....

 

[Bat17]

http://www.access-programmers.co.uk/forums/showpost.php?p=453715&postcount=5

Peter

 

[cyberpac9]

http://www.access-programmers.co.uk/forums/showpost.php?p=453715&postcount=5

Peter

i hate to be a pain, but this is not working for me...i should have stated that i'm using Access as a front-end to MSSQL...some Access VBA works, while others have special syntax...

when i try to implement your suggestion it gives me an error saying the "User defined type not defined"....has to be referring to DAO.Recordset....

 

[Bat17]

You may need to set a reference to DAO depending on your version of Access.
in any module goto Tools>References.... and check that "Microsoft DAO 3.6 Object Library" is selected (version number may vary)

HTH

Peter

 

[SammyJ]

Out of curiosity have you gotten the non-count method to work? IE, for the users who do want to see the details?

If so you must be successfully creating a SQL string which returns records whether from Access or from MSSQL, so you can create a recordset and get a recordcount very easily.

DCount won't work with a SQL string, it is designed for simple queries only.

Sam.

 

[cyberpac9]

Out of curiosity have you gotten the non-count method to work? IE, for the users who do want to see the details?

If so you must be successfully creating a SQL string which returns records whether from Access or from MSSQL, so you can create a recordset and get a recordcount very easily.

DCount won't work with a SQL string, it is designed for simple queries only.

Sam.

yes the query works...it works on the other form and in Query Analyzer (MS SQL tool)...so i know that part works...i just need it to give me a count...

so, if it is very easy, could you please enlighten me...i can't seem to get it to work...

@Bat17:
i added the reference for DAO and ran the code...i now get a "Run-time error 91: Object variable or With block variable not set"....and when i go to debug it takes me to the line: Set rst = CurrentDB.OpenRecordset (stSQL)

 

[SammyJ]

This should do it:

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

...

Set db = CurrentDB
Set rs = db.OpenRecordset(strSQL)

if rs.EOF then
intCount = 0
else
rs.movelast
intCount = rs.RecordCount
end if

...

 

[cyberpac9]

This should do it:

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

...

Set db = CurrentDB
Set rs = db.OpenRecordset(strSQL)

if rs.EOF then
intCount = 0
else
rs.movelast
intCount = rs.RecordCount
end if

...

i get the same runtime error i posted in my last message..."Run-time error 91: Object variable or With block variable not set"

 

[SammyJ]

Can you compile your database?

If so copy and paste in here the exact code on which the error is occurring.

 

[cyberpac9]

Can you compile your database?

If so copy and paste in here the exact code on which the error is occurring.

unable to post the database but i'll post the exact code:

Public Sub cmdCalculate_Click()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim stSQL As String
Dim intCount As Integer

stSQL = "SELECT dbo.tbl_class_participant.participant_iden, dbo.tbl_class.class_type_id, dbo.tbl_class.class_date, " & _
                      "dbo.tbl_class_participant.participant_lname, dbo.tbl_class_participant.participant_fname, dbo.tbl_class_participant.department_id, " & _
                      "dbo.tbl_class_participant.building_id, dbo.tbl_class_participant.participant_room_num, dbo.tbl_class_participant.participant_status, " & _
                      "dbo.tbl_class_participant.dept_other, dbo.tbl_class_participant.phone, dbo.tbl_class_participant.sort, dbo.tbl_class_trainer.trainer_fname, " & _
                      "dbo.tbl_class_trainer.trainer_lname " & _
                      "FROM dbo.tbl_class_participant INNER JOIN dbo.tbl_class INNER JOIN dbo.tbl_class_listing " & _
                      "ON dbo.tbl_class.class_id = dbo.tbl_class_listing.class_id ON dbo.tbl_class_participant.participant_id = dbo.tbl_class_listing.participant_id " & _
                      "INNER JOIN dbo.tbl_class_trainer ON dbo.tbl_class.trainer_id = dbo.tbl_class_trainer.trainer_id " & _
                      "WHERE EXISTS (SELECT * FROM dbo.tbl_class_listing WHERE dbo.tbl_class.class_id = dbo.tbl_class_listing.class_id) " & _
                      "AND (dbo.tbl_class.class_type_id = 'HAZWASTE') " & _
                      "AND (dbo.tbl_class.class_date BETWEEN '01/01/2006' AND '01/06/2006') " & _
                      "AND (dbo.tbl_class.trainer_id = '6')"

Set db = CurrentDb
Set rst = db.OpenRecordset(stSQL)

If rst.EOF Then
        intCount = 0
Else
        rst.MoveLast
        intCount = rst.RecordCount
End If

Result = intCount

On Error GoTo Err_cmdCalculate_Click

Exit_cmdCalculate_Click:
    Exit Sub

Err_cmdCalculate_Click:
    MsgBox Err.Description
    Resume Exit_cmdCalculate_Click
    
End Sub

NOTE: i have hard-coded the date, class name and trainer id at the end of stSQL, however this will come from an entry form...it is hard coded just for testing purposes...will change once i have this working properly...i have even tried removing those three items from the list and it still doesn't work....

thanks for your help...