Refreshing a subform

[RobRoy]

Main form lets users navigate through a table. Subform has a Combobox where the list of possible values is built by a query (limits list based on valid from / to date fields) that references a value (date) in the main form. Simple problem was Access only builds the list for the Combobox in the subform as the main form is loaded.

I thought I had this nailed, add a Refresh to the OnCurrent event of the subform. This works but caused all hell to break loose in both Access 2000 and 2010. It's taken me quite a few hours to discover this was the cause of problems printing forms . What would happen when printing a form that took a value from the previously mentioned main form was the print action would cause THAT form to be printed, potentially 100,000s of pages!



Now reading through a few posts here, studying the informative information in Wikis etc. I've now found I cannot force a Refresh of the subform using the OnCurrent event of the main form. I can force a Requery but that seemingly doesn't trigger the Combobox on the subform to rerun its Query.

One approach I'm now contemplating is to expand the query that lies behind the main form however I'm not exactly confident about how to link a table so it's filtered by a value in another able nor even if when I get that to work if it'll solve the problem. Any thoughts or advice much appreciated. I'm also thinking the printing nightmare I managed to get myself into is quite a trap that others need to be aware of.

[June7]

Why are you printing form? Forms are not intended for printing, reports are.

Try Requery the combobox specifically.

Me.comboboxname.Requery

[RobRoy]

Why are you printing form? Forms are not intended for printing, reports are.

Forms can be printed. These are single page documents that contain several subforms that pull data from related tables into lists on the main form.
A typical use for such a technique is printing an invoice.

Try Requery the combobox specifically.

Me.comboboxname.Requery

Tried that, it achieves the desired outcome the same as using Refresh.
It also causes the same problem with printing a form that references the form that isn't being printed and isn't the object that has focus.
Of some note here is when a form is used as a subform almost all events are not available. There does seem to be an issue here that Access will
let a user add OnEvent actions to a form and execute them even when that form becomes a subform. A search of the web reveals a number of
posts about "Access prints the wrong form".

[June7]

Forms can be printed but they are not ideal for that purpose, reports are.

The form or report RecordSource must be filtered to desired records else they will print everything.

I do print some forms but none involve subforms and they are filtered to a single record.

Instead of printing the form, have code print a report that has the same appearance.

I don't recall having a subform combobox RowSource dependent on value in main form. I am have difficulty visualizing your issue. If you want to provide db for analysis, follow instructions at bottom of my post.

[RobRoy]

First off I should perhaps point out that this database has been in use for over a decade at nearly 100 sites.

Forms can be printed but they are not ideal for that purpose, reports are.

The forms being printed have to exactly mimic paper forms supplied by the Government Printer.
In several parts of just about all the forms there's ruled spaces for lists.

The form or report RecordSource must be filtered to desired records else they will print everything.

I think you're confusing two issues here. One problem was Access managing to print the wrong form thanks it seems to me using the
OnCurrent event in a subform. The form Access should have printed was filtered to only one record, the wrong form that Access
printed wasn't.

The second issue is how to filter what populates the dropdown selection in a Combobox in a subform

Instead of printing the form, have code print a report that has the same appearance.

That's a task that'd involved 100s of hours of work, I doubt my client will pay for that much of my time simply to
make the application a bit more elegant.

I don't recall having a subform combobox RowSource dependent on value in main form.

That's a pretty common requirement. Imagine you sell gender specific services. You main Invoice form would
from your client table know the client's gender. A subform of the main form that you select the services provided
could have a Combobox for them and it would be desirable to filter the Combobox's Rowsource to only services for the client's gender.
For example males don't get bikini waxes and females don't get their beards trimmed.

I am have difficulty visualizing your issue. If you want to provide db for analysis, follow instructions at bottom of my post.

Unfortunately I develop and maintain these under contract but if it'd help I could throw together an exemplar.

[June7]

Not understanding how the code could could print the 'wrong' form. If the print command is to print the open object, how is it printing the wrong one?

I make the print button on the QuickAccessToolbar (I have customized the ribbon and QAT) available when the printable forms are opened instead of using a custom command button.

Seems I have encountered other users that tried to requery subform combobox based on parameter of value on main form and encountered the issue you faced. The subform actually loads before the main form. Try code in main form Current event to requery the subform combobox. Or the subform GotFocus event. Or the combobox GotFocus event.

Beyond this, think I would have to analyse code/db do be more helpful.

[RobRoy]

Not understanding how the code could could print the 'wrong' form. If the print command is to print the open object, how is it printing the wrong one?

I don't fully understand how that happens either but I do know what triggers it.
Here's the code that the Print icon on the custom toolbar calls:

Function PrintMe()
  On Error Resume Next
  DoCmd.RunCommand acCmdPrint
End Function

If I add a couple of lines of diagnostic code to get the current object I get the correct answer. the DoCmd method though prints the wrong form!

This only happens if the OnCurrent event of a subform does a refresh. Now here's the thing. A subform does not support the OnCurrent event HOWEVER there's a trap, one can be added by creating a form and then using it as a subform.

Events available for a form that is going to be used as a subform:



Events available when the same form is used as a Subform:

I make the print button on the QuickAccessToolbar (I have customized the ribbon and QAT) available when the printable forms are opened instead of using a custom command button.

Remarkably that gets around the problem of the wrong form being printed, go figure.

Seems I have encountered other users that tried to requery subform combobox based on parameter of value on main form and encountered the issue you faced. The subform actually loads before the main form. Try code in main form Current event to requery the subform combobox. Or the subform GotFocus event. Or the combobox GotFocus event.

Beyond this, think I would have to analyse code/db do be more helpful.

Already tried adding code in the main form to Requery the Subform, it does nothing.
Also tried the subform and Combobox GotFocus (and other) events, well actually it seems SubForms cannot have a GotFocus event.

There is several ways around this including making a modal popup form that handles edits of the subform data. Not terribly elegant and I'd actually done that on another variant of the same database and the customer wanted the same functionality in a the other variants but without using a modal popup. Sigh, trying to explain to users why certain things in Access just cannot be done

[June7]

That second image is showing properties of the subform container control, not the form object that the container holds. Click again on the 'subform' and view the properties.

Try the Enter event of the subform container control.

[RobRoy]

That second image is showing properties of the subform container control, not the form object that the container holds. Click again on the 'subform' and view the properties.

Thanks, something learned. Still doesn't explain why setting the OnCurrent Event to Me.Refresh screws with printing and causes other strange behaviour.

Try the Enter event of the subform container control.

I'm just about to do that, great minds think alike . I'll try stuffing the ComboBox's Rowsource with the filtered values from the table via some code.
If that works out I'll be much happier, I'm not fond of using the OnCurrent event at al las I've already got an OnCurrent event in the main form that runs a fair slab of code which can slow down the form as users scroll through the records.