Solved

Access form with autonumber not working after migration to SQl Server

Posted on 2004-08-05
4
433 Views
Last Modified: 2013-12-25
I have an access front end that has forms that use autonumbers to create appointments. After we moved the backend from access to sql server the form errors out whenever I try to enter the data. The error is:
ODBC --Call failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot inser duplicate key row in object 'tblAppt' with unique index 'PONo'.(#2601)
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.(#3621)

Here is the code behind the form:
Option Compare Database
Option Explicit


Private Sub ApptCont_BeforeUpdate(Cancel As Integer)
'requery the drop down

Me!ApptCont.Requery

End Sub

Private Sub ApptCont_DblClick(Cancel As Integer)
'Update the Contacts table

Dim intRec  As Integer
Dim rs As Object

intRec = Me!ApptID
DoCmd.OpenForm "frmApptCont", acNormal

Me.Requery

Set rs = Me.Recordset.Clone
rs.FindFirst "[ApptID] = " & intRec
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Me.ApptCont.Requery
Me!ApptCont.SetFocus

End Sub

Private Sub ApptDate_AfterUpdate()
Dim strName As String
   
    If IsLoaded("frmMenuMain") Then
       
        Me!User = Forms!frmMenuMain!txtUser
       
    Else
   
        MsgBox "Cannot create appointment.  See DSM Admin.", vbCritical, "Error"
        DoCmd.Close acForm, "frmAppt"
       
    End If
   
    If Me!ApptDate < Date Then
   
        MsgBox "Scheduling appointment in the past.", vbCritical, _
            "Cannot continue"
       
        Me!ApptDate = Null
        Me!ApptTime.SetFocus
        Me!ApptDate.SetFocus
       
    End If
End Sub

Private Sub cboFindID_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[ApptID] = " & str(Nz(Me![cboFindID], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

Private Sub cmdAdd_Click()
On Error GoTo Err_cmdAdd_Click


    DoCmd.GoToRecord , , acNewRec
    Me.ApptDate.SetFocus

Exit_cmdAdd_Click:
    Exit Sub

Err_cmdAdd_Click:
    MsgBox Err.Description
    Resume Exit_cmdAdd_Click
   
End Sub
Private Sub cmdDelete_Click()
On Error GoTo Err_cmdDelete_Click


    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_cmdDelete_Click:
    Exit Sub

Err_cmdDelete_Click:
    MsgBox Err.Description
    Resume Exit_cmdDelete_Click
   
End Sub
Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click


    DoCmd.Close
    If Err = 2501 Then Resume Next

Exit_cmdClose_Click:
    Exit Sub

Err_cmdClose_Click:
    MsgBox Err.Description
    Resume Exit_cmdClose_Click
   
End Sub

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
   
    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
   
    With rstAppt
        .AddNew
        !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


Private Sub Form_Current()

Me.cboFindID = Me.ApptID

End Sub
Private Sub cmdPrtPULog_Click()
On Error GoTo Err_cmdPrtPULog_Click

    Dim stDocName As String
   
    'appt status must not be opened
   
    If Me!Status <> "O" Then
   
        stDocName = "rptApptPULog"
        DoCmd.OpenReport stDocName, acNormal
       
    Else
   
        MsgBox "Print via Pick Up Log dialog box.", _
            vbInformation, "Appointment Open"
       
        DoCmd.OpenForm "frmApptPrtLog"
       
        DoCmd.Close acForm, "frmAppt"

    End If
   
Exit_cmdPrtPULog_Click:
    Exit Sub

Err_cmdPrtPULog_Click:
    MsgBox Err.Description
    Resume Exit_cmdPrtPULog_Click
   
End Sub


Any help would be appreciated.
0
Comment
Question by:infutech
  • 2
  • 2
4 Comments
 
LVL 5

Accepted Solution

by:
brianb99999 earned 500 total points
ID: 11731696
I don't think the problem is in your form, I think its in SQL.
Open the table you are trying to access in SQL Server.
Go to what used to be the autonumber field, make sure its datatype is numeric, length is 9, precision is 18 and Identity = Yes (Not for Replication) and Identity Seed is 1 and Identity increment is 1.

Brian.
0
 

Author Comment

by:infutech
ID: 11735572
Thanks for the suggestions. I made those changes and it still reported the same error. I am thinking that it writes the ApptID to the record in the table and then it tries to write to it again and it won't let it. It worked fine in Access and now it is erroring out. This is the only form that doesn't work.
0
 

Author Comment

by:infutech
ID: 11736554
Can anyone see problems in this subform code? It looks like the error is occuring when this subForm is called.

Option Compare Database

Private Sub cmdAdd_Click()
On Error GoTo Err_cmdAdd_Click


    DoCmd.GoToRecord , , acNewRec

Exit_cmdAdd_Click:
    Exit Sub

Err_cmdAdd_Click:
    MsgBox Err.Description
    Resume Exit_cmdAdd_Click
   
End Sub
Private Sub cmdDelete_Click()
On Error GoTo Err_cmdDelete_Click


    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_cmdDelete_Click:
    Exit Sub

Err_cmdDelete_Click:
    MsgBox Err.Description
    Resume Exit_cmdDelete_Click
   
End Sub

Private Sub PO_AfterUpdate()
On Error GoTo Err_Handle
'update the appt id in the location table

Dim cnnDB       As ADODB.Connection
Dim rstLoc      As ADODB.Recordset
Dim rstAppt     As ADODB.Recordset
Dim intDelete   As Integer

'open recordset
Set cnnDB = Application.CurrentProject.Connection
Set rstLoc = New ADODB.Recordset
Set rstAppt = New ADODB.Recordset
rstLoc.Open "tblLoc", cnnDB, adOpenForwardOnly, adLockPessimistic
rstAppt.Open "tblAppt", cnnDB, adOpenForwardOnly, adLockPessimistic

'locate the PO/location and update apptid
rstLoc.Find "PO = " & Me!PO

If IsNull(rstLoc.Fields("ApptID")) Then

    'update location and allow scheduling
    With rstLoc
        .Fields("ApptID") = Forms!frmAppt!ApptID
        .Update
    End With
   
Else
   
    'determine if PO is valid
    rstAppt.Find "ApptID =" & rstLoc.Fields("ApptID")
   
    'is there another appointment for the PO
    If rstAppt.Fields("Status") = "O" Then
   
        MsgBox "There is a pending Appointment for this PO: " _
            & rstLoc.Fields("ApptID"), vbInformation, "Cannot Process"
        intDelete = 1
        GoTo GetOut
       
    Else
   
        'update location and continue with scheduling
        With rstLoc
            .Fields("ApptID") = Forms!frmAppt!ApptID
            .Update
        End With
       
    End If

End If

GetOut:

    rstLoc.Close
    rstAppt.Close
   
    If intDelete = 1 Then
        Me.PO = 10000
        Me.PO.SetFocus
    End If
   
    Exit Sub
   
Err_Handle:
    If Err.Number = -2147217887 Then
        MsgBox "This PO is already allocated to an appointment.", _
            vbCritical, "Appointment Not Updated"
        intDelete = 1
        Resume GetOut
    ElseIf Err.Number = 3021 Then
        Resume Next
    Else
        MsgBox Err.Number & ": " & Err.Description
        Resume GetOut
    End If
   
End Sub

Private Sub PO_Exit(Cancel As Integer)
On Error GoTo Err_Handle

If Me.PO.Value = 10000 Then
    MsgBox "Please update PO number to a valid entry."
    DoCmd.CancelEvent
End If

GetOut:
Exit Sub

Err_Handle:
MsgBox Err.Number & ": " & Err.Description
Resume GetOut

End Sub


Private Sub TIP_DblClick(Cancel As Integer)
On Error GoTo Err_TIP_DblClick
   
    Const cQuote = """"  'Thats two quotes
   
    Me!TIP.DefaultValue = cQuote & Me!TIP.Value & cQuote

Exit_TIP_DblClick:
    Exit Sub

Err_TIP_DblClick:
    MsgBox Err.Description
    Resume Exit_TIP_DblClick
   
End Sub
0
 
LVL 5

Expert Comment

by:brianb99999
ID: 11748879
If ApptID is your unique autonumber key then you shouldn't be updating it manually - SQL will automatically increment it when you save the rest of a record.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

762 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now