Unanswered: please help access upsized to sql now code doesnt work
here is the code i was using from a command button which basically used the first and last name fields to make a primary key called chart number. now it doesnt work and i belive it is in the dim rs as dao.recordset statement because this data now resides on an sql server. here is the code snippet.
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
You may be able to use ADO but what I would do is use 3 queries:
SELECT Batch as NextB
UNION ALL Select "JE00000" from Dummy;
SELECT Max(Right([Nextb],5))+1 AS NxtBatch
Note that the second query references a table I created called Dummy that has just one record in it. I called it Dummy as a throw back to my Oracle 7 days. All I do is get the next batch number from the last query which will be right even if there are no records.