Run-time error

Are you trying to use @@Identity with Microsoft Access 97. If yes then it wont work with 97 but works with Access 2000 or later.

If you have a table which has an identity field contained within it such as a unique row identifier, you will get this message if you attempt to specify a value for this field in an insert or update statement.

There are basically two ways to resolve the issue;

1) Don't try to set a value for this field yourself, let SQL do it:

  Insert Into MyTable (Field1,Field2) Values('MyValue1',100)

Where MyTable has three fields:

KeyField int Identity(1,1)
Field1 varchar(200)
Field2 int

2) If you must specify the value yourself for some reason:

  Set IDENTITY_INSERT MyTable ON
  Insert Into MyTable (KeyField,Field1,Field2) Values(1234,'MyValue1',100)
  Set IDENTITY_INSERT MyTable OFF

In your SQL batch.

Sorry for being really dense but I don't really have any experience in this area.  (Deep end and dropped !)

What I have found out so far is that one user has this problem on a test machine.

(It works fine for me)

They are trying to save an application form which has been generated using forms produced within a VB6 application which links to a DBO on a SQL Server.

The database (applicationform.dbo) has a number of fields.
One of which is called applicationformid which is listed as the identity field in database properties.

I run on Access 97 on an XP machine - No problem
I believe the user is running on Access 97 on an NT machine.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_7zas.asp

Above site says :

At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, Microsoft® SQL Server™ returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for.

I tried opening several instances of the program to see if that might have caused the problem of a SET IDENTITY_INSERT ON but it worked fine.  Searched through the VB code to see if I could identify at which point the SET was happening but couldn't see any mention of it. ?

Ok, do you have the code for the area around where the problem occurs?

Sorry for being really dense but I don't really have any experience in this area.  (Deep end and dropped !)

What I have found out so far is that one user has this problem on a test machine.

(It works fine for me)

They are trying to save an application form which has been generated using forms produced within a VB6 application which links to a DBO on a SQL Server.

The database (applicationform.dbo) has a number of fields.
One of which is called applicationformid which is listed as the identity field in database properties.

I run on Access 97 on an XP machine - No problem
I believe the user is running on Access 97 on an NT machine.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_7zas.asp

Above site says :

At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, Microsoft® SQL Server™ returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for.

I tried opening several instances of the program to see if that might have caused the problem of a SET IDENTITY_INSERT ON but it worked fine.  Searched through the VB code to see if I could identify at which point the SET was happening but couldn't see any mention of it. ?

Tim - I believe this is the code that is running.  The msg appears when the user tries to save the record.

Private Sub cmdSaveApplicationform_Click()

    Dim blnNewCandidate                         As Boolean
    Dim blnDBUpdateOK                           As Boolean
    Dim blnNonUKCitizen                         As Boolean
    Dim blnLegalResident                        As Boolean
    Dim blnImmigrationControl                   As Boolean
    Dim blnUKResidentRestriction                As Boolean
    Dim blnEnglishWelshFluent                   As Boolean
   
    Dim blnAbortSave                            As Boolean
    Dim blnValidated                            As Boolean
    Dim blnGuaranteedInterview                  As Boolean
    Dim dtmDateOfBirth                          As Date
    Dim dtmReceivedDate                         As Date
    Dim dtmAdvertDate                           As Date
    Dim intButton                               As Integer
    Dim intTab                                  As Integer
    Dim intThisTab                              As Integer
    Dim intCount                                As Integer
    Dim intIndex                                As Integer
    Dim intSalutation                           As Integer
    Dim intCandidateid                          As Integer
    Dim intNationalityAtBirth                   As Integer
    Dim intNationalityNow                       As Integer
    Dim intWelshAbility                         As Integer
    Dim intPreferredInterviewLanguage           As Integer
    Dim intSource                               As Integer
    Dim strCandidateRef                         As String
    Dim strNextReference                        As String
    Dim strKeyReference                         As String
    Dim strReference                            As String
    Dim strSalutation                           As String
    Dim strFirstName                            As String
    Dim strMiddleName                           As String
    Dim strSurname                              As String
    Dim strNINumber                             As String
    Dim strErrMess                              As String
    Dim strHomePhone                            As String
    Dim strBusinessPhone                        As String
    Dim strMobilePhone                          As String
    Dim strEmail                                As String
    Dim strNonUKCitizenComment                  As String
    Dim strImmigrationControlComment            As String
    Dim strUKResidenceRestrictionComment        As String
    Dim strEmploymentGapComment                 As String
    Dim strInterviewRequirement                 As String
    Dim strSourceName                           As String
    Dim strOtherComments                        As String
    Dim strMessage                              As String
   
   
    blnAbortSave = False
    blnValidated = False
    intThisTab = tabApplicationForm.Tab
    validateTab intThisTab, False, strErrMess
   
    intTab = 0
    While (Not blnValidated) And (Not blnAbortSave)
        If strErrMess <> "" Then
            intButton = MsgBox(strErrMess, _
                        vbExclamation, _
                        "Application Form")
            mblnValidationCausedSwitch = True
            tabApplicationForm.Tab = intTab
            mblnValidationCausedSwitch = False
            blnAbortSave = True
        Else
            validateTab intTab, False, strErrMess
            If strErrMess = "" Then
                If intTab = 9 Then
                    blnValidated = True
                End If
                intTab = intTab + 1
            End If
        End If
    Wend
       
    If blnValidated Then
        blnNewCandidate = False
       
        If txtCandidateReference.Text = "" Then
            mclsCandidate.fetchNextCandidateReference strNextReference, _
                                                        intCount
            blnNewCandidate = True
        Else
            strReference = txtCandidateReference.Text
        End If
       
        blnDBUpdateOK = True
'
' Retrieve Candidate information from form
'
        intIndex = cboSalutation.ListIndex
        mclsSalutation.returnIDfromComboIndex intIndex, intSalutation
        strFirstName = txtFirstName.Text
        strMiddleName = txtMiddleName.Text
        strSurname = txtSurname.Text
        strNINumber = txtNINumber.Text
        If txtDateOfBirth.Text <> "" Then
            dtmDateOfBirth = txtDateOfBirth.Text
        End If
'
' Retrieve ApplicationForm information from form
'
        strHomePhone = txtHomePhone.Text
        strBusinessPhone = txtBusinessPhone.Text
        strMobilePhone = txtMobilePhone.Text
        strEmail = txtEmail.Text
        intNationalityAtBirth = _
            mclsNationalityAtBirth.returnIDfromComboText(cboNationalityAtBirth)
        intNationalityNow = _
            mclsNationalityNow.returnIDfromComboText(cboNationalityNow)
        blnNonUKCitizen = checkBoxBoolean(chkNonUKCitizen)
        strNonUKCitizenComment = txtNonUKCitizen.Text
        blnLegalResident = checkBoxBoolean(chkLegalResident)
        blnImmigrationControl = checkBoxBoolean(chkImmigrationControl)
        strImmigrationControlComment = txtImmigrationControl.Text
        blnUKResidentRestriction = checkBoxBoolean(chkUKResidenceRestriction)
        strUKResidenceRestrictionComment = txtUKResidenceRestriction
        strEmploymentGapComment = txtEmploymentGapComments.Text
        blnEnglishWelshFluent = checkBoxBoolean(chkEnglishWelshFluent)
        intWelshAbility = mclsWelshAbility.returnIDfromComboText(cboWelshAbility)
        If cboInterviewLanguage.Text = "" Then
            cboInterviewLanguage.Text = "English"
        End If
        intPreferredInterviewLanguage = _
            mclsInterviewLanguage.returnIDfromComboText(cboInterviewLanguage)
        blnGuaranteedInterview = checkBoxBoolean(chkGuaranteedInterview)
        strInterviewRequirement = txtInterviewRequirements.Text
        dtmReceivedDate = txtReceivedDate.Text
        intSource = mclsSource.returnIDfromComboText(cboSource)
        strSourceName = cboSourceName.Text
        If txtAdvertDate.Text <> "" Then
            dtmAdvertDate = txtAdvertDate.Text
        End If
        strOtherComments = txtOtherInf.Text
