By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,482 Members | 2,451 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,482 IT Pros & Developers. It's quick & easy.

MS Access Calendar open form from Day Block

RockKandee
P: 89
I am using the MS Calendar found on this site with Access 2013 on Windows 8.

http://bytes.com/topic/access/answer...ccess-calendar

I am working on adding a form that opens from a day block on the calendar.

When I use the code that is with this calendar

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "MainScheduleEvents", acNormal, , , acFormEdit, acDialog
The form opens when a day block is double clicked.

What I want to do is have the form open to the record where the date of the form matches the day block that is clicked on.

I tried using
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "MainScheduleEvents", , , "Start Date=" & txtDayBlock01.Value
and

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "MainScheduleEvents", _
  2.         WhereCondition:="Start Date=" & Me.txtDayBlock01
Neither of these will open the form at all.

I am guessing the problem is that I don't know what to call the calendar part for this code to work.

Is it possible to have the form open to a record on the corresponding date? If so, how should my code be changed?

Thanks in advance.
Dec 26 '13 #1

✓ answered by ADezii

The actual Date Values for each of the 42 Day Blocks (Text Boxes) in frmCalendar are contained within their respective Tag Properties as LONG INTEGERS. To Open a Form (MainScheduleEvents) based on a Date ([Start Date]) Criteria derived by dbl-clicking on a specific Day (the 7th in this case) the syntax would be:
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtDayBlock07_DblClick(Cancel As Integer)
  2.   DoCmd.OpenForm "MainScheduleEvents", , , "[Start Date] = #" & _
  3.                   CDate(Me![txtDayBlock07].Tag) & "#"
  4. End Sub
  5.  
Another Option would be to create a Private Sub-Routine in the Form's Class Module and pass to it the Date as derived from the dbl-click, as in:
  1. Sub-Routine Definition:
    Expand|Select|Wrap|Line Numbers
    1. Private Sub OpenForm(dteDateCriteria As Date)
    2.   DoCmd.OpenForm "MainScheduleEvents", , , "[Start Date] = #" & _
    3.                   dteDateCriteria & "#"
    4. End Sub
  2. Sample Call from the DblClick() Event of txtDayBlock07:
    Expand|Select|Wrap|Line Numbers
    1. Private Sub txtDayBlock07_DblClick(Cancel As Integer)
    2.   Call OpenForm(CDate(Me![txtDayBlock07].Tag))
    3. End Sub

Share this Question
Share on Google+
10 Replies


ADezii
Expert 5K+
P: 8,597
Is it possible to have the form open to a record on the corresponding date? If so, how should my code be changed?
Very possible, will return and show you how shortly.
Dec 26 '13 #2

ADezii
Expert 5K+
P: 8,597
The actual Date Values for each of the 42 Day Blocks (Text Boxes) in frmCalendar are contained within their respective Tag Properties as LONG INTEGERS. To Open a Form (MainScheduleEvents) based on a Date ([Start Date]) Criteria derived by dbl-clicking on a specific Day (the 7th in this case) the syntax would be:
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtDayBlock07_DblClick(Cancel As Integer)
  2.   DoCmd.OpenForm "MainScheduleEvents", , , "[Start Date] = #" & _
  3.                   CDate(Me![txtDayBlock07].Tag) & "#"
  4. End Sub
  5.  
Another Option would be to create a Private Sub-Routine in the Form's Class Module and pass to it the Date as derived from the dbl-click, as in:
  1. Sub-Routine Definition:
    Expand|Select|Wrap|Line Numbers
    1. Private Sub OpenForm(dteDateCriteria As Date)
    2.   DoCmd.OpenForm "MainScheduleEvents", , , "[Start Date] = #" & _
    3.                   dteDateCriteria & "#"
    4. End Sub
  2. Sample Call from the DblClick() Event of txtDayBlock07:
    Expand|Select|Wrap|Line Numbers
    1. Private Sub txtDayBlock07_DblClick(Cancel As Integer)
    2.   Call OpenForm(CDate(Me![txtDayBlock07].Tag))
    3. End Sub
Dec 27 '13 #3

RockKandee
P: 89
I have a HUGE smile on my face right now :D

THANK YOU!!!!

I am not sure what the forms class module is, so....

I used option 1 because I understood it.

Works perfect - Thank you so much.

Big kisses to you.
Dec 27 '13 #4

NeoPa
Expert Mod 15k+
P: 31,186
Kandee
"Big kisses to you."

I'm so hoping, for ADezii's sake, that you're a girl! J/K. With that name and that picture I was fairly confident of that a while back :-D

Let me just explain that "The form's class module" is simply another way of saying the area where code is stored for the event procedures for the form and all its objects. So, the same place where you would find the code in option #1.

The benefit of the second option, and I would really recommend using that if you possibly can, is that the functionality is encapsulated in a single subroutine procedure, rather than being duplicated across many instances (one for each day block). If, at any point, you need to change the precise logic of what you're doing, having it in one place rather than in every related event procedure will make your life a lot easier.

To encapsulate it even further :
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtDayBlock07_DblClick(Cancel As Integer)
  2.     Call OpenForm(Me.txtDayBlock07)
  3. End Sub
Expand|Select|Wrap|Line Numbers
  1. Private Sub OpenForm(txtVar As TextBox)
  2.     Call DoCmd.OpenForm(FormName:="MainScheduleEvents" _
  3.                       , WhereCondition:="[Start Date] = #" _
  4.                                       & Format(txtVar.Tag, "m\/d\/yyyy\#"))
  5. End Sub
Dec 27 '13 #5

ADezii
Expert 5K+
P: 8,597
@NeoPa:
Do I detect a little jealousy here, since you didn't get any 'Big Kisses'? (LOL).

P.S.-Just out of curiosity, why are you passing a Text Box Control to the Procedure as opposed to a pre-formatted Date Value? I would think that it would be advantageous for the Procedure to receive an actual Date Criteria as opposed to formatting it within the Procedure.
Dec 27 '13 #6

RockKandee
P: 89
YES - Those were wet, sloppy kisses from a girl - LOL

I get that the class module is on the same page as the other code, just not sure where on the page. Does it matter?

I would agree that saying it once so it would only have to change once (if needed) is a good idea. The great thing is that as I understand things better, I have this thread to come back to for reference, so I can improve things.

You guys rock!
Dec 27 '13 #7

NeoPa
Expert Mod 15k+
P: 31,186
ADezii
"Do I detect a little jealousy here, since you didn't get any 'Big Kisses'?"
Yes! Knowing I rock with you is certainly mollification though :-D

ADezii
"Just out of curiosity, why are you passing a Text Box Control to the Procedure as opposed to a pre-formatted Date Value?"
Because that is the simplest approach. The date value in your example is not preformatted - it is simply pre-Typed. This has very little effect on the ultimate result except to define the default format to use if a format isn't specified. As it is always sensible to specify a format for dates in SQL (See Literal DateTimes and Their Delimiters (#)), to ensure the project is portable and will work outside of the US of A, the formatting is done once in the OpenForm() procedure, rather than complicating each and every call to that procedure.

Essentially there are two issues here, and I feel my explanation may have confused them together somewhat, so I'll specify them separately.
  1. The more that is done (once) within the procedure, the less need be done in the calling code which occurs many times.
  2. The date in a SQL string should always be formatted in SQL format (which happens, by no accident of course, to be the same as that for the USA) because only in the USA is it right by default. If it's formatted anyway it needn't be cast into a date Type first.

I hope that explains why my code is different from yours. It is not my intention to criticise your code. As Kandee has already indicated, it works and is a godsend to her and many others.

RockKandee
"I get that the class module is on the same page as the other code, just not sure where on the page. Does it matter?"
Technically no. It can be anywhere within the module. I prefer to bring some order to where it is found by putting it after all calls to it from within the module, but that's purely to help me find it later rather than any limitation imposed by VBA.
Dec 27 '13 #8

RockKandee
P: 89
I am now trying to open a form with a matching subform date.

Tried this
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "Forms![MainScheduleCourses]![SubScheduleCourses]", , , "[SessionDate] = #" & _
  2.                   CDate(Me![txtDayBlock05].Tag) & "#"
Didn't work.

The calendar is fed from a query using Start Date but Start Date has no bound table. I used Start Date:SessionDate in the query that feeds the calendar. Because I couldn't figure out how to change the calendar to accept SessionDate.

Now I have Session date in a subform and would like the main form to open when the SessionDate in the subform matches the day block of the calendar.

Is this possible?

Thanks
Jan 2 '14 #9

zmbd
Expert Mod 5K+
P: 5,287
RK: How is this related to the original question?
New question, new thread. You can always include a link back to this thread for context (^_^)
Jan 2 '14 #10

RockKandee
P: 89
What I want to do is have the form open to the record where the date of the form matches the day block that is clicked on.
That was my original question ^^^

My new question...

would like the main form to open when the SessionDate in the subform matches the day block of the calendar.
How to open form from calendar with matching dates, would be how it is related.

I am off to ask a new question....
Jan 2 '14 #11

Post your reply

Sign in to post your reply or Sign up for a free account.