Requery Error

[Pyro]

Hi,

I have two subforms (both showing data from the same table) synced together by having the PK of the second subform set to the primary key of the first. When i cycle through the records on the first subform, i am requerying the second so that it displays the same record. For that i am using this code:

[COLOR=black][FONT=Verdana]Private Sub Form_Current()[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]On Error GoTo Err_Form_Current[/FONT][/COLOR]
 
[COLOR=black][FONT=Verdana]If Forms![frm_Contact_Register]![sfrm_Communication_D].SourceObject = "sfrm_Communication_D" Then[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]   Forms![frm_Contact_Register]![sfrm_Communication_D].Form.Requery[/FONT][/COLOR]
[FONT=Verdana][COLOR=black]End If[/COLOR][/FONT]
 
[FONT=Verdana][COLOR=black]Exit_Form_Current:[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]   Exit Sub[/COLOR][/FONT]
 
[COLOR=black][FONT=Verdana]Err_Form_Current:[/FONT][/COLOR]
[FONT=Verdana][COLOR=black]   If Err <> 2455 Then[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]         MsgBox Err.Description[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]         Resume Exit_Form_Current[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]   End If[/COLOR][/FONT]
 
[COLOR=black][FONT=Verdana]End Sub[/FONT][/COLOR]

However without the

If err <> 2455 then...

When the parent form is opened, I get the error (2455) "You entered an expression that has an invalid reference to the property Form/Report."

With the error trap, everything works as it should on the form - the requery works correctly, and the forms are synced as i want them.

Is there a simpler way to do this? or is there a way to fix it so that without the error trap, the code works as it should?


Thanks.

 

[vbaInet]

I'm not quite sure why you chose to conntinuously change the Source Object? Any particular reason?

 

[Pyro]

You are right, the test for the subform sourceobject was there when i thought that at the point of the parent form loading, and the code of the first subform firing, the second subform was not yet loaded? However removing that test as follows:

Private Sub Form_Current()
 
On Error GoTo Err_Form_Current
 
[COLOR=black][FONT=Verdana]Forms![frm_Contact_Register]![sfrm_Communication_D].Form.Requery[/FONT][/COLOR]
 
[FONT=Verdana][COLOR=black]Exit_Form_Current:[/COLOR][/FONT]
[FONT=Verdana][COLOR=black] Exit Sub[/COLOR][/FONT]
 
[COLOR=black][FONT=Verdana]Err_Form_Current:[/FONT][/COLOR]
[FONT=Verdana][COLOR=black] If Err <> 2455 Then[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]       MsgBox Err.Description[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]       Resume Exit_Form_Current[/COLOR][/FONT]
[FONT=Verdana][COLOR=black] End If[/COLOR][/FONT]
 
[COLOR=black][FONT=Verdana]End Sub[/FONT][/COLOR]

...doesn't change the situation. I still get the same error without the error trap. Any thoughts?

 

[vbaInet]

So lets get some things understood. Are your forms linked in a hierarchical structure? That is:

Subform2 gets filtered by Subform1 which gets filtered by MainForm?

Or:

Subform2 and Subform1 gets filtered by MainForm?

 

[Pyro]

Subform 1 is a continuous form showing records related to the parent form, and so it is linked to the parent. This is really only used as a list of those records (no additions or edits). Subform 2 shows an exploded view of one record from Subform 1. It is used to create new records, and or make edits, so it is linked to the parent form, for the purposes of enforcing referential integrity, and for cycling through records on the main form.

Subform 2 is also linked to subform 1 through its underlying query by setting its WHERE condition to only show the record displayed in subform 1.

I hope that makes sense.

Thanks.

 

[vbaInet]

I don't think Subform2 should be linked to the Main form. Referential integrity is enforced on table level, not on form level. As long as the relationships have been set right and you have enforced referential integrity on the joins then everything should work smoothly.

From what you've explained I think all you need to do is on the current event of Subform1, set the Filter property of Subform1 to whatever record ID you're filtering by. Then turn on filtering on Subform2. To reference subform2 do something like this:

MainFormName!NameOfSubform2Control.Form.Filter = "[ID] = " & Me.Subform1ControlId & ""
MainFormName!NameOfSubform2Control.Form.FilterOn = True

 

[Pyro]

The code that you have supplied works. The suggestion re: Referential Integrity works fine also.

However i still get the same error that i was getting earlier when the parent form is opened: (2455) "You entered an expression that has an invalid reference to the property Form/Report."

 

[vbaInet]

The code that you have supplied works. The suggestion re: Referential Integrity works fine also.

However i still get the same error that i was getting earlier when the parent form is opened: (2455) "You entered an expression that has an invalid reference to the property Form/Report."

I can't remember the order in which the current even fires, neither can i remeber whether the subform loads before the main form. So your problem has to do with the Current event firing before the controls are available. Zip and attach your db and I'll find a way round it.

 

[Pyro]

Here is a stripped version.

Thanks for your help.

 

[vbaInet]

I've attached it. See if you can work out what I did to make it work and let me know if that works.

 

[Pyro]

Thanks vbaInet. The code that you added didn't actually work for me . But the idea set me on the right path. I just added the same line of code for the On Enter event of subform 1 and now it does what it should.

I also thought that it was to do with the current event firing before the subform control was available, which is why i added the

If Err <> 2455 Then
MsgBox Err.Description
Resume Exit_Form_Current
End If

Which effectively prevented the error from showing, but i wasn't sure if there was another problem that i wasn't noticing, additionally, i didn't want that error to not show up in situations where the error was more valid..

 

[vbaInet]

There are a few ways around that. The idea behind what was to set the OnCurrent event only when that page is clicked. It wouldn't work if you have "[Event Procedure]" showing in the On Current event text box before load. So what you do is clear that text box. You can set it to "" before the form loads and set it (as I did) after Subform1 opens. It probably didn't work because you probably copied the code straight into your working db without clearing that text box?

You can trap that error but that could be trapping an error on your form you haven't yet dealt with.