Input Parameter in Module

  • Thread starter Thread starter Janet
  • Start date Start date
J

Janet

I'd like to be able to have a 'pop-up' input parameter
such as one uses in a query, but I'd like to code it (for
the first time.) I've tried researching, but am having
trouble locating exactly what I want to do. How can I
make the following work? And, I'd like to be able to
resuse the [Enter Month Ending Date] a few times in the
code.

Thanks for any help!

Dim varSQL As String, varDate As Date
varSQL = "Delete * from dbo_tblSubReceivables where
recPaidDate=" & [Enter Month Ending Date]
DoCmd.RunSQL varSQL
 
There's a built-in function InputBox that will do that.

Dim varSQL As String, varDate As Date, varInput As Variant

varInput = InputBox("Enter Month Ending Date", "Get Input")

' Check that they didn't click on the Cancel button
If Len(varInput) > 0 Then
If IsDate(varInput) Then
varDate = varInput
varSQL = "Delete * from dbo_tblSubReceivables where
recPaidDate=" & Format$(varDate, "\#mm\/dd\/yyyy\#")
DoCmd.RunSQL varSQL
End If
End If

The strange format in the SQL is to ensure that the date is handled
correctly: regardless of what your short date format has been set to, Access
wants the date to be in mm/dd/yyy format (and delimited with # characters)
 
You can use the Inputbox function - similar to msgbox but it returns the
value that is input by the user. To be honest, I don't use it because it
allows no validation. Instead I prefer to build dialog forms which are
simple, allow me to do basic validation (datatype, ranges, verify something
is entered).

Just create a form that asks for the value you want, then open it using the
acDialog parameter of Docmd.openform. When used, the code in the first form
will pause until the second form (the one being opened) is either hidden or
closed. With this in mind, on the second form put an 'OK' button which the
user clicks to indicate that a selection has been made. The code behind the
OK button should hide the form (rather than close it). Once the second form
is hidden, the code in original form will resume and can use the value in
the control on the now hidden form to set the value of the combo. Then the
second form is closed by the first form. Here's some sample code:

Code on first Form:

Private Sub Command19_Click()
dim dtMonthEnd as date
DoCmd.OpenForm "form2", , , , , acDialog
dtMonthEnd = Forms!form2.dtMonthEnd
' Now add your code to use the value

DoCmd.Close acForm, "form2"
End Sub

Code on second form:

Private Sub cmdOK_Click()
Me.Visible = False
End Sub
 
I'd like to be able to have a 'pop-up' input parameter
such as one uses in a query, but I'd like to code it (for
the first time.)
Click to expand...

You have had two good suggestions: a third one is to use a form and point
the parameters at the form. The querydef looks something like this:

SELECT MyField, YourField, TheOtherField
FROM MyTable
WHERE StartDate >= Forms!MyForm!txtStartDate
AND FinshDate <= Forms!MyForm!txtFinshDate

This is handy because you can use the querydef as the source for a report
or another form: just make sure that MyForm is loaded (can be hidden if you
want) at the time you open the report etc.

Hope that helps


Tim F
 
Geez! Thanks so much both of you. Isn't it nice to be
able to have a choice? I appreciate the prompt feedback.

Thanks again!
Janet
 
Hi Janet,

Happy new year and thank you for using MSDN Newsgroup! It's my pleasure to
assist you with your issue.

I think the solution from Doug Steele's solution sound good, that is:

Dim varSQL As String, varDate As Date, varInput As Variant
varInput = InputBox("Enter Month Ending Date", "Get Input")

' Check that they didn't click on the Cancel button
If Len(varInput) > 0 Then
If IsDate(varInput) Then
varDate = varInput
varSQL = "Delete * from dbo_tblSubReceivables where
recPaidDate=" & Format$(varDate, "\#mm\/dd\/yyyy\#")
DoCmd.RunSQL varSQL
End If
End If

You can also reraise InputBox to re-enter the date if the entered data is
not a valid Date format.
If you still have questions, please feel free to post new message here and
I am ready to help!

Best regards

Baisong Wei
Microsoft Online Support
 
Back
Top