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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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
TrygveCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.