[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Query definition disappearing after being run

Posted on 2004-10-27
7
Medium Priority
?
294 Views
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??
0
Comment
Question by:brd24gor
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 5

Expert Comment

by:niblick
ID: 12425303
How are you feeding a search to your query?
0
 
LVL 3

Author Comment

by:brd24gor
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:

PO_Top:
SELECT POMSTHST.PONUMM, POMSTHST.PODATE, VENDOR.VNDNO, VENDOR.VNDNAM, POMSTHST.USERRP, POMSTHST.EC, VENDOR.VNDPH, POMSTHST.USER, POMSTHST.DTSTMP
FROM POMSTHST INNER JOIN VENDOR ON VENDOR.VNDNO=POMSTHST.VNDNUM
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));

PO_Lines:
SELECT POACTHST.POMMS, POACTHST.PODSC, POACTHST.PAKQTY, POACTHST.UNTMSR, POACTHST.QTYORD, POACTHST.RTLPRC, POACTHST.REONUM, POACTHST.AVAIP1
FROM POACTHST
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.
0
 
LVL 58

Expert Comment

by:harfang
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!

Cheers.
0
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.

 
LVL 3

Author Comment

by:brd24gor
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.

Disappearance...
*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!

Brad
0
 
LVL 58

Accepted Solution

by:
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.

Cheers!

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

Author Comment

by:brd24gor
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!
0
 
LVL 3

Author Comment

by:brd24gor
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.

THANK YOU!!!!

Brad
0

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 describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
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 …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
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 …

656 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