February 26, 2015 at 9:43 am
I am using an MS Access 2010 .accdb file. I have linked tables to a SQL Server 2008R2 database.
I have a Main form which contains two SubForms. On the main form I have numerous combo boxes. The RowSource for these combo boxes is set using a MouseMove event (the reason for this would take to long to explain but it produces the result I want).
I have some code in the Current event which sets RowSources for some of the combo boxes.
I've discovered that when the MouseMove event is triggered for a combo box on the main form the Current event is triggered for the SubForm. What's odd It doesn't happen for all of the comboboxes on the main form but does consistently happen for some of the combo boxes.
This isn't really a showstopper problem but just an annoyance. I don't understand why the Current event for the SubForm is firing at all.
Does anyone know why this happens?
February 26, 2015 at 9:53 am
Minor update;
I've discovered that the Current event of the Subform is actually triggered before the MouseMove event, so, it's not related to the MouseMove event per-ce.
This is truly bizarre behavior. It seems kind of random - some combo boxes triggering it and others, which seem to be identically configured, not.
February 26, 2015 at 11:14 am
Do all of your controls have MouseMove events procedures? (Note that Labels share the event with a control unless they are unassociated with a control, and that the detail section of a form has one too.) If so it can become a real challenge to know which event is being triggered. About the only solution is to set a breakpoint in each procedure to find out who is being triggered, and that can be really tedious for a complex form as it appears you have. Because of the complications in using MouseMove events, we have avoided using them at all. We quickly found that you can use other events that give you better control and fewer race conditions.
Also note the following observations from the Office Dev Center:
"Moving a form can trigger a MouseMove event even if the mouse is stationary. MouseMove events are generated when the form moves underneath the pointer. If a macro or event procedure moves a form in response to a MouseMove event, the event can cascade (that is, continually generate MouseMove events).
If two controls are very close together, and you move the mouse pointer quickly over the space between them, the MouseMove event may not occur for the space (for example, this might be the MouseMove event for the form section). In such cases, you may need to respond to the MouseMove event in the contiguous control, as well as in the form section."
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
February 26, 2015 at 12:33 pm
I'm sorry for the longwinded response. I wouldn't blame you if you don't read it...
I've attached a screenshot which will help with understanding what I'm dealing with.
There's a main form (configured as a split form) with three SubForms at the bottom of the screen. The record displayed on the main form is called MileStone_Dates and has a PK called DB_Key. All of the SubForms have a FK called Milestone_Dates_DB_Key. The Subforms are linked to the Main Form as you would expect (Milestone_Dates_DB_Key = DB_Key).
The SubForm who's Current event is being triggered is the Information Requests SubForm. Interestingly, the Current event for the other two forms is not triggered in the same way.
The ComboBoxes framed in red are required values (defined as not null in the database).
The ComboBoxes which are the "problem children" (triggering the Current event of the Information Requests form) are; Regulatory Sector, Directorate, Team, and Type.
These ComboBoxes all have a MouseMove event which calls a Subroutine which dynamically builds their RowSource (I set/check a variable to ensure that even though there are multiple MouseMove events the RowSource is only built once).
What's odd is that Project Name, Group, Category, Purpose, and Application Decision are all similarly configured to the "problem children" but do not trigger the Current event of the Information Request SubForm
What you can't see is that for each of these combo boxes there is a TextBox placed over the Combobox which displays the current value of the column (the underlying query joins all of the reference tables to get the current value). The text boxes do not have a tabstop. Tabbing from field to field navigates from one ComboBox to the next There is an click on the TextBoxes event which sets the focus to the corresponding ComboBox. The GotFocus event of the ComboBox calls the same SubRoutine as the MouseMove event.
Further testing has revealed that the same behavior occurs with the GotFocus event. This tells me that the problem is not related to the mouse move event at all. I checked to see if the main form's Current event was being trigged some how (and subsequently triggering the Current event of the SubForm). This is not the case.
Please let me know if any of this twigs anything for you.
Thanks again for spending some time to think about this.
February 26, 2015 at 2:56 pm
Is your main form a bound form or an unbound form? If it is bound I fail to see the reason for the text boxes that display the current value of the combo box, as the combo box would do that by default. If it is unbound, then the sort of things going on would make more sense. But without seeing the underlying schema (tables and relationships), it is pretty difficult to make more specific suggestions. Would it be possible to post a simplified version with sample data that still demonstrates the problem?
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
February 27, 2015 at 6:41 am
Wendell;
The main form is bound. The reason for the text boxes is to enable the grid portion of the split form to display properly.
The reason for the split form is the users of this app are familiar/comfortable with the Excel-like functionality of a table showing their data as well as the sorting/filtering functionalities.
This app started out as a VERY simple - as little code as absolutely possible, replacement for an Excel workbook that was being shared by about a dozen people. V1 of this app is now being used by about 100 people regionally dispersed across a province. :w00t: I am the sole data architect and app developer. I am not a developer (or data architect) by profession but am thoroughly enjoying the learning process. I have done lots work with SLQ Server databases for about 15 years (mostly ad-hoc development as well as considerable data mining).
I will see what I can do about posting the DDL to build the DB and a copy of the .accdb file so you can have a better look at what's going on.
The other thing to point out is that some of the combo boxes have cascading dependencies; Team is dependent on Directorate. The four boxes in the Application Class section are dependent, from top to bottom - selecting a Group controls what's available for selection in Category and so on.
All of the reference/look-up tables which support the combo boxes have Effective date and Obsolete date columns to support the business requirement of obsoleting records so that the become invalid for current use so all of the RowSource code must take that into account. This functionality is another reason for the text boxes. The RowSources for the ComboBoxes must be requeried with each record navigation on the main from, except, it doesn't, really.... The only time the RowSource REALLY needs to be refreshed is in the instant before it's used. Hence the code on the gotfocus and mouse move events which builds the RowSource and issues a reqeury. If the user is just browsing records there's no need to be requerying all of those RowSources. This technique has vastly improved the record navigation performance as well as significantly reducing the number of database accesses related to record navigation.
Thanks again for sticking with me on this.
March 1, 2015 at 9:41 am
I've discovered the cause of the SubForm requery behavior, specifically the Current event of the SubForm being called.
First, a recap. I was experiencing an odd behavior on a SubForm - the current event was firing, when I was navigating controls (ComboBoxes) on the main form. I thought it was related to MouseMove event code on the controls but testing eliminated that as the cause. The controls causing the behavior also have GotFocus event code. It triggers the same behavior in the Subform. Both of these events build a query string to set the RowSource for the ComboBox and then call the Requery of the control.
What I have just discovered, by commenting out the;
ComboBox.RowSource = QueryString
is that THAT is what's causing the Current event of the Subform to fire! When it's commented out, no SubForm activity. When it's active, the Subform's Current event fires. I confirmed it by commenting out the statement on some of the controls and not on others. The active ones fire the Current event of the SubForm and the commented ones don't.
From this I conclude that this is intended behavior (although I can't imagine of why. Perhaps an undocumented "feature" :-)) and there's nothing to be done about this except to ensure that only the code I need to have executed on the SubForm is executing.
March 1, 2015 at 9:47 am
One last post. Now that I have determined the true source of the problem I have found a possible resolution.
Read here;
http://www.experts-exchange.com/Software/Microsoft_Applications/Q_27813872.html
March 2, 2015 at 3:37 pm
You may also find this Microsoft Community Office forum post to be helpful. There is substantial discussion about the trick of using text boxes over combo boxes to do what are often called Cascading Combo Boxes, and by persons who I am acquainted with and have the utmost respect for their technical skills. The general consensus is that making them work, and work well, especially at the complexity level you have is a daunting task.
I also have not had any positive experiences with the split form feature that was introduced in 2010. I much prefer to use a subform (or subforms) to display information about a selected record based on the selection of a single record in the main form. To select the main form record I generally use a treeview, or an unbound combo box to filter on a specific field and then display a single record or multiple records depending on the design. Let us know if you do finally nail down a solution.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
March 6, 2015 at 7:51 am
Wendell;
Thanks so much for the link above. It's a good description/discussion of the issue. It certainly confirms my experience and discoveries. Unfortunately the link to the video, which I would like to have seen, appears to be broken.
I've got the whole form working quite well now. All of the cascading combo boxes behave as desired in the body of the form. I discovered that I didn't need the text boxes to be visible and overlaying the combo boxes in the body of the form so I moved them off of the combo boxes and set their visible property to no. They show up in the grid portion of the form. I have hidden the combo boxes in the grid, just leaving the corresponding text box to display the value. This works suitably although not perfect. When navigating the body of the form, navigation in the grid doesn't track from column to column when navigating through the combo boxes (since the combo boxes are hidden in the grid and the text boxes are hidden in the form). This is an acceptable compromise since I have set the grid to be non-editable anyway. Users are really only using it for sorting, finding and filtering.
As for using the split form... Yes it does have some quirks but overall, for my solution, it provides a lot of desired functionality essentially for free - sorting, searching, filtering on any combination of attributes displayed on the form.
I am not familiar with the treeview that you mention. Could you please explain that?
Thanks again for your assistance!
March 6, 2015 at 9:11 am
Microsoft included a file that would support several new controls such as Rich Text text boxes, Date pickers, and a treeview. A treeview is much like what you see in Windows Explorer/File Manager when you are looking at folders in the left part of the window. We have used a commercial version of the treeview that is an ActiveX component and it is available from DBI Technologies, Inc. and works quite well. However writing the code to manage the treeview and link to forms is not a trivial task. I probably muddied the waters by mentioning it, as you have already invested considerable resources in getting the filtering working, but you might tuck the idea away.
Glad you have most things working to your satisfaction.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply