Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 346
  • Last Modified:

Entering date on a form

Experts,

I want to make it easy for users to enter the date of a transaction in the date field on a form.  Microsoft Date and Time Picker control 6.0 (SP4) works well.  The only problem is that sometimes the user may want to leave the date field blank.  Is it possible to make the Date and Time Picker not show a date?

If the answer above is no, then do you have any suggestions how by clicking in the date field another form pops up with a calendar from which the user double clicks the date he wants and the date field in the mainform is populated and the calendar goes away?

Thanks.
0
RishiSingh05
Asked:
RishiSingh05
  • 5
  • 5
  • 3
  • +1
3 Solutions
 
Rey Obrero (Capricorn1)Commented:
0
 
Arthur_WoodCommented:
I am doing EXACTLY what you describe, but in Excel - but the steps aare exactly the same in Access.

However, it is somewhat problematic to send you the FORM that I am using, along with the code to supprt the form.  The best I can do is to send you the code for the form, as describe the form, so here goes:


Private bActivate As Boolean
Public OKClicked As Boolean
Public ClearClicked As Boolean
Public CancelClicked As Boolean


Private Sub cboMonth_Change()
    If bActivate = False Then
        drawCalendar
    End If
End Sub

Private Sub cboYear_click()
    If bActivate = False Then
        drawCalendar
    End If
   
End Sub

Private Sub cmdCancel_Click()
    Unload Me
End Sub

Private Sub cmdClear_Click()
    ClearClicked = True
    Unload Me
   
End Sub

Private Sub cmdSelect_Click()
    OKClicked = True
    Unload Me
End Sub



Private Sub lbl1_Click()
    If lbl1.Caption <> "" Then
        lblDate.Caption = cboMonth.ListIndex + 1 & "/" & lbl1.Caption & "/" & cboYear.Text
        resetDayColor
        lbl1.BackColor = "65535"
    End If
End Sub
Private Sub lbl2_Click()
    If lbl2.Caption <> "" Then
        lblDate.Caption = cboMonth.ListIndex + 1 & "/" & lbl2.Caption & "/" & cboYear.Text
        resetDayColor
        lbl2.BackColor = "65535"
    End If
End Sub
Private Sub lbl3_Click()
    If lbl3.Caption <> "" Then
        lblDate.Caption = cboMonth.ListIndex + 1 & "/" & lbl3.Caption & "/" & cboYear.Text
        resetDayColor
        lbl3.BackColor = "65535"
    End If
End Sub
Private Sub lbl4_Click()
    If lbl4.Caption <> "" Then
        lblDate.Caption = cboMonth.ListIndex + 1 & "/" & lbl4.Caption & "/" & cboYear.Text
        resetDayColor
        lbl4.BackColor = "65535"
    End If
End Sub
Private Sub lbl5_Click()
    If lbl5.Caption <> "" Then
        lblDate.Caption = cboMonth.ListIndex + 1 & "/" & lbl5.Caption & "/" & cboYear.Text
        resetDayColor
        lbl5.BackColor = "65535"
    End If
End Sub
Private Sub lbl6_Click()
    If lbl6.Caption <> "" Then
        lblDate.Caption = cboMonth.ListIndex + 1 & "/" & lbl6.Caption & "/" & cboYear.Text
        resetDayColor
        lbl6.BackColor = "65535"
    End If
End Sub
Private Sub lbl7_Click()
    If lbl7.Caption <> "" Then
        lblDate.Caption = cboMonth.ListIndex + 1 & "/" & lbl7.Caption & "/" & cboYear.Text
        resetDayColor
        lbl7.BackColor = "65535"
    End If
End Sub
Private Sub lbl8_Click()
    If lbl8.Caption <> "" Then
        lblDate.Caption = cboMonth.ListIndex + 1 & "/" & lbl8.Caption & "/" & cboYear.Text
        resetDayColor
        lbl8.BackColor = "65535"
    End If
