Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Error 3704 during Access FE to SQL Server BE

Posted on 2004-08-19
4
Medium Priority
?
194 Views
Last Modified: 2013-12-25
I have an access form that I am using to reschedule an appointment. When the user clicks on the reschedule button it is supposed to create a new record inserting the data from the old record, and then move the current record to a history table. When it executes this code I am getting Error 3704
I know this has something to do with the way SQL Server handles new records however I can't figure it out.

Private Sub cmdFrmResched_Click()
On Error GoTo Err_cmdFrmResched_Click

    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim cnndb   As Connection
    Dim rstAppt As ADODB.Recordset
    Dim rstRes  As ADODB.Recordset
    Dim intApptID   As Integer
    Dim intPO As Integer
   
   
    Set cnndb = Application.CurrentProject.Connection
    Set rstAppt = New ADODB.Recordset
    Set rstRes = New ADODB.Recordset
   
    rstAppt.Open "tblAppt", cnndb, adOpenForwardOnly, adLockPessimistic
    rstRes.Open "tblApptResched", cnndb, adOpenForwardOnly, adLockPessimistic
   
    intApptID = Me!ApptID
   
    rstAppt.Find "ApptID = " & Me!ApptID
    With rstAppt
        .AddNew   <<<-------------------------------Seems to bomb here and go to the error handler and loop
        !ApptCont = Me.ApptCont
        .Update
    End With
   
    With rstRes
        .AddNew
        !ApptID = Me!ApptID
        !NewID = rstAppt.Fields("ApptID")
        .Update
    End With
   
    'move appointment to Rescheduled
    Me.Status = "R"
   
    DoCmd.RunCommand acCmdSaveRecord
   
    DoCmd.SetWarnings False
   
    'append the "R" and "C" records to history
    DoCmd.OpenQuery "qryApptHistoryR"
   
    'reassign PO records to new appt id
    DoCmd.RunSQL "UPDATE tblApptPO SET tblApptPO.ApptID = " _
        & rstAppt.Fields("ApptID") & " WHERE tblApptPO.ApptID= " & Me!ApptID

    'update Locations to new appt
    DoCmd.RunSQL "UPDATE tblLoc SET tblLoc.ApptID = " _
        & rstAppt.Fields("ApptID") & " WHERE tblLoc.ApptID= " & Me!ApptID
   
    'Delete the appointment from Current
    DoCmd.RunSQL "DELETE tblAppt.* FROM tblAppt " _
        & "Where tblAppt.ApptID = " & Me!ApptID
       
    DoCmd.SetWarnings True
       
    stDocName = "frmApptResch"
    stLinkCriteria = "[ApptID] = " & rstAppt.Fields("ApptID")
    DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormEdit

    DoCmd.Close acForm, "frmAppt"
   
Exit_cmdFrmResched_Click:

    rstAppt.Close
    rstRes.Close
    Exit Sub

Err_cmdFrmResched_Click:
    MsgBox Err.Number & " " & Err.Description
    Resume Exit_cmdFrmResched_Click
   
End Sub



Please Help!!
0
Comment
Question by:infutech
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 8

Expert Comment

by:mladenovicz
ID: 11842670
Try to open RS in this way

 rstAppt.Open "tblAppt", cnndb, adOpenDynamic, adLockOptimistic
0
 

Author Comment

by:infutech
ID: 11842883
Thanks I tried that and...
I get an Microsoft Access Msg Box stating: -2147217885 Record is deleted   Followed by 3219 Operation is not allowed in this context. Ending with 3704 in a continuous loop.
This process worked fine when the Db was native Access.
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 11844563
What is you cursor location on the connection object?

Leon
0
 
LVL 29

Accepted Solution

by:
leonstryker earned 2000 total points
ID: 11844640
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question