Subform not showing records after Stored Procedure

[BobJacksonNCI]

TIA!

Access 2000 with SQL 7.

Simulating plain Access Form/Subform with one-to-many tables.

Form + Subform using Select statement in Record Source to populate Form. Subform has Link Master and Link Child properties loaded.

This works fine when the form is opened - recordsets are all records at this time. Added buttons to trigger Stored Procedures to populate depending on status. When the button is selected, the form appears to work correctly, however the subform no longer shows any records.

I'm using this technique for another form that doesn't have a subform.

Button code follows.

Private Sub cmdShowAll_Click()

Me.RecordSource = "MgrTB_spr_Time_Off_Show_All"
Forms!frm_Time_Off_Status!frm_Time_Off_Status_Subform.Requery
Me.Requery

End Sub
Private Sub cmdShowNew_Click()

Me.RecordSource = "MgrTB_spr_Time_Off_Show_New"
Forms!frm_Time_Off_Status!frm_Time_Off_Status_Subform.Requery
Me.Requery

End Sub

Anyone know why the subform is empty when buttons are pushed? (Either button)

Please view Thread181-473997 for helpful hints regarding this site.

 

[BobJacksonNCI]

Thanks to all who looked!

I came across MS KB article 236368 which includes the following:

"You can use the SELECT statement and the WHERE clause with tables and views, but not with stored procedures. Therefore, values in the LinkChildFields and LinkMasterFields properties are ignored if a stored procedure is the record source of a subform."

I'm posting so that others won't waste time assisting, and ALSO because this Project-specific problem is not straight-forward.

My form/subform worked fine when opened. The problem arose when I wanted to limit the data reported on the main form and did so by replacing the Record Source with a Stored Procedure that selected depending on the contents of a status field. What was unclear to me is that the Link Master/Link Child properties would be ignored when the FORM'S Record Source was changed to a Stored Procedure. I would've understood sooner if I had been using a Stored Procedure for the Subform - but I wasn't.

A solution was to change the Record Source on the Subform from the detail table to a Stored Procedure with parameter - the parameter being the many-to-one field.

HTH,
Bob
Thread181-473997 provides information regarding this site.