473,554 Members | 2,836 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Using Proc to return RecordSet to Subform

Hello,
In Access ADP's that connect to SQL Server databases, any time I have
a situation where I have a combo box in a main form that looks up a
record in a subform, the subform record source has to be based on
either a View or a Table. I can almost always use a View, and it helps
to do this since I can have better control over the size of the
RecordSet of the subform.

There are times when the use of a Stored Procedure would give me
greater control than a View would, but I've always had problems when
trying to use a Proc as the RecordSource of a sub form. If I'm really
just interested in the content of the RecordSet, maybe I could still
use a Proc to fill a RecordSet for use in the subform without having
to address the RecordSource of the subform(?).

I n my current situation, I have a main form that can accommodate one
of several subforms. This application will be used in a K-12 school
system. In the main form, the user selects the name of a test from a
combo box, then the grade level of the test in a second combo box.
After the user makes these selections, they click a button named
‘Set', and the appropriate subform appears in the Detail section of
the main form.

The subforms reflect the selected test and grade level. The
RecordSource for each subform are separate SQL Server Views that are
made distinct by the test and grade level they filter for. It s kind
of annoying to have to have all of these Views, but using a Proc that
takes parameters as the single RecordSource for all subforms has not
worked for me.

Continuing with the main form, after the test and grade level have
been selected and the appropriate sub form is present, the user then
uses a combo box to select a school site. The After Update event of
the cmbSiteLocation combo box executes a Proc that fills the RowSource
of a ‘cmbTeacher' combo box so that the correct teachers for the
selected site will appear in the ‘Teacher' combo box:

Private Sub Combo0_AfterUpd ate()
Me.cmbTeachers. RowSource = "EXEC dbo.ADTeacherCo mbo_sp " &
Me.cmbSiteLocat ion
End Sub

The After Update event of the cmbTeachers combo box does the same
thing to populate the RowSource of a combo box called cmbStudents:

Private Sub Combo7_AfterUpd ate()
Me.cmbStudents. RowSource = "EXEC dbo.ADStudentCo mbo_sp " &
Me.cmbTeachers
End Sub

The cmbStudent combo box is designed to look up a record in the
subform (or start a new record).

What I would like to do, is create the RecordSet for any of the
subforms at the point where a selection is made from cmbTeachers. I
would like to use a Proc for this, one that would take the TeacherID
value from cmbTeachers and return just the student records for that
teacher.

I have been given the following sample code to return a RecordSet from
a Proc:
*************** *************** *****
dim cn as new adodb.connectio n
dim rs as adodb.recordset
dim strSQL as string
dim lngRecords as long

strSQL = "Exec GetStudentRecor ds_sp '" & _
Me.cmbTeachers

cn.open CurrentProject. Connection
set rs = cn.execute(strS QL, lngRecords, adCmdText)
*************** *************** *****
Can I use something like this to create a RecordSet for any given
subform after a selection is made from cmbTeachers regardless of which
subform appears? If so, how might I implement this? Would I just leave
the RecordSource property of the subforms empty if I used something
like this?

Thank you for your help!

CSDunn
Nov 12 '05 #1
0 2344

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
2276
by: CSDunn | last post by:
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...
8
2066
by: CSDunn | last post by:
Hello, I have a situation in which I need to address three SQL Server 2000 Stored Procedure parameters in the OnClick event of an Option Group. The Option Group lives on an Access 2000 ADP form. In another situation where I had to set the RowSource of a combo box based on a parameter value that was delivered to a proc from another combo...
0
1359
by: CSDunn | last post by:
Hello, In Access ADP's that connect to SQL Server databases, any time I have a situation where I have a combo box in a main form that looks up a record in a subform, the subform record source has to be based on either a View or a Table. I can almost always use a View, and it helps to do this since I can have better control over the size of...
6
9847
by: Wendy Powley | last post by:
I have a subform which represents a 1:N relationship with the main form. I would like to be able to read values from an external file, fill the subform with the values read & allow the user to view/edit the values via the subform. I thought this would be accomplished using a simple loop; read values, assign the various fields of the...
0
1540
by: Jason | last post by:
I would like to be able to place a command button on a primary (parent) form that opens up a subform. I want to use this subform to search for or limit the recordset of data in the primary form. The database is setup as follows: the primary form is named "TestDataCleanup" which is based on a table named "Test_Results". This primary form...
3
2774
by: shumaker | last post by:
This code from the subform works for getting the value of a field of the main form named "WorkSheet": MsgBox Form_WorkSheet.Recordset.Fields("Clerk").Value Each record in the mainform datasheet called "WorkSheet" has a subform. I am trying to get the controls on the subform to reference the fields in the main form, because I think that it...
4
1889
by: Deano | last post by:
There's an interesting chapter on this in the Access 2000 Developer's Handbook (chapter 8) but I'm trying to rework the code so the user has the option of saving changes or rolling them back. There is a boolean transaction variable that tracks whether the code is in a transaction. If this is true the unload event fires and asks if changes...
30
2245
by: Tim Marshall | last post by:
Here's the scenario, A2003, Jet back end, illustrated with some cut down code at the end of the post: A proc dims a snapshot recordset (dim rst as Dao.recordset) and opens it. There are several nested do loops, going through the records in rst using .movenext. At one point in one of the loops, we'll say the rst is at record "a". Now,...
14
7731
by: ml_sauls | last post by:
I've built a system to enter and manage purchase orders. This is in use by >10 clients. Some use it in Access 97, most are in A2k. About half use it through a Citrix implementation. It is separated into a front end and 3 back ends (1 network for permanent storage, 1 local for temp storage, and 1 local for storage for particular user). ...
0
7778
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8008
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7527
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7857
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6114
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5412
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5135
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3525
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1109
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.