468,765 Members | 1,104 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,765 developers. It's quick & easy.

No data for subform after Proc executes

Hello,
I have a combo box (Combo7) that needs to call a function during the
After Update event of the combo box. The function resides in an Access
2000 ADP Module called MMAnswerData_code.

The following is the code of the function:

Public Function SubFormRS(FrmTarget As Object)
Forms!frmAD_OpeningForm!FrmTarget.RecordSource = "EXEC
dbo.AdSubFormRecSource " & Forms!frmAD_OpeningForm!SubFormFilter
End Function

The function takes a form object as its argument and attempts to set
the record source of the form to the records returned by a SQL Server
2000 Proc called 'ADSubFormRecSource'. This Proc takes a parameter
called '@SubFormFilter varchar(19)'. The parameter gets its value from
Forms!frmAD_Opening!SubFormFilter. 'SubFormFilter' is a text box on
the main form that that uses three concatenated values from a total of
three combo boxes to form the parameter required by @SubFormFilter.

The After Update event of Combo7 looks like this:

*******************
Private Sub Combo7_AfterUpdate()

If Me.Combo4 = "HMR3" And Me.Combo2 = "01" Then
SubFormRS (frmG1_HMR3_Q3)
End If

Me.Combo11.RowSource = "EXEC dbo.ADStudentCombo_sp " & Me.Combo7
******************
Combo7 also sets the rowsource of another combo box during the
AfterUpdate event. The parameter being sent to the SubFormRS is the
name of a form.
The Proc (at a high level) looks like this:
******************************
Create Procedure ADSubformRecSource_sp
@SubFormFilter varchar (19)
AS
Select *
From tblADRawAnswerData AD
Left Outer Join Student_Data_Main SD On SD.Permnum = AD.Permnum
Inner Join Teacher_Data_Main TD On TD.TeacherID = SD.TeacherID

Where SD.Status is null
and
AD.TestShortName + Cast(AD.TestGrade as Varchar(2)) + TD.TeacherID =
@SubFormFilter
******************************
I have tested the Proc with a parameter, and the proc works fine.

When I make a selection from Combo 7, I don't get an error message,
but it is evident that there is no recordset for the subform (the
bound text box controls in the subform all have '#Name?' in them).

What can I do so that when a selection is made from combo7, the record
source of the subform populates with records per the Proc parameter?

Thanks for your help!

CSDunn
Nov 12 '05 #1
4 2029
CSDunn wrote:
Hello,
I have a combo box (Combo7) that needs to call a function during the
After Update event of the combo box. The function resides in an Access
2000 ADP Module called MMAnswerData_code.

The following is the code of the function:

Public Function SubFormRS(FrmTarget As Object)
Forms!frmAD_OpeningForm!FrmTarget.RecordSource = "EXEC
dbo.AdSubFormRecSource " & Forms!frmAD_OpeningForm!SubFormFilter
End Function

< snip >

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

I believe your reference to the subform's record source is incorrect.
Perhaps:

Forms!frmAD_OpeningForm!FrmTarget.Form.RecordSourc e = "EXEC " & _
" AdSubFormRecSource " & Forms!frmAD_OpeningForm!SubFormFilter

When referring to the Properties/Method of a subform you must include
".Form." between the subform's Control name and the subform's
property/method.

You don't need the default owner prefix (dbo) before the SP name, unless
there are other SPs w/ the same name, but different owners.

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

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

iQA/AwUBQHS2p4echKqOuFEgEQJPgQCfQZxVpI2sZNgus/5o6Z0qhUCuZ3sAn2p5
a7WeeB6ErtAYlVPgI8UIBfGb
=P3Yc
-----END PGP SIGNATURE-----

Nov 12 '05 #2
Hmmm. Not to sound rude, but the code is incorrect in so many ways
it's hard to know where to start. Trust me, though, we ALL started
out with the same misconceptions on how to code properly, so don't
take it personal :) It takes trial and error, and a good memory for
'what worked/didn't work last time'. I don't have the patience to
really read closely and post a solution using your exact field/control
names, but here's a some tips, both for a better app and to get better
help from the group:

1) a form must be OPEN to set it's recordsource via code. Maybe try
putting a text box on the form with the combo, and set it's value (in
afterupdate) to:

me.txtSQL = "EXEC dbo.AdSubFormRecSource '" &
Forms!frmAD_OpeningForm!SubFormFilter & "'" (note two single quotes
you may be forgetting ;)

and then in the On Open event of the second form, say
me.recordsource = Forms!frmAD_OpeningForm!txtSQL

2) NAME your controls and ALL your objects SOMETHING MEANINGFUL!!!
Don't be lazy and use the default chosen by Access. NOTHING screams
'unprofessional' quite like this practice. It also makes it much
harder for people like us to help you, as we can't 'picture' anything
from the control names, nor can anyone else that you hand your project
off to later ;).

Personally, I use camelCase, and something like the following
nomenclature:
Controls:
txtTeacherFilter (would be a textbox containing a string like
"[pkTeacherID]=1234"
cboTeacherID (combobox to choose pkTeacherID, note the pk to designate
a field which is a Primary Key. A table representing a teacher/course
relationship (many to many) would have [fkTeacherID] and [fkCourseID]
fields. These would be populated by [pkTeacherID] of tblTeachers and
[pkCourseID] of tblCourses.
lstCourseID (a listbox containing courses)

Tables:
tblTeachers (table of teachers)
tblCourses (table of courses)
tblTeacherCourse (many-to-many table, what I call a 'join' table,
though that's probably not an accepted term, representing which
teachers teach which courses. I use singular rather than plural to
designate this is a join table)
tlkpBuildings (a lookup aka unchanging table of buildings available
for courses to be in)

Forms/Subforms:
frmTeacher (form showing teachers in tblTeachers)
sfrmTeacherCourses (continuous subform on frmTeacher which shows all
courses for one teacher - again, note singular Teacher, plural Courses
- this kind of naming REALLY helps keep your stuff straight!)

Reports/Subrpts:
rptTeachers (report of teachers - note that if the report is used to
print out just one teacher at a time, I'd probably call it rptTeacher)
srptTeachersCourses (subreport of courses on the rptTeachers)

Queries:
qfrmTeachers (query used as recordsource of frmTeacher)
qlstTeachers (query rowsource of cboTeacherID or lstTeacherID - note:
personally, I use this nomenclature for both tbl and tlkp-type tables,
as long as the point is that the query is to populate combo or list
boxes)
qsrptTeacherCourses (query recordsource of subreport showing Courses)

Note: A good application NEVER has people looking at the database
window itself. THerefore, maintain a consistency to the names that
will be useful to a developer, NOT the end user!
3) If you really like the external module dealie you got going, in
order to pass a form around different modules as an object, you want
to say something like (the form names I chose are random, not meant to
reflect your situation):
********************
dim frm as form
set frm = Forms("frmMain")
RunSub(frm)
**************
Public Sub RunSub (frmIn as Form)
frmIn.recordsource = Forms!frmAD_OpeningForm!txtSQL

BUT, remember rule 1 above! This code works with OPEN forms only.

To refer to a recordsource of a subform from a main form, you'd say
the following. Note I recommend using the . nomenclature over the !,
as it's far more universal:

Forms("frmTeacher").ctlTeacherCourses.Form.Records ource = me.txtSQL

Where ctlTEacherCourses is the name of the CONTROL on the form which
houses the subform [sfrmTeacherCourses].

Hope this all helps somewhat...

Peace,
Brett

cd***@valverde.edu (CSDunn) wrote in message news:<80**************************@posting.google. com>...
Hello,
I have a combo box (Combo7) that needs to call a function during the
After Update event of the combo box. The function resides in an Access
2000 ADP Module called MMAnswerData_code.

The following is the code of the function:

Public Function SubFormRS(FrmTarget As Object)
Forms!frmAD_OpeningForm!FrmTarget.RecordSource = "EXEC
dbo.AdSubFormRecSource " & Forms!frmAD_OpeningForm!SubFormFilter
End Function

The function takes a form object as its argument and attempts to set
the record source of the form to the records returned by a SQL Server
2000 Proc called 'ADSubFormRecSource'. This Proc takes a parameter
called '@SubFormFilter varchar(19)'. The parameter gets its value from
Forms!frmAD_Opening!SubFormFilter. 'SubFormFilter' is a text box on
the main form that that uses three concatenated values from a total of
three combo boxes to form the parameter required by @SubFormFilter.

The After Update event of Combo7 looks like this:

*******************
Private Sub Combo7_AfterUpdate()

If Me.Combo4 = "HMR3" And Me.Combo2 = "01" Then
SubFormRS (frmG1_HMR3_Q3)
End If

Me.Combo11.RowSource = "EXEC dbo.ADStudentCombo_sp " & Me.Combo7
******************
Combo7 also sets the rowsource of another combo box during the
AfterUpdate event. The parameter being sent to the SubFormRS is the
name of a form.
The Proc (at a high level) looks like this:
******************************
Create Procedure ADSubformRecSource_sp
@SubFormFilter varchar (19)
AS
Select *
From tblADRawAnswerData AD
Left Outer Join Student_Data_Main SD On SD.Permnum = AD.Permnum
Inner Join Teacher_Data_Main TD On TD.TeacherID = SD.TeacherID

Where SD.Status is null
and
AD.TestShortName + Cast(AD.TestGrade as Varchar(2)) + TD.TeacherID =
@SubFormFilter
******************************
I have tested the Proc with a parameter, and the proc works fine.

When I make a selection from Combo 7, I don't get an error message,
but it is evident that there is no recordset for the subform (the
bound text box controls in the subform all have '#Name?' in them).

What can I do so that when a selection is made from combo7, the record
source of the subform populates with records per the Proc parameter?

Thanks for your help!

CSDunn

Nov 12 '05 #3
Brett,
I appreciate the input. For what its worth, I'm a guy who is trying to
move an organization into the .NET world, and I've spent a lot of time
trying to get up to speed on C#, ASP.NET, ADO.NET, and UML. Its a
little after 1:00am where I am, and here I am, trying to implement
better techniques into a form interface I have tried to migrate away
from for the last two years. I have spent very little time in study
with VBA, because two years ago, I saw a different future. Yet its me,
and my ADP, two years later, at 1:00am.

The good news is, I still believe. Its 1:00am, and I am delirious, but
I still believe.

Thank you again for your help. I will go to bed and examine your
suggestions more carefully when daylight returns.

CSDunn

bv******@sfhp.org (brett valjalo) wrote in message news:<1f**************************@posting.google. com>...
Hmmm. Not to sound rude, but the code is incorrect in so many ways
it's hard to know where to start. Trust me, though, we ALL started
out with the same misconceptions on how to code properly, so don't
take it personal :) It takes trial and error, and a good memory for
'what worked/didn't work last time'. I don't have the patience to
really read closely and post a solution using your exact field/control
names, but here's a some tips, both for a better app and to get better
help from the group:

1) a form must be OPEN to set it's recordsource via code. Maybe try
putting a text box on the form with the combo, and set it's value (in
afterupdate) to:

