Solved

Calendar Box and Lines for a Dynamics custom form

Posted on 2010-11-10
22
461 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:taverny
  • 15
  • 7
22 Comments
 
LVL 18

Accepted Solution

by:
Steve Endow earned 500 total points
ID: 34113301
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
 

Author Comment

by:taverny
ID: 34113400
Steve,
Thanks for the response. How you do in your code to include a text field for dates?

Thanks
David
0
 

Author Comment

by:taverny
ID: 34114224
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
 

Author Comment

by:taverny
ID: 34115182
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
 

Author Comment

by:taverny
ID: 34115658
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
 
LVL 18

Assisted Solution

by:Steve Endow
Steve Endow earned 500 total points
ID: 34123124
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
 

Author Comment

by:taverny
ID: 34123852
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
 

Author Comment

by:taverny
ID: 34124529
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
 
LVL 18

Assisted Solution

by:Steve Endow
Steve Endow earned 500 total points
ID: 34124532
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
 

Author Comment

by:taverny
ID: 34124548
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
 

Author Comment

by:taverny
ID: 34124653
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 18

Expert Comment

by:Steve Endow
ID: 34124904
Ya, formatting the contents of text boxes is tedious.  You could try including:

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

to remove the slashes.
0
 

Author Comment

by:taverny
ID: 34124989
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
 

Author Comment

by:taverny
ID: 34125109
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
 
LVL 18

Expert Comment

by:Steve Endow
ID: 34125276
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
 
LVL 18

Expert Comment

by:Steve Endow
ID: 34125295
And I believe you want to use ElseIf for your check of 8 characters.
0
 

Author Comment

by:taverny
ID: 34125297
it's say that I am missing one more
0
 

Author Comment

by:taverny
ID: 34125305
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
 
LVL 18

Expert Comment

by:Steve Endow
ID: 34125334
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
 

Author Comment

by:taverny
ID: 34125352
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
 

Author Comment

by:taverny
ID: 34125380
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
 

Author Closing Comment

by:taverny
ID: 34125391
GREAT JOB!!!
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This is a walkthrough guide I wrote whilst upgrading my on-premise MS Dynamics CRM 3.0 deployment to 4.0. This covers the actual installation of the product to a working level for my system, I ran into a lot of issues that the steps below fixed so h…
Automatically creating a Trello card using data from a Microsoft Dynamics CRM record turned out to be an easy project that yielded great results.  Here's how I did this for an internal team at General Code.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

757 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now