Query definition disappearing after being run

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??
Who is Participating?
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 :)
How are you feeding a search to your query?
brd24gorAuthor Commented:
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.
Ultimate Tool Kit for Technology Solution Provider

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 now.

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!

brd24gorAuthor Commented:
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!

brd24gorAuthor Commented:
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!
brd24gorAuthor Commented:
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.


Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.