me.txtSQL = "EXEC dbo.AdSubFormRecSource '" &
Forms!frmAD_OpeningForm!SubFormFilter & "'" (note two single quotes
you may be forgetting ;)

and then in the On Open event of the second form, say
me.recordsource = Forms!frmAD_OpeningForm!txtSQL

2) NAME your controls and ALL your objects SOMETHING MEANINGFUL!!!
Don't be lazy and use the default chosen by Access. NOTHING screams
'unprofessional' quite like this practice. It also makes it much
harder for people like us to help you, as we can't 'picture' anything
from the control names, nor can anyone else that you hand your project
off to later ;).

Personally, I use camelCase, and something like the following
nomenclature:
Controls:
txtTeacherFilter (would be a textbox containing a string like
"[pkTeacherID]=1234"
cboTeacherID (combobox to choose pkTeacherID, note the pk to designate
a field which is a Primary Key. A table representing a teacher/course
relationship (many to many) would have [fkTeacherID] and [fkCourseID]
fields. These would be populated by [pkTeacherID] of tblTeachers and
[pkCourseID] of tblCourses.
lstCourseID (a listbox containing courses)

Tables:
tblTeachers (table of teachers)
tblCourses (table of courses)
tblTeacherCourse (many-to-many table, what I call a 'join' table,
though that's probably not an accepted term, representing which
teachers teach which courses. I use singular rather than plural to
designate this is a join table)
tlkpBuildings (a lookup aka unchanging table of buildings available
for courses to be in)

Forms/Subforms:
frmTeacher (form showing teachers in tblTeachers)
sfrmTeacherCourses (continuous subform on frmTeacher which shows all
courses for one teacher - again, note singular Teacher, plural Courses
- this kind of naming REALLY helps keep your stuff straight!)

Reports/Subrpts:
rptTeachers (report of teachers - note that if the report is used to
print out just one teacher at a time, I'd probably call it rptTeacher)
srptTeachersCourses (subreport of courses on the rptTeachers)

Queries:
qfrmTeachers (query used as recordsource of frmTeacher)
qlstTeachers (query rowsource of cboTeacherID or lstTeacherID - note:
personally, I use this nomenclature for both tbl and tlkp-type tables,
as long as the point is that the query is to populate combo or list
boxes)
qsrptTeacherCourses (query recordsource of subreport showing Courses)

Note: A good application NEVER has people looking at the database
window itself. THerefore, maintain a consistency to the names that
will be useful to a developer, NOT the end user!
3) If you really like the external module dealie you got going, in
order to pass a form around different modules as an object, you want
to say something like (the form names I chose are random, not meant to
reflect your situation):
********************
dim frm as form
set frm = Forms("frmMain")
RunSub(frm)
**************
Public Sub RunSub (frmIn as Form)
frmIn.recordsource = Forms!frmAD_OpeningForm!txtSQL

BUT, remember rule 1 above! This code works with OPEN forms only.

To refer to a recordsource of a subform from a main form, you'd say
the following. Note I recommend using the . nomenclature over the !,
as it's far more universal:

Forms("frmTeacher").ctlTeacherCourses.Form.Records ource = me.txtSQL

Where ctlTEacherCourses is the name of the CONTROL on the form which
houses the subform [sfrmTeacherCourses].

Hope this all helps somewhat...

Peace,
Brett

cd***@valverde.edu (CSDunn) wrote in message news:<80**************************@posting.google. com>...
Hello,
I have a combo box (Combo7) that needs to call a function during the
After Update event of the combo box. The function resides in an Access
2000 ADP Module called MMAnswerData_code.

The following is the code of the function:

Public Function SubFormRS(FrmTarget As Object)
Forms!frmAD_OpeningForm!FrmTarget.RecordSource = "EXEC
dbo.AdSubFormRecSource " & Forms!frmAD_OpeningForm!SubFormFilter
End Function

The function takes a form object as its argument and attempts to set
the record source of the form to the records returned by a SQL Server
2000 Proc called 'ADSubFormRecSource'. This Proc takes a parameter
called '@SubFormFilter varchar(19)'. The parameter gets its value from
Forms!frmAD_Opening!SubFormFilter. 'SubFormFilter' is a text box on
the main form that that uses three concatenated values from a total of
three combo boxes to form the parameter required by @SubFormFilter.

The After Update event of Combo7 looks like this:

*******************
Private Sub Combo7_AfterUpdate()

If Me.Combo4 = "HMR3" And Me.Combo2 = "01" Then
SubFormRS (frmG1_HMR3_Q3)
End If

Me.Combo11.RowSource = "EXEC dbo.ADStudentCombo_sp " & Me.Combo7
******************
Combo7 also sets the rowsource of another combo box during the
AfterUpdate event. The parameter being sent to the SubFormRS is the
name of a form.
The Proc (at a high level) looks like this:
******************************
Create Procedure ADSubformRecSource_sp
@SubFormFilter varchar (19)
AS
Select *
From tblADRawAnswerData AD
Left Outer Join Student_Data_Main SD On SD.Permnum = AD.Permnum
Inner Join Teacher_Data_Main TD On TD.TeacherID = SD.TeacherID

Where SD.Status is null
and
AD.TestShortName + Cast(AD.TestGrade as Varchar(2)) + TD.TeacherID =
@SubFormFilter
******************************
I have tested the Proc with a parameter, and the proc works fine.

When I make a selection from Combo 7, I don't get an error message,
but it is evident that there is no recordset for the subform (the
bound text box controls in the subform all have '#Name?' in them).

What can I do so that when a selection is made from combo7, the record
source of the subform populates with records per the Proc parameter?

Thanks for your help!

CSDunn

Nov 12 '05 #4
Yeah, I too have been doing .net stuff of late (vb.net to be exact)
rather than Access, although I've developed in Access for nearly 6
years at this point, off and on...

I used to be here at cdms pretty much daily from 1999-2001, but yours
is the first 'serious' reply I've given here in at least a year.
Hopefully, it'll be of some use...

peace,
brett

Show Bush the Door in 04!!!

cd***@valverde.edu (CSDunn) wrote in message news:<80**************************@posting.google. com>...
Brett,
I appreciate the input. For what its worth, I'm a guy who is trying to
move an organization into the .NET world, and I've spent a lot of time
trying to get up to speed on C#, ASP.NET, ADO.NET, and UML. Its a
little after 1:00am where I am, and here I am, trying to implement
better techniques into a form interface I have tried to migrate away
from for the last two years. I have spent very little time in study
with VBA, because two years ago, I saw a different future. Yet its me,
and my ADP, two years later, at 1:00am.

The good news is, I still believe. Its 1:00am, and I am delirious, but
I still believe.

Thank you again for your help. I will go to bed and examine your
suggestions more carefully when daylight returns.

CSDunn

bv******@sfhp.org (brett valjalo) wrote in message news:<1f**************************@posting.google. com>...
Hmmm. Not to sound rude, but the code is incorrect in so many ways
it's hard to know where to start. Trust me, though, we ALL started
out with the same misconceptions on how to code properly, so don't
take it personal :) It takes trial and error, and a good memory for
'what worked/didn't work last time'. I don't have the patience to
really read closely and post a solution using your exact field/control
names, but here's a some tips, both for a better app and to get better
help from the group:

1) a form must be OPEN to set it's recordsource via code. Maybe try
putting a text box on the form with the combo, and set it's value (in
afterupdate) to:

