Odd multi-form with cascading combo box requery problem (1 Viewer)

Hello all,

I have been banging my head against a problem which seemed to me shouldn't have been a problem at all. Access seems to be of a different opinion however. Or i'm not doing it right

I have a form with a tabview control with 2 tabs. Each tab has a subform (sf_1 on tab1 and sf_2 on tab2) in it containing 2 cascading combo boxes cboDept and cboRoom. cboRoom shows a list of all rooms in the selected department. Each form is based on a different query on the same table.

The goal is to have each combo box on both forms display the same value. When i select a new dept and room on sf_1 it should instantly reflect this change in sf_2 and vice versa.

The weird thing about it is that it works the first time i do it, just after the main form has loaded. For example: I select the new dept & room in sf_1 and the changes are instantly reflected in sf_2 as i would expect. However, when i try it again, cboDept changes as it should but i simply cannot get cboRoom to requery on sf_2. It keeps displaying its old data but with a blank selection since the value from cboRooms on sf_1 isn't contained in its current selection set.

When i change the dept and the room, close the main form and open it again the changes have been saved correctly and both forms display the previously selected dept and room. It's really odd, especially since i'm not getting any error messages whatsoever.

Private Sub cboDept_AfterUpdate()
cboRooms.Requery 'to update the rooms after selecting a new dept
Forms!Frm_Tabs!sf_2.Requery 'to have the other form update cboRooms to have the correct list
End Sub

Private Sub cboRooms_AfterUpdate()
Forms!Frm_Tabs!sf_2.Requery 'to have the other form update cboRooms and display the correct value
End Sub

That was my first try, after that i've tried a bunch of different ways to get that comb box to update but to no avail.

I tried setting focus on the other form and then on the combo box and then requerying the combo box. No workie

I tried directly referencing the combo box and requerying like this:
Forms!Frm_Tabs!sf_2!cboRooms.Requery
But it just gave me an error that it didn't support this command.

I tried first refreshing the current form and then requerying the other form, I tried refreshing the current form, setting focus on the other form and then refreshing that form and then requerying that form. Then I tried the same thing but including requerying the combo box. Nothing worked

Is there something blaringly obvious that i'm missing here? I'm starting to think so.

Any help is appreciated

Thank you for the reply boblarson. Unfortunately your suggestion didn't work either. After working once cboRooms on the other form always keeps the same content and refuses to update while cboDept always instantly reflects the correct value. I'm assuming since its content doesn't have to change which wouldn't subject it to the same problem as cboRooms.

I've tried getting it to update like this as well

Private Sub cboDept_AfterUpdate()
cboRooms.Requery 'to update the rooms after selecting a new dept
[Forms]![Frm_Tabs]![Sf_2].[Form]![cboRooms].Requery
'to have the other form update cboRooms to have the correct list
End Sub

And like this

Private Sub cboDept_AfterUpdate()
cboRooms.Requery 'to update the rooms after selecting a new dept
Forms!Frm_Tabs.Form.sf_2.Requery
[Forms]![Frm_Tabs]![Sf_2].[Form]![cboRooms].Requery
'to have the other form update cboRooms to have the correct list
End Sub

Neither works, cboRooms just remains stuck.

If there were some type of lock on the table where the cbo's get their values from, access should throw an error and somehow inform me shouldn't it?

The problem isn't that cboRooms on Sf_2 doesn't accept the new value it just doesn't show it because it just doesn't show the correct content. Well i think it accepts it since the selection goes blank

The thing i really don't get is why it does it ONCE but never again after that untill i close and reopen the form.

Haven't had much chance to digest everything you've written here but from experience on something similar, I had to move the code from the AfterUpdate event to the Form_Current event.

If that fails, perhaps check the RowSource property on the second combo box. It may have changed to reflect the first fresh selection, ie SELECT rooms WHERE dept = 'abcde'

This may not be much help but it might trigger a few ideas that will help you solve the problem.

Hey jc, i tried your suggestion of moving the code somewhere else.
I first tried the Form_Current but that didn't do much.

Now i thought that maybe i'd been going too far out looking for a solution so i tried something simple. Putting the requery for the forms in the tab1_click and tab2_click events. Since this didn't seem to work out either i tried putting a breakpoint in there to see if the events actually got raised. Well, it seems that i can click on the tabs 'till my hands start to cramp up because the events just don't get raised.

Private Sub tab1_Click()
me.sf_1.requery
End Sub

Private Sub tab1_Click()
me.sf_2.requery
End Sub

It can't really get any simpler than that can it? I tried with the other events like double click or mouse up and they don't get raised either. Is this a known problem with the tab control or is my access simply buggy or is there something wrong with the setting of my main form? Maybe this is also why i can't get cboRooms to update, maybe the requery method just doesn't go through or something...

