CODEWITHSUNDEEP
sqlformsms-accessms-access-2007
skevthedev
skevthedev

Reputation: 465

query criteria based on form field

So I have a query where I select a field and set the criteria so that it only selects records based on the current value of a particular field in my form. The criteria looks like this.

[Forms]![FORMAL_CERT_REVIEW_CHECK_FORM]![REVIEW_CHECK_ID]

Pretty simple stuff. But I am running into the issue where when I run the query I get the prompt that says I need to enter a value. I know that this usually happens when you set the criteria to something that may not exist or you spelt it incorrectly, but I have checked all of this and it seems like everything looks fine.

I was curious if there is something I could be missing, like a property on the field or something that I have not thought of.

Upvotes: 4

Views: 13431

Answers (4)

Alan W.
Alan W.

Reputation: 1

Doesn't seem like this was ever solved, and I just came up with the same issue. I solved it by deleting the form and recreating it. Like you I had, [Forms]![MyForm]![ID] and out of no where it started asking for user input for the criteria on my listbox query. Making a new form and copying over the fields seems to have fixed it.

Upvotes: -2

Craig Fath
Craig Fath

Reputation: 1

I have experienced this on several occasions after making a design change to the form. There is no fault with what you have done - it is an access corruption. The solution is to copy the database to another file name, delete your forma and query, compact and repair, and then import the form and query again. This normally solves the problem. It appears importing it resets the internal references allowing the form to work as it should.

Upvotes: 0

Zoltan
Zoltan

Reputation: 1

Your description doesn't specify whether your form is a simple form or a sub-form. I came across the same issue and realized that I was only entering the sub-form's name in the criteria.

Assuming that you have FORMAL_CERT_REVIEW_CHECK_FORM sub-form under PARENT_FORM, your criteria should read

[Forms]![PARENT_FORM]![FORMAL_CERT_REVIEW_CHECK_FORM]![REVIEW_CHECK_ID]

I hope this helps you or others. Used in Access 2016.

Upvotes: 0

HansUp
HansUp

Reputation: 97101

When you directly open a query which includes a reference to a form control, Access is able to retrieve the query's parameter value from that control on the open form.

However, if you attempt to use the same query as the source for a recordset, Access does not resolve the query parameter from the open form.

For example, this is my query, qryREVIEW_CHECK_ID.

SELECT f.id, f.datetime_field, f.some_text
FROM tblFoo AS f
WHERE f.id=[Forms]![FORMAL_CERT_REVIEW_CHECK_FORM]![REVIEW_CHECK_ID];

With FORMAL_CERT_REVIEW_CHECK_FORM open, everything works fine when I open the form directly ... like this for example ...

DoCmd.OpenQuery "qryREVIEW_CHECK_ID"

However, using that query as the source for a recordset triggers error 3061, "Too few parameters. Expected 1."

Dim db As DAO.database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("qryREVIEW_CHECK_ID")

The workaround is to open the recordset from the QueryDef object. And include Eval() so that Access will resolve the parameter using the parameter's name.

Dim qdf As DAO.QueryDef
Set qdf = db.QueryDefs("qryREVIEW_CHECK_ID")
qdf.Parameters(0) = Eval(qdf.Parameters(0).Name)
Set rs = qdf.OpenRecordset

Upvotes: 4

Related Questions