me.txtSQL = "EXEC dbo.AdSubFormRecSource '" &
Forms!frmAD_OpeningForm!SubFormFilter & "'" (note two single quotes
you may be forgetting ;)

and then in the On Open event of the second form, say
me.recordsource = Forms!frmAD_OpeningForm!txtSQL

2) NAME your controls and ALL your objects SOMETHING MEANINGFUL!!!
Don't be lazy and use the default chosen by Access. NOTHING screams
'unprofessional' quite like this practice. It also makes it much
harder for people like us to help you, as we can't 'picture' anything
from the control names, nor can anyone else that you hand your project
off to later ;).

Personally, I use camelCase, and something like the following
nomenclature:
Controls:
txtTeacherFilter (would be a textbox containing a string like
"[pkTeacherID]=1234"
cboTeacherID (combobox to choose pkTeacherID, note the pk to designate
a field which is a Primary Key. A table representing a teacher/course
relationship (many to many) would have [fkTeacherID] and [fkCourseID]
fields. These would be populated by [pkTeacherID] of tblTeachers and
[pkCourseID] of tblCourses.
lstCourseID (a listbox containing courses)

Tables:
tblTeachers (table of teachers)
tblCourses (table of courses)
tblTeacherCourse (many-to-many table, what I call a 'join' table,
though that's probably not an accepted term, representing which
teachers teach which courses. I use singular rather than plural to
designate this is a join table)
tlkpBuildings (a lookup aka unchanging table of buildings available
for courses to be in)

Forms/Subforms:
frmTeacher (form showing teachers in tblTeachers)
sfrmTeacherCourses (continuous subform on frmTeacher which shows all
courses for one teacher - again, note singular Teacher, plural Courses
- this kind of naming REALLY helps keep your stuff straight!)

Reports/Subrpts:
rptTeachers (report of teachers - note that if the report is used to
print out just one teacher at a time, I'd probably call it rptTeacher)
srptTeachersCourses (subreport of courses on the rptTeachers)

Queries:
qfrmTeachers (query used as recordsource of frmTeacher)
qlstTeachers (query rowsource of cboTeacherID or lstTeacherID - note:
personally, I use this nomenclature for both tbl and tlkp-type tables,
as long as the point is that the query is to populate combo or list
boxes)
qsrptTeacherCourses (query recordsource of subreport showing Courses)

Note: A good application NEVER has people looking at the database
window itself. THerefore, maintain a consistency to the names that
will be useful to a developer, NOT the end user!
3) If you really like the external module dealie you got going, in
order to pass a form around different modules as an object, you want
to say something like (the form names I chose are random, not meant to
reflect your situation):
********************
dim frm as form
set frm = Forms("frmMain")
RunSub(frm)
**************
Public Sub RunSub (frmIn as Form)
frmIn.recordsource = Forms!frmAD_OpeningForm!txtSQL

BUT, remember rule 1 above! This code works with OPEN forms only.

To refer to a recordsource of a subform from a main form, you'd say
the following. Note I recommend using the . nomenclature over the !,
as it's far more universal:

Forms("frmTeacher").ctlTeacherCourses.Form.Records ource = me.txtSQL

Where ctlTEacherCourses is the name of the CONTROL on the form which
houses the subform [sfrmTeacherCourses].

Hope this all helps somewhat...

Peace,
Brett

cd***@valverde.edu (CSDunn) wrote in message news:<80**************************@posting.google. com>...
Hello,
I have a combo box (Combo7) that needs to call a function during the
After Update event of the combo box. The function resides in an Access
2000 ADP Module called MMAnswerData_code.

The following is the code of the function:

Public Function SubFormRS(FrmTarget As Object)
Forms!frmAD_OpeningForm!FrmTarget.RecordSource = "EXEC
dbo.AdSubFormRecSource " & Forms!frmAD_OpeningForm!SubFormFilter
End Function

The function takes a form object as its argument and attempts to set
the record source of the form to the records returned by a SQL Server
2000 Proc called 'ADSubFormRecSource'. This Proc takes a parameter
called '@SubFormFilter varchar(19)'. The parameter gets its value from
Forms!frmAD_Opening!SubFormFilter. 'SubFormFilter' is a text box on
the main form that that uses three concatenated values from a total of
three combo boxes to form the parameter required by @SubFormFilter.

The After Update event of Combo7 looks like this:

*******************
Private Sub Combo7_AfterUpdate()

If Me.Combo4 = "HMR3" And Me.Combo2 = "01" Then
SubFormRS (frmG1_HMR3_Q3)
End If

Me.Combo11.RowSource = "EXEC dbo.ADStudentCombo_sp " & Me.Combo7
******************
Combo7 also sets the rowsource of another combo box during the
AfterUpdate event. The parameter being sent to the SubFormRS is the
name of a form.
The Proc (at a high level) looks like this:
******************************
Create Procedure ADSubformRecSource_sp
@SubFormFilter varchar (19)
AS
Select *
From tblADRawAnswerData AD
Left Outer Join Student_Data_Main SD On SD.Permnum = AD.Permnum
Inner Join Teacher_Data_Main TD On TD.TeacherID = SD.TeacherID

Where SD.Status is null
and
AD.TestShortName + Cast(AD.TestGrade as Varchar(2)) + TD.TeacherID =
@SubFormFilter
******************************
I have tested the Proc with a parameter, and the proc works fine.

When I make a selection from Combo 7, I don't get an error message,
but it is evident that there is no recordset for the subform (the
bound text box controls in the subform all have '#Name?' in them).

What can I do so that when a selection is made from combo7, the record
source of the subform populates with records per the Proc parameter?

Thanks for your help!

CSDunn

Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by CSDunn | last post: by
14 posts views Thread by aaron kempf | last post: by
6 posts views Thread by Tom | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by Marin | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.