Access 2007: Split Forms with Subforms

[Megalog]

Hello all! This is my first posting here, although I've been lurking here for months and getting lots of help & tips from the past posts here.

I'm in the process of updating our company's Access 2003 database to 2007, and while we're finding some of the new perks to be neat, I'm having a problem getting some of them to behave as we've envisioned. For starters, we're using the new Split Form feature. When the split form datasheet shows up at the bottom of the form, it's enabled to expand linked data from another subform/subdatasheet that's on the main form (most of our forms have a tab control with multiple tabs, each usually containing list boxes or subforms).

My question is:
a) is it possible to enable/disable that expansion option alltogether, only showing the data relevant to the main form, and

b) in the case when we want to use it, and we have multiple subforms on a main form, how do we select -which- subform is shown on the split form datasheet? It appears to only show one linked datasheet, and it's rarely the one we would want.

Thanks for your time guys, hope this was stated as clearly as possible.

-Lee

[Megalog]

I now also have a totally separate problem that I need someone to verify if it's a bug or not with Split Forms.

On our forms, we have a combo box at the top that is used for jumping to record numbers (This list is filtered by a variety of choices, and the filter is applied to the form filter). By navigating records this way, all the On Current events work properly, and all the form data displays correctly.

But, if I navigate to a different record using the datasheet shown with the Split Form view, the data remains visually accurate on the form fields, but any calculations being used with the On Current events are using old data from prior records. I've tried adding in extra object requeries, and it helps in some instances, but I dont want to have to do an extra requery on the whole form basically to get the events working properly. =/ It's very frustrating, considering the correct data is visually in all the fields. Anyone else had any problems with this? Is everyone avoiding 2007 like the plague? ;)

I've looked on bug lists (allen brownes, microsofts) and havent seen this listed anywhere.

Thanks for your time,

Lee

[Megalog]

Well it's been a few days since I've posted this, and nobody has replied, which I somewhat expected.

I've done some further testing with these problems, specifically the one in the 2nd part of my post, and thought I'd share my findings for anyone else who tries to use split forms in Access 2007. Hopefully you'll avoid the headaches I've had, or be able to identify the problem better than I have by seeing the issues presented here. I'd like for someone with Access 2007 to do some testing on their own to verify some of these bugs I'm finding. (Or even better, prove me wrong and guide me possibly to where I've gone astray)

First off, I believe the split form feature to be truly bugged. If your form is set to have a split, then the following bugs happen when you change records via clicking on the datasheet, OR when you use the form's Navigation Buttons.
Note: the record selector works normally if the Split Form feature is turned back off. If it's on, it inherits the problems that the split form datasheet has.

The following do NOT work during the Form-Current event, or events that have to do with a record # changing. (Form Load event seems to function, thankfully)

-Declared variables at the module level show up empty
-Combo Box data shows as null
-Toggle button on/off status needs an additional requery
-Toggle button captions need to be cleared first before applying a new value
-Label captions cannot be changed at all
-Unbound text fields need to be requeried, clicked on, or focus set, to display newly applied values

That's what I've found -so far-. All the issues are with unbound data or controls.
Now, I've managed a workaround for the variables and combo box data not working (which was my biggest issue). Luckily, in my case, these are all set on form load, and will not change while the same user is logged into the database. I had to make a label for each variable and set it's caption to whatever property I needed to preserve during the Load event, and then use those caption values in the Current events. It's a completely lame work-around, but so far the only way I've been able to get this database upgraded and working as it had before, using the new split form feature.

Also, another small bug of a different sort, is with Subform/Subreport controls. First off, if you have a subform control using a datasheet, your split form datasheet will be expandable to display this subdata beneath every row. It's a neat feature, allowing you to further filter on this subdata. But, if the subform control is using an actual form, the filtering is bugged and you'll get a sql error when trying to filter. My workaround for this, is to link a query to the subform control. By using a query only, the Split Form datasheet will expand, and easily allow you to filter without any problems.

My original problem that I posted about, however, still exists. If you have multiple subforms, there's no way that I've found to select WHICH of those expand in the split form datasheet.

[jgraham8524]

I can confirm the issue with the labels not updating when changing records on the datasheet portion of the split view. This doesn't seem to be an issue when changing records using the navigation buttons. Have you found a solution to this? I am looking to visually alert my users of conditionally required data using the label. Turns out I can change the properties of the text boxes, but it's not as visually appealing. I'm going to try creating two labels and see if I can change visibility properties as forecolor doesn't work.

[orzeszek]

I have had similar problems with controls not updating when modified from a split form’s Current event. I have found that changing the reference from, for example, lblTest or Me.lblTest to Forms!MyForm.lb lTest fixes the problem.

[Megalog]

I have had similar problems with controls not updating when modified from a split form’s Current event. I have found that changing the reference from, for example, lblTest or Me.lblTest to Forms!MyForm.lb lTest fixes the problem.

Thanks for taking the time to report this. Hopefully they'll fix all the bugs with the split forms in the future.. and give us designers a bit more control over the options.

[orzeszek]

Glad to help. I have found that the Me object in the Current event seems to refer to the datasheet half of the split form when it fires as a result of action taken in the datasheet half. Thus, if you then change some object, it changes the object in the datasheet half. Sometimes this leads to an automatic, corresponding change to the object on the form half, but not always.

[atof]

Thank you orzeszek! That did the trick! Like Megalog, this was driving me nuts. I stepped through the code with the debugger and saw that the form object property changes I specified SHOULD have been firing -- but they didn't. Using the Forms!formname. object format fixed the problem. Clearly another Access 2007 bug, but the workaround did the job. Many thanks for the post.

[cosm ic]

Saved me a lot of time...

[suisgrand]

orzeszek,I only signed up to this forum to thank you for your tip. you are as star:-)