'
' If its a new candidate Insert Candidate and Application form
' Otherwise Update both.
'
'        MsgBox "Salutation           " & intSalutation & _
'                "Nationality at Birth " & intNationalityAtBirth & _
'                "Nationality Now      " & intNationalityNow
                       
        gclsDBControl.beginTransaction
        If blnNewCandidate Then
            intCandidateid = mclsCandidate.insertCandidate(strNextReference, _
                                                             intSalutation, _
                                                             strFirstName, _
                                                             strMiddleName, _
                                                             strSurname, _
                                                             strNINumber, _
                                                             dtmDateOfBirth, _
                                                             intCount)
            gintApplicationFormID = mclsApplicationForm.insertApplicationForm( _
                                            intCandidateid, strHomePhone, _
                                            strBusinessPhone, _
                                            strMobilePhone, _
                                            strEmail, _
                                            intNationalityAtBirth, _
                                            intNationalityNow, _
                                            blnNonUKCitizen, _
                                            strNonUKCitizenComment, _
                                            blnLegalResident, _
                                            blnImmigrationControl, _
                                            strImmigrationControlComment, _
                                            blnUKResidentRestriction, _
                                            strUKResidenceRestrictionComment, _
                                            strEmploymentGapComment, _
                                            blnEnglishWelshFluent, _
                                            intWelshAbility, _
                                            intPreferredInterviewLanguage, _
                                            blnGuaranteedInterview, _
                                            strInterviewRequirement, _
                                            dtmReceivedDate, _
                                            intSource, _
                                            strSourceName, _
                                            dtmAdvertDate, strOtherComments, _
                                            intCount)
                   
        Else
            strKeyReference = txtCandidateReference.Text
            intCandidateid = mclsCandidate.updateCandidate(strKeyReference, _
                                                            intSalutation, _
                                                            strFirstName, _
                                                            strMiddleName, _
                                                            strSurname, _
                                                            strNINumber, _
                                                            dtmDateOfBirth, _
                                                            intCount)
            mclsApplicationForm.updateApplicationForm gintApplicationFormID, intCandidateid, _
                                                        strHomePhone, _
                                                        strBusinessPhone, _
                                                        strMobilePhone, _
                                                        strEmail, _
                                                        intNationalityAtBirth, _
                                                        intNationalityNow, _
                                                        blnNonUKCitizen, _
                                                        strNonUKCitizenComment, _
                                                        blnLegalResident, _
                                                        blnImmigrationControl, _
                                                        strImmigrationControlComment, _
                                                        blnUKResidentRestriction, _
                                                        strUKResidenceRestrictionComment, _
                                                        strEmploymentGapComment, _
                                                        blnEnglishWelshFluent, _
                                                        intWelshAbility, _
                                                        intPreferredInterviewLanguage, _
                                                        blnGuaranteedInterview, _
                                                        strInterviewRequirement, _
                                                        dtmReceivedDate, _
                                                        intSource, _
                                                        strSourceName, _
                                                        dtmAdvertDate, strOtherComments, _
                                                        intCount
        End If
       
        If blnDBUpdateOK Then
            blnDBUpdateOK = False
            If mclsAddress.updateDBfromFlex(msgAddress) Then
                If mclsLanguageAbility.updateDBfromFlex(msgLanguages) Then
                    If mclsPreviousEmployment.updateDBfromFlex(msgPreviousEmployment) Then
                        If mclsProfQualifications.updateDBfromFlex(msgProfQuals) Then
                            If mclsQualifications.updateDBfromFlex(msgEduQuals) Then
                                If mclsReferee.updateDBfromFlex(msgReferees) Then
                                    If mclsRelation.updateDBfromFlex(msgRelations) Then
                                        If mclsVacancyApplication.updateDBfromFlex _
                                                                    (msgVacancies) Then
                                            blnDBUpdateOK = True
                                        End If
                                    End If
                                End If
                            End If
                        End If
                    End If
                End If
            End If
        End If
        If blnDBUpdateOK Then
            gclsDBControl.commitTransaction
            If blnNewCandidate Then
                strMessage = "Candidate has been added to the database." + _
                            vbCrLf + vbCrLf + _
                            "Assigned Candidate Reference Number: " + _
                            strNextReference + vbCrLf + vbCrLf + _
                            "Do you want to enter/modify Equal Opportunity data now"
            Else
                strMessage = "Candidate/Application form updated succesfully" + _
                            vbCrLf + vbCrLf + _
                            "Do you want to enter/modify Equal Opportunity data now"
            End If
            intButton = MsgBox(strMessage, _
                        vbYesNo, "Application Form")
            If intButton = vbYes Then
                    openForm frmEqualOpportunities, False
            End If
'            clearApplicationForm
        Else
            gclsDBControl.rollbackTransaction
            MsgBox "Database error prevented changes from being applied", _
                    vbExclamation + vbOKOnly, "ApplicationForm"
        End If
           
    End If
   
End Sub

ScoobyTwo:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
Experts: Post your closing recommendations!  Who deserves points here?

ScoobyTwo, an EE Moderator will handle this for you.
Moderator, my recommended disposition is:

    Refund points and save as a 0-pt PAQ.

DanRollins -- EE database cleanup volunteer