brd24gor
asked on
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??
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??
How are you feeding a search to your query?
ASKER
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.VNDN
WHERE (((POMSTHST.PONUMM)=Forms!
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_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.
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.VNDN
WHERE (((POMSTHST.PONUMM)=Forms!
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_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.
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.
* 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.
ASKER
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
*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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
ASKER
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
THANK YOU!!!!
Brad