Leban's Calendar in a subForm

[Lorenzo]

Hello there,
I am getting stuck with somenthing I had previously worked-around...yes
shame on me!
I am trying to pop up Stephen's calendar from 2 subForms (..one at a time,
clearly). The main form containing the 2 subForms has itself one date field
that opens the calendar and that works fine but when it comes to open it
form the subForms...I get a ref error. What am I doing wrong? Should I
change the private mc to different names?


Ciao,
Lorenzo

 

[Jeff Conrad]

in message:

Hello there,
I am getting stuck with something I had previously worked-around...yes
shame on me!
I am trying to pop up Stephen's calendar from 2 subForms (..one at a time,
clearly). The main form containing the 2 subForms has itself one date field
that opens the calendar and that works fine but when it comes to open it
form the subForms...I get a ref error. What am I doing wrong? Should I
change the private mc to different names?

Hi Lorenzo,

I cannot reproduce the problems you are having. I have not used Stephen's
calendar before under the same scenario as you described so I set up a
test database. I followed your example with a main form (and date control)
and two subforms each having their own date controls. I even used the
*exact* same field and control names across all three areas just to see if
that might present a problem. I had no problems whatsoever. The calendar
popped up just fine for each control and they worked independently. No
error messages came up either.

I was using Stephen's latest version for this test. You might want to download
and use the latest version files as that could be an issue, I'm not sure. After
downloading and importing, check your References to see if everything is OK.
Then Compile your database and re-test.

If you are still having problems, please provide us with your Access and
Windows versions. Also, please tell us on what area the error occurs. On
the main form, sub form, etc. If it only happens on one form or subform,
please post the code you have in *that* form's Load and Unload events,
as well as the date control's code to call up the calendar.

 

[Lorenzo]

Hello Jeff,
first of all I really want to thank you for your nice reply. I am also
happy that a new scenario has been brought up to and for the community.
I have found out why the Calendar wasn't working. In my database I had the
following situation: in the main Form containing the two subForms, the
calendar was popping properly, but not in the two subForms due to the fact
that I had on the mainForm two buttons for hiding or showing the two
subForms. Let me explain better, the mainForm had a Form Load event that by
default was setting the 2 subForms visibility to false. The two sets of
buttons "show" and "hide" served to show or to hide the two subForms each
one of them containing one date control calling a Calendar's instance.

Well the fact is that with this scenario the instance called from the
subForms resulted invalid. You might want to try it yourself in your
testing DB to see if there is a way to make it work like this.

I have tried setting the subFormMySubForm visibility set to true one more
time right after the dteMyDateControl_dblClickEvent that calls the Calendar
from the subForm but that really didn't help...

I believe this is something beyond my knowledge so I leave it up to you and
I really hope you can give me some hints on how to work it around. Having
the opportunity to hide and show the subForms would come in handy so ...
thank you in advance.

Do you mind if I bug you a little more with another question?
My application has a reservation table called let say "tblReservation" where
I have dteArrival and dteDeparture date values. I have a function that
loops through records to check weather an apartment is booked or not. I was
thinking of how would be great to display the booked dates on the Calendar
with a background different color. The idea is the same as the today' s
circle Yes/No built in functionality that Stephen wrote for the Calendar.

Maybe this is something too complicated too achieve for me so don't worry if
this is not possible.

Ciao da Firenze,
Lorenzo

 

[Jeff Conrad]

in message:

Hello Jeff,
first of all I really want to thank you for your nice reply.

You're welcome, it is my pleasure to help.

I am also happy that a new scenario has been brought up to and for the community.
I have found out why the Calendar wasn't working. In my database I had the
following situation: in the main Form containing the two subForms, the
calendar was popping properly, but not in the two subForms due to the fact
that I had on the mainForm two buttons for hiding or showing the two
subForms. Let me explain better, the mainForm had a Form Load event that by
default was setting the 2 subForms visibility to false. The two sets of
buttons "show" and "hide" served to show or to hide the two subForms each
one of them containing one date control calling a Calendar's instance.

Well the fact is that with this scenario the instance called from the
subForms resulted invalid. You might want to try it yourself in your
testing DB to see if there is a way to make it work like this.

Interesting.
My test scenario was not exactly like that, but I will certainly do some
testing this weekend to see if I can reproduce the problem.

I have tried setting the subFormMySubForm visibility set to true one more
time right after the dteMyDateControl_dblClickEvent that calls the Calendar
from the subForm but that really didn't help...

I believe this is something beyond my knowledge so I leave it up to you and
I really hope you can give me some hints on how to work it around. Having
the opportunity to hide and show the subForms would come in handy so ...
thank you in advance.

Let me first see if I can reproduce the problem and then I will see what
kind of options we can come up with.

Do you mind if I bug you a little more with another question?

Not at all.

My application has a reservation table called let say "tblReservation" where
I have dteArrival and dteDeparture date values. I have a function that
loops through records to check weather an apartment is booked or not. I was
thinking of how would be great to display the booked dates on the Calendar
with a background different color. The idea is the same as the today' s
circle Yes/No built in functionality that Stephen wrote for the Calendar.

Maybe this is something too complicated too achieve for me so don't worry if
this is not possible.

Humm...my gut tells me this is probably not possible using Stephen's calendar,
but "cannot do" are not in Stephen's vocabulary so I will leave that particular
question in his capable hands.


You might consider a form-based calendar to achive your results of displaying
the booked times. Here are some samples that may help with this area:

http://www.weascend.com/Downloads/downloadpage.htm

(Several good samples there that may help)

http://www.olaz.net/descargas/access/CalendarioAnual.zip

http://www.freeaccess.de/downloaddetails.asp?id=2

http://www.pointltd.com/Downloads/Details.asp?dlID=32

http://www.viescas.com/Info/links.htm#Downloads

http://www.aa-sistemas.eti.br/ayudadeaccess/archivos/actcall97.zip

http://www.aa-sistemas.eti.br/ayudadeaccess/archivos/actcall2k.zip

http://wolfwares.com/Access/microsoft_access.htm

Hope that gives you some ideas,

 

[Stephen Lebans]

The MonthCalendar does support the DayState property but it is certainly
not an easy property to use.

In the Form's Load event or anytime before you open the MonthCalendar
use code like:


mc.SetBoldDayState numYear:=2004, numMonth:=1, day:=1, ResetMonth:=True
This BOLDS the date of January 1,2004


It also RESETS any existing BoldDayStates for this Month to NOTHING. In
other words ResetMonth means to clear out all existing Bold DayStates
for this specific month. If you are issuing multiple calls to set this
property then you would only set the optional ResetMonth parameter to
TRUE for the first call!

--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.

 

[Lorenzo]

Hello Stephen,
thanks a lot for your prompt reply. It is so good to see you are so willing
to help novices like me with your Cal !
How is doing your coaching ? I am sure all your pupils must be pretty lucky
to have such a good lead !

Ok let me get this clear since I have just started ( less than 6 months)
learning and at the same time developing my first programming skills
sometimes things are not quite clear to me as they are to an experienced
developer.

Let's assume that in my testing DB I have the following function that check
departure dates ( I have another one that checks arrival dates as well):
The function checks two things, if the start and end dates have been already
taken , and also if a period has been already taken.



Private Function CheckDeparture()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim dteRequest As Date
Dim dteRequest1 As Date
Dim dteStart As Date ' Declare vairiables
Dim dteEnd As Date
Dim strSQL As String
Dim intSelection As Integer


intSelection = Me![cboApt] ' this is the apartment that
gets selected in the form

dteRequest1 = dteDataArrivo.Value ' it will be used to check the
period if is taken or not
dteRequest = dteDataPartenza.Text ' used to check departure dates


strSQL = "SELECT [dteDataArrivo], [dteDataPartenza] FROM qryPrenotazioni
" ' string SQL
strSQL = strSQL & "WHERE [IDApt] = " & intSelection

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot) '
apertura tabella

rs.MoveFirst

Do While True
With rs
dteStart = rs!dteDataArrivo 'checks the
values in the table
dteEnd = rs!dteDataPartenza

If (dteRequest > dteStart) And (dteRequest <= dteEnd - 1) Then
msgBox " La data: " & dteRequest _
' dteEnd -1 is used to clear a departure day when the room IS available
& vbCrLf & "per l'appartamento " & cboApt.Column(1) & _
" NON è disponibile "
DoCmd.CancelEvent
Exit Function
End If
If (dteStart > dteRequest1) And ((dteEnd < dteRequest) And
(dteRequest > dteStart)) Then ' This checks if a period is booked
msgBox "Il seguente periodo è già stato prenotato dal " & _
vbCrLf & dteStart & " al " & dteEnd
DoCmd.CancelEvent
Exit Function
End If
Exit Function
End With
rs.MoveNext
Loop

End Function

Where should I start planning insert the BoldDayState? Is there a backColor
property that I could control with a loop?
For example I was thinking to the following option ...

for each
date present in the reservation table '
(dteRequest > dteStart) & (dteRequest <= dteEnd )
color the background of the day with a color ' ... ???
next


Please Stephen maybe this is just pure delirium but be patient I am doing my
best to learn...! I really don't want to get you mad on a weekend !!!
Lorenzo
Firenze-Italia

 

[Lorenzo]

Hello Jeff,
thanks again for your kind reply. I have also received a reply from
"Sensei" Stephen in person which was actually an honor !
Please let me know at your convenience if you solve the "hide" & "show"
matter for the calendar. If by remote chance I personally do it, I will
drop the solution to this thread.

Happy Sunday,
Lorenzo

P.S. thanks for the resources.

 

[Stephen Lebans]

You are correct. As you stated:
for each
date present in the reservation table '
(dteRequest > dteStart) & (dteRequest <= dteEnd )
color the background of the day with a color ' ...
???
next


Before you make your call to the SHowMonthCalendar function you would
follow the logic you outlined. Use the DatePart method to seperate out
the desired dates into their component values. Then set the DayState
prop as I indicated previously.
Assuming you use 3 variables named iYear, iMonth, and iDay
mc.SetBoldDayState numYear:= iYear, numMonth:=iMonth, day:=iDay,
ResetMonth:=False

Hmm, in order to clear our any previous DayState settings you would need
to set ResetMonth:=TRUE for the very first date you set in the main
loop.



--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.

Hello Stephen,
thanks a lot for your prompt reply. It is so good to see you are so willing
to help novices like me with your Cal !
How is doing your coaching ? I am sure all your pupils must be pretty lucky
to have such a good lead !

Ok let me get this clear since I have just started ( less than 6 months)
learning and at the same time developing my first programming skills
sometimes things are not quite clear to me as they are to an experienced
developer.

Let's assume that in my testing DB I have the following function that check
departure dates ( I have another one that checks arrival dates as well):
The function checks two things, if the start and end dates have been already
taken , and also if a period has been already taken.



Private Function CheckDeparture()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim dteRequest As Date
Dim dteRequest1 As Date
Dim dteStart As Date ' Declare vairiables
Dim dteEnd As Date
Dim strSQL As String
Dim intSelection As Integer


intSelection = Me![cboApt] ' this is the apartment that
gets selected in the form

dteRequest1 = dteDataArrivo.Value ' it will be used to check the
period if is taken or not
dteRequest = dteDataPartenza.Text ' used to check departure dates


strSQL = "SELECT [dteDataArrivo], [dteDataPartenza] FROM qryPrenotazioni
" ' string SQL
strSQL = strSQL & "WHERE [IDApt] = " & intSelection

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot) '
apertura tabella

rs.MoveFirst

Do While True
With rs
dteStart = rs!dteDataArrivo 'checks the
values in the table
dteEnd = rs!dteDataPartenza