End Sub
Private Sub lbl9_Click()
    If lbl9.Caption <> "" Then
        lblDate.Caption = cboMonth.ListIndex + 1 & "/" & lbl9.Caption & "/" & cboYear.Text
        resetDayColor
        lbl9.BackColor = "65535"
    End If
End Sub
Private Sub lbl10_Click()
    If lbl10.Caption <> "" Then
        lblDate.Caption = cboMonth.ListIndex + 1 & "/" & lbl10.Caption & "/" & cboYear.Text
        resetDayColor
        lbl10.BackColor = "65535"
    End If
End Sub
Private Sub lbl11_Click()
    If lbl11.Caption <> "" Then
        lblDate.Caption = cboMonth.ListIndex + 1 & "/" & lbl11.Caption & "/" & cboYear.Text
        resetDayColor
        lbl11.BackColor = "65535"
    End If
End Sub
Private Sub lbl12_Click()
    If lbl12.Caption <> "" Then
        lblDate.Caption = cboMonth.ListIndex + 1 & "/" & lbl12.Caption & "/" & cboYear.Text
        resetDayColor
        lbl12.BackColor = "65535"
    End If
End Sub
Private Sub lbl13_Click()
    If lbl13.Caption <> "" Then
        lblDate.Caption = cboMonth.ListIndex + 1 & "/" & lbl13.Caption & "/" & cboYear.Text
        resetDayColor
        lbl13.BackColor = "65535"
    End If
End Sub
Private Sub lbl14_Click()
    If lbl14.Caption <> "" Then
        lblDate.Caption = cboMonth.ListIndex + 1 & "/" & lbl14.Caption & "/" & cboYear.Text
        resetDayColor
        lbl14.BackColor = "65535"
    End If
End Sub
Private Sub lbl15_Click()
    If lbl15.Caption <> "" Then
        lblDate.Caption = cboMonth.ListIndex + 1 & "/" & lbl15.Caption & "/" & cboYear.Text
        resetDayColor
        lbl15.BackColor = "65535"
    End If
End Sub
Private Sub lbl16_Click()
    If lbl16.Caption <> "" Then
        lblDate.Caption = cboMonth.ListIndex + 1 & "/" & lbl16.Caption & "/" & cboYear.Text
        resetDayColor
        lbl16.BackColor = "65535"
    End If
End Sub
Private Sub lbl17_Click()
    If lbl17.Caption <> "" Then
        lblDate.Caption = cboMonth.ListIndex + 1 & "/" & lbl17.Caption & "/" & cboYear.Text
        resetDayColor
        lbl17.BackColor = "65535"
    End If
End Sub
Private Sub lbl18_Click()
    If lbl18.Caption <> "" Then
        lblDate.Caption = cboMonth.ListIndex + 1 & "/" & lbl18.Caption & "/" & cboYear.Text
        resetDayColor
        lbl18.BackColor = "65535"
    End If
End Sub
Private Sub lbl19_Click()
    If lbl19.Caption <> "" Then
        lblDate.Caption = cboMonth.ListIndex + 1 & "/" & lbl19.Caption & "/" & cboYear.Text
        resetDayColor
        lbl19.BackColor = "65535"
    End If
End Sub
Private Sub lbl20_Click()
    If lbl20.Caption <> "" Then
        lblDate.Caption = cboMonth.ListIndex + 1 & "/" & lbl20.Caption & "/" & cboYear.Text
        resetDayColor
        lbl20.BackColor = "65535"
    End If
End Sub
Private Sub lbl21_Click()
    If lbl21.Caption <> "" Then
        lblDate.Caption = cboMonth.ListIndex + 1 & "/" & lbl21.Caption & "/" & cboYear.Text
        resetDayColor
        lbl21.BackColor = "65535"
    End If
End Sub
Private Sub lbl22_Click()
    If lbl22.Caption <> "" Then
        lblDate.Caption = cboMonth.ListIndex + 1 & "/" & lbl22.Caption & "/" & cboYear.Text
        resetDayColor
        lbl22.BackColor = "65535"
    End If
End Sub
Private Sub lbl23_Click()
    If lbl23.Caption <> "" Then
        lblDate.Caption = cboMonth.ListIndex + 1 & "/" & lbl23.Caption & "/" & cboYear.Text
        resetDayColor
        lbl23.BackColor = "65535"
    End If
End Sub
Private Sub lbl24_Click()
    If lbl24.Caption <> "" Then
        lblDate.Caption = cboMonth.ListIndex + 1 & "/" & lbl24.Caption & "/" & cboYear.Text
        resetDayColor
        lbl24.BackColor = "65535"
    End If
End Sub
Private Sub lbl25_Click()
    If lbl25.Caption <> "" Then
        lblDate.Caption = cboMonth.ListIndex + 1 & "/" & lbl25.Caption & "/" & cboYear.Text
        resetDayColor
        lbl25.BackColor = "65535"
    End If
End Sub
Private Sub lbl26_Click()
    If lbl26.Caption <> "" Then
        lblDate.Caption = cboMonth.ListIndex + 1 & "/" & lbl26.Caption & "/" & cboYear.Text
        resetDayColor
        lbl26.BackColor = "65535"
    End If
End Sub
Private Sub lbl27_Click()
    If lbl27.Caption <> "" Then
        lblDate.Caption = cboMonth.ListIndex + 1 & "/" & lbl27.Caption & "/" & cboYear.Text
        resetDayColor
        lbl27.BackColor = "65535"
    End If
End Sub
Private Sub lbl28_Click()
    If lbl28.Caption <> "" Then
        lblDate.Caption = cboMonth.ListIndex + 1 & "/" & lbl28.Caption & "/" & cboYear.Text
        resetDayColor
        lbl28.BackColor = "65535"
    End If
End Sub
Private Sub lbl29_Click()
    If lbl29.Caption <> "" Then
        lblDate.Caption = cboMonth.ListIndex + 1 & "/" & lbl29.Caption & "/" & cboYear.Text
        resetDayColor
        lbl29.BackColor = "65535"
    End If
End Sub
Private Sub lbl30_Click()
    If lbl30.Caption <> "" Then
        lblDate.Caption = cboMonth.ListIndex + 1 & "/" & lbl30.Caption & "/" & cboYear.Text
        resetDayColor
        lbl30.BackColor = "65535"
    End If
End Sub
Private Sub lbl31_Click()
    If lbl31.Caption <> "" Then
        lblDate.Caption = cboMonth.ListIndex + 1 & "/" & lbl31.Caption & "/" & cboYear.Text
        resetDayColor
        lbl31.BackColor = "65535"
    End If
End Sub
Private Sub lbl32_Click()
    If lbl32.Caption <> "" Then
        lblDate.Caption = cboMonth.ListIndex + 1 & "/" & lbl32.Caption & "/" & cboYear.Text
        resetDayColor
        lbl32.BackColor = "65535"
    End If
End Sub
Private Sub lbl33_Click()
    If lbl33.Caption <> "" Then
        lblDate.Caption = cboMonth.ListIndex + 1 & "/" & lbl33.Caption & "/" & cboYear.Text
        resetDayColor
        lbl33.BackColor = "65535"
    End If
End Sub
Private Sub lbl34_Click()
    If lbl34.Caption <> "" Then
        lblDate.Caption = cboMonth.ListIndex + 1 & "/" & lbl34.Caption & "/" & cboYear.Text
        resetDayColor
        lbl34.BackColor = "65535"
    End If
End Sub
Private Sub lbl35_Click()
    If lbl35.Caption <> "" Then
        lblDate.Caption = cboMonth.ListIndex + 1 & "/" & lbl35.Caption & "/" & cboYear.Text
        resetDayColor
        lbl35.BackColor = "65535"
    End If
