Combo box not working on a subform

[Petrol]

I have an unbound report containing several sub-reports and sub-forms, bound to various queries. One of the sub-forms lists people who attended a particular event, by filtering from a query listing people who attended any event. In the header for this sub-form there is an unbound combo box to allow the user to select the wanted event from a separate table; the Row Source for the combo box is

 SELECT [EventTypes].[EventCode], [EventTypes].[EventType], [EventTypes].[SeqNum], [EventTypes].[Next_Walk] FROM EventTypes WHERE Nz(Next_Walk)<>"" ORDER BY [SeqNum];  

The AfterUpdate event procedure for the combo box then uses the chosen event code to set the filter, and requeries the form (Me.Requery) to display the required list of attendees as a continuous form.

It all works fine when the sub-form is displayed on its own, but as soon as it is placed on the main report the combo box stops working. When clicked it displays the dropdown list OK, and the cursor can select one of the entries, but clicking the entry has no effect. The dropdown does not close, and even the BeforeUpdate event doesn't fire, let alone the wanted AfterUpdate event. No messages are given.

Can anyone help on this one?

[isladogs]

I've read this several times but I'm finding it difficult to follow.

Several things:
1. When using Nz you should provide the value if false: Nz(Next_Walk,"")<>""

2. Reports are normally for display/printing only. You cannot update data on a report.

3. Although some interaction is possible when a report is in REPORT view, this is normally limited to actions like clicking a button or hyperlink. In PRINT PREVIEW mode no interaction is possible.

4. Although forms can be used as subreports, the full form behaviour is not available. Better to create a subreport for the purpose

Hopefully that covers the reasons for your issues

[jimatqsi]

Can you compile the code without error? Do you have warnings turned off?

Jim

[Petrol]

Isladogs and jimatqsi, thank you both for your thoughts. I'm sorry the OP was difficult to follow - I agree, for I found it difficult to try to describe the situation succinctly, and had several goes at writing it.

Essentially, I have a report which gives various bits of information about events we run. Because the various bits of information are derived from different queries, they are reported though several different subreports on the main report. One of those subreports listed the leadership team for our next forthcoming event. This has been running well for several years.

Recently, however, we have expanded the range of events we run, so it became necessary to allow the user to select which for event he wants to see the leadership team. All the other subreports are unchanged, but I just want to be able to select one of the events for that particular subreport. Because of the dictum "Thou shalt never allow input or change anything on a report", I changed the subreport to a subform - the documentation is clear that subforms are allowed on reports, in order to allow people to change things. However I don't want to actually change any of the underlying data, just to select which subset of it is displayed. Surely this is a reasonable ask? To produce multiple copies of the entire report, all identical except for different versions of this subreport, would be very wasteful, inefficient and inelegant, as well as using up many slots on my rather limited main menu system.

Responding to your thoughtful comments,
1. Microsoft documentation (both support.microsoft.com and docs.microsoft.com) make it clear that the value-if-null parameter in the Nz function is optional, and if omitted for a string variable defaults to a ZLS. I have often used it this way. However, changing it to include the optional parameter explicitly didn't help.
2. True, reports are for display/printing only. But I don't want to update any data, just display a subset of the data in the query by using the filter method.
3. Yes, it is only in the Report view that I want to select the subset.
4. Originally it was a subreport, but that was even more limited than the subform. In the subform I lock all the controls displaying data about the team members - only the combo box to select the event displayed is unlocked.
5. Yes, the database compiled without error and warnings are enabled. The combo box looks as if it will work OK - the dropdown list appears and its entries can be selected. They are actioned when the subform is run alone (it applies the filter and the correct subset of team leaders is displayed) but when put on the main report nothing happens when the selected entry is clicked.

I would really appreciate being able to get this working, as there seems to be no other non-clumsy way to achieve the end result and a lot of time and effort has been expended already in setting it up this way. Users are used to and appreciate the report in its original form, but just want the ability to select the appropriate data in the subform.

[isladogs]

I can't see any way you will ever get this to work

However, there is a very simple and well tested approach.
Users select the required values in a form and those values are used to open a report/subreport filtered to those values.
There is no reason that I can think of why that would require different versions of the subreport.
You already have a form (subform) to make the selection...so what's the issue?

And as for using Nz without the false argument, whilst I agree that it works now, MS have gradually tightened up on code over the years and so there maybe no certainty that it will work in the future. I wouldn't risk it myself.

Anyway. Good luck.

P.S. I very occasionally use a form in a subreport. Most recently that was so I could use a disconnected ADO recordset for the subreport data....something that is impossible using a 'standard' report. However, as soon as that form is placed in a report it loses most of the form's functionality

[Petrol]

Thanks, isladogs, that (i.e. the suggestion in your second paragraph) sounds promising. In fact, I thoought that was what I was doing. I just haven't found a way to make it work.
As you say, I already have a form (subform) to make the selection. The issue is that the combo box on that subform won't work :(. If I could get it to make the selection, all would be well.

[isladogs]

Using a combo box in a form to filter the form itself or a report based on it is a very common approach used in numerous Access apps.
Do a Google search for filter an Access report using a combo box and I expect you will find something helpful.
The MS Northwind example database will have examples though the code may have examples of bad practice such as MVFs.

In fact I have an example that I referenced in this current thread Filtering a subform using combos. There is a design view screenshot in post #17 and how to find it is mentioned in post #4. Hope that helps.