If (dteRequest > dteStart) And (dteRequest <= dteEnd - 1) Then
msgBox " La data: " & dteRequest _
' dteEnd -1 is used to clear a departure day when the room IS available
& vbCrLf & "per l'appartamento " & cboApt.Column(1) & _
" NON è disponibile "
DoCmd.CancelEvent
Exit Function
End If
If (dteStart > dteRequest1) And ((dteEnd < dteRequest) And
(dteRequest > dteStart)) Then ' This checks if a period is booked
msgBox "Il seguente periodo è già stato prenotato dal " & _
vbCrLf & dteStart & " al " & dteEnd
DoCmd.CancelEvent
Exit Function
End If
Exit Function
End With
rs.MoveNext
Loop

End Function

Where should I start planning insert the BoldDayState? Is there a backColor
property that I could control with a loop?
For example I was thinking to the following option ...

for each
date present in the reservation table '
(dteRequest > dteStart) & (dteRequest <= dteEnd )
color the background of the day with a color ' ... ???
next


Please Stephen maybe this is just pure delirium but be patient I am doing my
best to learn...! I really don't want to get you mad on a weekend !!!
Lorenzo
Firenze-Italia


"Stephen Lebans"

 

[Jeff Conrad]

in message:

Hello Jeff,
thanks again for your kind reply. I have also received a reply from
"Sensei" Stephen in person which was actually an honor !
Please let me know at your convenience if you solve the "hide" & "show"
matter for the calendar. If by remote chance I personally do it, I will
drop the solution to this thread.

Hi Lorenzo,

Well I have been trying like crazy to reproduce this problem, and
all of my attempts have failed. I believe my test database exactly
matches your specifics. I have two subforms on a main form, each
with it's own calendar field to pop up Stephen's calendar. In the form
load event I set the visibility of each to false. I have two buttons to
show and hide the subforms just as you do. No matter how many
times I have tried this, even on different machines, I cannot seem
to reproduce the error you are seeing. The calendar pops up with
the correct date for each field and no error messages appear.

I'm not sure if this is good news or bad news for you.


Have you considered instead of hiding the subforms just to lock
them? Perhaps setting their Enabled and Locked properties to
true?

I would be curious to know if this has any effect on the error.
There could also be some code elsewhere that is interfering
with the calendars, but since I am unable to reproduce the
error I can only guess. Do you have any Timer events running
in the background?

P.S. thanks for the resources.

You're welcome.

 

[Lorenzo]

Hello dear Jeff,
what to say...my database acted a little weird lately so I assume that all
the coding I have stuffed within the mainReservationForm
isn't helping finding the bugs. This is what I will do: clean up the
mainForm as well as the subForms and use as little code I need to achieve
what I want.
This mainForm holding the 2 subForms was getting a little too loaded with
automatisms. I think that restarting from scratch these Forms will help
debugging the errors.

I have something very important to ask you though that maybe can clear out
even more any mistakes at design-level I might have done and that are
causing the app to brake down and act funny. The application so far has
worked as planned but I am not sure if I have done things correctly.
I really need to understand if I have made some planning mistakes that will
bring the application in the future to don't work properly.

I said the app started behaving funny because beside the problem with the
Calendar, for example when I try to delete a record in the
mainReservationForm that HAS correlated fields in the two subForms Access
prompts its message to confirm the deletion (but without warning me that
there are correlated fields that will be deleted as well, which is what I
would expect), I confirm the deletion and when I open the
mainFormReservation again the record I deleted is still there... ??? If
I delete the same record from the table (tblReservation) source of the query
that feeds the mainFormReservation no problems at all...

To make it easier to understand let me explain what I have.

tblReservation 'is the spinal bone of the entier appl
IDReservation, intReservationNumber, dteReservation, ....., IDClient,
IDApartment, IDAgency ...so the reservation has a Client, Apartment, etc.

tblInvoices ' the table for invoices is very crucial since a Reservation
can have multiple invoices: one for deposit, one for balance...
IDInvoice, intInvoiceNumber, dteInvoiceDate, curAmount,.....IDReservation *

* !!! IMPORTANT !!! I include IDReservation here in tblInvoices so I can
have multiple Invoices for that Reservation but, is this correct?, is this
the right way to do it? In the relationships I have applied the Referential
Integrity plus checked the two options Cascade update and delete Related
Fields between tblReservation and tblInvoices. This because I want to
update/delete the invoice/invoices whenever I delete that Reservation.
The type of JOIN I have used is the OUTER since I can really select all the
records from both tables.



tblCleanings ' This is another crucial table in my appl
IDCleaning, intCleaningNumber, dteDateCleaning, ....., IDReservation

Here again I need to have multiple cleaning services for that one
Reservation but here for example I might also have cleanings not related to
any Reservation maybe just because a cleaning can be just for maintenance/
other. In fact in my app, from this table, has another Form independent
from this mainReservationForm to add cleaning services. To achieve this
result I have applied again the Referential Integrity and the Cascade update
and delete Related Fields but the type of Join here is a RIGHT-OUTER JOIN so
I can still have cleaning services not related to any Reservation but I can
still delete those cleaning services correlated to a Reservation. We may
apply the same rule to the above case for invoices, I might have in the
future to relate an invoice to other then Reservations, correct?


Ok the mainReservationForm is generated by the following:
qryReservation that is based on tblReservation plus the join with tblClients
and other tables such as tblApartments, tblAgencies...etc. From those
tables I only retrieve the values I need to address a client, an apartment,
an Agency to that reservation.

The first subForm is generated by tblInvoices. Here I can add/delete as
many invoices for that IDReservation.
The second subForm generated by tblCleanings and functions same as above.

On a testing database this works fine but I ask you if this is formally
correct or there is another method to achieve this.
With this scenario will I encounter problems or it is correct and I should
keep working on like this? If this is the case then I will just clean up
the mainReservationForm and keep it simple so that I will eradicate the
possible bugs and I am sure I will not encounter the errors for the Calendar
and the show/hide buttons....

If I am doing something completely wrong please point it out to me so I can
start working in the right direction. I am thinking of many-to-many
relationships ... Am I in one of those cases? Do I need to create a
"Junction Table". If I do please let me know where to find tutorials on
this. I am not too familiar with it.

What to say Jeff, if you have got so far reading my long, long call for help
you are a Saint now...!

Lorenzo
Do you plan to come Florence ???

 

[Jeff Conrad]

in message:

Hi Lorenzo,
Comments in-line.....

Hello dear Jeff,
what to say...my database acted a little weird lately so I assume that all
the coding I have stuffed within the mainReservationForm
isn't helping finding the bugs. This is what I will do: clean up the
mainForm as well as the subForms and use as little code I need to achieve
what I want.
This mainForm holding the 2 subForms was getting a little too loaded with
automatisms. I think that restarting from scratch these Forms will help
debugging the errors.

That may help the issue. If you have done a lot of design work in this database
it could be having some residual "stuff" left over.

I have something very important to ask you though that maybe can clear out
even more any mistakes at design-level I might have done and that are
causing the app to brake down and act funny. The application so far has
worked as planned but I am not sure if I have done things correctly.
I really need to understand if I have made some planning mistakes that will
bring the application in the future to don't work properly.

I said the app started behaving funny because beside the problem with the
Calendar, for example when I try to delete a record in the
mainReservationForm that HAS correlated fields in the two subForms Access
prompts its message to confirm the deletion (but without warning me that
there are correlated fields that will be deleted as well, which is what I
would expect), I confirm the deletion and when I open the
mainFormReservation again the record I deleted is still there... ??? If
I delete the same record from the table (tblReservation) source of the query
that feeds the mainFormReservation no problems at all...

Sound odd.
You might consider doing a Decompile on this database along with importing
everything into a new database container. Make sure you make some backups.

Here is some information provided by MVP Allen Browne on decompile:The Decompile switch was undocumented in earlier versions of Access, but has
been documented for 3 versions now.

Decompiling may not give the same results as importing into a new database.

In an mdb file, there are 2 copies of the code:
- a text version (what you view and edit);
- a compiled version (what actually runs).

