B
box2003
A peculiar situation I have recently encountered involves a form and query
and when it is called from a form event, the query SQL is erased after the
query is run. I will present the scenario below using FormA as an example.
On FormA I have four controls, a Preview Button, Export Button, group text
box "txtGroup", and a report type "txtRptType". Clicking the Preview button
calls a query that has a parameter reference to the FormA text box,
"txtGroup". When run, the result is a read only preview of the query results
on screen.
<docmd for query>
DoCmd.OpenQuery "qryTerritoryOpenTeamMbr", acViewNormal, acReadOnly
<Query SQL>
* qryTerritoryOpenTeamMbr *
SELECT Nbd.opendate, Nbd.closeddate, Nbd.ckcloseddate, Nbd.teammbr,
Nbd.asrsID, Nbd.appdate, Nbd.prd1, Nbd.first1, Nbd.last1, Nbd.ph1, Nbd.prd2,
Nbd.first2, Nbd.last2, Nbd.ph2, Nbd.fssc, Nbd.tsd, Nbd.dollaramt,
Nbd.accttype, Nbd.ownlast1, Nbd.ownfirst1, Nbd.ownlast2, Nbd.ownfirst2
FROM NBTData as nbd
WHERE Nbd.ckcloseddate=false AND Nbd.teammbr=Forms!frmTerritoryRpt!txtGroup;
Clicking the Export button calls the same query above using the
Docmd.OutPutTo method on the query, with the same parameter reference to the
FormA text box, txtGroup, and exports the results to a specified location and
name, nbtPath & nbtName.
<Insert call to export>
Init in this case is a reference to FormA control txtGroup.
rpttype is also a referenc to a defined FormA report type.
nbtPath = "\\SomeSamplePathName\NBTracker\Report Output\Territory Report\"
nbtName = "Territory_Report_" & Format(Now(), "ddd_mmmddyyyy") & "_" & Init
& "_" & rpttype & ".xls"
DoCmd.OutputTo acOutputQuery, "qryTerritoryOpenTeamMbr", nbtPath & nbtName
In the above, I have noticed when I click the preview or export button, the
preview or export will occur but, sometimes when I click the button again an
error message is displayed, “at least one object must be specified …†On
investigation, I discover the query syntax has been deleted. No where in my
program code for the Preview or Export event am I requesting SQL syntax to be
deleted.
After performing resolution scenarios, which at times appeared to correct
the problem, the problem would still surface.
Resolution:
I ended up creating a new duplicate form from scratch and modified the
procedures to first create a table (TerritoryReport) from the above query
(query syntax not shown), then query the table for the results in preview
mode. In export, the table is again created then exported. The initial
problem has disappeared, at least for the 100 tests I ran on the query and
procedure, nothing has resurfaced. As of this posting, the problem has not
reoccurred for several days, and the procedure is used extensively by the
small workgroup of 10 users.
<Query SQL>
* qryTerritoryOpenTeamMbr_mt *
<Export>
DoCmd.OutputTo acOutputTable, "TerritoryReport", acFormatXLS, nbtPath &
nbtName
Initial Observation
Of note on the original form FormA, when I would add objects to the form,
the control size would be some unthinkable large size and font, and I always
had to scale everything down. To me, this seemed indication of some level of
corruption to the form, which very well could have a corruptive propagation
effect to the query I was calling, or other procedures associated with the
form, and did. Also, the form from which FormA was called was in Modal, as
also was the FormA, in modal, modal on top of modal. The new FormA is not in
Modal, as no longer is the form from which FormA is called, not in Modal.
Compact and repair did not have any effect on correcting the perceived
problem.
Conclusion
Given the initial syntax above, and the many times previously I have written
similar procedures, I have never experienced the problem which I have
described. I was asked what the problem was, and was not able to provided a
legitimate answer, only to respond that perhaps some level of corruption had
been introduced to the FormA, and affected the form and events associated
with the form.
Any ideas from this group on what may have caused or be the cause of the
problem, or just a general similar experience you may have had and your
resolution.
Thanks for your assistance.
and when it is called from a form event, the query SQL is erased after the
query is run. I will present the scenario below using FormA as an example.
On FormA I have four controls, a Preview Button, Export Button, group text
box "txtGroup", and a report type "txtRptType". Clicking the Preview button
calls a query that has a parameter reference to the FormA text box,
"txtGroup". When run, the result is a read only preview of the query results
on screen.
<docmd for query>
DoCmd.OpenQuery "qryTerritoryOpenTeamMbr", acViewNormal, acReadOnly
<Query SQL>
* qryTerritoryOpenTeamMbr *
SELECT Nbd.opendate, Nbd.closeddate, Nbd.ckcloseddate, Nbd.teammbr,
Nbd.asrsID, Nbd.appdate, Nbd.prd1, Nbd.first1, Nbd.last1, Nbd.ph1, Nbd.prd2,
Nbd.first2, Nbd.last2, Nbd.ph2, Nbd.fssc, Nbd.tsd, Nbd.dollaramt,
Nbd.accttype, Nbd.ownlast1, Nbd.ownfirst1, Nbd.ownlast2, Nbd.ownfirst2
FROM NBTData as nbd
WHERE Nbd.ckcloseddate=false AND Nbd.teammbr=Forms!frmTerritoryRpt!txtGroup;
Clicking the Export button calls the same query above using the
Docmd.OutPutTo method on the query, with the same parameter reference to the
FormA text box, txtGroup, and exports the results to a specified location and
name, nbtPath & nbtName.
<Insert call to export>
Init in this case is a reference to FormA control txtGroup.
rpttype is also a referenc to a defined FormA report type.
nbtPath = "\\SomeSamplePathName\NBTracker\Report Output\Territory Report\"
nbtName = "Territory_Report_" & Format(Now(), "ddd_mmmddyyyy") & "_" & Init
& "_" & rpttype & ".xls"
DoCmd.OutputTo acOutputQuery, "qryTerritoryOpenTeamMbr", nbtPath & nbtName
In the above, I have noticed when I click the preview or export button, the
preview or export will occur but, sometimes when I click the button again an
error message is displayed, “at least one object must be specified …†On
investigation, I discover the query syntax has been deleted. No where in my
program code for the Preview or Export event am I requesting SQL syntax to be
deleted.
After performing resolution scenarios, which at times appeared to correct
the problem, the problem would still surface.
Resolution:
I ended up creating a new duplicate form from scratch and modified the
procedures to first create a table (TerritoryReport) from the above query
(query syntax not shown), then query the table for the results in preview
mode. In export, the table is again created then exported. The initial
problem has disappeared, at least for the 100 tests I ran on the query and
procedure, nothing has resurfaced. As of this posting, the problem has not
reoccurred for several days, and the procedure is used extensively by the
small workgroup of 10 users.
<Query SQL>
* qryTerritoryOpenTeamMbr_mt *
<Export>
DoCmd.OutputTo acOutputTable, "TerritoryReport", acFormatXLS, nbtPath &
nbtName
Initial Observation
Of note on the original form FormA, when I would add objects to the form,
the control size would be some unthinkable large size and font, and I always
had to scale everything down. To me, this seemed indication of some level of
corruption to the form, which very well could have a corruptive propagation
effect to the query I was calling, or other procedures associated with the
form, and did. Also, the form from which FormA was called was in Modal, as
also was the FormA, in modal, modal on top of modal. The new FormA is not in
Modal, as no longer is the form from which FormA is called, not in Modal.
Compact and repair did not have any effect on correcting the perceived
problem.
Conclusion
Given the initial syntax above, and the many times previously I have written
similar procedures, I have never experienced the problem which I have
described. I was asked what the problem was, and was not able to provided a
legitimate answer, only to respond that perhaps some level of corruption had
been introduced to the FormA, and affected the form and events associated
with the form.
Any ideas from this group on what may have caused or be the cause of the
problem, or just a general similar experience you may have had and your
resolution.
Thanks for your assistance.