I have a problem with values assigned to text boxes in a form becoming inaccessible? I really don’t understand what is happening.
In a form I select opening and closing dates for a set of reports and assign the dates to text boxes. From that form I open a second form with several quick lookups. One of those requires emptying a table then repopulating it with the following query.
INSERT INTO tempWhoWentWhere ( siteID, clientID, eventID, event_date ) SELECT DISTINCT tblEvent.siteID, qryFirstEvent.clientID, tblEvent.eventID, tblEvent.event_date FROM qryFirstEvent INNER JOIN tblEvent ON qryFirstEvent.MinOfevent_date = tblEvent.event_date WHERE (((tblEvent.event_date)>=[Forms]![frmReportMenu]![program_start_date] And (tblEvent.event_date)<=[Forms]![frmReportMenu]![program_end_date]));
The start and end dates are those mentioned earlier.
If I create a stand alone query and test it after I set the dates then it produces the expected results. I can then open the second form and the query still works properly.
However after I click on the button which activates the following code the query produces no results.
Dim curDb As Database, strAttend As String, strNewPreNat As String, stdat As Date, endat As Date, strQry As String
Dim recAttend As Recordset, recNewPreNat As Recordset, recStats As Recordset
Set curDb = CurrentDb()
strQry = "Delete * FROM tempWhoWentWhere"
curDb.Execute (strQry)
strQry = (as above)
curDb.Execute (strQry)
Whats even more curious is that it fails even if I stop the script prior to executing the query. This script fails on the second execute command with 'not enough parameters – expected 2'.
After a little more investigation it appears that the problem is not restricted to this query. The dates are not available to any other query.
I tried assigning the date to foo in the aforementioned script and that works OK.
Can anyone shed some light on this?