Solved

Counting records in a querydef

Posted on 1998-05-04
6
1,597 Views
Last Modified: 2010-07-27
I have a problem when i count records in a querydef.  It works ok when i do not have a 'WHERE' call in the querydef and correctly gives me the number of records, but when i have the 'WHERE' statement included the line set rst = querydf asks for another parameter. Any ideas
Thanks
here is the code that iam using

Dim dbs As Database, rst As Recordset
Dim qd As QueryDef

Set dbs = CurrentDb
Set qd = dbs.CreateQueryDef("", " SELECT * from Projects_ WHERE 'Field1' = [Forms]![Front page]![field1] ")
 
   

Set rst = qd.OpenRecordset()
   
    MsgBox rst.RecordCount
    rst.Close
    Set dbs = Nothing
0
Comment
Question by:tuck
6 Comments
 
LVL 1

Expert Comment

by:Edithf
ID: 1961683
The reason why you are asked for another parameter is when you run the query you don't have the form "Front Page" open or you don't a field called [Forms]![Front page]![field1].

If you have that form open and the name field1 is on the form then this should work without asking you another parameter
0
 
LVL 12

Expert Comment

by:Trygve
ID: 1961684
Another problem with this syntax is that Access may misinterpret the forms... part as a parameter that is to be input by the user. A better way of writing your line is like this;

Set qd = dbs.CreateQueryDef("", " SELECT * from Projects_ WHERE 'Field1' = '" & [Forms]![Front page]![field1] & "'")

Your query is comparing the field on the form against the string value 'Field1'. If you want it to compare Field1 on the table against the field on the form you should write this.

Set qd = dbs.CreateQueryDef("", " SELECT * from Projects_ WHERE [Field1] = '" & [Forms]![Front page]![field1] & "'")

Note that the form-part has been extracted from the string to force access to look upon it as a reference to a field on the form. Also note the apostrophes on each side forcing access to look upon the value as a string value.

Hope this helps !
Trygve

0
 
LVL 1

Author Comment

by:tuck
ID: 1961685
Let me no how to transfer the points to trygve as this answer is the one that works thanks for the answer.
The forms were open and the fields were there.
Trygve answer seems most correct.

0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 2

Expert Comment

by:marti
ID: 1961686
Ask him to post his comment as an answer
0
 

Expert Comment

by:boazm
ID: 1961687
tuck, I agree with Trygve's answer, there just one more tip:  opening a Recordset does not always gives the RecordCount property the proper value right away. In that case you have to perform rst.MoveLast before using rst.RecordCount

0
 
LVL 12

Accepted Solution

by:
Trygve earned 100 total points
ID: 1961688
Thanks good people !

boazm is right. To ensure getting the correct recordcount you should include the rst.movelast line.

Another way of getting hold of the recordscount is to use a DCount statement.

Dim NoOfRecs as long
NoOfRecs=DCount("[Field]","Projects_","[Field1] = '" & [Forms]![Front page]![field1] & "'")
MsgBox(NoOfRecs)

Have a nice day everyone !
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now