Is it possible to assign a command to a variable and then run the variable?

[Seth Schrock]

I have a form that has two textboxes, txtStartDate & txtEndDate, that are used as criteria for a query. Also on the form are two buttons, btnRefresh & btnPrintReport. Both buttons need do to the same checking to make sure that the textboxes have dates in them. I would like to make that check be done in a function. The problem is that what needs done in the If False portion is different for the two buttons. btnRefresh will requery a subform and btnPrintReport will open a report. Is it possible to do something like the following?

Function

Function DateCheck()
If IsNull(Me.txtStartDate) Then
    MsgBox ("Please enter a start date")
Else
    If IsNull(Me.txtEndDate) Then
        Me.txtEndDate = Date
        strCommand
    End If
End If
End Function

btnPrintReport On_Click

Private Sub btnPrintReport_Click()
 
strCommand = DoCmd.OpenReport "rptPurchaseDateReport", acViewPreview
DateCheck
 
End Sub

btnRefresh On_Click

Private Sub btnRefresh_Click()
 
strCommand = Me.sfrmPurchaseDateReport.Requery
DateCheck
 
End Sub

strCommand is declared as a string at the top of the forms VBA (not in a function or private sub).

So basically, each button assigns its command to the variable and then runs the DateCheck function which uses the command assigned to the variable.

I know that my code doesn't work, but I would like to do something like this so that I can easily change the DateCheck function if I have to and have that change both buttons.

[Seth Schrock]

I have come up with a work-around, but I don't know if it is the best solution. I added a boolean variable blnDateCheckResult and set it as either true or false depending on the result of the DateCheck function. Then, in the button's On_Click events, I checked the value of blnDateCheckResult to see whether or not to run the button's specific command (requery the subform or open the report). Here is what I have now:


In the general section:

Option Compare Database
Option Explicit
 
Dim strCommand As String
Dim blnDateCheckResult As Boolean

Function:

Function DateCheck()
If IsNull(Me.txtStartDate) Then
 
    MsgBox ("Please enter a start date")
 
    blnDateCheckResult = False
Else
 
    If IsNull(Me.txtEndDate) Then
        Me.txtEndDate = Date
    End If
 
    blnDateCheckResult = True
 
End If
End Function

btnRefresh On_Click:

Private Sub btnRefresh_Click()
 
DateCheck
 
If blnDateCheckResult = True Then
    Me.sfrmPurchaseDateReport.Requery
End If
 
End Sub

btnPrintReport On_Click:

Private Sub btnPrintReport_Click()
 
DateCheck
 
If blnDateCheckResult = True Then
    DoCmd.OpenReport "rptPurchaseDateReport", acViewPreview
End If
 
 
End Sub

Does this look like a good solution? It does work, but I am aware that there are certain ways of doing things that do work, but aren't necessarily the best way or could even be a bad way.

[Rabbit]

You could do it that way. But you could also have the function return the boolean instead of storing it in a global variable. Then you can just do something like this:

If DateCheck Then
   'Do something
End If

[Seth Schrock]

With my nested Ifs, what would the boolean result be? And I guess I'm not sure how to "return the boolean".

[Rabbit]

Basically, in your DateCheck function, where ever you see blnDateCheckResult, replace it with DateCheck.

[twinnyfo]

Seth,

Your function will assume that the data type it returns in Boolean, but for clarity's sake, you may also want to explicitly declare your function to return a Boolean result:

Function DateCheck() as Boolean
 

[Seth Schrock]

Thanks Rabbit. That does make it a little cleaner. I also realized that it probably wouldn't be a good idea to try what I had originally had in mind since it would limit you to only one command per variable. I like this way much better.

Thanks twinnyfo. I had thought about that and checked in one of my books to see how.