Using unbound Mainform data as criteria in Subform

[Ian Chappel]

I have a Mainform containing just a MonthView control. When I click a single
date, I would like a subform to display only records matching this date.

What is the simplest/best way to do this? I can do it fine using code to
assemble the SQL string as the subform's recordsource, but it _should_ work
(shouldn't it?) by setting the date field's criteria in the subform's
recordsource to Forms!Mainform!ctrlDate. I have tried this by basing the
form on a query, and entering the query directly as the form's Data source -
not sure if this makes a difference???

In fact it works fine _but_ only after clicking the MonthView control a
couple of times, but it doesn't display the data on loading or even
selecting another date - it does the next time (and thereafter) though!

I think I'm in a muddle with my combination of correct Events on the correct
Forms and the correct Recalc/Refresh/Requery method. With enough trial and
error I could probably fix it, but I'd rather someone could explain exactly
what is needed (and why) in this sort of situation!

Thanks.

 

[Nikos Yannacopoulos]

Ian,

The fact that you entered the SQL expression in the form's Record Source
property, instead of using a saved query, is immaterial; it should work
just fine regardless. Your problem is most likely an events issue, as
you guessed; if the MonthView control is unbound - and I guess it is,
then the Update event does not fire (because there's nothing to
update?); for that matter, none of the very few events provided in form
design / properties will do the job. The trick is to use the Update
event just to get to the VBA window, from where you can select the
SelChange event from the drop-down list in the top RHS of the screen,
and put your Requery code there.
You might have to use the form's Open or Load event to also do a Requery.

HTH,
Nikos

 

[Ian Chappel]

Thanks Nikos,

Got it sorted now. In fact the problem was more to do with me setting the
MonthView's control to =Now() rather than =(Date) on the form's load event.
I always get confused in VBA when I can't find a =today() function as in
Excel! So now amount of tinkering with events and methods would sort it.

 

[Nikos Yannacopoulos]

Ian,

Another thing to note, when working with ActiveX controls, is to use the
Load event (like you did) to assign values; if you use the Open event it
won't work, it's too early - I believe the control itself needs
sometime to load, which also happens during the Open event.

You may have already known this, but someone else out there may have
been banging their head against the wall, like I did before I found out!

Regards,
Nikos

 

[Ian Chappel]

Not used ActiveX controls much - I'll take note of your tip!