Runtime Error 3197- ..... Same data at the same time

[Synergy]

I have an unbound form to add new inventory items to a table. With the
Access Backend this was fine. Now the backend is SQL Server. The Field
[invID] was orignally a Primary Key/Auto Number field in Access. It is now
an Int field. I also added a field sqlID as a Primary Key Identity Insert
field. Inventory is a linked SQL Server table.

My code adds a new record, assigns a tempoary number to the part number
field, then does a DAO Findfirst to pull up the new record for editing.
Then a new mart number is generated and update. This update fails with the
message:

Runtime error '3197':
The Microsoft Jet database engine stopped the process becuase you and
another user are attempting to change the same data at the same time.

This is not the case. Also when I open the table in Access and attempt to
Delete the new records, I get the same message message when I press the
Delete Key, without the error Number. I can however Delete the records
through Enterprise manager.

Here is my code. The last line, .Update is where it fails.


***** Start Code

Dim db As Database
Dim rs As Recordset
Dim ctl As Control
Dim criteria As String
Set db = CurrentDb
Set rs = db.OpenRecordset("Inventory", dbOpenDynaset, dbSeeChanges)

'if Part Number is entered manually, the a check must be made for an
existing value
If [EnterPartNumber] = True Then
criteria = "[InvPartNumber] = '" & [invPartNumber] & "'"
rs.FindFirst criteria
If Not rs.NoMatch Then 'Duplicate Part number exists
MsgBox "The Part number that you entered already exists in Inventory.
Choose another!", , PTitle
[invPartNumber].SetFocus
Exit Sub
End If
End If

'Check for Null values in (Required) fields!
'Required Fields have a Tag property set to DataReq
With rs
For Each ctl In Me.Controls
If Left(ctl.Tag, 7) = "DataReq" Then
'.Fields(ctl.Name) = ctl.Value
If IsNull(ctl) Or ctl = "" And ctl.Enabled = True Then
MsgBox "You must enter a value for " & Mid(ctl.Name, 4,
Len(ctl.Name) - 3), , "Value Missing"
ctl.SetFocus
Exit Sub
End If
End If
Next
End With

' vvvvvvv Part Number Check goes here vvvvvvvvv

'[invPartNumber] is a special case and needs to be checked based on whether
'the system is to generate the number or the user manually inputs it.
' ^^^^^^^ Part Number Check goes here ^^^^^^^^^

'Enter new record into Inventory table
'First check for existing Record according to Maufacturer, Type and H1 - H2
fields!
criteria = "[invManufacturer] = '" & [invManufacturer] & "'"
criteria = criteria & " and [invType] = '" & [invType] & "'"
If invModel <> "" Then
criteria = criteria & " and [invModel] = '" & [invModel] & "'"
End If
If invH1 <> "" Then
criteria = criteria & " and [invH1] = '" & [invH1] & "'"
End If
If invH2 <> "" Then
criteria = criteria & " and [invH2] = '" & [invH2] & "'"
End If
If invH3 <> "" Then
criteria = criteria & " and [invH3] = '" & [invH3] & "'"
End If
If invH4 <> "" Then
criteria = criteria & " and [invH4] = '" & [invH4] & "'"
End If
If invH5 <> "" Then
criteria = criteria & " and [invH5] = '" & [invH5] & "'"
End If
If invH6 <> "" Then
criteria = criteria & " and [invH6] = '" & [invH6] & "'"
End If

rs.FindFirst criteria
'>>> Start of No Match section
If rs.NoMatch Then
With rs
.AddNew
For Each ctl In Me.Controls
If Left(ctl.Tag, 4) = "Data" Then
If ctl.Enabled = True Then
.Fields(ctl.Name) = ctl.Value
End If
End If
Next
'[invPartNumber] has to be handled seperately, because it is partially
generated
'using the autonumber [invID].
'This cannot be derived until the the record is created in the table
If [EnterPartNumber] = False Then
Dim varPartNumber As String
Dim varDeptCat As String
varDeptCat = [invDept].Column(1) & [invCategory]
'Assign random number to Partnumber to identify the record in order to
find it
Randomize
varPartNumber = "Temp Part" & Int((10000 * Rnd) + 1) 'Assign a random
number between 1 and 10000
rs![invPartNumber] = varPartNumber
End If

'Assign InvID 'Since changing to SQL Server, Autonumbers don't work in
DAO
rs![invID] = DMax("[invID]", "Inventory") + 1

'Save the record
.Update
End With

'Find New Record (by Temporary Part Entry) then reassign generated Part
number
criteria = "[InvPartNumber] = '" & varPartNumber & "'"
rs.FindFirst criteria
rs.Edit
'Edit Part number with System Generated Value

rs![invPartNumber] = varDeptCat & "." & rs![invID]
varPartNumber = rs![invPartNumber]
'Check the 4 positions after the Dot (.)and place leading zeros when Lenth
< 4
Select Case Len(Mid([varPartNumber], 6, 4))
Case 1
[varPartNumber] = Left([varPartNumber], 5) & "000" &
Mid([varPartNumber], 6, 4)
Case 2
[varPartNumber] = Left([varPartNumber], 5) & "00" &
Mid([varPartNumber], 6, 4)
Case 3
[varPartNumber] = Left([varPartNumber], 5) & "0" &
Mid([varPartNumber], 6, 4)
End Select
rs![invPartNumber] = varPartNumber

rs.Update

***** End Code


rs![invPartNumber] has been generated correctly, which is the purpose of the
update.

Thanks for any help.

Mark A. Sam

 

[Synergy]

Forget the code that I posted on my initial post. The problem is basically
this. Using the DAO Addnew method, will create records which cannot be
edited or deleted in Access.

I can however edit and delete any other records as well as add new records
from the table view, which can be edited or deleted.

I tested another table and had no problem adding or editing or deleting.
There is something about the table that is the problem. Is there such as
thing as corruption in SQL Server?

God Bless,

Mark A. Sam