Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Mishawaka, Indiana, USA
    Posts
    145
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Button Code - Input Variable Just Once (Acess XP)

    I use the following code in a menu button to open a report.
    The boss wants to see zero data even if the data does not exist.
    Thus, I use an update query to reset “Dummy Product” date values to a “Start Date”.
    Then the report is opened and the user is prompted for a [Start Date] and [End Date].
    Upon closing the report, the “Dummy Product” date values are reset to #11/14/1942#.

    Using this methodology causes the user to input the [Start Date] parameter twice.
    I would like to just input the [Start Date] parameter just once.
    Please Help! And Thank You.

    Private Sub cmdRptAirEmissions2_Click()
    On Error GoTo Err_cmdRptAirEmissions2_Click

    'Set the Dummy Product Date to Start Date
    Dim strSql As String
    DoCmd.SetWarnings False
    strSql = "UPDATE tblDryerlog SET tblDryerlog.[Date] = [Start Date]WHERE (((tblDryerlog.Product)='Dummy'));"
    DoCmd.RunSQL (strSql)
    DoCmd.SetWarnings True

    'Call Report in View Mode
    Dim stDocName As String

    stDocName = "rptAirEmissions"
    DoCmd.OpenReport stDocName, acPreview

    'Reset Dummy Product Date to 11/14/1942
    Dim strSql2 As String
    DoCmd.SetWarnings False
    strSql2 = "UPDATE tblDryerlog SET tblDryerlog.[Date] = #11/14/1942# WHERE (((tblDryerlog.Product)='Dummy'));"
    DoCmd.RunSQL (strSql2)
    DoCmd.SetWarnings True

    Exit_cmdRptAirEmissions2_Click:
    Exit Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Button Code - Input Variable Just Once (Acess XP)

    I'd put text boxes for the start date and end date on the form. You can then replace the parameters in the query with

    [Forms]![NameOfForm]![txtStartDate]

    and

    [Forms]![NameOfForm]![txtEndDate]

    where NameOfForm is the name of your form and txtStartDate and txtEndDate are the names of the text boxes. In your code, you can use
    <code>
    strSql = "UPDATE tblDryerlog SET tblDryerlog.[Date] = #" & _
    Format(Me.txtStartDate, "mm/dd/yyyy") & " WHERE tblDryerlogProduct='Dummy'"</code>

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Mishawaka, Indiana, USA
    Posts
    145
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Button Code - Input Variable Just Once (Acess

    Thank you. I will try that. I have used this technique in the past but not in the context of buttons.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •