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
