I have a form that generates a report based that selects between two dates entered in textboxes (txtStartDate & txtEndDate).
Why does this work?
PHP:
Private Function CheckDates()
Dim btns As String
btns = vbExclamation
Dim StartDate As Variant
Dim EndDate As Variant
StartDate = Me.txtStartDate
EndDate = Me.txtEndDate
If IsNull(StartDate) Then
Dim MsgBoxStartTitle As String
Dim MsgBoxStartText As String
Dim MsgBoxStartDateError As String
MsgBoxStartTitle = "No Start Date Entered!"
MsgBoxStartText = "Please enter a starting date for the report!"
MsgBoxStartDateError = MsgBox(MsgBoxStartText, btns, MsgBoxStartTitle)
Me.txtStartDate.SetFocus
ExitCmd = True
Exit Function
Else
End If
End Function
but this doesn't?
PHP:
Private Function CheckDates()
Dim btns As String
btns = vbExclamation
Dim StartDate As Variant
Dim EndDate As Variant
StartDate = Me.txtStartDate
EndDate = Me.txtEndDate
Select Case StartDate
Case Is = Null
Dim MsgBoxStartTitle As String
Dim MsgBoxStartText As String
Dim MsgBoxStartDateError As String
MsgBoxStartTitle = "No Start Date Entered!"
MsgBoxStartText = "Please enter a starting date for the report!"
MsgBoxStartDateError = MsgBox(MsgBoxStartText, btns, sgBoxStartTitle)
Me.txtStartDate.SetFocus
ExitCmd = True
Exit Function
Case Is <> Null
ExitCmd = False
End Select
End function
Also. why won't it work when I use this
Dim StartDate as Date
But it works with
Dim StartDate as Variant
You've declared it as a function although it doesn't return a value so it would be better as a Sub.
You don't need to dimension variables for the message box especially if you are only using it once in the running of the routine so you can put them directly into the MsgBox command.
There's no need to create an EndDate variable as you never used it in the code.
There's no need to make a StartDate variable as you have this routine written in your form's module meaning you can reference the control txtStartDate directly.
ExitCmd isn't specified within the routine - no idea what that is doing there.
If there is no ELSE to your IF statement there's no need to put an ELSE in - just close it with the END IF statement.
Code:
Private Sub CheckDates()
If IsNull(Me.txtStartDate) Then
MsgBox "Please enter a starting date for the report!", vbExclamation, "No Start Date Entered!"
Me.txtStartDate.SetFocus
Exit Sub
End If
End Sub
Off the bat, I should have probably mentioned that I am using Access 97 for this database.
I've tried replacing Null with Empty and " " but it still doesn't work.
I do use the EndDate but I didn't included it here for the sake of brevity. Probably should have deleted it, sorry about that!
The reason for the CmdExit = True/False is because I pass that value to my report buttons (Preview/Print/Mail/Save As) so that if CmdExit = True it will exit the command button and not bother to show a preview or accidentaly print and so on...
I've tried to not dim the MsgBox variable but it'll give me a compile error saying variable not defined. I've done it before without Dim-ing and I don't know what I get this error.
There's no need to make a StartDate variable as you have this routine written in your form's module meaning you can reference the control txtStartDate directly.
- Good point but now that I think of it, I may declare it as a global variable (is that the correct term?) like I did ExitCmd so that I can use it when I specify the name of the file when I save it as a SnapShot file. I like to do it that way so that if I have to reuse the code but have different names for txtBoxes I just have to change in the Dim statment. Or is there a reason I shouldn't do it?
The reason for the CmdExit = True/False is because I pass that value to my report buttons (Preview/Print/Mail/Save As) so that if CmdExit = True it will exit the command button and not bother to show a preview or accidentaly print and so on...
I've tried to not dim the MsgBox variable but it'll give me a compile error saying variable not defined. I've done it before without Dim-ing and I don't know what I get this error.
You'll be getting the error if you use brackets in a MsgBox statement when you don't have to.
i.e.
Code:
MsgBox "No Need For Brackets", vbOkOnly, "Example One"
Result = MsgBox("Now Brackets Are Needed", vbYesNo, "Example Two")
I may declare it as a global variable (is that the correct term?) like I did ExitCmd so that I can use it when I specify the name of the file when I save it as a SnapShot file.
Global variable/public variable (same thing). You should try to limit the use of these in your database as everytime you reference one the whole module in which it was declared is loaded into the computer's memory.
I like to do it that way so that if I have to reuse the code but have different names for txtBoxes I just have to change in the Dim statment. Or is there a reason I shouldn't do it?
btns = vbExclamation
Select Case Me.txtStartDate
Case Is = Empty
MsgBoxStartTitle = "No Start Date Entered!"
MsgBoxStartText = "Please enter a starting date for the report!"
MsgBoxStartDateError = MsgBox MsgBoxStartText, btns, MsgBoxStartTitle
Me.txtStartDate.SetFocus
ExitCmd = True
Exit Function
Case Is <> Empty
ExitCmd = False
End Select
I've tried this but it doesn't work. It won't return ExitCmd = true.
This is the code I have behind my Preview Button
PHP:
Private Sub cmdPreviewDateRange_Click()
On Error GoTo Err_cmdPreviewDateRange_Click
Call CheckDates
Select Case ExitCmd
Case Is = True
Exit Sub
Case Is = False
End Select
Dim stDocName As String
stDocName = Me.cboReportDateRange.Column(0)
DoCmd.OpenReport stDocName, acPreview
Exit_cmdPreviewDateRange_Click:
Exit Sub
Err_cmdPreviewDateRange_Click:
MsgBox Err.Description
Resume Exit_cmdPreviewDateRange_Click
End Sub
This time you are referencing the textbox and not a variant - a textbox will return Null if it is empty.
What you'd rather use, though, is the IsDate() function.
Code:
btns = vbExclamation
If Not IsDate(Me.txtStartDate) Then
ExitCmd = False
Else
MsgBoxStartTitle = "No Start Date Entered!"
MsgBoxStartText = "Please enter a starting date for the report!"
MsgBoxStartDateError = MsgBox(MsgBoxStartText, btns, MsgBoxStartTitle)
Me.txtStartDate.SetFocus
ExitCmd = True
Exit Function
End If
I don't see the need to use the SELECT CASE function as your situation is basically an either/or possibility and not something where you can have many probabilities i.e. 10 possible cases.
Select Case ExitCmd
Case Is = True
Exit Sub
Case Is = False
End Select
As all you need to say, in one line, is:
Code:
If ExitCmd = True Then Exit Sub
If it's true then the sub will exit; there's no need to check if it's false because if it wasn't then it defeats the purpose of being a boolean (either True or False - no leeway) so if it hasn't exited the sub then it is obviously false and will continue to run the remainder of your code.
but I get an error message saying "Expected End Of Statement"
MsgBox MsgBoxStartText, btns, MsgBoxStartTitle is ok BUT it'll still give ma an error message saying variable not defined for the MsgBoxStartTitle & MsgBoxStartText.
Doe having the follwing cause my having to Dim variables?
PHP:
Option Compare Database
Option Explicit
Forgive me if all this seems obvious but I am still trying to figure out VBA for Access. AND THANK YOU for all your help so far!
Option Compare Database
Option Explicit
Dim ExitCmd As Boolean
Private Function CheckDates()
Dim btns As String
btns = vbExclamation
Dim ReportName As String
ReportName = Me.cboReportDateRange.Column(0)
If Me.cboReportDateRange = "" Then
Dim MsgBoxReportTitle As String
Dim MsgBoxReportText As String
Dim MsgBoxReportError As String
MsgBoxReportTitle = "No report selected!"
MsgBoxReportText = "Please select a report."
MsgBoxReportError = MsgBox(MsgBoxReportText, btns, MsgBoxReportTitle)
Me.cboReportDateRange.SetFocus
ExitCmd = True
MsgBox "Report " & ExitCmd
Exit Function
Else
ExitCmd = False
MsgBox "Report " & ExitCmd
Exit Function
End If
Dim StartDate As Variant
Dim MINDate As Variant
Dim MAXDate As Variant
StartDate = Me.txtStartDate
MINDate = DMin("MinOfUsageDay", "qryAvailableUsageDaysMIN")
MAXDate = DMax("MaxOfUsageDay", "qryAvailableUsageDaysMAX")
Select Case StartDate
Case Is = Empty
Dim MsgBoxStartTitle As String
Dim MsgBoxStartText As String
Dim MsgBoxStartDateError As String
MsgBoxStartTitle = "Out of range!"
MsgBoxStartText = "Please enter a new starting date for the report!"
MsgBoxStartDateError = MsgBox(MsgBoxStartText, btns, MsgBoxStartTitle)
Me.txtStartDate.SetFocus
MsgBox "Start is empty"
ExitCmd = True
Exit Function
Case Is < MINDate
Dim MsgBoxMINTitle As String
Dim MsgBoxMINText As String
Dim MsgBoxMINDateError As String
MsgBoxMINTitle = "Enter later start date!"
MsgBoxMINText = "Please enter a new starting date for the report!"
MsgBoxMINDateError = MsgBox(MsgBoxMINText, btns, MsgBoxMINTitle)
Me.txtStartDate.SetFocus
MsgBox "Start is < MIN"
ExitCmd = True
Exit Function
Case Is > MAXDate
Dim MsgBoxMAXTitle As String
Dim MsgBoxMAXText As String
Dim MsgBoxMAXDateError As String
MsgBoxMAXTitle = "Enter earlier start date!"
MsgBoxMAXText = "Please enter a starting date for the report!"
MsgBoxMAXDateError = MsgBox(MsgBoxMAXText, btns, MsgBoxMAXTitle)
Me.txtStartDate.SetFocus
MsgBox "Start is > MAX"
ExitCmd = True
Exit Function
Case Else
ExitCmd = False
MsgBox "Start is within valid range"
Exit Function
End Select
End Function
Private Sub cmdPreviewDateRange_Click()
On Error GoTo Err_cmdPreviewDateRange_Click
Call CheckDates
If ExitCmd = True Then
Exit Sub
End If
Dim stDocName As String
stDocName = Me.cboReportDateRange.Column(0)
DoCmd.OpenReport stDocName, acPreview
Exit_cmdPreviewDateRange_Click:
Exit Sub
Err_cmdPreviewDateRange_Click:
MsgBox Err.Description
Resume Exit_cmdPreviewDateRange_Click
End Sub
I can't get it to work!!!
None of the MsgBoxes for the StartDate Select Case statements are shown?!?!?!?!?
Does it matter that I have an input mask for txtStartDate?
If I choose a report from the report combobox (cboReportDateRange), I will get the message Report False, but I don't choose anything, I an error message saying "Invalid use of Null".
I get the same message if I use If Me.cboReportDateRange = "" Then
As I said before change CheckDates to a Sub and not a Function as you are not using it to return a value.
I've modified your code and it now works - there were many silly errors in there which will explain why it wouldn't work.
Firstly, here's the CheckDates sub rewritten to work with all the unnecessary variables you have dimensioned taken out. You even tried to dimension three variables twice within the same routine.
Code:
Private Sub CheckDates()
Dim ReportName As String
Dim MINDate As Date
Dim MAXDate As Date
'ReportName = Me.cboReportDateRange.Column(0)
If IsNull(Me.cboReportDateRange) Then
MsgBox "You have not selected a report to generate. Please select a report.", vbExclamation, "No report selected!"
ExitCmd = True
Me.cboReportDateRange.SetFocus
Exit Sub
Else
ExitCmd = False
End If
MINDate = DMin("MinOfUsageDay", "qryAvailableUsageDaysMIN")
MAXDate = DMax("MaxOfUsageDay", "qryAvailableUsageDaysMAX")
If IsNull(Me.txtStartDate) Then
MsgBox "Please enter a starting date for the report!", vbExclamation, "No Start Date Entered!"
Me.txtStartDate.SetFocus
ExitCmd = True
Exit Sub
End If
If IsNull(Me.txtEndDate) Then
MsgBox "Please enter a ending date for the report!", vbExclamation, "No End Date Entered!"
Me.txtEndDate.SetFocus
ExitCmd = True
Exit Sub
End If
Select Case Me.txtStartDate
Case Is < MINDate
MsgBox "You've entered a start date that is later that the latest avaiable start date. Please enter a new starting date for the report!", vbExclamation, "Out of Range!"
Me.txtStartDate.SetFocus
ExitCmd = True
Exit Sub
Case Is > MAXDate
MsgBox "Please enter a starting date for the report!", vbExclamation, "No Start Date Entered!"
Me.txtStartDate.SetFocus
ExitCmd = True
Exit Sub
Case Else
ExitCmd = False
End Select
End Sub
You should not consider this code to be a finished article as there is massive scope for development.
A good routine does what it has to do in 60 lines of code or less and a good routine does one operation.
Yours was rather big and performs a number of operations.
I'd suggest not checking the dates on the click of the button but on each textbox's BeforeUpdate event - that way if it is an illegal date you can inform them with a message box and make use of that particular event's Cancel argument.
The OnClick of the button could easily just look at the vaues of the StartDate, EndDate, and the report combo and inform the user if they are Null and exit the sub and running the report if all our values are okay.
So, on to why it would not work:
1) On the click of the button the first thing you were doing was making a variable called ReportName and trying to assign the value of a combobox to it. If the combobox hadn't had a selection made then an error would occur (Invalid Use Of Null)
2) Next step, you were asking if the combobox was empty. Why reference the combobox if you've just assigned its value to the variable ReportName? You then dimensioned three variables for a message box but I'll discuss these at the end of the thread - let's just say unnecessary for now. Of course, the combobox would never be EMPTY, if it had no value it would be NULL so your code wouldn't notice that as it ran through.
3) In the same part as #2 you exit the routine if the combobox doesn't have a value and even if it does. As checking if it is EMPTY will make the program think there is a value in the combobox the code follows on through and sets your boolean to TRUE and then exits the routine altogether.
4) Dim StartDate As Variant, and the others. Variants take up bigger amounts of memory than the other variables and it is a best practice to choose the data type most suited to your variable in this case, the title says it all, a Date variable.
5) The method in which you got the maximum date and the minimum date looks as if you've created two queries for the sole purpose of retrieving the MAX and MIN dates. This is a step too much. You can simply use DMAX("Field", "tblBase") where tblBase is the table you are getting the data from. Domain Aggregate functions are notoriously slow enough without having to run a query each to get the respective values you want.
6) When checking the dates are valid you have this structure:
Code:
If date is not okay then
do something
exit the routine
Else
exit the routine
End If
Can you see why this bit wasn't working?
When evaluating on a value/function that will return True or False and you are only interested in one of those values there is no need to bother with the other.
Code:
If date is not okay Then
do something
exit the routine
End If
7) Finally, your select case statements, no matter what, had the outcome that you would exit the routine. Not really helpful when you still have code to run through in that routine.
Just as an extra thing, regarding message boxes and variable declaration.
You seem to have the habit of doing this (see below) and I don't know whether someone showed you it that way once and you've always done it like that or it was in a book, etc.
Code:
Dim MsgBoxMAXTitle As String
Dim MsgBoxMAXText As String
Dim MsgBoxMAXDateError As String
MsgBoxMAXTitle = "No Start Date Entered!"
MsgBoxMAXText = "Please enter a starting date for the report!"
MsgBoxMAXDateError = MsgBox(MsgBoxMAXText, btns, MsgBoxMAXTitle)
I'm not saying there is anything wrong with it, it's just unnecessary for the task at hand.
You are diplaying a message box and btns[/b] as you declared elsewhere in the code is storing the vbExclamation constant (the fact that it is an Access constant begs the question why you need to store it).
A message box, such as you have created, only has one button: it's value is vbOk.
In your code, when you click that OK button you are storing the integer value for vbOk in the variable MsgBoxMAXDateError although you never have the need to use it in your code. So why bother?
The only problem I can see with your declaration of variables for the message and title parts of the message box - for every message box you are declaring three variables: one to hold its message, one to hold its title, and one to hold a result that you'll never use.
If your message boxes are going to contain the same messages then it would be a smarter approach to use two variables (for the message and the title) but in these cases you are better off, and more efficient, to just say:
Code:
MsgBox "This is a message box", vbExclamation, "This is its title"
The only time you would need to store the result of a message box is when you want to evaluate the option chosen by the user, let's say, in a vbYesNo box.
i.e
Code:
Dim intResult As Integer
intResult = MsgBox("Are you bored reading all of this?", vbQuestion + vbYesNo, "Question for the Attentive")
If intResult = vbYes Then
MsgBox "Well don't worry, we're almost at the end.", vbInformation, "Relief"
Else
MsgBox "Bored? How can you be bored? Code is fun!", vbExclamation, "Boredom Alert"
End If
As a general rule, but not written, you should declare all your variables at the start of your routine - it helps from making mistakes like you did by dimensioning variables with the same name twice within the same routine.
