Query definition disappearing after being run

Posted on 2004-10-27
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
    LVL 5

    Expert Comment

    How are you feeding a search to your query?
    LVL 3

    Author Comment

    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

    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!

    LVL 3

    Author Comment

    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

    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 :)
    LVL 3

    Author Comment

    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!
    LVL 3

    Author Comment

    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.

    THANK YOU!!!!


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…

    933 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now