Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Counting records in a querydef

Posted on 1998-05-04
6
Medium Priority
?
1,895 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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
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 400 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

927 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