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.
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
NewNum = 1
'If NewNum = 1 Then
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.