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

 

[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?

 

[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.

 

[CementCarver]

Matt,

I'm just downloading your revisions to my db. I'll take a look at it and see how you managed to transfer the date from the calendar form to the three fields in question.....

Man, it works like a charm.....!! I noticed that you put a submit button on the bottom, rather that the UserSelectDate you had in your db.

Mine seemed so simple compared to your tracking system and I will definitely review your code additionally to learn about sub form, and logging into a db.

Thanks again Matt.... I appreciate this...........Walt

 

[Zaeed]

Not a problem, i put that button there just so that you change the date if you clicked the wrong one. The best way i've found to learn is simply to view other code that does things similar to what you want to do and work out how its being done.
You will pick it up pretty quick.

If you want to read a book on it, i would suggest one of the Microsoft Access for dummies, they are a great source of information, covering everything from form design to advanced VBA coding, i have the access 2003 one and it helped quite a bit, and probably a lot cheaper than doing a course.