'Requery' of object '_SubForm' failed Problem, example included

[Ralph Taylor]

Anyone have any advice regarding the following?

I created a little MDB file with the following 3 tables and records
(using Access 2003 BTW):

tblProject
fldProjectID
tblStand
fldStandNo
fldProjectID
tblOrderStandProject
fldOrderNo
fldProjectID
fldStandNo

All of the above records are text and primary keys, the relationship
is:
tblproject(1) to tblstand(Many)
tblStand(1) to tblOrderStandProject(Many) (using both keys)

Then I use Access's form wizard to create a form using all the above
tables, with tblProject as the master, and the other two as subforms.

Open the tblProject form (there are currently no records, this makes
no difference), click "Records/Refresh" from the top menu in Access.

That error "method 'Requery' of object '_SubForm' failed" appears
(feel a surge of joy), I click the [OK] button

/Microsoft Shared/DW20.EXE has a quick unauthorised chat with
131.107.103.243, THEN the typical ERROR REPORT (Illegal Operation)
screen appears, and Access falls on its butt (lame).

And thats about that.

The only code that appears to be generated by the wizard is:

============Form_tblStand Subform============
Sub Form_Current()

Dim ParentDocName As String

On Error Resume Next
ParentDocName = Me.Parent.Name

If Err <> 0 Then
GoTo Form_Current_Exit
Else
On Error GoTo Form_Current_Err
Me.Parent![tblOrderStandProject Subform].Requery
End If

Form_Current_Exit:
Exit Sub

Form_Current_Err:
MsgBox Error$
Resume Form_Current_Exit

End Sub
============End============
The above 'requery' is where it crashes Access.

I've tried changing various things, like the way access references the
Subform object to perform the requery, without success. Sometimes the
error goes away for a while, leading one to think its been solved,
then pops back. Not very consistant.

Any help would be great.

 

[Allen Browne]

Suggestions

1. Uncheck the Name AutoCorrect boxes under:
Tools | Options | General
Then compact the database to get completely rid of this stuff:
Tools | Database Utilities | Compact
Explanation:
http://members.iinet.net.au/~allenbrowne/bug-03.html

2. Add a text box to each subform for the foreign keys.
a) Open the tblStand subform in design view.
b) Open the Field List (View menu).
c) Drag ProjectID onto the subform. You can set the Visible proeprty of the
text box to No.
d) Save and close this subform.
e) Open the tblOrderStandProject subform, and add text boxes for both
ProjectID and StandNo. Save and close.

Although Access should be able to work without these text boxes, there is a
bug in Access 2002 and 2003 that causes it to crash if the fields are not
represented by text boxes. (In that case, the LinkChildField refers to an
object of the undocumented type AccessField, and the bug seems to relate to
that type. Adding the text boxes means it is referring to an object of type
Textbox, which circumvents the bug.)

3. To refer to the form in the subform control, use the ".Form" bit, i.e.:
Me.Parent![tblOrderStandProject Subform].Form.Requery
Anecdotally, Access 2003 is less forgiving about this than 2000/2002, so the
correct reference can help.

4. Any conditional formatting used?
It is quite easy to trigger an endless loop by requerying if you are using
conditional formatting. This issue is not documented as fixed in Service
Patch 1 for Office 2003, but we are seeing this issue less frequently after
the SP has been applied, so apply it if you have not yet done so.

5. If none of that solves the problem, the other alternative is to use the
Current event of the form to reassign the RecordSource of the subform,
instead of requerying it. The recordsource would be set to a string with a
WHERE clause that displays just the records you want, and you would have to
programmatically clear the LinkMasterFields and LinkChildFields because
Access (over-helpfully) assigns these when you change the RecordSource.
Hopefully you will not have to go that route.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Anyone have any advice regarding the following?

I created a little MDB file with the following 3 tables and records
(using Access 2003 BTW):

tblProject
fldProjectID
tblStand
fldStandNo
fldProjectID
tblOrderStandProject
fldOrderNo
fldProjectID
fldStandNo

All of the above records are text and primary keys, the relationship
is:
tblproject(1) to tblstand(Many)
tblStand(1) to tblOrderStandProject(Many) (using both keys)

Then I use Access's form wizard to create a form using all the above
tables, with tblProject as the master, and the other two as subforms.

Open the tblProject form (there are currently no records, this makes
no difference), click "Records/Refresh" from the top menu in Access.

That error "method 'Requery' of object '_SubForm' failed" appears
(feel a surge of joy), I click the [OK] button

/Microsoft Shared/DW20.EXE has a quick unauthorised chat with
131.107.103.243, THEN the typical ERROR REPORT (Illegal Operation)
screen appears, and Access falls on its butt (lame).

And thats about that.

The only code that appears to be generated by the wizard is:

============Form_tblStand Subform============
Sub Form_Current()

Dim ParentDocName As String

On Error Resume Next
ParentDocName = Me.Parent.Name

