Form Syntax to get Data 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 ?

 

[PHV]

Set the RecordSource property of the form to something like this:
"SELECT ... WHERE MRN=N'" & Forms!PatientFrm!MRN & "'"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[kensaffer]

Thank you for the tip BUT nothing happened. Actually the sam ething happened as before where no data was retrieved and when I ran it in Query, I was not prompted for an MRN.
So this didn't work