[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Query can't find object

Posted on 2012-09-14
Medium Priority
Last Modified: 2012-09-19
Hi Everyone,

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


Question by:Andy419
  • 4
LVL 77

Expert Comment

ID: 38399671
The method I use is to modify the Where clause in SQL view......

where ([CatchSummary].[CatchByCode]=[Forms]![Masterform]![NameForSearchCombo]) or [Forms]![Masterform]![NameForSearchCombo] is null

Author Comment

ID: 38399759
Thanks!  That didn't seem to work either, but maybe this is a larger issue:  I tried to do a very basic select query using a date field as the criteria.  I even used the builder to make sure the object was being seen and was pasted in correctly.  Even with this the query couldn't see the object and put up a parameter query dialog box.    Does anyone know why the query is not recognizing any of the objects on the master form?  I've done this probably 1,000 times without any issues -- I don't get why this time it doesn't work.

Author Comment

ID: 38399875
One more update -- it seems like the objects are not being loaded to memory(?) when the DB is opened.  When I first open the DB and try to run the query I get a dialog box since the object isn't being recognized.  If I load use the Object Browser or just use <F-11>, once I exit that screen, the query works just fine.  Is there a way to make sure that all objects are loaded when the DB is opened?

Accepted Solution

Andy419 earned 0 total points
ID: 38399920

I started this DB by copying an existing DB I am using.  Looking at the VBA screen I noticed that the project for this one still had the name of the older one.  Once I renamed the project to the current name, I did not have anymore issues with the objects not being found.
(for now anyway)

Author Closing Comment

ID: 38412919
Seems to be working now

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

872 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