Button Code – Input Variable Just Once (Acess XP)

Topic

New Reply

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

Reply | Quote

Replies

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

strSql = "UPDATE tblDryerlog SET tblDryerlog.[Date] = #" & _
Format(Me.txtStartDate, "mm/dd/yyyy") & " WHERE tblDryerlogProduct='Dummy'"

Reply | Quote

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

Reply | Quote