new to sql please help

i use access 2000 as my front end. i had a form named main with a couple of unbound fields on it . i used to put in last name and first name and then in a query i would put "like [forms]![main]![text0] & "*"
and also "like [forms]![main]![text2] & "*"

the lookup patient button would open my patient form based on the query results of what i typed in on the main form . ex: i type in riley it would give me my patient form with all the riley's in it.

how do i get my access form to open from information i am looking for in a query now that my whole database is back on a sql server.

by the way i upsized to a adp file no longer using mdb. please if possible explain in detail how to do this as i am having trouble with the syntax of sql. i understand i cant use "*" as a wildcard now but % instead.

thanks in advance.Hoe are you connecting to SQL Sever?

You shouldn't have to change your code...did you create linked tables?|||im assuming they not linked tables since it is now a access project i dont see the little globe when i used to use link tables.

i think everything is useing passthorugh queries now all my tables are on the sql server. just the forms and macros are on the frontend now.

my code i had to change was something like

dim db as dao.database
dim rs as dao.recordset

i had to change that to
dim conn as adodb.connection
dim rs as new adodb.recordset

and quite a few other things

if i left them linked the queries should have stayed working but im trying to get away from passing things through the jet engine and using the full power of sql .|||this was my old code to make a chartnumber based on the first and last name fields. this is the one that didnt work .

Public Function chartlookup()
Dim db As DAO.Database <--believe to be the problem
Dim rs As DAO.Recordset <-- belive to be the problem
Dim SQL As String
Dim NewNum As Integer
Dim NeWChartNum As String

SQL = "Select max(Cint(Right([chartnumber],6))) As RecNum From tblpatientinfo WHERE UCase(Left([chartnumber],5)) = '" & UCase(Left([Forms]![fpatient]![lname], 3)) & UCase(Left([Forms]![fpatient]![fname], 2)) & "'"

Set db = CurrentDb() <-- -problem
Set rs = db.OpenRecordset(SQL) <--problem

If IsNull([Forms]![fpatient]![chartnumber]) = False Then GoTo 400

If rs.EOF = False Then
If IsNull(rs!RecNum) = False Then
NewNum = rs!RecNum + 1
Else
NewNum = 1
End If
End If
'If NewNum = 1 Then

[Forms]![fpatient]![chartnumber] = UCase(Left([Forms]![fpatient]![lname], 3)) & UCase(Left([Forms]![fpatient]![fname], 2)) & Format(NewNum, "000000")
'End If|||Well this is a TOTAL guess...

If you're doing passthrus, you'll need SQL Server syntax..

(Why not just call stored procedures if you're making such a radical change?)

anyway...

SQL = "SELECT (MAX(CONVERT(int,(RIGHT(chartnumber))))
FROM tblpatientinfo
WHERE UPPER(LEFT(chartnumber,5)) = " _
& "'" UCase(Left([Forms]![fpatient]![lname], 3)) & UCase(Left([Forms]![fpatient]![fname], 2)) & "'"

my front end skills have gotten rusty...

You might want to also look to post here:

http://www.sqlteam.com/forums/forum.asp?FORUM_ID=3|||i did get that code to work but my main real problem is how do i get user sitting at my main form where he has to enter a patients lst name and first then when he hits the lookup patient button it opens my patient record with only the patient he typed in from the previous form . this was easy on access just added the expression into the query . but sql doesnt seem to support forms based queries. so what i really need is what does sql use instead of forms based queries to prompt a user for what they want to fill a record with.

if i use select lname from tblpatientinfo where lname="riley"

my form will only give me patients with last name riley

but how do i get it to ask what name we are looking for i guess would be the real question here. do i make a stored procedure to prompt for the information and base the form on the stored procedure or does my from on open have to request what im looking for. i may not be useing the correct wording so please be patient but sql is a whole new world to me.
i tried useing a filter on my form but if i put the stored procedure in the filter it doesnt recognize the stored procedure name only access query names it seems.