Query definition disappearing after being run

Posted on 2004-10-27
Medium Priority
Last Modified: 2010-05-18
I have a simple database that stores information on POs and I have built a simple form to search for and display them.  The general PO information is stored in a table while the information about the products contained on it are stored in a seperate table.  The form I use to display the PO is split into two parts:  The main form displays basic info like the PO#, vendor# and information, etc. while the detail lines are displayed on a subform.  Each of these forms are linked to two different queries; the main form is linked to PO_Top and the subform is linked to PO_Lines.

All fine and dandy up to this point.

The problem comes when I run my search and this form is displayed.  The first time it is run it works like a charm. However, when I go to run it again, The form comes up completely white.  I go to look at the design of my PO_Lines query and there is nothing in there anymore!  No SQL, no definitions, nothing.  Its like the query clears itself out.  This is even more perplexing in the fact that the only VBA code contained in this thing is the DoCmd.OpenForm "PO Display" command attached to the search button.

Does anyone have any suggestions as to where I should start looking to solve this problem??
Question by:brd24gor
  • 4
  • 2

Expert Comment

ID: 12425303
How are you feeding a search to your query?

Author Comment

ID: 12425511
Through an unbound form that contains text boxes for inventory code, PO number, and vendor number.  These fields can be null or contain a value.  Here is the SQL for each of my queries:

WHERE (((POMSTHST.PONUMM)=Forms![PO Search]!txtPONum Or Forms![PO Search]!txtPONum Is Null) And ((POMSTHST.PODATE)=Forms![PO Search]!txtPODate Or Forms![PO Search]!txtPODate Is Null) And ((VENDOR.VNDNO)=Forms![PO Search]!txtVendorNum Or Forms![PO Search]!txtVendorNum Is Null));

WHERE (((POACTHST.PONUMA)=Forms![PO Display]!txtPONum Or Forms![PO Display]!txtPONum Is Null));

PO_Top will populate the main form with the returned values, while PO_Lines takes the PO returned by PO_Top and display the detail lines associated with that PO.

Hope this clarifies.
LVL 58

Expert Comment

ID: 12432116
Im confused about the links here...

* What is the name of the main form? Is it [PO Search] or is that another independant form?
* The only code in the main form is the one opening [PO Display], right?
* But the subform uses [PO Display], control txtPONum to filter by the field [PONUMA]
* Are the tables linked through POMSTHST.PONUMM = POACTHST.POMMS?
* Do you use a link master field / link child field for the subform?

As for the dissapearance...
* Is the .Recordsource of the subform cleared? (i.e. no longer PO_Lines, but blank)
* Else is it just the subform that is blank? (no fields, not even blank controls)
* Else is it the query definition that is blank? (the query PO_Lines exists, but the SQL is cleared/deleted)
* Else is just that the query PO_Lines does not show records anymore?

Depending on the answers, we will be able to figure this out!

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.


Author Comment

ID: 12433469
Trying to solve confusion...
*PO_Search is the main unbound form that collects the search values.
*Correct, the only code is a solitary line opening PO_Display
*PO_Display isn't a subform, it is a form opened by PO_Search that displays the general info on a PO.  PO_Lines is the subform that is placed on PO_Display showing the individual products contained on the PO.  Basically, there are three search values on PO_Search that can be typed in: txtPONum, txtVendor(vendor #), and txtDate (date of PO).  The query PO_Top will return fields with zero or more of the text boxes filled in (zero returning all POs).  The subform, PO_Lines, grabs the PO_Number from a text box on PO_Display called txtPONum (not to be confused with the text box of the same name on the PO Search form).
*The two tables are joined by PO Number: POMSTHST.PONUMM = POACTHST.PONUMA
*Not sure what you mean on this one.

*The problem query (PO_Lines) is still there and is listed under my queries, but there is no definition OR SQL in the query.  All of the fields in the definition view are blank and there is just SELECT; in the SQL view.  The definition doesn't even contain any tables.
*When I run the query the first time (successfully), and then try to run it again, the PO_Display form comes back white with no controls on it, not even my subform is visible.

If you need any more clarification, let me know.  I realize this isn't for many points but it is all I have left.  Any help or suggestion would be greatly appreciated.  Thank you!

LVL 58

Accepted Solution

harfang earned 160 total points
ID: 12440825
OK, things are clear now.

There are four ways to specify the recordsource of a form or subform
1) leave blank (for PO_Search for instance) creating a user dialog
2) select a table name
3) select a query name
4) enter the SQL definition directly in the .RecordSource property

The reason for you to use 3/4 instead of 2 in the form PO_Display is that you want to filter the records according to the selection fields on PO_Search.
For the subform, you want to show only those records from table POACTHST matching the parent form. The query however allows also the parent field txtPONum to be Null, in which case all records are shown. It that something your really need?
If not, try the following:
1) As recordsource for the subform, simply enter "POACTHST".
2) In the properties of the subform control on PO_Display, enter "PONUMM" as LinkMasterField and "PONUMA" as LinkChildField.
These settings have three effects:
* The where clause is automatically generated by Access ("WHERE PONUMA = Form.Parent!PONUMM")
* The query is automatically requeried whenever PONUMM changes (by navigating to another record or editing)
* The default value of PONUMA in the subform is dynamically set to the field Form.Parent!PONUMM

Ok, this still doesn't explain the dissapearance, but you will not need the query PO_Lines anymore.

If your program changes the .RecordSource (apparently, this is not the case), you should be able to find the line of code doing that by searching all modules for "RecordSource = ".
If your program changes the content of the query PO_Lines, this is through the SQL property. Try finding the string ".SQL =" in all modules.

I have no other ideas at this time.


BTW: never mind about points, I'm no in this for the money :)

Author Comment

ID: 12443948
Based on your ealier questions, you spurred me into some research and I am actually trying to do something similar to what you are saying.  It is not completely working yet, but you explanation above looks to be exactly what I need.  I will keep you posted.  Thanks greatly for the help!

Author Comment

ID: 12445274
Excellent, excellent, and excellent!!!  While this didn't solve the original question, it made this much simpler than dealing with and extra query.  Possibly my Access was possessed with it being this close to Halloween?  Anyway, everything works great now and I learned something in the process.



Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

607 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