If the database corrupts, these 2 may differ.
Decompiling discards the compiled version.
It then recreates the compiled code from the text version.
The inconsistency is then gone.

If you import from one mdb into a new file, using the same version, Access
may import both the text version and the compiled version exactly as they
were. If the two copies were inconsistent, the new version can therefore
still have the inconsistency. This is not common, but it does happen.

So, the safest procedure would be:
1. Decompile the old mdb.
2. Compact it to get rid of the space used by temp objects and the compiled
code.
3. Import into a new (blank) database. At this point you are importing only
the text version as the compiled version has been discarded.
4. Set your minimal references, and compile.

To make it easier to understand let me explain what I have.

tblReservation 'is the spinal bone of the entier appl
IDReservation, intReservationNumber, dteReservation, ....., IDClient,
IDApartment, IDAgency ...so the reservation has a Client, Apartment, etc.

tblInvoices ' the table for invoices is very crucial since a Reservation
can have multiple invoices: one for deposit, one for balance...
IDInvoice, intInvoiceNumber, dteInvoiceDate, curAmount,.....IDReservation *

* !!! IMPORTANT !!! I include IDReservation here in tblInvoices so I can
have multiple Invoices for that Reservation but, is this correct?, is this
the right way to do it? In the relationships I have applied the Referential
Integrity plus checked the two options Cascade update and delete Related
Fields between tblReservation and tblInvoices. This because I want to
update/delete the invoice/invoices whenever I delete that Reservation.
The type of JOIN I have used is the OUTER since I can really select all the
records from both tables.



tblCleanings ' This is another crucial table in my appl
IDCleaning, intCleaningNumber, dteDateCleaning, ....., IDReservation

Here again I need to have multiple cleaning services for that one
Reservation but here for example I might also have cleanings not related to
any Reservation maybe just because a cleaning can be just for maintenance/
other. In fact in my app, from this table, has another Form independent
from this mainReservationForm to add cleaning services. To achieve this
result I have applied again the Referential Integrity and the Cascade update
and delete Related Fields but the type of Join here is a RIGHT-OUTER JOIN so
I can still have cleaning services not related to any Reservation but I can
still delete those cleaning services correlated to a Reservation. We may
apply the same rule to the above case for invoices, I might have in the
future to relate an invoice to other then Reservations, correct?


Ok the mainReservationForm is generated by the following:
qryReservation that is based on tblReservation plus the join with tblClients
and other tables such as tblApartments, tblAgencies...etc. From those
tables I only retrieve the values I need to address a client, an apartment,
an Agency to that reservation.

The first subForm is generated by tblInvoices. Here I can add/delete as
many invoices for that IDReservation.
The second subForm generated by tblCleanings and functions same as above.

On a testing database this works fine but I ask you if this is formally
correct or there is another method to achieve this.
With this scenario will I encounter problems or it is correct and I should
keep working on like this? If this is the case then I will just clean up
the mainReservationForm and keep it simple so that I will eradicate the
possible bugs and I am sure I will not encounter the errors for the Calendar
and the show/hide buttons....

If I am doing something completely wrong please point it out to me so I can
start working in the right direction. I am thinking of many-to-many
relationships ... Am I in one of those cases? Do I need to create a
"Junction Table". If I do please let me know where to find tutorials on
this. I am not too familiar with it.

I would suggest Lorenzo that you re-post this particular question in the
tablesdbdesign group. A discussion of table design and setup is quite
a bit off-topic from the initial issue of this thread. Just re-post what you
have here with a suitable Subject line of "Need some comments on
current table setup" (or something similar) into that group. That way
you can be sure to receive many more comments on this area than just
from me, OK?

What to say Jeff, if you have got so far reading my long, long call for help
you are a Saint now...!

Thank you for the kind words, but I would not put myself in that category.

Lorenzo
Do you plan to come Florence ???

Florence, Oregon on the coast?
Sure, we go there once in a while.


Oh, you meant the "other" Florence.
Probably not anytime soon I'm afraid, but it would be nice to see your
beautiful city.