Counting records in a querydef

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
LVL 1
tuckAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
TrygveConnect With a Mentor Commented:
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
 
EdithfCommented:
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
 
TrygveCommented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
tuckAuthor Commented:
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
 
martiCommented:
Ask him to post his comment as an answer
0
 
boazmCommented:
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
All Courses

From novice to tech pro — start learning today.