Calendar Box and Lines for a Dynamics custom form

Hi,
I am designing my own form in MS Dynamics GP with VBA. I was able to write some code and create button,but I would like to know how I can add a calendar box to enter a date from the calendar popup window. I don't want anything fancy, the calendar that dynamics is using is perfect for me. also I would like to have the date to default to todays date by default.

Also I would like to add some line and make the form a little more nice to look at , how can I draw some lines with VB?

Thanks in advance
tavernyAsked:
Who is Participating?
 
Steve EndowConnect With a Mentor Microsoft MVP - Dynamics GPCommented:
Hi,

From what I can tell, there is no simple 'drop down' style calendar control in VBA, at least in GP 9 and GP 10.

Here is an article on how you can use the MS Access Calendar Control to create your own calendar window.  Not as nice as a simple control, but it's one option.

http://www.fontstuff.com/vba/vbatut07.htm

Thanks,

Steve Endow
Dynamics GP Certified Trainer
Dynamics GP Certified Professional


0
 
tavernyAuthor Commented:
Steve,
Thanks for the response. How you do in your code to include a text field for dates?

Thanks
David
0
 
tavernyAuthor Commented:
I found something regarding the calendar:

This page has a sample for a calendar control:. CalendarControl10.zip
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=cf81786e-716d-4d71-bc01-daebd19dcd2b&displaylang=en

I downloaded the package and imported in Dynamics but it gave me an error regarding the "Microsoft Calendar Control 11.0" not registered . So I looked online for the object and I found this module:

http://www.fontstuff.com/mailbag/qvba01.htm

I downloaded mscal_office2003.zip and register the control.
after that,I was able to import the sample code , but I still couldn't see the references, so I looed around in VB and found under insert > components >controls "Microsoft Calendar Control 11.0". I checked it and now under reference I can select it as well.

but I still doesn't see the control under my toolbox . So I am not sure what else I need to do to include the calendar control to my form.

Any suggestions?

thanks m,
David
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
tavernyAuthor Commented:
ok I got some progress,I found the control for the calendar. I right click on the ToolBox and added Calendar Control 11.0.

Now I am gonna try to figure out how to use it.
0
 
tavernyAuthor Commented:
Steve,

I guess I need your help on this matter.
The text box for the date is setup.
What I would like to do is to have the same way that dynamics works on Date field. Basically when we type in the box , the box is limited to 12 characters: MMDDYYYY , then when we leave the box the data is reformated to MM/DD/YYYY and to make sure that the data is correct. Also I am gonna try to hide and show the calendar next to the box and when we pick the date in the calendar it will put it automatically and close the calendar.
David

Date.JPG
0
 
Steve EndowConnect With a Mentor Microsoft MVP - Dynamics GPCommented:
Hi,

Since VBA doesn't have predefined text field formats, I believe you will need to do something like this:

If IsDate(Text1.Text) Then
	Text1.Text = Format(Text1.Text, "mm/dd/yyyy")
End If

Open in new window



Thanks,

Steve Endow
Dynamics GP Certified Trainer
Dynamics GP Certified Professional
0
 
tavernyAuthor Commented:
Hi Steve,

this is exactly what I needed , it does work when I type 12122012
but if I type 01012010 , the formated field becomes 10/14/4670. it's pretty weird.
do you knjow why ?
Thanks
David
0
 
tavernyAuthor Commented:
HI Steve,

I can't figure out the issue and I think there is a lot of code involved , so I think I am gonna disable the box to type in it and let the user choose the date from the calendar directly. but i would like by default to let the calendar hiligh the today's day by default.
attached is the latest code I tried to modify the text field, but there  is some glitch
txtDate.Text = (Left(txtDate.Text, 2) & "/" & Mid(txtDate.Text, 3, 2) & "/" & Mid(txtDate.Text, 5, 4))

Open in new window

0
 
Steve EndowConnect With a Mentor Microsoft MVP - Dynamics GPCommented:
Hi,

Interesting.  It seems that both the Format and FormatDateTime functions need some type of formatting or delimiters themselves to work.  Even then, I can't get the functions to handle "01/01/2010".  Very weird.

My only recommendation would be to try something like:

      myDate = "01012010"
    If Len(myDate) = 6 Then
        parsedDate = Left(myDate, 2) & "/" & Mid(myDate, 3, 2) & "/" & Mid(myDate, 5, 2)
    End If
    If Len(myDate) = 8 Then
        parsedDate = Left(myDate, 2) & "/" & Mid(myDate, 3, 2) & "/" & Mid(myDate, 5, 4)
    End If