Attach your database in a zip file and I'll have a look. I may be able to come up with something by actually doing it rather than thinking about it.

Cheers,
JC

Eureka. i "solved" it. Or rather, i found a workaround. It's pretty silly to have to do it like this though. I don't know if it's a workaround or the way you have to do it but anyway.

The problem was apparently a combination of 2 problems. The first was that the changes didn't get committed to the table quickly enough and second was what seems a display problem/bug/glitch whatever you like to call it.

The first was solved by requerying every time i selected a new value for both cboDept and cboRooms. I used requery since there doesn't seem to be anything resembling a commit method?

So after this the values were correct for cboDept and cboRooms on both forms. However cboRooms still showed empty because it didn't get the correct content based on cboDept. Why? I have no real idea. Maybe something to do with the way Access handles forms and events?

To "fix" this i had to also have a LostFocus event for cboDept. Then i had to set focus on for example sf_2 then set focus on cboDepts and then on another control and then back to the current form, sf_1. This way the LostFocus event gets raised for cboDept and cboRooms updates its content and displays the value it was actually containing. This happens very quickly so the user doesn't really know

So now things look like this:

Same for both forms:

Private Sub cboDept_Afterupdate()
cboRooms.Requery
Me.Requery
End Sub

Private Sub cboDept_LostFocus()
cboRooms.Requery
End Sub

Code for sf_1:

Private Sub cboRooms_Afterupdate()
Me.Requery

[Forms]![Frm_Tabs]![sf_2].SetFocus
[Forms]![Frm_Tabs]![sf_2].[Form]![cboDept].SetFocus
[Forms]![Frm_Tabs]![sf_2].[Form]![cboRooms].SetFocus
[Forms]![Frm_Tabs]![sf_1].SetFocus
End Sub

Setting focus to the other form is essential, otherwise cboRooms won't get updated.

Personally i think this was a very annoying problem. I can live with the fact that the values didn't get committed to the table quickly enough. That's at least somewhat logical, but the cbo not updating is downright weird and extremely annoying. I'm sure there's also a perfectly logical explanation for it but i can't find it. I'm just glad it's over with

Thanks to jc and boblarson for your help

Glad you got it to work for you but at the same time all of the stuff you are going through really didn't need to happen. There's obviously something you weren't catching correctly and if you could have attached the database like jc1104 suggested we could have figured that out.

The offer still stands though if you want me (or us) to take a look so we can give you feedback on what exactly wasn't set up properly. I know from lots of experience that there are many ways to get to a solution, not all are optimal but work. As a learning tool we might be able to suggest some things that may make your life easier in the future. But only if you want.

I will take you both up on that offer. I'm really wondering what i should have done differently to prevent such a thing to occur.

Apparently the file was juuuust a bit too large (461kb)

Here's the link where you can get it:
http://users.pandora.be/limen/xfer/db.zip

This is the 'real' app. I simplified it to explain the problem here. The names are also mostly dutch but an ID's an ID right

The problem was located in the main form "Frm_Patienten" on the subforms "Sub_Frm_Patienten_Allemaal" (1st tab) and "Sub_Frm_Patienten_Opgenomen" (2nd tab). The cbo's in question are "cboAfdeling" and "cboLocatie".

Thank you for still wanting to take a look

Since it is in Dutch, it may take me a bit longer to get it, but bear with me. I have to go into get a hernia repaired tomorrow, so I may not be in any shape for about a week to even look too closely. If I don't get it today, I will keep looking and if you don't hear anything back within, say two weeks, just post another post on this post and remind me.

That being said, someone else may beat me to it. But in looking briefly, it appears that part of your problem may be the subform on subform and the fact that your very first tab (Alle Patiënten) appears to be what you were referring to as your main form but it is on a subform, which it didn't have to be.

First i'll wish you a good recovery from your hernia

The "main form" i'm referring to is "Frm_Patienten". It's a non databound form with the tab control on it. The tab control contains one "subform" per tab.

I'm personally not a big fan of using dutch in programs and programming but if you have to, you have to

The layout is like this:
Frm_Patienten:
>Tab control
>>Tab1. "Alle patienten" : Sub_Frm_Patienten_Allemaal
>>Tab2. "Opgenomen" : Sub_Frm_Patienten_Opgenomen
>>Tab3. "Opnames" : Sub_Frm_Patienten_Opnames

the last subform also has a subform called "Sub_Sub_Frm_Opnames" or something. I'm no longer at work atm so i don't have it in front of me.

I hope this gives you a better overview.

Thank you for the effort you've put in so far