Validation on form not working

[ITguy1981]

I have a daily time time. Two of the field in the table are employee name and date. I have a form for this table to input data. I also have a form to allow me to easier find specific records. Basically, another form allows me to enter part of the employee name, a start date, and end date and then a button launches the command to open the daily time form and find those records that meet the criteria. The problem I'm having is having a validation on the starting and ending date criteria boxes that I use to search for certain records. If either are blank I don't get any record returns. I put a validation rule on each that is <=Date(), but the validation only seems to work if an actual date is entered that does not meet the validation criteria. So if I enter an employee name and leave the start date and end date boxes empty and then click the button on my form to launch the daily time form the validations don't register. I tried using <=Date() And Is Not Null, but that didn't either. Any suggestions? Can I add something to the button that opens the Daily Time form to check and see if my start date and end date are empty?

 

[John Big Booty]

<=Date() And Is Not Null will work, however it will only work for new records or where you make a change to that field in an existing record.

You could put some code along the lines;

    If Me.StartDate <= Date() Or IsNull(Me.StartDate) Then
        MsgBox "Start Date must contain a date later than today's date"
        Cancel = True
        Me.StartDate.SetFocus
    End If

In your Command Button Code

 

[vbaInet]

I tend to always use the IsDate() function when dealing with dates because it also validates Null values too. So I'm going to steal John Big Booty's code

    If Not IsDate(Me.StartDate) Then
        MsgBox "Start Date must be a valid date"
        Cancel = True
        Me.StartDate.SetFocus
    ElseIf Me.StartDate <= Date Then
        MsgBox "Start Date must contain a date later than today's date"
        Cancel = True
        Me.StartDate.SetFocus
    End If

 

[ITguy1981]

Thanks guys. Now I just need to figure out the code to get it to open my form. I normally use the regular access property builder to select, on click, open form, select the form name, etc. I should learn VB.

 

[ITguy1981]

I've got another question, but I've got the code in place for the on click. It's checking the validation of both my StartDate and EndDate boxes and if both validate it opens the form. Here is the code:
Private Sub Command6_Click()
If Me.StartDate > Date Or IsNull(Me.StartDate) Then
MsgBox "Must enter a start date before or equal to today"
Cancel = True
Me.StartDate.SetFocus
ElseIf Me.EndDate > Date Or IsNull(Me.EndDate) Then
MsgBox "Must enter an end date before or equal to today"
Cancel = True
Me.EndDate.SetFocus
ElseIf Me.StartDate <= Date Then
DoCmd.OpenForm "Daily Time from Employee Name Search", acNormal, , , acFormReadOnly
End If
End Sub
Here is my new question. If the dates entered are valid, but there are no records within the date I just get a blank for with no fields or anything. How can i get it to see there are no fields and just have a msg box that says "No results for this search" instead of opening a blank form?

 

[John Big Booty]

In On Load event of the form being opened you could some code along the line of;

If Me.RecordSet.RecordCount = 0
     DoCmd.Close
     MsgBox "There where no records found for the entered Criteria
End If

Another option would be as part of your Command button use the Dcount() function to check if any records will be found and Exit the Sub prior to the form being called. This link might help with that process if you choose to pursue it.

 

[ITguy1981]

If I try to enter the code On Load I get an error. I put in If Me.RecordSet.RecordCount = 0 and then go to enter more code I get: Compile Error: Expected: Then or GoTo.

 

[John Big Booty]

My apologies I left the Then out of the code it should look like;

If Me.RecordSet.RecordCount = 0 [B][COLOR="Red"]Then[/COLOR][/B]
     DoCmd.Close
     MsgBox "There where no records found for the entered Criteria
End If

 

[ITguy1981]

I entered the code on load of my form which works, but I get an error of:
RunTime error "2501"
The openform action was cancelled.
Is there a way to keep the debugger from coming up with this error? If the error didn't popup everything would be working as I wanted it to.

 

[John Big Booty]

Try the following variation;

If Me.Recordset.RecordCount = 0 Then
     DoCmd.Close
     MsgBox "There where no records found for the entered Criteria"
     [B][COLOR="Red"]Exit Sub[/COLOR][/B]
End If

 

[JANR]

@ITguy1981

You need to trap the 2501 error in your caller Sub, just put this under the line which opens the form.

If Err.Number = 2501 Then Err.Clear

JR

 

[vbaInet]

@ITguy1981

You need to trap the 2501 error in your caller Sub, just put this under the line which opens the form.

If Err.Number = 2501 Then Err.Clear

JR

Or count the report's record source (which I would imagine is a query) using the DCount() function before you open the report. So:

Private Sub Command6_Click()
    If Me.StartDate > Date Or IsNull(Me.StartDate) Then
        MsgBox "Must enter a start date before or equal to today"
        Cancel = True
        Me.StartDate.SetFocus
    ElseIf Me.EndDate > Date Or IsNull(Me.EndDate) Then
        MsgBox "Must enter an end date before or equal to today"
        Cancel = True
        Me.EndDate.SetFocus
    Else
        If Nz(DCount("*", "Report Query Name", "[DateField] BETWEEN #" & Me.StartDate & "# AND #" & Me.EndDate & "#"), 0) = 0 Then
            MsgBox "No results for this search", vbOKOnly
        Else
            DoCmd.OpenForm "Daily Time from Employee Name Search", acNormal, , , acFormReadOnly
        End If
    End If
End Sub

 

[ITguy1981]

Hopefully, this is my last question. Where do I put the code for the debug? Do I put in the code for my on click event for the button that opens the form or does it go in the on load of the form that is being opened?

 

[ITguy1981]

Here is my debug info.
Private Sub Command6_Click()
If Me.StartDate > Date Or IsNull(Me.StartDate) Then
MsgBox "Must enter a start date before or equal to today"
Cancel = True
Me.StartDate.SetFocus
ElseIf Me.EndDate > Date Or IsNull(Me.EndDate) Then
MsgBox "Must enter an end date before or equal to today"
Cancel = True
Me.EndDate.SetFocus
ElseIf Me.StartDate <= Date Then
DoCmd.OpenForm "Daily Time from Employee Name Search", acNormal, , , acFormReadOnly
End If
End Sub
The orange text is what is highlighted from the debugger.

 

[vbaInet]

Hopefully, this is my last question. Where do I put the code for the debug?

What debug do you mean? Please elaborate.

Do I put in the code for my on click event for the button that opens the form or does it go in the on load of the form that is being opened?

I think that's pretty clear in my last post. If it was to go somewhere else I would have mentioned it.

 

[ITguy1981]

I was able to get the error handling issue resolved. I found a similar issue and copied some code to fit my database. Here is what I used.
Private Sub Command6_Click()
On Error GoTo Err_Command6_Click
If Me.StartDate > Date Or IsNull(Me.StartDate) Then
MsgBox "Must enter a start date before or equal to today"
Cancel = True
Me.StartDate.SetFocus
ElseIf Me.EndDate > Date Or IsNull(Me.EndDate) Then
MsgBox "Must enter an end date before or equal to today"
Cancel = True
Me.EndDate.SetFocus
ElseIf Me.StartDate <= Date Then
DoCmd.OpenForm "Daily Time from Employee Name Search", acNormal, , , acFormReadOnly
End If
Exit_Command6_Click:
Exit Sub
Err_Command6_Click:
If Err <> 2501 Then
MsgBox "Error Number: " & Err.Number & vbCrLf & _
"Description: " & Err.Description
End If
Resume Exit_Command6_Click
End Sub