If Err <> 0 Then
GoTo Form_Current_Exit
Else
On Error GoTo Form_Current_Err
Me.Parent![tblOrderStandProject Subform].Requery
End If

Form_Current_Exit:
Exit Sub

Form_Current_Err:
MsgBox Error$
Resume Form_Current_Exit

End Sub
============End============
The above 'requery' is where it crashes Access.

I've tried changing various things, like the way access references the
Subform object to perform the requery, without success. Sometimes the
error goes away for a while, leading one to think its been solved,
then pops back. Not very consistant.

Any help would be great.

 

[Ralph Taylor]

Hi Allen,

Thank you very very much for your suggestions. I had actually come to
a solution whilst waiting for the message to be posted, and have
replied as soon as I could.
I did try suggestions 1-3 anyway, but alas, it still crashes. In the
example I have not used conditional formatting but its probably wise
for me to up the service pack level anyway. Suggestion 5 sounds like
it would have worked.

Anyway I figured, in my example, that Access might be having a problem
with there being a pair of fields in each of the LinkChildFields and
LinkMasterFields for the link between the Subforms tblStand and
tblOrderStandProject, so I've added an Autonumber (Long Integer),
fldStandID, to tblstand and replaced fldStandNo in
tblOrderStandProject with fldStandID (Long Integer). This is used in
the LinkChildFields/LinkMasterFields, with no further crashing.

Thanks once again.

Suggestions

1. Uncheck the Name AutoCorrect boxes under:
Tools | Options | General
Then compact the database to get completely rid of this stuff:
Tools | Database Utilities | Compact
Explanation:
http://members.iinet.net.au/~allenbrowne/bug-03.html

2. Add a text box to each subform for the foreign keys.
a) Open the tblStand subform in design view.
b) Open the Field List (View menu).
c) Drag ProjectID onto the subform. You can set the Visible proeprty of the
text box to No.
d) Save and close this subform.
e) Open the tblOrderStandProject subform, and add text boxes for both
ProjectID and StandNo. Save and close.

Although Access should be able to work without these text boxes, there is a
bug in Access 2002 and 2003 that causes it to crash if the fields are not
represented by text boxes. (In that case, the LinkChildField refers to an
object of the undocumented type AccessField, and the bug seems to relate to
that type. Adding the text boxes means it is referring to an object of type
Textbox, which circumvents the bug.)

3. To refer to the form in the subform control, use the ".Form" bit, i.e.:
Me.Parent![tblOrderStandProject Subform].Form.Requery
Anecdotally, Access 2003 is less forgiving about this than 2000/2002, so the
correct reference can help.

4. Any conditional formatting used?
It is quite easy to trigger an endless loop by requerying if you are using
conditional formatting. This issue is not documented as fixed in Service
Patch 1 for Office 2003, but we are seeing this issue less frequently after
the SP has been applied, so apply it if you have not yet done so.

5. If none of that solves the problem, the other alternative is to use the
Current event of the form to reassign the RecordSource of the subform,
instead of requerying it. The recordsource would be set to a string with a
WHERE clause that displays just the records you want, and you would have to
programmatically clear the LinkMasterFields and LinkChildFields because
Access (over-helpfully) assigns these when you change the RecordSource.
Hopefully you will not have to go that route.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Anyone have any advice regarding the following?

I created a little MDB file with the following 3 tables and records
(using Access 2003 BTW):

tblProject
fldProjectID
tblStand
fldStandNo
fldProjectID
tblOrderStandProject
fldOrderNo
fldProjectID
fldStandNo

All of the above records are text and primary keys, the relationship
is:
tblproject(1) to tblstand(Many)
tblStand(1) to tblOrderStandProject(Many) (using both keys)

Then I use Access's form wizard to create a form using all the above
tables, with tblProject as the master, and the other two as subforms.

Open the tblProject form (there are currently no records, this makes
no difference), click "Records/Refresh" from the top menu in Access.

That error "method 'Requery' of object '_SubForm' failed" appears
(feel a surge of joy), I click the [OK] button

/Microsoft Shared/DW20.EXE has a quick unauthorised chat with
131.107.103.243, THEN the typical ERROR REPORT (Illegal Operation)
screen appears, and Access falls on its butt (lame).

And thats about that.

The only code that appears to be generated by the wizard is:

============Form_tblStand Subform============
Sub Form_Current()

Dim ParentDocName As String

On Error Resume Next
ParentDocName = Me.Parent.Name

If Err <> 0 Then
GoTo Form_Current_Exit
Else
On Error GoTo Form_Current_Err
Me.Parent![tblOrderStandProject Subform].Requery
End If

Form_Current_Exit:
Exit Sub

Form_Current_Err:
MsgBox Error$
Resume Form_Current_Exit

End Sub
============End============
The above 'requery' is where it crashes Access.

I've tried changing various things, like the way access references the
Subform object to perform the requery, without success. Sometimes the
error goes away for a while, leading one to think its been solved,
then pops back. Not very consistant.

Any help would be great.