Thanks,

Steve Endow
Dynamics GP Certified Trainer
Dynamics GP Certified Professional
0
 
tavernyAuthor Commented:
Thanks Steve,
that's what I was trying to get. I was giving up . I am gonna try that to see if I can get it to working.
Thank you .
David
 
0
 
tavernyAuthor Commented:
Steve,
 it does work ,
there is just a little tweek that I need to do .
If the user enter 01/01/2012 , then it doesn't work :)
so I tried to create a test to make sure the user enter only numbers, but if the user enters only numbers correctly and then click another box , but then go back into it and want to change it . then it fails because of the slash.
it's really not easy this box .

David

Private Sub txtDate_AfterUpdate()
If IsNumeric(txtDate) Then
    If Len(txtDate) = 6 Then
        txtDate.Text = (Left(txtDate, 2) & "/" & Mid(txtDate, 3, 2) & "/" & "20" & Mid(txtDate, 5, 2))
    End If
    If Len(txtDate) = 8 Then
        txtDate.Text = (Left(txtDate, 2) & "/" & Mid(txtDate, 3, 2) & "/" & Mid(txtDate, 5, 4))
    End If
Else
    MsgBox ("wrong Date format")
End If
End Sub

Open in new window

0
 
Steve EndowMicrosoft MVP - Dynamics GPCommented:
Ya, formatting the contents of text boxes is tedious.  You could try including:

Replace(txtDate.Text, "/", "")

to remove the slashes.
0
 
tavernyAuthor Commented:
ok, I think I found the proper code, of course with all your examples.
let me know if you see anything wrong or weird. It does work, I just have to make sure I din't forget anything that my users mind find.


Private Sub txtDate_AfterUpdate()
Dim slash As String
Dim num As String
slash = "//"
num = (Mid(txtDate, 1, 2) & Mid(txtDate, 4, 2) & Mid(txtDate, 7, 4))

    If Len(txtDate) = 10 And slash = (Mid(txtDate, 3, 1) & Mid(txtDate, 6, 1)) And IsNumeric(num) = True Then
            MsgBox ("Date good!!!!!")
    Else
        If IsNumeric(txtDate) Then
            If Len(txtDate) = 6 Then
                txtDate.Text = (Left(txtDate, 2) & "/" & Mid(txtDate, 3, 2) & "/" & "20" & Mid(txtDate, 5, 2))
            End If
            If Len(txtDate) = 8 Then
                txtDate.Text = (Left(txtDate, 2) & "/" & Mid(txtDate, 3, 2) & "/" & Mid(txtDate, 5, 4))
            End If
        Else
        MsgBox ("wrong Date format")
        End If
    End If

Open in new window

0
 
tavernyAuthor Commented:
Steve,

I guess I didn't go far before finding an error. If the text field doesn't have 6, 8 or 10 characters, then the code doesn't test for that :)
so I added another msgbox toward the end , but I guess my if , else are wrong and I can't figure out , how an if inside another if works with the end if.

I thought :
if---------- then
   if-----------then
   else
   -------------
  else
  ------------
end if
Private Sub txtDate_AfterUpdate()
Dim slash As String
Dim num As String
slash = "//"
'num hold the data from the date field without the //
num = (Mid(txtDate, 1, 2) & Mid(txtDate, 4, 2) & Mid(txtDate, 7, 4))

    If Len(txtDate) = 10 And slash = (Mid(txtDate, 3, 1) & Mid(txtDate, 6, 1)) And IsNumeric(num) = True Then
            MsgBox ("Date good!!!!!")
    Else
        If IsNumeric(txtDate) Then
            If Len(txtDate) = 6 Then
                txtDate.Text = (Left(txtDate, 2) & "/" & Mid(txtDate, 3, 2) & "/" & "20" & Mid(txtDate, 5, 2))
            Else
                If Len(txtDate) = 8 Then
                txtDate.Text = (Left(txtDate, 2) & "/" & Mid(txtDate, 3, 2) & "/" & Mid(txtDate, 5, 4))
            Else
                MsgBox ("Wrong Date Format")
        Else
            MsgBox ("wrong Date format")
        End If
    End If
End Sub

Open in new window

0
 
Steve EndowMicrosoft MVP - Dynamics GPCommented:
You need one more End If after your inner loop:

               Else
                       MsgBox ("Wrong Date Format")
              End If
        Else
            MsgBox ("wrong Date format")
End If
0
 
Steve EndowMicrosoft MVP - Dynamics GPCommented:
And I believe you want to use ElseIf for your check of 8 characters.
0
 
tavernyAuthor Commented:
it's say that I am missing one more
0
 
tavernyAuthor Commented:
yep you got it , the elseif and the end if solve the issue.
I tried "ELSE IF" and not "ELSEIF" , one space can mess up the whole thing.
0
 
Steve EndowMicrosoft MVP - Dynamics GPCommented:
You have an extra If here with no End If:

If Len(txtDate) = 8 Then
                txtDate.Text = (Left(txtDate, 2) & "/" & Mid(txtDate, 3, 2) & "/" & Mid(txtDate, 5, 4))

Look into using ElseIf instead.
0
 
tavernyAuthor Commented:
Thanks Steve,

I think it works pretty well now. the only test that I don't do is for a correct date basically if I put 32/34/1002 it will take it .
I am not sure if I want to get into that , it might be very complicate to test for each number.
but attached is the workign code that I have.

thanks.
Private Sub txtDate_AfterUpdate()
Dim slash As String
Dim num, num1 As String
slash = "//"
'num hold the data from the date field without the //
num = (Mid(txtDate, 1, 2) & Mid(txtDate, 4, 2) & Mid(txtDate, 7, 4))
num1 = (Mid(txtDate, 1, 2) & Mid(txtDate, 4, 2) & Mid(txtDate, 7, 2))

'if 12/12/1212
    If Len(txtDate) = 10 And slash = (Mid(txtDate, 3, 1) & Mid(txtDate, 6, 1)) And IsNumeric(num) = True Then
            MsgBox ("Date good!!!!!")
 'if 12/12/12
    ElseIf Len(txtDate) = 8 And slash = (Mid(txtDate, 3, 1) & Mid(txtDate, 6, 1)) And IsNumeric(num1) = True Then
            txtDate.Text = (Left(txtDate, 2) & "/" & Mid(txtDate, 4, 2) & "/20" & Mid(txtDate, 7, 2))
            MsgBox ("Date good!!!!!")
    ElseIf IsNumeric(txtDate) Then
            If Len(txtDate) = 6 Then
                txtDate.Text = (Left(txtDate, 2) & "/" & Mid(txtDate, 3, 2) & "/20" & Mid(txtDate, 5, 2))
            ElseIf Len(txtDate) = 8 Then
                txtDate.Text = (Left(txtDate, 2) & "/" & Mid(txtDate, 3, 2) & "/" & Mid(txtDate, 5, 4))
            Else
                MsgBox ("Wrong Date Format")
            End If
    Else
            MsgBox ("wrong Date format")
    End If
End Sub

Open in new window

0
 
tavernyAuthor Commented:
Steve,

I think I can close this question. you did a great Job and you really helped me a lot on my project.
I will probably have more questions regarding my form when I am done with the other issue first.

Thank you so much.
David

I am attaching the final code that I have if anyone needs it in the future.
Private Sub txtDate_AfterUpdate()
Dim slash As String
Dim num, num1 As String
slash = "//"
'num hold the data from the date field without the //
num = (Mid(txtDate, 1, 2) & Mid(txtDate, 4, 2) & Mid(txtDate, 7, 4))
num1 = (Mid(txtDate, 1, 2) & Mid(txtDate, 4, 2) & Mid(txtDate, 7, 2))

'if 12/12/1212
    If Len(txtDate) = 10 And slash = (Mid(txtDate, 3, 1) & Mid(txtDate, 6, 1)) And IsNumeric(num) = True Then
            MsgBox ("Date good!!!!!")
 'if 12/12/12
    ElseIf Len(txtDate) = 8 And slash = (Mid(txtDate, 3, 1) & Mid(txtDate, 6, 1)) And IsNumeric(num1) = True Then
            txtDate.Text = (Left(txtDate, 2) & "/" & Mid(txtDate, 4, 2) & "/20" & Mid(txtDate, 7, 2))
            MsgBox ("Date good!!!!!")
    ElseIf IsNumeric(txtDate) Then
            If Len(txtDate) = 6 Then
                txtDate.Text = (Left(txtDate, 2) & "/" & Mid(txtDate, 3, 2) & "/20" & Mid(txtDate, 5, 2))
            ElseIf Len(txtDate) = 8 Then
                txtDate.Text = (Left(txtDate, 2) & "/" & Mid(txtDate, 3, 2) & "/" & Mid(txtDate, 5, 4))
            Else
                MsgBox ("Wrong Date Format")
            End If
    Else
            MsgBox ("wrong Date format")
    End If
End Sub

Open in new window

0
 
tavernyAuthor Commented:
GREAT JOB!!!
0
All Courses

From novice to tech pro — start learning today.