Form Referencing in Query

[primagic]

I have the following SQL which works fine in an access database, however how does one refer to forms in a SQL database.

SELECT tblSchools.SchoolID, tblSchools.SchoolName, tblSchools.Phone, tblSchools.AddressLine1, Postcode.Town, State.StateAbv, Postcode.Postcode, tblSchools.SchoolID, qryCountofBookings.[Number of Bookings]
FROM (State INNER JOIN (tblSchools INNER JOIN Postcode ON tblSchools.PostcodeID = Postcode.PostcodeID) ON State.StateID = Postcode.StateID) LEFT JOIN qryCountofBookings ON tblSchools.SchoolID = qryCountofBookings.SchoolID
WHERE (((RemovePunc([tblSchools].[SchoolName])) Like "*" & [Forms]![frmSearch]![Search2] & "*") AND ((State.StateID) Like "*" & [Forms]![frmSearch]![cboState] & "*")) OR (((tblSchools.SchoolID) Like "*" & [Forms]![frmSearch]![Search2] & "*") AND ((State.StateID) Like "*" & [Forms]![frmSearch]![cboState] & "*"))
ORDER BY tblSchools.SchoolName;

 

[danvlas]

That's because SQL Server is not aware of any client objects.


You have two options:

1. Create in SQL Server a stored procedure with parameters, like:

Create Procedure uspNameOfProc
@Search2 varchar(Max),
@State varchar(Max)
As
Set nocount on
SELECT tblSchools.SchoolID, tblSchools.SchoolName, tblSchools.Phone, tblSchools.AddressLine1, Postcode.Town, State.StateAbv, Postcode.Postcode, tblSchools.SchoolID, qryCountofBookings.[Number of Bookings]
FROM (State INNER JOIN (tblSchools INNER JOIN Postcode ON tblSchools.PostcodeID = Postcode.PostcodeID) ON State.StateID = Postcode.StateID) LEFT JOIN qryCountofBookings ON tblSchools.SchoolID = qryCountofBookings.SchoolID
WHERE (((RemovePunc([tblSchools].[SchoolName])) Like '%' + @Search2 + '%') AND ((State.StateID) Like '%' + @State + '%')) OR (((tblSchools.SchoolID) Like '%' + @Search2 + '%') AND ((State.StateID) Like '%' + @State + '%'))
ORDER BY tblSchools.SchoolName

If you used the query for a form, change the recordsource of the form to the stored procedure and use the InputParameters property of the form to perform the search:

Me.InputParameters=[Forms]![frmSearch]![Search2] & ";" & [Forms]![frmSearch]![State]


2. The second solution is simpler: create a view (no parameters allowed) as:

Create View viwViewName
As
SELECT tblSchools.SchoolID, tblSchools.SchoolName, tblSchools.Phone, tblSchools.AddressLine1, Postcode.Town, State.StateAbv, Postcode.Postcode, tblSchools.SchoolID, State.StateID, qryCountofBookings.[Number of Bookings]
FROM (State INNER JOIN (tblSchools INNER JOIN Postcode ON tblSchools.PostcodeID = Postcode.PostcodeID) ON State.StateID = Postcode.StateID) LEFT JOIN qryCountofBookings ON tblSchools.SchoolID = qryCountofBookings.SchoolID

In the form, change the recordsource property of the form to:

Me.RecordSource="Select * From viwViewName SRC WHERE (((SRC.[SchoolName])) Like "*" & [Forms]![frmSearch]![Search2] & "*") AND ((SRC.StateID) Like "*" & [Forms]![frmSearch]![cboState] & "*")) OR (((tblSchools.SchoolID) Like '%'" & [Forms]![frmSearch]![Search2] & "'%') AND ((SRC.StateID) Like '%'" & [Forms]![frmSearch]![cboState] & "'%'))
ORDER BY SRC.SchoolName"

Stored procedures perform better than views, especially on large sets of data.
Views are easier to use in client applications.


You have to make sure that the query "qryCountofBookings" exists on the server.

Another solution (which I do not recommend) is to link the SQL Server tables to a mdb file. In this case, the application will run without any changes, but you lose the huge power of SQL Server.

I have noticed that you're using 'Like'on StateID, which resembles a numeric primary key. Are you sure it's OK?

HTH


Daniel Vlas
Systems Consultant