Form data retrieval now not working in SQL Server

[kensaffer]

Using Access 2000 and upsized to SQL Server so this ocde was working fine in the MDB.
Got a set of forms that use a query to retrieve the data/ The query is Select.... WHERE (((ADtbl.MRN)=[Forms]![PatientFrm]![MRN]));
The PatientFrm.MRN is the data search criteria with the user not being asked except when the main form is entered. When I run the query I am prompted for a MRN. so it works fine in the MDB.
In the ADP all the data is retrieved. The VIEW has this code
WHERE (MRN = N'(ADtbl.MRN)=[Forms]![PatientFrm]![MRN]')
I've changed it to
WHERE (MRN = N'[Forms]!PatientFrm!MRN')

BUT neither one works, it doesn't ask me for a MRN and retrieves all the data.
Help.......What should the critera syntax be ?

 

[ronspree]

Ken,

You have to treat SQL Server as a separate program. Your adp just communicates with it. SQL Server does not understand any reference to form objects. Instead, use placeholders for the form objects, and then pass values to this placeholders separately.

In SQL Server, the placeholder symbol is "?" (without the quotes).

FORMS: Recordsource specifications are sent to the SQL Server, so your recordsource should be:

SELECT .... WHERE (MRN = ?)

The form's InputParameters' are sent to the SQL server as values to be supplied to the placeholders. Separate with commas if more than one.

InputParameters: [Forms]![PatientFrm]![MRN]