473,239 Members | 1,541 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,239 software developers and data experts.

Combo box not working on a subform

204 128KB
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
Expand|Select|Wrap|Line Numbers
  1.  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?
Jan 15 '21 #1
6 3351
452 Expert Mod 256MB
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
Jan 15 '21 #2
1,271 Expert 1GB
Can you compile the code without error? Do you have warnings turned off?

Jan 15 '21 #3
204 128KB
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.
Jan 15 '21 #4
452 Expert Mod 256MB
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
Jan 15 '21 #5
204 128KB
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.
Jan 16 '21 #6
452 Expert Mod 256MB
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.
Jan 16 '21 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

by: martim07 | last post by:
I am doing all of my coding with VBA.I have a Combo Box that is populated with values from a field. When I click on any item in the Combo Box it brings up a sub form. It works fine except for the...
by: MLH | last post by:
I have created two forms: frmBrowseNegsMainform and frmBrowseNegsSubform. I put a subform control on the first of these. The SourceObject property for the subform control is, of course,...
by: Kurt | last post by:
If one has a working subform, adds a new field to the subform's table, then adds the field (control) to the subform, MS Access 2003 (11.6566.6568) SP2 will not see that new field in Form View. The...
by: Ecohouse | last post by:
I have a main form with two subforms. The first subform has the child link to the main form identity key. subform1 - Master Field: SK Child Field: TrainingMasterSK The second subform has a...
by: Ecohouse | last post by:
have a main form with two tabs, and each tab has a subform. The first tab's subform refers to the mainform (this works). The second tab's subform refers to the identity key of the first tab's...
by: Dave | last post by:
I want to put the information that the user selects in my combo boxes into a subform that lies on the same form as the combo boxes. Thanks for your help already, Dave
by: wayniac | last post by:
What I am trying to do is select an item from the combo box and have it displayed in the subform. Then once you scroll though the subform and find the field you like, click on it and it will...
by: Gordon Padwick | last post by:
A form contains controls, one or more of which can be other forms. A form that contains another form is known as a main form. A form contained by a main form is known as a subform. A subform itself...
by: Yousaf Shah | last post by:
Hello everybody i am building my dataabse for patient record. my main record entry form "patients Record" is a tabbed form with tabsas follows tab "NOK" having subform NOK, tab "History/Exam"...
Jerry Maiapu
by: Jerry Maiapu | last post by:
I have a many to many relationship implemented into a db with 2 parent forms with their respective child forms. ( 3 tables, 1 link table) for each subform I have a lookup combo on FK Field...
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.