Generating a query from two forms

[Chris B]

I have a query which looks for the student name inserted in a specific form.
I do not want this form open to all users (due to other functions on the
form). However, I do want all users to be able to access the query. I have
designed another form for general users to insert the student name.
How can I get the query to look for the student name in whichever form is
open?

My only current solution is to copy the query and fill in the criteria
differntly. This is not a problem, however, the query generates a report
which I need for both levels of user. If I copy the query I will need to
create another report which is identical to the first except linked to the
new query.

This is obviously a problem if I want to update the report at any time.

I hope someone can help!

Thanks

 

[Klatuu]

How can I get the query to look for the student name in whichever form is open?

This sentence causes some concern. Queries do not search for data in a
form. The search for data in a table. Forms have no data of their own.
They are a "window" into the data.

If your query is contstructed properly, it should not matter which form you
are using to do the search. If you have form specific criteria in the query,
then you will need to address that issue.

Post back the SQL of the query and we can help find a way to do this.

 

[Chris B]

Hi thanks for the reply,

I can see now that I was unclear. The situation is that users enter a name
on a form, the criteria of the query then looks at this name to decide which
records to display from the table.

Hope that is more clear!

Anyway here is the SQL for the query

SELECT tblIncident.[Praise/Concern], tblIncident.ID, tblIncident.Student,
tblIncident.Staff, tblIncident.Department, tblIncident.Date,
tblIncident.[Incident Type], tblIncident.[Reason for Report],
tblIncident.[Detention?], tblIncident.Action, tblIncident.[Head of Department
Action], tblIncident.[Form Tutor Action], tblIncident.[Head of Year Action],
tblIncident.[Assistant Head Action], tblIncident.[Date of Detention],
tblStaffDetails.[First Name] AS [tblStaffDetails_First Name],
tblStaffDetails.Surname AS tblStaffDetails_Surname, tblStaffDetails.Title,
tblStudentDetails.[First Name] AS [tblStudentDetails_First Name],
tblStudentDetails.Surname AS tblStudentDetails_Surname,
tblStudentDetails.[Tutor Group], tblStudentDetails.House
FROM tblStaffDetails INNER JOIN (tblStudentDetails INNER JOIN tblIncident ON
tblStudentDetails.[Roll Number] = tblIncident.Student) ON
tblStaffDetails.[Staff Initials] = tblIncident.Staff
WHERE (((tblIncident.[Praise/Concern])="Concern") AND
((tblIncident.Student)=[Forms]![frmStudentConcerns]![cboStudent]));

What I want is for the criteria for the field Student to go to whichever
form the user has open (which will depend on his level of access).

Thanks again for taking the time to help.

 

[Klatuu]

This can be done by modifying the query at runtime.

First this to do is to take all criteria out of your stored query. Then, at
run time, you can put the WHERE part of the query in the query

Dim qdf As QueryDef
Dim strQry as String
Dim strWhere as String

'Create the Where criteria
strWhere = "WHERE (((tblIncident.[Praise/Concern])="Concern") AND
((tblIncident.Student)=[Forms]!" & Me.Name & "![cboStudent]));"

'Get the Query to change
Set qdf = CurrentDb.QueryDefs(strQryName)
strQry = qdf.SQL
'Put the Where in the Query
strQry = Replace(strQry, ";", strWhere)
'Write the query back out
qdf.SQL = strQry
qdf.Close

set qdf = nothing

Note that since you will be using this in two different forms, you can
either name the combo the same name (the easy way) or you will have to modify
it to put the combo name it.


--
Dave Hargis, Microsoft Access MVP

Hi thanks for the reply,

I can see now that I was unclear. The situation is that users enter a name
on a form, the criteria of the query then looks at this name to decide which
records to display from the table.

Hope that is more clear!

Anyway here is the SQL for the query

SELECT tblIncident.[Praise/Concern], tblIncident.ID, tblIncident.Student,
tblIncident.Staff, tblIncident.Department, tblIncident.Date,
tblIncident.[Incident Type], tblIncident.[Reason for Report],
tblIncident.[Detention?], tblIncident.Action, tblIncident.[Head of Department
Action], tblIncident.[Form Tutor Action], tblIncident.[Head of Year Action],
tblIncident.[Assistant Head Action], tblIncident.[Date of Detention],
tblStaffDetails.[First Name] AS [tblStaffDetails_First Name],
tblStaffDetails.Surname AS tblStaffDetails_Surname, tblStaffDetails.Title,
tblStudentDetails.[First Name] AS [tblStudentDetails_First Name],
tblStudentDetails.Surname AS tblStudentDetails_Surname,
tblStudentDetails.[Tutor Group], tblStudentDetails.House
FROM tblStaffDetails INNER JOIN (tblStudentDetails INNER JOIN tblIncident ON
tblStudentDetails.[Roll Number] = tblIncident.Student) ON
tblStaffDetails.[Staff Initials] = tblIncident.Staff
WHERE (((tblIncident.[Praise/Concern])="Concern") AND
((tblIncident.Student)=[Forms]![frmStudentConcerns]![cboStudent]));

What I want is for the criteria for the field Student to go to whichever
form the user has open (which will depend on his level of access).

Thanks again for taking the time to help.

How can I get the query to look for the student name in whichever form is open?

This sentence causes some concern. Queries do not search for data in a
form. The search for data in a table. Forms have no data of their own.
They are a "window" into the data.

If your query is contstructed properly, it should not matter which form you
are using to do the search. If you have form specific criteria in the query,
then you will need to address that issue.

Post back the SQL of the query and we can help find a way to do this.