Subform won't update on parent form, but will elsewhere

[bbobely]

Hi,
I've built a sequence that works thusly:
User chooses a value from a combo box. This causes a query to run displaying records with a field value matching the combo box in subform 1. Then, the record ID of whichever record is active in subform 1 is used to populate a text box. This text box has its after update property enabled that runs a second query which should populate a second form by matching the record id to entries in a many-to-many junction table. Everything works fine, but the second subform (which is visible on a main form with subform 1) doesn't update. If I open form 2 (which is what subform 2 is based on)from the navigation panel, it contains the correct records based on the record ID from the textbox. When I press f9, subform 2 on the main form will populate with the correct records from the first entry in subform 1, but will not populate correctly if I select any other entry in subform 1 and press f9. The textbox value changes correctly according to the entry selected in subform 1 and form 2 always contains the correct records when opened from the navigation panel after selecting different entries in subform 1. Any ideas? Sorry about the ramble.

Thanks,
Bob

[bbobely]

I stuck a msgbox in the afterupdate event of the text box control and it didn't come up, so I'm assuming this code isn't executing at all. The textbox gets it value through this:

=[Requests_Query].Form![Request Number]

and this is the code in the update event property:

Private Sub Text_AfterUpdate()
Dim RSQQueryName As String
MsgBox ("sux")
RSQQueryName = "Request_Skills_Query"

'I think you must have this line to save the record, first
DoCmd.RunCommand acCmdSaveRecord

'Then execute the query
DoCmd.OpenForm RSQQueryName


End Sub

I've tried the Dirty and Change properties also, but nothing. Can anyone help?

[NeoPa]

Sorry Bob. I couldn't work out what the situation was. Communication is always so important. I'm afraid your post was pretty unclear.

[bbobely]

The basic problem is that I have a subform (subform 2) which doesn't update. Subform 2 is based on a query. The query uses a textbox to capture the active record number from subform 1. I tried to use the afterupdate property of the textbox to run the query when the active record in subform 1 changes. But apparently the afterupdate property is not working properly because I stuck a msgbox in there and it didn't pop up (see code above). Although the msgbox doesn't pop up, the query does change - I can see this by opening the query from the navigation panel. Why won't subform 2 update even though the underlying query updates? Why won't the msgbox appear? Is the afterupdate event not occuring?

[NeoPa]

I'm sorry Bob, but almost all the questions you ask depend on my knowing your database much better than I can from your explanations (which are themselves mainly references to things I know nothing of). I'm going to see if I can make some intelligent guesses as to where you're coming from.

I expect you are looking to run some code in the OnCurrent event. I'm not sure whether this would be for the main form or simply the other subform. AfterUpdate() only fires when relevant data has been changed by the operator.

When you want to rerun the query in the second subform ([Subform 2]) you need to call the form's .Requery method. See Referring to Items on a Sub-Form for how to handle items on different subforms etc.

[bbobely]

Thanks for the help, maybe I can provide a little more clarification. Both subforms are unbound. The records in subform 2 should change depending on which record in subform 1 is active. I tried to accomplish this by using a textbox as an intermediary to capture the active record number from subform 1 (the requests_query subform in the code above). A query then runs based on the changing active record number of subform 1 captured in the textbox; I tried to trigger the query using the afterupdate event of the textbox. Subform 2 should then be updated based on the new results of the query. But subform 2 doesn't update. My guess is that none of the event triggers for the textbox are working, since I've tried OnChange and OnDirty as well and nothing works. I've also used .Requery in line 10 above instead of .OpenForm. Still - nothing.

[missinglinq]

But apparently the afterupdate property is not working properly

The AfterUpdate event is working exactly as it is designed to work! As NeoPa has already said, a control's AfterUpdate() event only fires when the data in it has been entered physically, either by the user typing in the data or pasting it in from elsewhere. It does not fire when it is populated thru code, which is what you are doing here.

To get the AfterUpdate() to fire under these conditions you have to explicitly Call the sub.

Linq ;0)>

[NeoPa]

This is getting somewhat frustrating. You keep coming up with things I've already provided answers to. The event you need is the Form_Current event. Of course the others won't work. I've already said that.