asked on

passing a value from an access textbox to sqlserver view

Hi experts I need to pass a textbox varable accross to a view in sql server 2005
What I have is a field called ID to access the data on the form I would use [Forms!teacheraccessmain!ID.Value] = "27" the value that will be in the value will be the ID log in value of a user, the sytem will use this information to return data that will be displayed only relating to them. what happens is I have a access front end passing off to a SQl server for DB access. I am putting in palce a small secure access DB that allows the user to display and change only records that when the user logs in with there ID number I use this number to filter the view in SQlserver to return only there information. just not sure how to extract the data in the textbox and feed this into the view.. any ideas. the system has to run in Access and only access..

the view as it now stands is

SELECT TOP (100) PERCENT dbo.Teacher.TeacherID, dbo.Teacher.TeachFName, dbo.Teacher.TeachLName, dbo.Class.ClassID, dbo.Student.FamilyName,
dbo.Student.GivenName, dbo.StudentCourse.StudCourseID, dbo.StudentCourse.QualificationIssued, dbo.Class.ClassDesc,
dbo.StudentModules.ModuleID, dbo.StudentModules.Status, dbo.StudentModules.DateComplete, dbo.StudentModules.FinalExamMark,
dbo.StudentModules.FinalExamMarkDate, dbo.StudentModules.retest, dbo.StudentModules.retestdate, dbo.StudentModules.admineval,
dbo.StudentModules.adminevaldate, dbo.StudentModules.moduleclosed, dbo.Attendance.AttendanceID, dbo.Attendance.StudentID
FROM dbo.StudentCourse INNER JOIN
dbo.Student ON dbo.StudentCourse.StudentID = dbo.Student.StudentID INNER JOIN
dbo.StudentModules ON dbo.StudentCourse.StudCourseID = dbo.StudentModules.StudCourseID INNER JOIN
dbo.Attendance ON dbo.Student.StudentID = dbo.Attendance.StudentID INNER JOIN
dbo.Teacher INNER JOIN
dbo.Class ON dbo.Teacher.TeacherID = dbo.Class.TeacherID ON dbo.Attendance.ClassID = dbo.Class.ClassID
ORDER BY dbo.Teacher.TeacherID, dbo.Class.ClassID

SOLUTION

Lowfatspread

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sydneyguy

ASKER

1) create a parameterized access query for the view
I know I have to create a new query but what I require is an example of what the data passed to the query would look like, have not been a big user of this area but was tring to find out whether I pass a value of for example see below

FROM dbo.StudentCourse INNER JOIN
dbo.Student ON dbo.StudentCourse.StudentID = dbo.Student.StudentID INNER JOIN
dbo.StudentModules ON dbo.StudentCourse.StudCourseID = dbo.StudentModules.StudCourseID INNER JOIN
dbo.Attendance ON dbo.Student.StudentID = dbo.Attendance.StudentID INNER JOIN
dbo.Teacher INNER JOIN
dbo.Class ON dbo.Teacher.TeacherID = dbo.Class.TeacherID ON dbo.Attendance.ClassID = dbo.Class.ClassID
where studentID = Forms!teacheraccessmain!ID.Value
****** it is the above value Forms!teacheraccessmain!ID.Value that I am not sure how to pass to the query that lives in sqlserver

ASKER CERTIFIED SOLUTION

Lowfatspread

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial