• form values inaccessible to queries

    Author
    Topic
    #476095

    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?

    Viewing 1 reply thread
    Author
    Replies
    • #1276203

      When you include a reference to a form in an SQL string, the reference to the form needs to be outside the string double quotes so it is just the value of the form control that is used in the SQL.

      Code:
      Dim strQRY as string
      strQRY= “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)=#” & format([Forms]![frmReportMenu]![program_start_date],”mm/dd/yyyy”)  & “# And  (tblEvent.event_date)<=#" & format([Forms]![frmReportMenu]![program_end_date],"mm/dd/yyyy") & "))"
       

      #

    • #1276226

      I knew that or at least I used to/should have. Thanks for the format tip. We have the same issue here compounded by the fact that half the machines I come across are set to US date format.

    Viewing 1 reply thread
    Reply To: form values inaccessible to queries

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: