Re-open embedded subform? 2

[ThinWhiteDude]

Hopefully this will not sound too bizarre to someone. I am using version 2003 and here’s my dilemma:

I am using an embedded subform as a calendar to pick up a date parameter for a report. Once the user has chosen a date, the date button (actually a label) is highlighted with a different backcolor.

I am trying to refresh it for times when no date is chosen or the user wants to clear all the parameters. Basically, what I’d really like is for it to rerun some of it’s open code. This code would then redraw the current month as it looks when the main form (w. subform) is opened.

Although I’ve searched the fora, I didn’t see anything quite like this, but may not have known what keywords to use.

Any help or suggestions will be greatly appreciated.

TWD

 

[mguidry5]

TWD,

Have you considered using the activex calendar object built into Access? I'm not sure which is available in 2003, but I use version 10 with Access XP (I think). I usually use it to populate a bound or unbound text field on my forms.

Also, you said:

I am trying to refresh it for times when no date is chosen or the user wants to clear all the parameters.

I'm not sure what you want to do here - why would you have to refresh a date that hasn't been selected by a user?

Good Luck,
MG

 

[ThinWhiteDude]

MG,

Actually, I have been using the ActiveX controls on all my projects until now. This particular client wants more formatting and customizing than the ActiveX allows, plus the PCs that are running the front end are using various versions of Windows, so I wanted to avoid the problem of making sure all the references are in order (yes, I’m lazy). With that in mind, I have been trying to work with this form, and have been able to get it to do everything I want except this one thing.

Let me try to explain better what is happening and what I need.

When the user opens the main form I have the calendar on it and several comboboxes to choose parameters for their report. The calendar displays the current month and all the day buttons look the same. Should the user decide to narrow the date span down for the report, he/she uses the calendar to select the appropriate year,month,day. The day chosen gets a different background color so that the calendar displays the new year and month and the day they selected is visibly obvious to them. I capture their date chosen in an invisible textbox and use it in the code to produce the report. If they change their mind about using a date, I have a “Clear All” button that clears all comboboxes and the invisible date textbox so they can start all over again. But the calendar still displays the last year/month/day chosen with the day of the month button highlighted. I want it to return it’s display to the current year, month and day (as it does on opening), and don’t know how to get it to do this.

I hope that makes the situation clearer and am sorry if I’ve been obtuse. If I need to clarify more, please let me know.

Thanks for your patience,

TWD

 

[payback]

Move your FormOpen code to a separate subroutine in the form's code e.g. create a Private subroutine called RefreshForm.

On the forms Open event, call that code i.e. Call RefreshForm. Put this same code on a commandbutton.

Understand? If not let me know what you need further help on.

 

[ThinWhiteDude]

Payback,

I believe I understand what you have suggested, so I moved the code into a private subroutine in the subform, and called it on open. Works fine.

How would I then call that routine from the main form? I thought the only way I could do that was to create a public routine. The open code has so many "form specific" control references that I have tried to avoid making it a public routine, however. . .

Would the only solution be to put a command button on the subform, the calendar itself, to call the refresh routine?

or have I misunderstood?

TWD

 

[TheAceMan1]

How are ya ThinWhiteDude . . . .

In you [blue]Clear All[/blue] button add:

[blue]   Call NewRoutineName[/blue]

Note:
[ol][li]A [blue]Private[/blue] routine/function can only be called by other routines in the [blue]same module[/blue]. This is why events are all private, because they have the same name for each form.[/li]
[li]A [blue]Public[/blue] routine/function is [blue]available globally[/blue] throughout the DB.[/li][/ol]

See Ya! . . . . . .

 

[payback]

Silly me!

Is the subform a Form in form view (i.e. not continuous or datasheet)?

Do you have room to put the Refresh button on the subform? You can also refer to controls on the main form from the subform using the Me.Parent!controlname syntax to clear them if necessary.

What controls do you have in the main form i.e. is the main form required at all or can it all be handled as a main form instead of a subform?

Without seeing it, hard to picture but some ideas from the above might help.

 

[ThinWhiteDude]

AceMan. . .
Thank you for clearing up, looks like I would need to turn this into a public routine if necessary.

Payback. . .
The subform is in form view, unbound, just a bunch of labels (in fact, it looks suspicously like the ActiveX control) and comboboxes to choose Month and Year.

I would love to enlarge the subform and put the main form controls on it (that's a GREAT idea!) but I am hoping to put two other calendar subforms on the main form for a date range parameter (to capture start and end dates in the range) if I can get this to work.

It's looking like I will need to put the button to refresh on the calendar subform itself and call the private routine. That will help immensely, however, so I thank you for that suggestion.

With this in mind, I wonder: is there a way to "click" a button on a subform through code on a main form (or is this another public vs private thing?)

Much thanks to you both for the help. I need to leave town for the weekend but I will mull all this over and post back with what I finally do.
TWD

 

[TheAceMan1]

ThinWhiteDude . . . . .

Just another quick note . . .

For public routines/functions in form code modules:
[ol][li]The form has to be open.[/li]
[li]To call the routine/function:

[blue][purple]For routines:[/purple]
   Call Forms!FormName.RoutineName()
[purple]For functions[/purple]
   Something = Forms!FormName.FunctionName()[/blue]

[/li][/ol]

See Ya! . . . . . .

 

[payback]

Something for you to try -

Have the Refresh code in the main form as Private.

Say you have two subforms the same but the name of each in the main form is different e.g. SubForm1 and SubForm2.

Refer to each subform explicitly e.g.

Dim sFormName as string
sFormName = SubForm1
Call RefreshForm(sFormName)

In the RefreshForm subroutine have something like -

Forms!MainFormName(sFormName).Form!Control1Name = etc.

for each control you wish to change.

Note the different syntax where I am not using ! to designate the name of the subform in the main form but am using the index syntax which lets you use a string variable instead. I know I have mixed my conventions but I am working "not live Access" so can not test first, but you will probably get the idea.

 

[ThinWhiteDude]

Payback & AceMan,

After a weekend away from this and thinking of it in the wee hours of the mornings, I came up with a somewhat arcane solution. Tried it this morning, and it works. Here it is:

First, I created a “parking place” for my cursor on the Calendar subform. I discovered this trick earlier last week while searching the fora for a solution to another question. AceMan, you’re a genius to come up with this, it’s come in handy for me many times already. The txtPark is width:0 and height:0 so it’s virtually invisible and nobody can see the cursor.

I called the now Private opening procedure from the OnFocus event of this textbox.

I set the focus on the subform and then on the txtPark control through the clear button on my main form.

Convoluted as it may seem, it works beautifully. I’ll probably make it (txtPark) invisible and unhide it when I run the clear procedure and re-hide it when that’s done to prevent any accidents.

AceMan, I am giving you a star for the whole “Park the Cursor” trick. I wanted to do that when I found it last week but didn’t know if it was appropriate to revive an old thread to award a star!

Payback, your star was for suggesting I put the opening code in the General area where I could call it from almost anywhere, that was a great idea!

I can’t thank you both enough. I have learned so much from monitoring Tek-Tips and everyone has been extremely kind and helpful. I wish I could help others as much as I have been helped, but I fear my technical know-how with Access falls much too short.

Thank you both again,
TWD

 

[payback]

Thanks for the kind words.

I have also learnt a lot from these forums over the last few years although have been developing in Access since 1997.

Only recently (since last year?) have I been replying and assisting - perhaps in the future you might be also. Remember that any posts you make are also being read by others so it could be helping them as well.