Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Posts
    41

    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
    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

    400 End Function

  2. #2
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    You may be able to use ADO but what I would do is use 3 queries:

    NextJEBatchA:
    SELECT Transactions.Batch
    FROM Transactions
    WHERE (((Left([batch],2))="JE"));

    NextJEBatchB:
    SELECT Batch as NextB
    FROM NextJEBatchA
    UNION ALL Select "JE00000" from Dummy;

    NextJEBatch:
    SELECT Max(Right([Nextb],5))+1 AS NxtBatch
    FROM NextJEBatchB;

    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.

  3. #3
    Join Date
    Jun 2002
    Location
    Vienna, VA
    Posts
    53
    You must open the recordset this way:

    <code>
    Set rsTemp = dbThis.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
    </code>


    the dbOpenDynaset, dbSeeChanges are the SQL Server Specific stuff.


    Leah

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •