Validate that a user has entered data in parameter

[herrld]

I have a macro that runs via button on a form.
The macro uses the TransferText action to run a query and send the output to a .csv file. This works great, BUT..
I want to validate that the user puts data in when prompted by the query's parameters. I don't want it to run if the user makes a mistake and hits enter without putting a date in the StartDate and EndDate parameter boxes.

thanks, Linda

 

[grtammi]

Hi Linda,

In the button's OnClick event, you'll need the following code to check for validate data:

Private Sub YourButton_OnClick()
On Error Resume Next
If Not IsDate(StartDate) Or Not IsDate(EndDate) Then
MsgBox "Please enter valid dates."
Exit Sub
Else
DoCmd.RunMacro "MacroNameHere"
End If
End Sub

Of course, you'll need to modify the names of your date fields and your macro name also.

HTH

Greg
If you don't understand, just nod and smile ...

 

[herrld]

I got an error. Could it be because the user is not prompted for the StartDate & EndDate parameter until after the macro starts?
Is this code asking to check for an error before the macro starts running? Linda

 

[grtammi]

Hi Linda,

Post the error and I'll help you out best I can. If you don't understand, just nod and smile ...

 

[herrld]

Ok,
I added the code to the button's OnClick event.
I go to Form view and click on the button.
I get MsgBox "Please enter valid dates."

In case it matters...The parameter for StartDate and EndDate is within the query that the macro runs.
The table field is 'RCV.RCV_DATE'. RCV.RCV_DATE is actually a Date/Time field in the underlying table.
The parameter is:
Between [StartDate]and [EndDate]+1
Linda

 

[grtammi]

try changing StartDate and EndDate to explicit references, such as follows:

Forms!MyForm!StartDate
Forms!MyForm!EndDate

The way it looks to me right now, is that the macro is trying to find 2 variables named StartDate and EndDate, rather that 2 text boxes on a form.

HTH

Greg
If you don't understand, just nod and smile ...