records disappearing in Access 2007

[msblane]

Hi, I am building an Access database. I am using code in a form to add records to a table (tblSKAProcessesLink) that has two related keys (SKAID and ProcessID) with a many to one relationship with tblSKAs and tblProcesses respectively where the related keys are primary keys in the latter two tables. Both SKAID and ProcessID are also contained in a related table, tblSKARolesLink. I am having trouble with records that I have added in code disappearing. I have checked the tables and they are definitely gone. I have set warnings to true and I do not receive any warnings that records are deleted, but I do receive a warning when the records are added via code. I have checked all the relationships but none of them have cascade delete set. Can anyone help me? I am a self-taught developer with many years of experience but patchy. I would appreciate any help. Thanks.

 

[GohDiamond]

Did you provide a definite way to save the record you say you added? IF it was never actually added/saved you can get those same results.

Goh

 

[msblane]

I am sure the records are saved because I can see them in the table - they only disappear later. I have added the code used to add records to the tblSKAProcessesLink as well as tblSKARolesLink, and attached a picture of the relationships in case that helps. The only table that is losing records is tblSKAProcessesLink.

Private Sub cmdLinkProcess_Click()
Dim ProcessChoices As ListBox
Dim AddSQL As String
Dim CurrentRow As Integer
Dim SKAID As Long

SKAID = Me.SKAID
Set ProcessChoices = Me.lstAvailableProcesses

For CurrentRow = 0 To ProcessChoices.ListCount - 1
If ProcessChoices.Selected(CurrentRow) Then
AddSQL = "INSERT INTO tblSKAProcessesLink (ProcessID, SKAID) VALUES (" & ProcessChoices.ItemData(CurrentRow) & ", " & SKAID & ")"
'DoCmd.SetWarnings (False)
DoCmd.RunSQL (AddSQL)
'DoCmd.SetWarnings (True)
End If
Next CurrentRow
Me.lstLinkedProcesses.Requery
Me.lstAvailableProcesses.Requery
End Sub

Private Sub cmdLinkRole_Click()
Dim RoleChoices As ListBox
Dim AddSQL As String
Dim CurrentRow As Integer
Dim PRow As Integer
Dim SKAID As Long
Dim ProcessLinkChoices As ListBox
Dim ProcessID As Long


SKAID = Me.SKAID
Set RoleChoices = Me.lstAvailableRoles
Set ProcessLinkChoices = Me.lstLinkedProcesses

For PRow = 0 To ProcessLinkChoices.ListCount - 1
If ProcessLinkChoices.Selected(PRow) Then
ProcessID = ProcessLinkChoices.ItemData(PRow)
For CurrentRow = 0 To RoleChoices.ListCount - 1
If RoleChoices.Selected(CurrentRow) Then
AddSQL = "INSERT INTO tblSKARolesLink (RoleID, SKAID, ProcessID) VALUES (" & RoleChoices.ItemData(CurrentRow) & ", " & SKAID & "," & ProcessID & ")"
'DoCmd.SetWarnings (False)
DoCmd.RunSQL (AddSQL)
'DoCmd.SetWarnings (True)
End If
Next CurrentRow
End If
Next PRow
Me.lstLinkedRoles.Requery
Me.lstAvailableRoles.Requery
End Sub

 

[GohDiamond]

I see a circular relationship which might be a problem:
tblSKAProcessesLink <-->tblProcesses
tblProcesses<-->tblSKARolesLink
tblSKARolesLink<-->tblSKAs
tblSKAs<-->tblSKAProcessesLink

I'd check if the defined relationship may be preventing the data initially inserted into the table from being retained in the tblSKAProcessesLink

Just spitballing here, but that's where I'd want to research first
-Goh

 

[msblane]

Thanks, I will check that out. Can I just ask though, if the relationship allows the record to be written to the table, why would it not be retained? I was wondering about the relationships anyway though so will keep looking. Thanks!