Junction Tables and 3164 Errors

[pdevito3]

Hi all,

I have an events table and a junction table that has a composite key with two fields; those are the only fields on the junction table and are FKs in my events table.

I have a form that fills in events to my events table and also fills in values to my junction table. The control source is a query of the two tables along with a couple of other reference tables for metadata associations. The form works fine... the first time. My problem is that, after I have entered an event that has my unique composite key for the first time (thereby entering a new record to my junction table), once I go to enter a second event that uses the same key, access tries to enter the record again and throwing a "3164: field cannot be updated" error instead of referencing the already created composite key.

Some other helpful info: I have the form set the composite values to the selected values on form load. Normally, I would just set the primary key to the primary key values and access would follow my logic, but in this case it always tries to make a new record.



Any ideas? What gives?

Thanks

[ranman256]

I think this might be Access's predictability 'mode?'.
Like when you are typing in data into a table, it predicts youre going to enter that text again, and fills it in for you.
This is good for lots o data entry, but BAD when entering indexes.

[pdevito3]

Soooo what would you suggest?

[orange]

You have an Events table and a "junction table". What is the subject matter of this database?
What is the design of the junction table?
Can you post a jpg of your relationships window, or a copy of your database?

More info on junction tables at this TrainSignal tutorial.

[June7]

If you want to provide db for analysis, follow instructions at bottom of my post.

I deleted the duplicate thread.

[pdevito3]

For those that end up here with the same issue, I resolved it by:

1) Removing the fields causing the issue from the control source
2) On form open, add similar code to below to insert the values into the table directly if needed.

This isn't ideal, as a record will be created even if the user cancels and doesn't actually create a record, but since this is just a composite table used for calculations, it was an acceptable workaround for me. Hope this helps someone!

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Handler
 
Dim exceptionSQL As String
Dim checkSQL As String
Dim existsRS As DAO.Recordset
 
Set existsRS = CurrentDb.OpenRecordset("SELECT tblAppointmentException.AppointmentID, tblAppointmentException.InstanceID From tblAppointmentException;") 'WHERE (((tblAppointmentException.AppointmentID)=" & Me.AppointmentID & _
    ") AND ((tblAppointmentException.InstanceID)=" & Me.InstanceID & "));")
    existsRS.FindFirst ("AppointmentID = " & Forms!frmMain!frmAppointmentLogSub.Form!AppointmentID & " AND InstanceID = " & Forms!frmMain!frmAppointmentLogSub.Form!InstanceID)
    If existsRS.NoMatch Then
        exceptionSQL = "INSERT INTO " _
                            & "tblAppointmentException (AppointmentID,InstanceID) " _
                            & " VALUES ('" & Forms!frmMain!frmAppointmentLogSub.Form!AppointmentID & "'," _
                            & "'" & Forms!frmMain!frmAppointmentLogSub.Form!InstanceID & "')"
        DoCmd.SetWarnings False
        DoCmd.RunSQL exceptionSQL
        DoCmd.SetWarnings True
    Else
        Set existsRS = Nothing
    End If
   
    Set existsRS = Nothing
   
Exit_Handler:
    Set existsRS = Nothing
    Exit Sub
   
Err_Handler:
    Call LogError(Err.Number, Err.Description, conMod & ".Form_Unload")
    Resume Exit_Handler
   
End Sub