?
Solved

Form using calendar control

Posted on 2006-06-08
8
Medium Priority
?
260 Views
Last Modified: 2008-02-01
hello!

I have a form using the Calendar control with two boxes:
Start Date
End Date

The user clicks in the Start date and the Calendar comes up and the user selects the date then the user selects the End Date field and the calendar switches focus to this field and the user selects the date.

What I am wondering is how could I verify the information that the start date is earlier than the end date before the user clicks OK or After the user clicks OK which runs my report so the user can view the information?

Is there a code I can use in the OK control box and does anyone know what I would need to put in there?
0
Comment
Question by:MilitaryDonut
  • 6
  • 2
8 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 16861997
ok, on both txtboxes, on their BeforeUpdate, u call a validation routine
e.g.


private sub StartDate_BeforeUpdate(Cancel as Integer)
    Cancel = ValidateDates
end sub

private sub EndDate_BeforeUpdate(Cancel as Integer)
    Cancel = ValidateDates
end sub


private function ValidateDates as Integer
    validatedates = false
    if isnull(Me.StartDate) = false and isnull(me.enddate) = false then
             if format(me.startdate,"YYYYMMDD") > format(me.enddate,"YYYYMMDD") then
                    msgbox "Invalid date range"
                    validatedates = True
              end if
    end if
end function


 
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16862043
ok, I added the BeforeUpdate handlers so validation is done when the user edits a value in there

From the ok button, u need to verify both values are entered and dates are valid

private function ValidateDates2 as boolean

    'assume the worst
    validatedates2 = false

    if isnull(Me.StartDate) = false and isnull(me.enddate) = false then
             if format(me.startdate,"YYYYMMDD") > format(me.enddate,"YYYYMMDD") then
                    msgbox "Invalid date range"
             else
                    validatedates2 = true
              end if
     else
            msgbox "Both a start and end date must be specified"
    end if
end function



This function returns true if valid, false otherwise

so in your ok button u can do this

if validatedates2 = false then
    msgbox "Back to drawing board... Enter valid dates"
...

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16862124
are these textboxes editable by the user?

if so then the other validation to add is IsDate
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 65

Expert Comment

by:rockiroads
ID: 16862185
here it is again with IsDate added


this overwrites everything else Ive done before (hence name being ValidateDates)

Private Function ValidateDates as Boolean

    'Assume the worst
    ValidateDates = False
   
    'Check both dates entered
    If IsNull(Me.StartDate) = False And IsNull(Me.EndDate) = False Then
   
        'Check both are valid dates
        If IsDate(Me.StartDate) = True And IsDate(Me.EndDate) = True Then
       
            'Check start > end
            If CDate(Me.StartDate) > CDate(Me.EndDate) Then
                MsgBox "Start greater than end"
            Else
                ValidateDates = True
            End If
        Else
            MsgBox "Invalid dates specified"
        End If
    Else
        MsgBox "Both Start and End Dates have to be filled"
    End If
   
End Sub




Note I used CDATE this time, this should work as well as Format
Im so used to using Format which is why I put that down first
0
 

Author Comment

by:MilitaryDonut
ID: 16862327
Hey,

thanks!

Here is what I have for my coding on this form:

I added your coding but I don't understand something.  When I run the compile this seems to give me both msg boxes and continues running my DoCmd when I click OK on my button?  Why would it continue?  I was thinking it should stop until the proper dates are entered then continue with my DoCmd?

Am I wrong?  Or did I place it wrong?

Option Compare Database
Option Explicit
Dim cboOriginator As ComboBox

Private Sub cboEndDate_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
    Set cboOriginator = cboEndDate
    ocxCalendar.Visible = True
    ocxCalendar.SetFocus

    If Not IsNull(cboEndDate) Then
        ocxCalendar.Value = cboEndDate.Value
    Else
        ocxCalendar.Value = Date
    End If
   
   
End Sub

Private Sub cboStartDate_Click()
Set cboOriginator = cboStartDate
    ocxCalendar.Visible = True
    ocxCalendar.SetFocus

    If Not IsNull(cboStartDate) Then
        ocxCalendar.Value = cboStartDate.Value
    Else
        ocxCalendar.Value = Date
    End If


End Sub

Private Sub cboStartDate_KeyPress(KeyAscii As Integer)
Set cboOriginator = cboStartDate
    ocxCalendar.Visible = True
    ocxCalendar.SetFocus

    If Not IsNull(cboStartDate) Then
        ocxCalendar.Value = cboStartDate.Value
    Else
        ocxCalendar.Value = Date
    End If


End Sub

Private Sub cboStartDate_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
    Set cboOriginator = cboStartDate
    ocxCalendar.Visible = True
    ocxCalendar.SetFocus

    If Not IsNull(cboStartDate) Then
        ocxCalendar.Value = cboStartDate.Value
    Else
        ocxCalendar.Value = Date
    End If


End Sub

Private Sub ocxCalendar_Click()
' Copy chosen date from calendar to originating combo box
    cboOriginator.Value = ocxCalendar.Value
' Return the focus to the combo box and hide the calendar and
    cboOriginator.SetFocus
    ocxCalendar.Visible = False
' Empty the variable
    Set cboOriginator = Nothing

End Sub

Private Sub cmdExit_Click()
On Error GoTo Err_cmdExit_Click


    DoCmd.Close

Exit_cmdExit_Click:
    Exit Sub

Err_cmdExit_Click:
    MsgBox Err.Description
    Resume Exit_cmdExit_Click
   
End Sub

Private Sub OK_Click()
If ValidateDates2 = False Then
    MsgBox "Back to drawing board... Enter valid dates"
End If

DoCmd.OpenForm "frmSummary4StartEndDateFind"
DoCmd.OpenForm "frmNote"

End Sub


Private Function ValidateDates2() As Boolean

    'assume the worst
    ValidateDates2 = False

    If IsNull(Me.cboStartDate) = False And IsNull(Me.cboEndDate) = False Then
             If Format(Me.cboStartDate, "YYYYMMDD") > Format(Me.cboEndDate, "YYYYMMDD") Then
                    MsgBox "Invalid date range"
             Else
                    ValidateDates2 = True
              End If
     Else
            MsgBox "Both a start and end date must be specified"
    End If
End Function

0
 
LVL 65

Accepted Solution

by:
rockiroads earned 2000 total points
ID: 16862736
ok, in cmdOK, if it fails u need to exit the sub and not open the forms
e.g.

Private Sub OK_Click()

If ValidateDates2 = False Then
    MsgBox "Back to drawing board... Enter valid dates"

'NEW LINE HERE - WE DONT WANT TO OPEN THE FORM
    Exit Sub
End If

DoCmd.OpenForm "frmSummary4StartEndDateFind"
DoCmd.OpenForm "frmNote"

End Sub




See the updated ValidateDates I put, that looks better if u want to use that instead

0
 

Author Comment

by:MilitaryDonut
ID: 16863167
Rockiroads!

Wow!  that is great!  I love it!  This can't get any better!  

I used the updated ValidateDates coding.  I agress this seems cleaner.

Thanks for being patient with this newbie!
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16863217
No probs
And it looks like you understood what Ive done, thats the main thing

Happy coding!!!
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question