Query can't find object
Posted on 2012-09-14
(This is Access 2007, not sure if the question is posted to the right spot)
I have a 2 part question. What I am trying to do is use the entry from a combo box as the criteria for a field in a select query. If a name is selected, use that name, if nothing is selected search everything. The bound column for the combo box is the person's ID number, so although the selection is text the search uses a numeric field. After lots of searching yesterday, I was able to finally come up with the below as an expression, with the criteria set as "true". "CatchSummary" is the table I am searching, "CatchByCode" is the ID number for the person. "NameForSearchCombo" is the combo box on teh master form which returns the ID number of the person selected. If nothing is selected, search for all (ID #s >0); if something is selected search for just that one. This was working yesterday, but when I opened the DB today, the query can't find the "NameForSearchCombo" object and presents a parameter query dialog box. After some fiddling around, using just the name instead of all the iif language, I was able to get it working again. I then closed and reopened the DB and the object again was not found.
So my questions are: 1) Is there a better way to do this? and 2) Why does the DB "lose" this object one it is closed.
Expression in the Field row: