Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2006
    Posts
    5

    Unanswered: ADP/SQL Server issue with ADO objects

    I recently converted my access application to an ADP and am having some errors with a simple procedure im trying to write off a click event. I think the errors have to do with how I am opening and closing the recordset objects. Please see below...Thanks.

    Private Sub Add_Record_Click()
    On Error GoTo Err_Add_Record_Click

    Dim cn As New ADODB.Connection
    Dim rs1 As New ADODB.Recordset
    Dim rs2 As New ADODB.Recordset
    Dim strSQL As String

    Dim intUserid As Integer

    cn.ConnectionString = CurrentProject.Connection
    cn.Open


    strSQL = "select user_id from tblusers where sign_on ='" & fOSUserName() & "';"
    rs1.Open strSQL, cn
    intUserid = rs1!user_id


    strSQL = Null

    strSQL = "select * from tblstatistics where user_id = " & intUserid & " and work_date = '" & [WORK_DATE] & "' and system_id = " & [SYSTEM_ID] & ";"
    rs2.Open strSQL, cn

    If rs2.RecordCount <> 0 Then
    MsgBox "You have already entered in a record for this day and hospital system. No action taken.", , "Duplicate Record"
    rs1.Close
    rs2.Close
    cn.Close
    Set rs1 = Nothing
    Set rs2 = Nothing
    Set cn = Nothing

    Exit Sub
    End If


    strSQL = Null

    strSQL = "insert into tblstatistics(WORK_DATE,USER_ID,SYSTEM_ID,SLA_RECE IVED,SLA_PROCESSED,SLA_PENDING,PRICE_DISCREPANCIES ,CONTRACT_REVISIONS,CONTRACT_PRICE_UPDATES,PRICE_T APE_ITEMCOUNT,EDI_ACCOUNT_CHANGES,CONTRACT_CONVERS IONS,REPORT_REQUESTS,OTHER_MAINTENANCE,SPECIAL_PRO JECTS_REC,SPECIAL_PROJECTS_PROC,COMMENTS,DATE_CREA TED) " & _
    "values([WORK_DATE]," & intUserid & ",[SYSTEM_ID],[SLA_RECEIVED],[SLA_PROCESSED],[SLA_PENDING],[PRICE_DISCREPANCIES]," & _
    "[CONTRACT_REVISIONS],[CONTRACT_PRICE_UPDATES],[PRICE_TAPE_ITEMCOUNT],[EDI_ACCOUNT_CHANGES],[CONTRACT_CONVERSIONS]," & _
    "[REPORT_REQUESTS],[OTHER_MAINTENANCE],[SPECIAL_PROJECTS_REC],[SPECIAL_PROJECTS_PROC],[COMMENTS],now())"

    cn.Execute strSQL

    MsgBox "The record has been added", , "Success!"



    Exit_Add_Record_Click:
    rs1.Close
    rs2.Close
    cn.Close
    Set rs1 = Nothing
    Set rs2 = Nothing
    Set cn = Nothing
    Exit Sub

    Err_Add_Record_Click:
    MsgBox "The Record was not added", , "Operation Cancelled"
    Resume Exit_Add_Record_Click


    End Sub

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You'll probably get better and more prompt answers in the access forum...

    Could you provide more detail about the errors you're receiving and which line is tripping them?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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