By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,105 Members | 2,620 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,105 IT Pros & Developers. It's quick & easy.

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

Seth Schrock
Expert 2.5K+
P: 2,941
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
Expand|Select|Wrap|Line Numbers
  1. Function DateCheck()
  2. If IsNull(Me.txtStartDate) Then
  3.     MsgBox ("Please enter a start date")
  4. Else
  5.     If IsNull(Me.txtEndDate) Then
  6.         Me.txtEndDate = Date
  7.         strCommand
  8.     End If
  9. End If
  10. End Function
btnPrintReport On_Click
Expand|Select|Wrap|Line Numbers
  1. Private Sub btnPrintReport_Click()
  2.  
  3. strCommand = DoCmd.OpenReport "rptPurchaseDateReport", acViewPreview
  4. DateCheck
  5.  
  6. End Sub
btnRefresh On_Click
Expand|Select|Wrap|Line Numbers
  1. Private Sub btnRefresh_Click()
  2.  
  3. strCommand = Me.sfrmPurchaseDateReport.Requery
  4. DateCheck
  5.  
  6. 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.
Jul 26 '12 #1

✓ answered by 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:

Expand|Select|Wrap|Line Numbers
  1. If DateCheck Then
  2.    'Do something
  3. End If

Share this Question
Share on Google+
6 Replies


Seth Schrock
Expert 2.5K+
P: 2,941
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:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Dim strCommand As String
  5. Dim blnDateCheckResult As Boolean

Function:
Expand|Select|Wrap|Line Numbers
  1. Function DateCheck()
  2. If IsNull(Me.txtStartDate) Then
  3.  
  4.     MsgBox ("Please enter a start date")
  5.  
  6.     blnDateCheckResult = False
  7. Else
  8.  
  9.     If IsNull(Me.txtEndDate) Then
  10.         Me.txtEndDate = Date
  11.     End If
  12.  
  13.     blnDateCheckResult = True
  14.  
  15. End If
  16. End Function
btnRefresh On_Click:
Expand|Select|Wrap|Line Numbers
  1. Private Sub btnRefresh_Click()
  2.  
  3. DateCheck
  4.  
  5. If blnDateCheckResult = True Then
  6.     Me.sfrmPurchaseDateReport.Requery
  7. End If
  8.  
  9. End Sub
btnPrintReport On_Click:
Expand|Select|Wrap|Line Numbers
  1. Private Sub btnPrintReport_Click()
  2.  
  3. DateCheck
  4.  
  5. If blnDateCheckResult = True Then
  6.     DoCmd.OpenReport "rptPurchaseDateReport", acViewPreview
  7. End If
  8.  
  9.  
  10. 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.
Jul 26 '12 #2

Rabbit
Expert Mod 10K+
P: 12,366
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:

Expand|Select|Wrap|Line Numbers
  1. If DateCheck Then
  2.    'Do something
  3. End If
Jul 26 '12 #3

Seth Schrock
Expert 2.5K+
P: 2,941
With my nested Ifs, what would the boolean result be? And I guess I'm not sure how to "return the boolean".
Jul 26 '12 #4

Rabbit
Expert Mod 10K+
P: 12,366
Basically, in your DateCheck function, where ever you see blnDateCheckResult, replace it with DateCheck.
Jul 26 '12 #5

twinnyfo
Expert Mod 2.5K+
P: 3,284
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:

Expand|Select|Wrap|Line Numbers
  1. Function DateCheck() as Boolean
  2.  
Jul 26 '12 #6

Seth Schrock
Expert 2.5K+
P: 2,941
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.
Jul 26 '12 #7

Post your reply

Sign in to post your reply or Sign up for a free account.