Creating Primary Key value when adding a new record

[titobudd]

Hello,

I have a table with a primary key field (long integer) that is indexed, and is set to not allow duplicates. I am currently using DMax to identify the highest PK value and am adding 1 to this number. When I try to update the field with this number when adding a new record, I get a run time error stating I would end up with a duplicate value in the PK field, even though the number is unique. I am not sure what I am doing wrong. Any feedback is appreciated.

Thanks.

 

[MarkK]

Are you using transactions? Note that domain aggregate functions won't see changes to data inside transactions until those transactions are committed.

 

[titobudd]

Are you using transactions? Note that domain aggregate functions won't see changes to data inside transactions until those transactions are committed.

I am afraid I don't know what you mean, which probably means no. I will post my code. Perhaps that will reveal something useful:

Public Sub ADD_Condition()

Dim maxOID As Long
Dim cboSupportRatingTxt As String
Dim cboSignRatingTxt As String
Dim cboVisibilityTxt As String
Dim SignMainGeneralOID As Long
Dim SignInspectionsOID As Long

maxOID = DMax("[SignInspectionsOID]", "SignInspections")

Debug.Print maxOID

sql = "SELECT SignInspections.[InspectionDate], SignInspections.[SupportRating], SignInspections.[SignRating], SignInspections.[Visibility], SignInspections.[cgLastModified], SignInspections.[SignInspectionsOID], SignMainGeneral.[SignMainGeneralOID] " & _
      "FROM SignInspections " & _
      "INNER JOIN SignMainGeneral ON SignInspections.[SignMainGeneralOID] = SignMainGeneral.[SignMainGeneralOID] " & _
      "WHERE SignMainGeneral.[ID] = '" & Me.cboID & "'" & _
      "ORDER BY SignInspections.[SignInspectionsOID]DESC"



'Assign updatable cursor and lock type properties.
rs.CursorType = adOpenDynamic
rs.LockType = adLockOptimistic

rs.Open sql, localConnection, , , adCmdText

'Set variables to combo box text
Me.cboSupportRating.SetFocus
cboSupportRatingTxt = Me.cboSupportRating.Text

Me.cboSignRating.SetFocus
cboSignRatingTxt = Me.cboSignRating.Text

Me.cboVisibility.SetFocus
cboVisibilityTxt = Me.cboVisibility.Text

SignMainGeneralOID = rs!SignMainGeneralOID

SignInspectionsOID = maxOID + 1

Debug.Print SignInspectionsOID

If rs.EOF = False Then
    'Update the field for the selected record
    'rs.MoveFirst
    
    With rs
        .AddNew
        .Fields.Item("SignMainGeneralOID") = SignMainGeneralOID
        .Fields.Item("InspectionDate") = Me.txtInspectionDate
        .Fields.Item("SupportRating") = cboSupportRatingTxt
        .Fields.Item("SignRating") = cboSignRatingTxt
        .Fields.Item("Visibility") = cboVisibilityTxt
        .Fields.Item("cgLastModified") = Now
        .Fields.Item("SignInspectionsOID") = SignInspectionsOID
        .Update
    End With
Me.cmdAddCondition.SetFocus
MsgBox "New Condition Record Added for sign with ID " & Me.cboID.Value & ".", vbInformation, "Record Added"
Else
    MsgBox "That sign ID does not match any current records.", vbExclamation
Me.cboID.SetFocus
rs.Close
    
Set rs = Nothing


Exit Sub
End If


rs.Requery
rs.Close

Set rs = Nothing

End Sub

Thanks.

(Now that I have read up on transactions, I can say that I am not using them.)

 

[MarkK]

I've refactored your code as follows...

Public Sub ADD_Condition()
   Dim rs As New ADODB.Recordset
   
   rs.Open _
      "SELECT SignMainGeneralOID, InspectionDate, SupportRating, SignRating, Visibility, cgLastModified, SignInspectionsOID FROM SignInspections", _
      CurrentProject.Connection, adOpenDynamic, adLockOptimistic
   With rs
      .AddNew
      .Fields("SignMainGeneralOID") = Me.cboID
      .Fields("InspectionDate") = Me.txtInspectionDate
      .Fields("SupportRating") = Me.cboSupportRatingTxt
      .Fields("SignRating") = Me.cboSignRatingTxt
      .Fields("Visibility") = Me.cboVisibilityTxt
      .Fields("cgLastModified") = Now
      .Fields("SignInspectionsOID") = DMax("SignInspectionsOID", "SignInspections") + 1
      .Update
      .Close
   End With
    
   Set rs = Nothing
End Sub

There are a bunch of steps you don't need to take. You don't need to setfocus and then read the text property of a control. The value property is available by default, so I've removed that code and the variables you used temporarily. You can assign directly from the controls value to the new record in the recordset.
I would not add a record to a recordset based on more than one table. There's too much room for confusion about what gets added in that context. Add record to one table at a time.
Note that the DMax() is run at the exact time the record is updated.
The code I posted does not contain an if block. Write a new procedure that checks whether the addition should occur. Call that routine first and if the addition should occur, that routine should call the 'addrecord' routine. That kind of modularization will enormously simplify your work. Consider ...

Sub AddRecordController
  If SomeAddConditionIsTrue Then 
    Add_Condition
  Else
    MsgBox "That sign ID does not match any current records.", vbExclamation
  End If
End Sub

Function SomeAddConditionIsTrue as Boolean
[COLOR="Green"]  'this routine does whatever is required to validate the addition.
  'check that all data is present and within acceptable ranges
  'return true if the addition may proceed.
[/COLOR]  SomeAddConditionIsTrue = True
End Function

Does that make sense?
Cheers,
Mark

 

[titobudd]

Yes. That makes sense. Please give me a moment to digest and test. Thank you.

 

[titobudd]

So I took this bit of code from your revisions:

rs.Open _
       "SELECT SignMainGeneralOID, InspectionDate, SupportRating,  SignRating, Visibility, cgLastModified, SignInspectionsOID FROM  SignInspections", _
       CurrentProject.Connection, adOpenDynamic, adLockOptimistic

I commented out everything I had regarding my original sql query. I tested and it worked! There was obviously an issue with my query.

I will be adding the conditional bit you recommended also. Thank you for your feedback!