End Sub
Private Sub lbl36_Click()
    If lbl36.Caption <> "" Then
        lblDate.Caption = cboMonth.ListIndex + 1 & "/" & lbl36.Caption & "/" & cboYear.Text
        resetDayColor
        lbl36.BackColor = "65535"
    End If
End Sub
Private Sub lbl37_Click()
    If lbl37.Caption <> "" Then
        lblDate.Caption = cboMonth.ListIndex + 1 & "/" & lbl37.Caption & "/" & cboYear.Text
        resetDayColor
        lbl37.BackColor = "65535"
    End If
End Sub
Private Sub lbl38_Click()
    If lbl38.Caption <> "" Then
        lblDate.Caption = cboMonth.ListIndex + 1 & "/" & lbl38.Caption & "/" & cboYear.Text
        resetDayColor
        lbl38.BackColor = "65535"
    End If
End Sub
Private Sub lbl39_Click()
    If lbl39.Caption <> "" Then
        lblDate.Caption = cboMonth.ListIndex + 1 & "/" & lbl39.Caption & "/" & cboYear.Text
        resetDayColor
        lbl39.BackColor = "65535"
    End If
End Sub
Private Sub lbl40_Click()
    If lbl40.Caption <> "" Then
        lblDate.Caption = cboMonth.ListIndex + 1 & "/" & lbl40.Caption & "/" & cboYear.Text
        resetDayColor
        lbl40.BackColor = "65535"
    End If
End Sub
Private Sub lbl41_Click()
    If lbl41.Caption <> "" Then
        lblDate.Caption = cboMonth.ListIndex + 1 & "/" & lbl41.Caption & "/" & cboYear.Text
        resetDayColor
        lbl41.BackColor = "65535"
    End If
End Sub
Private Sub lbl42_Click()
    If lbl42.Caption <> "" Then
        lblDate.Caption = cboMonth.ListIndex + 1 & "/" & lbl42.Caption & "/" & cboYear.Text
        resetDayColor
        lbl42.BackColor = "65535"
    End If
End Sub




Private Sub UserForm_Activate()
   
    OKClicked = False
    bActivate = True
   
    Dim i As Integer
   

   
   
   
    '--- load the Months
    For i = 1 To 12
        '--- cboMonth.AddItem Format(i & "/1/2004", "mmmm")
        cboMonth.AddItem MonthName(i, True)
    Next i
   
    If lblDateFrom = "" Then
        '--- lblDateFrom = Format(Now, "mm/dd/yyyy")
        lblDateFrom = Now
    End If
       
       
    For i = Year(lblDateFrom) - 80 To Year(lblDateFrom) + 20
        cboYear.AddItem i
    Next
    '--- load the txtyear
    '--- lblYear.Caption = Format(lblDateFrom, "yyyy")
    'lblYear.Caption = Year(lblDateFrom)
    cboYear.Text = Year(lblDateFrom)
   
    '--- select month
    '--- cboMonth.Text = Format(lblDateFrom, "mmmm")
    cboMonth.Text = MonthName(Month(lblDateFrom), True)
   
   
    '--- lblDate.Caption = Format(lblDateFrom, "mm/dd/yyyy")
    lblDate.Caption = lblDateFrom
   
    drawCalendar
   
    bActivate = False
End Sub

Private Function drawCalendar()
    'find out the day of the 1st
   
