Passing Values to a Form (calendar)

[CementCarver]

Hi All,

Thanks to "Beginning Access97 VBA Programming" by R. Smith & D. Sussman, I was able to implement the use of a calendar form to allow a client to select a date, rather than keying one in. And here I thought I was done with the problem....Wrong !!! The calendar form only works if existing records already have a date(shortdate) associated with the record.

But when I try to create a new record, the date field is "null" and I can't figure out how to pass a date value to the calendar control even though it's receiving a null. I get this "type mismatch" error I've bumped into the part of the code that was supplied via the book where the "property set datecontrol (Byval ctldate as Control) is set, but I can't figure out
how to deal with this incoming "null" value from the newly created record.

Can anyone help? I'll check in later on tonight if anyone is needing part of the code as reference to what I'm trying to accomplish.

Thanks so much.......CementCarver

 

[Uncle Gizmo]

Free calendar form download here

There is a free calendar form you can download from my web site here, there is also a set of video showing you how to set up the calendar form, and also it has some advanced features that you might like to take advantage of.

 

[CementCarver]

Thanks Gizmo

Many thanks. I'll download the free calendar and check out the supplied
videos too.

...CementCarver

 

[Zaeed]

I have a calendar form in my db which sounds similar to yours.. it is a stand alone form, which is called when the user clicks on the date field of a form.
I have a global variable called calendarFlag which i set to a value corresponding to the field clicked.. i.e. 1 or 2 or 3 etc

Within my calendar form coding, i have a select case which uses that global variable to decide where to dump the date, using this line of code.

    Case 2
            [Form_frm_Change]!frm_Change_Date_Finish.Value = UserSelectedDate
    DoCmd.Close

form is called frm_Change, and the particular text box im filling is called frm_Change_Date_Finish

That line is different for each Case of the Select function.

UserSelectedDate is an ActiveXCtl0.Value

 

[CementCarver]

Thanks Matt.......I'll give your idea a try.

...CementCarver

 

[CementCarver]

Matt,

So you use the case 1,2,3 to set the default date? Is this set in a box beside the date field?

I'm not too well versed in this programming environment and I'm not really sure how you use that global variable to set this date value.

Can you please offer more code/help?

...Walter

 

[Zaeed]

On my main form, i have a subform, which displays all tasks for a given change.
Each of these tasks has 4 fields, one of these is a date field. For that field, in its OnClick procedure, i have it set a global variable. This variable is located in a Module, defined like this

Public CalendarFlag As Integer

When a user clicks on the date field, CalendarFlag is set to 3, and then opens the calendar flag. This is the Sub attatched to the date field.

Private Sub tbl_Tasks_subform_DateRequired_Click()
    Dim strOpen As String

    CalendarFlag = 3
    strOpen = "frm_Calendar"
    DoCmd.OpenForm strOpen, acNormal, , , , acDialog
End Sub

I do it this way since i have a few different places that i use a date, so instead of retyping multiple code, i simply have the one calendar do the lot.

The variable CalendarFlag is then read by the Calendar form in the Select Case method, which determines which field to insert the date into.

 

[Zaeed]

If you go to this thread, i have a copy of my db there, you can have a look at what im doing.

http://www.access-programmers.co.uk/forums/showthread.php?t=141503

use the password bob to get through the login.
Note that that version doesnt work that well, so i'd suggest you right click on the form frm_Change and have a look at it that way.

You'll see the subform Tasks at the bottom, the field DateRequired is one of the fields that uses the calendar flag. The form frm_Calendar is the my calendar.

 

[CementCarver]

Matt,

What username to I use to login?

...Walteer

 

[CementCarver]

Matt,

Downloaded your link to your database. Sorry about the stupid question about logging in... I thought you were referring to logging into this forum with that password....

I'm reviewing your code now. And, thanks very much for being so helpful. I'm in a drastic learning curve and need all the help I can get.

..Walt

 

[Zaeed]

Not a problem, I know how you feel. Im a Engineering student, atm im doing work placement which is required in order to graduate. I've been given a project to build this change tracking system. Access isnt really the ideal choice, and i've never really used it except when mucking around, so this has been a big learning curve for me too. So don't worry, you'll get there.

 

[CementCarver]

Matt,

I'm kind of getting the general idea on how your db is working, but I'm getting an error regarding setting a variable for the following:

Me!ActiveXCtl0.SetFocus
ActiveXCtl0.Day = ThisDay
ActiveXCtl0.Month = ThisMonth
ActiveXCtl0.year = ThisYear
ActiveXCtl0.Refresh

Where does this ActiveXCt10 thing come from? I've added my calendar control to my project but it sure doesn't look like that....?

Stuck Again............Walter

ps. I'm in the mapping industry here in Canada and all of a sudden I'm supposed to learn this API our vendor is supplying. But I'm just trying to get this simple tracking system to work for a client and all he wants is to be able to pick a darn date......

 

[Zaeed]

its the form itself.. If you have a look at the calendar form in design view, and click on the calendar, you will see that it is an activeX object. You could simply copy that from my db to yours, thats how i got it into mine in the first place.

If you want to find it yourself, open a form in design view, go to Insert -> Active X Control

A list of objects appear, scroll down till you find Calendar Control 11.0
Thats it..
I like it because it works by itself, you don't have to create and program a calendar. And it looks good for the end user, making it easy to use

NOTE: ActiveXCtl0 refers to the ActiveX Calendar on the calendar flag, thats what it is called

 

[CementCarver]

Matt,

All I have are Calendar Control 9.0 and I've been able to design a new form and use that Active X control.

I was able to use the box to the right of your Change form to activate the calendar, but now it's not passing the value I select back into the field I need it to populate. Plus I'm getting an invalid qualifier for the following code

Private Sub Mpac_Tracking_SentToMPAC_Click()
UserSelectedDate = ctlCalendar.Value

[Form_MPAC_tracking]!SentToMPAC.Value = UserSelectedDate
DoCmd.Close

End Sub

It's bombing out on the ctlCalendar.value ???

I just tried to import your calendar and when I try to open it, it says that "the record source "tbl_Change" specified on this form or report does not exist?

This learning VB code from a book is giving me a headache.....Walt

 

[Zaeed]

UserSelectedDate = ctlCalendar.Value

Make sure thats what your calendar is called

[Form_MPAC_tracking]!SentToMPAC.Value = UserSelectedDate

looks ok

the reason it doesnt work when you copy my form is that i have the coding telling it to paste into frm_Change. Delete this coding from the form and it'll work..

Do you want to post up your db and i'll take a look at it?

 

[Uncle Gizmo]

I have been watching this thread with interest, wondering whether I should reply or not!

I believe most of the problems you are having have been solved with my calendar form, all the hard work is done for you in the class module, all you have to do:

Is make sure you have one reference set,

Reference:
Microsoft visual basic for applications extensibility 5.3 (or the one relevant to your version of MS Access)

Copy the class "clsGetActiveFrm" Into your project.

Copy the form "frmCalendar" Into your project.

And that's it, the calendar form should work with any text box, any combo box, and from any command button, by simply calling the calendar form with the code created by the open form wizard: (you don't even need to know how to write code)

Note:
When the calendar for me is called from a command button it is assumed you have a text box to receive the date named in a similar manner to the command button, for a text box named "txtStartDate" you would need a command button named "btnStartDate"

Private Sub btnStartDate_Click()
    Dim stDocName As String
    stDocName = "frmCalendar"
    DoCmd.OpenForm stDocName
End Sub      'btnStartDate

(your code will look slightly different to the code above as I have removed some unnecessary items generated by the wizard but you can leave these in it won't make any difference)

There is a free calendar form you can download from my web site here,

If you are unclear about anything take advantage of the videos provided, this should help you set the calendar form up, if you still have any problems just Drop me a line!

 

[Zaeed]

gizmo, thats exactly the same as what im doing, except the calendar is already in access, you don't have to download it

 

[CementCarver]

Matt,

That's very kind of you. I think I butchered my copy last night while we were talking. I was at work and stayed late and all I ended up was just getting frustrated with nothing to show for it.

The form I'm trying to get the date to populate is called Mpac_tracking
and you'll see the three date fields. One is called SenttoMpac, ReceivedfromMpac and resolved.

I'm currently looking into taking a VB course at a local college to aid in my learning curve.
View attachment MPAC_TRACK.zip
I've attached a non butchered copy.

Thanks so much Matt......Walt

 

[CementCarver]

Gizmo and Matt,

Thanks gentlemen. Gizmo, I've viewed one of your videos and looked on in interest. But you last post regarding the "Copy the class "clsGetActiveFrm" Into your project" I do not understand.

Matt was nice enough to supply me with a copy of his db and I could basically understand Matt's programming logic, but I just couldn't replicate the same for my db.

I certainly need to investigate this more.

Thanks again guys........

...Walter

 

[Zaeed]

Here you go. I commented out everything you had in your calendar form.

It works now, at least how I think you want it to work.. I've commented the blocks so you know whats going on.