Hi -

With so many calendar requests, I'm working on trying to replicate the output of a commercial product (AnyTime Organizer) that apparently uses two subforms to deal with the appointments/events, to-do list, calendar situation.

On the left is a listing of potential appointment times, starting with 6:00 AM thru 10:00 PM in hourly increments. Clicking on one of those opens a popup form with allows you modify the selected time (with a drop-down giving options in 5 minute increments) and enter text describing the appointment event.

I've created a subform that mimics the visual effect. At this point, it's unconnected to any table/query. I'm kind of in a quandry as to how to approach this. Obviously you wouldn't want to store a whole bunch of date/times having no corresponding appointments/events attached. Since the user has the capability to select any date, the table would have the potential to grow entirely out of hand.

Incidentially, on the right side of the form is an undated ToDo list, apparently the 2nd subform, allowing the user to enter tasks. Included in this subform is a 2 month subform calendar (this month/next month) allowing the user to select desired date.

This approach, provided I can get it up and running, would certainly attack the often heard request for a calendar where the user can display appointments/tasks for particular dates.

If anyone has any thought on this, I'd love to hear them.

Best wishes - Bob

I think the only time I've tried something like this I bound the schedule report to a stored query, which I built dynamically before opening the report.

Essentially you only STORE the start and end times/dates, but for the source of the report, you generate a temporary recordsource with all the missing intervening times/dates.

Does that make sense? (sorry if this is off-base)

Mine for telemarketing to make appointments for salesmen is based on two tables.

When the "diary" is opened (from the form with the prospect's details) a tabular form opens. The header is full of unbound textboxes and a SetValue fills those with the prospects details such as phone, address etc. When the diary is closed the mainform has it values SetValued from the unbound text boxes, thus any changes are transferred.

The tablular form has the date on the left field and displayed as full long date as in Friday, 22 August 2008. Across the record there are 7 upper boxes for appt start time and 7 lower boxes for finish time, which I set at +30 mins by defaut but it can be changed.

When the "date" is clicked a combo with times opens. When the time is clicked it is inserted into the first upper box that is null and the default finish time in the lower box. The times then sort "across the row" in ascending time order. Yes, you can sort across the row in Access but it is a "work around"

The tabular form shows 7 days on the screen and commencing with "today". The scroll bar takes care of +7 days and their are 365 records.

Notes that are entered with regards to the appointment are entered in an unbound textbox on the header.

When the diary is exited (and assuming an appt was made) the details are inserted into a new record in a Many table.

When the diary is opened the telemarketer can double click on any box with a time and that will open a related form based on the Many table and show details of the prospect and most importantly the location/suburb of the appointment so as to allow travelling time etc. The telemarketer can also click in a box on the far right and that will bring up a tabular display of all the appt details for that day and once again suburb is the key issue.

There is no universal diary system due to the vastly different circumstances. For example, the telemarketer canvassing for insurance appts is in a fast moving sales situation. However, the recpetionist for a medical specialist is completely different. She has a slow moving non sales situation but needs to cater for appts with both new and existing patients. Thus she needs ease of entering a new record. On the other hand the the telemarketer is never calling an existing client and never needs to enter a new person record. The receptionist for the medical specialist does not need to worry about locality since all the appointments will be in the doctors rooms.

Craig and Mike -

Thank you both for your input!

Craig - I had been thinking along the same lines. Am now trying to implement the temporary recordsource.

Mike - a SetValue fills those with the prospects details such as phone, address etc. When the diary is closed the mainform has it values SetValued from the unbound text boxes, thus any changes are transferred. Would you please post a few lines of code demonstrating how you've used the SetValue action.

Thanks again to both of you - Bob

Bob

Just SetValue macro actions. The macro is in the attached. The form Z is based on the maintable and the form M is on the Many table holding the appt details.

I am always on the lookout for the best way to make diary/appointment makers.

Thanks Mike for your input -

I'm using the following sub to populate Label1, Label3, etc.. thru Label33 with times 06:00 a thru 10:00 p. It works without problem, however I'm just wondering if it could be better handled with SetValue.

Private Sub Form_Load()
Dim dteHold As Date
Dim strHold As String
Dim strLbl As String
Dim i As Integer
Dim n As Integer

dteHold = #6:00:00 AM#
strHold = Left(Format(dteHold, "hh:nn am/pm"), Len(Format(dteHold, "hh:nn am/pm")) - 1)
n = 1
For i = 1 To 17
strLbl = "Label" & n
Me(strLbl).Caption = strHold
n = n + 2
dteHold = DateAdd("h", 1, dteHold)
strHold = Left(Format(dteHold, "hh:nn am/pm"), Len(Format(dteHold, "hh:nn am/pm")) - 1)
Next i
End Sub

Your thoughts appreciated.

Bob

Hi -

With so many calendar requests, I'm working on trying to replicate the output of a commercial product (AnyTime Organizer) that apparently uses two subforms to deal with the appointments/events, to-do list, calendar situation.

On the left is a listing of potential appointment times, starting with 6:00 AM thru 10:00 PM in hourly increments. Clicking on one of those opens a popup form with allows you modify the selected time (with a drop-down giving options in 5 minute increments) and enter text describing the appointment event.

I created someting very similar in my own DB, although it's just a single user db and at the moment and it's too deeply intertwined within my own to extract the relevant parts and post them here I can offer some specific pointers if you like. I did have a very basic cal posted here, that showed who was booked to what on a specific date in a cal type layout it was very basic but it might have given you some ideas, since I'm leaving the forum and have deleted all the examples I ever posted here it no longer exists here, however I still have a copy that you're more than welcome to use how you wish, pm me your EMail and I'll send it to you. Good luck

Bob,

SetValue (or any macro action) is always a one by one situation and as such you can't "set the scene" with For i = 1 To 17 etc.

The plus of the macro is the drop down lists for forms, queries so no spelling problems etc. The other advantage and this why I always use macros where they can do the job is that they are much easier to change over the telephone with a remote user. Also, like a module you can email them in a DB with an export macro whereas code would mean sending forms and that is a problem if the remote user has different form styles.

With macros you can say to the person on the phone.......count down 9 lines and click on etc and etc.

Thanks MIke -

I've always referred to macros as 'the devils workshop' and, as such, the SetValue approach was totally foreign to me.

However, I'm open to improvement, hence the post. With your sample usage, thought I may be missing something.

Thanks for your response.

Bob

Bob,

SetValue would equate to = in code

Forms![2ProspectT]!Label1895.ForeColor = 32768

Forms!PrintAndClose.RecordSource = "GridJoinNamesSingle"

In a macro the right side of the = would be Expression and the left would be Item. The condition column equals If in code.

The analogy I often use is the macro is like a motor mechanic who is restricted to using parts that he can buy whereas code is like the mechanic being able to make his own parts.

DoCmd.OpenForm "ProspectNotes", acNormal, "", "[NameNumber]=[Forms]![PrintandClose]![NameNumber]", acEdit, acNormal

In a macro OpenForm would be the action line, ProspectNotes for form name and the rest for Where.

I guess code goes across the page and macros go down the column. Macros are normalised:D

Hi -

I'm still wrestling with this project. Have run into a snag which I know has a very simple solution but somehow I'm not seeing it.

On the form described in previous posts, I've got a subform (frmCal) with two identical calendars fsubCal1 and fsubCal2 set side by side. The intent is to show the current month (e.g. Sep 08) in the left calendar and the next month (e.g. Oct 08) in the right month.

I have no problem creating modifying/monthly calendars when they are main forms --this is not the issue--, but have gotten seriously 'wrapped around the axle' in how to address these calendars when they are subforms on a subform. My intent is to use the same code, with an adjustment for the month in question, from a subprocedure to modify each of the calendars in turn.

If you could point out the 'error of my ways' with a little sample code, I'd be most appreciative.

Thanks - Bob

I have written an appointment booking module in a VB app I currently maintain. However, I found that I had to invest in some external ocx controls. There are some I don't use, as they come in one big bundle, about 80 in total. But the calander controls are really great. They have been a worthwhile investment and I dont's know how I would get on without them. Here is a link to the site in question.

http://www.dbi-tech.com/product_page_Studio_Controls.asp

I am not in any way connected to this site and do not receive any commision, this is just a helping hand as to acheiving your goals.

David

Hi David -

Thanks for that! There's obviously a wealth of information to be had, although I'm not too keen on external OCXs.

My problem at the moment is to properly phrase this call (snippet follows):

Public Sub GrowCal()

Dim boo5thRow As Boolean
Dim boo6thRow As Boolean
Dim dteDate As Date
Dim dteHold As Date
Dim i As Integer
Dim n As Integer
Dim Int1 As Integer
Dim Int2 As Integer
Dim ctl As Control
Dim IsToday
Dim f As Form

For i = 1 To 2
Set f = Choose(n, Forms!frmCal!fsubCal1.Form, Forms!frmCal!fsubCal2.Form)


...

This is the line that's not working and I'm getting a 'Can't find frmCal...' error.

Bob

is this just a code snippet bob

you are using i in the control loop, but n in the expression, which will fail to select a valid choose option, as it is null

Help also says choose evaluates all parts of the choose expressions.

finally, are the fsubcal1 and fsubcal2 references resolving correctly. In Access Help, the help includes squarebrackets even without spaces
ie Forms![frmCal]![fsubCal1].Form - not sure if thats significant

is one of these that the issue?

otherwise can you set the choose option to a string value of the form name, and then assign the form object, based on the string - to pinpoint what is causing the problem

Hi Gemma -

you are using i in the control loop, but n in the expression, which will fail to select a valid choose option, as it is null

For sure! Have tried so many combinations apparently contaminated that in the process. N is valid later on in the code, but not here.

...In Access Help, the help includes squarebrackets even without spaces ie Forms![frmCal]![fsubCal1].Form - not sure if thats significant

I also tend to think that's not significant, but just to be safe placed square brackets as indicated.

Ok, now when I open frmCal on its own -- not as a sub to frmMain -- it processes past the point I was having trouble, and continues to process until it hangs when attempting to address a field, i.e.:

For n = 1 To 42
Set ctl = Me("txt" & Format(n, "00"))

...returns: "...Access cannot find the field 'txt01' referred to in your expression"

Conversely, when run as a subform to frmMain, it hangs on the
Set f = Choose(i, Forms![frmCal]![fsubCal1].Form, Forms![frmCal]![fsubCal2].Form) line.

You should note that this is tested code which works perfectly when dealing with just one calendar, running by itself and not a sub of a sub. Have reviewed my existing code samples which employ subs (and there are dozens) but just can't find one that approximates this situation.

What to do, what to do?!

Bob

When I refer to open forms and controls within said forms I usually use the following syntax

X = Forms("MainForm")("Control")

or

X = Forms("MainForm")("SubForm")("Control")

However I do not tend to use subforms except in rare circumstances so I cannot 100% confirm the latter syntax.

CodeMaster::cool:

Hi Gemma -



For sure! Have tried so many combinations apparently contaminated that in the process. N is valid later on in the code, but not here.



I also tend to think that's not significant, but just to be safe placed square brackets as indicated.

Ok, now when I open frmCal on its own -- not as a sub to frmMain -- it processes past the point I was having trouble, and continues to process until it hangs when attempting to address a field, i.e.:

For n = 1 To 42
Set ctl = Me("txt" & Format(n, "00"))

...returns: "...Access cannot find the field 'txt01' referred to in your expression"

Conversely, when run as a subform to frmMain, it hangs on the
Set f = Choose(i, Forms![frmCal]![fsubCal1].Form, Forms![frmCal]![fsubCal2].Form) line.

You should note that this is tested code which works perfectly when dealing with just one calendar, running by itself and not a sub of a sub. Have reviewed my existing code samples which employ subs (and there are dozens) but just can't find one that approximates this situation.

What to do, what to do?!

Bob
Isn't it having something to do with the fact that subforms are just another control, usually you have to set focus to the subform first and then the control:confused:

Rich -

I'm playing with that right now. Believe you're correct about setting focus to the control, but I can't find a single example of setting focus to the subform. If you have such a thing, would love to see it.

Thanks - Bob

i see what you mean -i have tried to refer to forms controls before in a general code module, to be able to reuse code, often without getting it work

i tend to put code in the form module now, even though its a bit more work, to avoid these issues

Bob,

This sort of thing it much easier to resolve if you can post a demo…not A2K7 please.

Regards,
Chris.