Need help with Proc in SubForm

[CSDunn]

Hello,
I have a Form (frmRCMain) / Subform (frmRCSub) arrangement (MS Access 2003
ADP) in which the users clicks a combo box (Combo48) in the main form to
find a record in the subform. The bound column of the combo box is a field
called 'PERMNUM'. The main form's record source is SQL Server 2000 Stored
Procedure called 'RCMainForm_sp'. The subform's record source is a table
called 'tblRCStudentScores'. The linking child and master fields between the
two is also 'PERMNUM'. The PERMNUM field is basically a student ID, so when
the user selects a student name in the combo box, the matching student
record shows up in the subform.

If possible, I would like to set up the subform to use a Stored Procedure
for its record source. I want for the subform SP to take an @PERMNUM
parameter when the user makes a student name selection in Combo48, and show
the correct record.

The current SQL Server 2000 table being used as the record source for
'frmRCSub' has about 200 fields in it, all of which are needed as
textboxes/checkboxes in the subform. The table currently has about seven
thousand records in it, and PERMNUM is the Primary Key of the table. I'm
hoping that by using an SP for the record source of the subform instead of
the table, there will be a performance gain for the application and the SQL
Server database.

I have set up the following SP for the subform:

CREATE Procedure RCInputFormSub_sp
@Permnum varchar(12)
AS
SELECT * FROM tblRCStudentGrades
WHERE Permnum = @Permnum

I named 'RCInputFormSub_sp' as the record source for the subform 'frmRCSub',
and configured the Input Parameters of 'frmRCSub' as follows:

@Permnum varchar = forms!frmRCMain!Permnum

When the ADP opens, a form appears to the user called 'frmSwitchBoard'. The
user clicks a button labeled 'Report Card' to open 'frmRCMain'. During
testing, when I click the 'Report Card' button, a 'Parameter Value' box
opens before I see 'frmRCMain', which is not what I want to have happen. I
need for the form/subform to open, then have the parameter value 'sent' to
the SP recordsource of the subform after the user has made a selection from
Combo48 in the main form, and then show the record based on the selection
made in Combo48.

How can I do this?

Thanks for your help!

CSDunn

 

[MGFoster]

Hello,
I have a Form (frmRCMain) / Subform (frmRCSub) arrangement (MS Access 2003
ADP) in which the users clicks a combo box (Combo48) in the main form to
find a record in the subform. The bound column of the combo box is a field
called 'PERMNUM'. The main form's record source is SQL Server 2000 Stored
Procedure called 'RCMainForm_sp'. The subform's record source is a table
called 'tblRCStudentScores'. The linking child and master fields between the
two is also 'PERMNUM'. The PERMNUM field is basically a student ID, so when
the user selects a student name in the combo box, the matching student
record shows up in the subform.

If possible, I would like to set up the subform to use a Stored Procedure
for its record source. I want for the subform SP to take an @PERMNUM
parameter when the user makes a student name selection in Combo48, and show
the correct record.

The current SQL Server 2000 table being used as the record source for
'frmRCSub' has about 200 fields in it, all of which are needed as
textboxes/checkboxes in the subform. The table currently has about seven
thousand records in it, and PERMNUM is the Primary Key of the table. I'm
hoping that by using an SP for the record source of the subform instead of
the table, there will be a performance gain for the application and the SQL
Server database.

I have set up the following SP for the subform:

CREATE Procedure RCInputFormSub_sp
@Permnum varchar(12)
AS
SELECT * FROM tblRCStudentGrades
WHERE Permnum = @Permnum

I named 'RCInputFormSub_sp' as the record source for the subform 'frmRCSub',
and configured the Input Parameters of 'frmRCSub' as follows:

@Permnum varchar = forms!frmRCMain!Permnum

When the ADP opens, a form appears to the user called 'frmSwitchBoard'. The
user clicks a button labeled 'Report Card' to open 'frmRCMain'. During
testing, when I click the 'Report Card' button, a 'Parameter Value' box
opens before I see 'frmRCMain', which is not what I want to have happen. I
need for the form/subform to open, then have the parameter value 'sent' to
the SP recordsource of the subform after the user has made a selection from
Combo48 in the main form, and then show the record based on the selection
made in Combo48.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Subforms load before the main form loads. Therefore, the parameter pop
ups when you try to load the frmRCMain. Its subform is trying to get
the data from the main form, which hasn't yet loaded it's data. That's
when it prompts the user for the Permnum value.

It is usually better to use a View as the RecordSource of a subform.
Then the subform control's Master/Child link properties "prompt" the
subform for the correct records when the master form changes records.

If you want to keep your set up (using an SP) you could change the
master form (frmRCMain)'s OnCurrent event to change the RecordSource of
the subform. E.g. (untested):

Private Sub Form_Current()

Const SQL_SUBFORM = "EXEC RCInputFormSub_sp "

Static intFired As Integer

If intFired > 0 Then
Me!SubFormControlName.Form.RecordSource = _
SQL_SUBFORM & "'" & Nz(Me!Permnum,0) & "'"
Else
intFired = intFired + 1
End If

End Sub

Note: I put single-quotes around the Permnum parameter since it is a
VARCHAR in the SP.

I put the Nz() function on the Me!Permnum in the re-setting of the
subform's RecordSource in case that control value is null.

N.B.: That check on intFired is there because when the master form
opens the OnCurrent event fires 2 times. The 1st time OnCurrent fires
the control Permnum isn't recognized. The second firing is when the
control is recognized & the value pulled. Wierd, but true in Access 97
untested in other versions. If you're using another version of Access
& the above code doesn't work then just get rid of the check & increment
of intFired. If it doesn't work after that... well, I don't know.

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQGOb8YechKqOuFEgEQLmkwCfe9wIqz3otRXjxXVs0J2S/ANxzeMAn2+Z
4XqvOCoOuQNxpY/iep4Jnwdz
=hJbi
-----END PGP SIGNATURE-----

 

[CSDunn]

MGFoster,
Thanks for this idea, I will try it and let you know how it goes.

CSDunn