Dim iDaysinMonth
Dim iStartDay
Dim iDay
iStartDay = Weekday(cboMonth.ListIndex + 1 & "/1/" & cboYear.Text)
iDaysinMonth = DateDiff("d", cboMonth.ListIndex + 1 & "/1/" & cboYear.Text, DateAdd("m", 1, CDate(cboMonth.ListIndex + 1 & "/1/" & cboYear.Text)))
   
    'draw the date
    Dim oLbl
    For Each oLbl In Me.Controls
        If oLbl.Tag = "xDay" Then
            iDay = VBA.Mid(oLbl.Name, 4, Len(oLbl.Name)) - iStartDay + 1
            If iDay > 0 And iDay < iDaysinMonth + 1 Then
                oLbl.Caption = VBA.Mid(oLbl.Name, 4, Len(oLbl.Name)) - iStartDay + 1
                oLbl.BackColor = "16777152"
                If IsDate(lblDate.Caption) Then
                    If CInt(Day(lblDate.Caption)) = CInt(oLbl.Caption) Then
                        oLbl.BackColor = "65535"
                    End If
                   
                End If
               
            'Debug.Print oLbl.Name
            Else
                oLbl.Caption = ""
                oLbl.BackColor = "-2147483633"
            End If
           
        End If
       
    Next
End Function
Private Function resetDayColor()
Dim oLbl

For Each oLbl In Me.Controls
    If oLbl.Tag = "xDay" Then
        If oLbl.Caption <> "" Then
            oLbl.BackColor = "16777152"
        End If
       
    End If
   
Next

End Function

  The form consists of a Month Name combo box at the top (Jan through Dec which when cliked has the value as the corresponding month NUMBER 1 - 12), and a second combobox for the Year ( how many years you need is up to you, either in the future, or in the past),  Then Most of the form is made up of Label Controls with the number of the Day of the Week, laid out in rows of 7 (Monday through the following Sunday, for each week in the month  These are shown with a Colored background - Gray meaning Not selected, Yellow indicating the Current Date (when the Current Month and Current Year are selected in the combos or when the form is first show - showing the current date), and Green when a specific Date has been chosen by the user.  The Chosen Data is also shown on a Second Label control, in the format MM/DD/YYYY as confirmastion of the user's choice.  There are Three button controls at the bottom of the Form (Select - to select the indicated date, and close the form, Cancel - to allow the User to Cancel the Date selection - in which case NOTHING changes on the form from which the Calendar was opened, and Clear - which allows the user to CLEAR the date, thus clearing the date or entering NOTHING on the calling form).

I hope that this makes sense to you.

AW
0
 
RishiSingh05Author Commented:
AW,

I need to design a form/calendar with the combo boxes and controls as you have specified towards the end of your post, correct? Next, where does all the code go?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Rey Obrero (Capricorn1)Commented:
RishiSingh05
did you check the thread i posted?
0
 
netcoolCommented:
Hi,

I have a suggestion to your question, what you can do is
place a textbox in the screen, then reduce the date
picker control to only the arrow box avaliable, then
Arrange the textbox to near of the date picker control, it will appear like a picker control
Then when every the user go and choose the date by click the down arrow that display the calender.

Write this code in the closeup

Public ActiveXctl1_CloseUp()
      text1 = Activexctl1.Value
end sub

Set text1 as a date format.

Then the user can allow the text1 as null.


Check this idea.. I think this will solve the problem. If you have any question let me know
0
 
RishiSingh05Author Commented:
Capricorn1 _

I have seen all the threads.  Will be testing them in the course of the day.  Thanks.
0
 
RishiSingh05Author Commented:
netcool,

On my home pc I am able to see Microsoft Date and Time Picker Control under More Controls.  But not on my pc at work.  Both have Access 2003.  Any thoughts on this?
0
 
netcoolCommented:
Hi,

This may be installation problem also, try to remove the MS-office and install back. try that.
Still you can insert the ActivexCtl, go the the design mode of the Form.

Go to the Menu Insert then click ActiveXControls. that will show the controls then click will insert the control in the form.

This is my suggestion on this.

0
 
Arthur_WoodCommented:
ok, now that you have created this new form to represent the calandar, using the code that I posted, you can implement the form like this

Name the Form: frmCalendar

then when you wnat to show this form, to allow the user to assign a date, say by clicking on a button:

Private Sub btnCalendar()

     Dim cal as frmCalendar
     Set cal = New frmCalendar

     cal.lblDateFrom.Caption= txtDate.Text  ' assuming that you want to pre-load the calendar with a date shown on this form
     Cal.Show vbModal

     if not cal.CancelClicked then
         if not cal.ClearClicked then

             txtDate.Text = cal.lblDate.Caption
         Else
             txtDate.Text = ""
         End If
    end if
End Sub


AW
0
 
RishiSingh05Author Commented:
Netcool -
I could not find "Close Up" in the Event properties.  

Anyway, I have to click a date on the calendar and then click in the text box for the text box to be updated.  Users will expect that when they click or double-click a date on the calendar it shows up instantly in the text box.  Can this be done?
0
 
Arthur_WoodCommented:
That can be done, but it will be very messy in the code for the Calendar form that I sent you.  You would need to add a line to EVERY ONE of the lblNN controls (nn=1 to 42) to Hide the form, after the lable has been clicked.  This would be the same as having the user select the Day number, and then manually clicking on the OK button.  Thus each of the lblN_Click events would look like this:

Private Sub lbl1_Click()
    If lbl1.Caption <> "" Then
        lblDate.Caption = cboMonth.ListIndex + 1 & "/" & lbl1.Caption & "/" & cboYear.Text
        resetDayColor
        lbl1.BackColor = "65535"
        OKClicked = True
        Unload Me
    End If
End Sub


for all of the lblN_Click procedures, where N goes from 1 to 42

As for the Date and Time Picker, I suspect that you also have Visual Basic 6.0 installed on your home PC, but not your work PC - Correct?.  I have Access 2000, and Visual Studio 6 installed on this PC, and I have the Date and Time Picker, Version 6.0 as an available control, so it has nothing to do with the version of Access, but what other tools are also onstalled on the PC.

AW
0
 
netcoolCommented:
Hi

Don't see in the Event Properities of the ActivXctl.

Right click the mouse after selecting the Activexctl. The you will see the Build Events as a first in the Pop menu on right click on the control.

Click the Build Events Come this as default

Private Sub ActiveXCtl5_CallbackKeyDown(ByVal KeyCode As Integer, ByVal Shift As Integer, ByVal CallbackField As String, CallbackDate As Date)

End Sub

But don't choose this click the right dropdown you will see the CloseUP

Then put the code there

Private Sub ActiveXCtl5_CloseUp()
txtDateTo = ActiveXCtl5.Value
End Sub

Because the close up will be a good way to do this

If you have any issue let me now
0
 
RishiSingh05Author Commented:
Hi netcool -

Your instructions are simple enough but I am not able to follow...

<<The you will see the Build Events as a first in the Pop menu on right click on the control.>>  yes I see this.

<<Click the Build Events>> when I click "Build Event" I am given three options: expression, macro or code builder.

<<Come this as default:  Private Sub ActiveXCtl5_CallbackKeyDown(ByVal KeyCode As Integer, ByVal Shift As Integer, ByVal CallbackField As String, CallbackDate As Date) End Sub>>        I don't see this as default.

<<But don't choose this click the right dropdown you will see the CloseUP>>  I don't see a right dropdown.

Sorry if I am missing the obvious!
0
 
netcoolCommented:
Hi

I didnt mention that , when you are opening first time you will see the three options, choose >> code builder then you will see this

<<Come this as default:  Private Sub ActiveXCtl5_CallbackKeyDown(ByVal KeyCode As Integer, ByVal Shift As Integer, ByVal CallbackField As String, CallbackDate As Date) End Sub>>

The ActiveXctl5 is a sample , but for code screen, you will have your name of your control their then

<<But don't choose this click the right dropdown you will see the CloseUP>>  

if you have any problem let me now
0
 
netcoolCommented:
Hi

if you have problem still let me know, give you email, i will send you the screen shots and what to do.

0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 5
  • 5
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now