A
Applebaum
Hello. I have a contacts database whose back end is corrupting due to
increased use of a data entry process. Three tables are involved here:
tblContacts - ContactID is primary key
tblOrganizations - OrgID is p.k.
tblAffiliations - AffID is p.k., also contains ContactID and OrgID
tblContacts and tblOrganizations have a many to many relationship, with
referential integrity.
In order to add a new contact, I've created the following process. It's
always worked fine until this week, when we've hired someone to do data
entry exclusively.
Every Contact (an actual person) entered must also have a record in
tblAffiliations, affiliating that person with an organization. There were
business reasons why we had to set it up this way. When a person does not
have an organization, they get affiliated with the organization called
"Unaffiliated".
The data entry process is as follows:
Form1 verifies that the contact doesn't already exist. They type a last
name into a combo box, if the name's already there, they get taken to the
existing record. If it's not already there, the NotInList event includes
SQL code which inserts what they've typed into a new record in tblContacts.
Then, in the combo box's AfterUpdate event, they get taken to Form2, opened
to the new contact's record, to fill out details.
Form2 includes a sbfrmAffiliations, where they choose which organization to
affiliate that contact with. If they try to close Form2 without affiliating
this contact with at least one organization, they get prompted with the
question: "No affiliation entered. Enter as unaffiliated?" If they click
yes then a new record gets created in tblAffiliations with our new contact
as ContactID and "1" as OrganizationID (1 is the OrgID of "Unaffiliated").
Now that we have someone putting these forms through heavy use, just about
daily he enounters an "Invalid Argument" error message when attemtping to
add an affiliation, or when attempting to access any form based on
tblAffiliations. Not knowing what this error message means or what caused
it, I finally tried a compact/repair on the back end, and this fixed the
problem. However, it keeps recurring, although not consistently.
Interestingly, half the time this has happened, the primary key in
tblAffiliations, AffID, is no longer the primary key. The table just loses
its primary key. I can reset it to AffID, no problem, and everything works
again.
But why is this happening, and how can I fix it??
Many thanks in advance!! Relavent code posted below:
---------Form1--------
Private Sub Combo0_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_Combo0_NotInList
Dim ctl As Control
Dim strSQL As String
Set ctl = Me!Combo0
If MsgBox("Item entered is not in list. Add it?", vbOKCancel) = vbOK
Then
Response = acDataErrAdded
strSQL = " INSERT INTO tblContacts (LastName) SELECT '" & NewData &
"'"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
ctl.Value = NewData
DoCmd.SetWarnings True
Else
Response = acDataErrContinue
ctl.Undo
End If
Exit_Combo0_NotInList:
Exit Sub
Err_Combo0_NotInList:
If Err = 3075 Then
Err = 0
Resume Next
Else
If Err = 2113 Then
Err = 0
Resume Next
MsgBox Str(Err)
MsgBox Err.Description
Resume Exit_Combo0_NotInList
End If
End If
End Sub
Private Sub Combo0_AfterUpdate()
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Form2"
stLinkCriteria = "[ContactID]=" & Me![Combo0]
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, "Form1"
End Sub
----------------Form2--------------
Private Sub Form_Unload(Cancel As Integer)
Dim strMsg As String
If IsNull(Me.FirstName) Then
Cancel = True
strMsg = strMsg & "First Name can't be blank" & vbCrLf
Else
If IsNull(Me.LastName) Then
Cancel = True
strMsg = strMsg & "Last Name can't be blank" & vbCrLf
End If
End If
If Cancel Then
strMsg = strMsg & vbCrLf & _
"Correct the entry, or press <Esc> twice to undo."
MsgBox strMsg, vbExclamation, "Invalid entry"
End If
On Error GoTo ErrorHandler
If Me.sbfrmAffiliations!HowMany = 0 And Not IsNull(Me.ContactID)
Then
answer = MsgBox("No affiliation entered. Record as
Unaffiliated?", vbYesNo, "Unaffiliate")
If answer = vbYes Then
With DoCmd
.SetWarnings False
.OpenQuery "Unaffiliate"
.SetWarnings True
End With
Me.sbfrmAffiliations.Requery
ElseIf answer = vbNo Then
MsgBox "Affiliate this Contact", , "Affiliation required"
With DoCmd
.SetWarnings False
.CancelEvent
.SetWarnings True
End With
Me!sbfrmAffiliations.SetFocus
End If
End If
Exit Sub
ErrorHandler:
If Err.Number = 2501 Then
Resume Next
End If
End Sub
---------query: Unaffiliate------------------
INSERT INTO tblAffiliations ( ContactID, OrganizationID )
SELECT [Forms]![Form2]![ContactID] AS ContactID, 1 AS OrgID;
Again, many thanks!!
increased use of a data entry process. Three tables are involved here:
tblContacts - ContactID is primary key
tblOrganizations - OrgID is p.k.
tblAffiliations - AffID is p.k., also contains ContactID and OrgID
tblContacts and tblOrganizations have a many to many relationship, with
referential integrity.
In order to add a new contact, I've created the following process. It's
always worked fine until this week, when we've hired someone to do data
entry exclusively.
Every Contact (an actual person) entered must also have a record in
tblAffiliations, affiliating that person with an organization. There were
business reasons why we had to set it up this way. When a person does not
have an organization, they get affiliated with the organization called
"Unaffiliated".
The data entry process is as follows:
Form1 verifies that the contact doesn't already exist. They type a last
name into a combo box, if the name's already there, they get taken to the
existing record. If it's not already there, the NotInList event includes
SQL code which inserts what they've typed into a new record in tblContacts.
Then, in the combo box's AfterUpdate event, they get taken to Form2, opened
to the new contact's record, to fill out details.
Form2 includes a sbfrmAffiliations, where they choose which organization to
affiliate that contact with. If they try to close Form2 without affiliating
this contact with at least one organization, they get prompted with the
question: "No affiliation entered. Enter as unaffiliated?" If they click
yes then a new record gets created in tblAffiliations with our new contact
as ContactID and "1" as OrganizationID (1 is the OrgID of "Unaffiliated").
Now that we have someone putting these forms through heavy use, just about
daily he enounters an "Invalid Argument" error message when attemtping to
add an affiliation, or when attempting to access any form based on
tblAffiliations. Not knowing what this error message means or what caused
it, I finally tried a compact/repair on the back end, and this fixed the
problem. However, it keeps recurring, although not consistently.
Interestingly, half the time this has happened, the primary key in
tblAffiliations, AffID, is no longer the primary key. The table just loses
its primary key. I can reset it to AffID, no problem, and everything works
again.
But why is this happening, and how can I fix it??
Many thanks in advance!! Relavent code posted below:
---------Form1--------
Private Sub Combo0_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_Combo0_NotInList
Dim ctl As Control
Dim strSQL As String
Set ctl = Me!Combo0
If MsgBox("Item entered is not in list. Add it?", vbOKCancel) = vbOK
Then
Response = acDataErrAdded
strSQL = " INSERT INTO tblContacts (LastName) SELECT '" & NewData &
"'"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
ctl.Value = NewData
DoCmd.SetWarnings True
Else
Response = acDataErrContinue
ctl.Undo
End If
Exit_Combo0_NotInList:
Exit Sub
Err_Combo0_NotInList:
If Err = 3075 Then
Err = 0
Resume Next
Else
If Err = 2113 Then
Err = 0
Resume Next
MsgBox Str(Err)
MsgBox Err.Description
Resume Exit_Combo0_NotInList
End If
End If
End Sub
Private Sub Combo0_AfterUpdate()
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Form2"
stLinkCriteria = "[ContactID]=" & Me![Combo0]
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, "Form1"
End Sub
----------------Form2--------------
Private Sub Form_Unload(Cancel As Integer)
Dim strMsg As String
If IsNull(Me.FirstName) Then
Cancel = True
strMsg = strMsg & "First Name can't be blank" & vbCrLf
Else
If IsNull(Me.LastName) Then
Cancel = True
strMsg = strMsg & "Last Name can't be blank" & vbCrLf
End If
End If
If Cancel Then
strMsg = strMsg & vbCrLf & _
"Correct the entry, or press <Esc> twice to undo."
MsgBox strMsg, vbExclamation, "Invalid entry"
End If
On Error GoTo ErrorHandler
If Me.sbfrmAffiliations!HowMany = 0 And Not IsNull(Me.ContactID)
Then
answer = MsgBox("No affiliation entered. Record as
Unaffiliated?", vbYesNo, "Unaffiliate")
If answer = vbYes Then
With DoCmd
.SetWarnings False
.OpenQuery "Unaffiliate"
.SetWarnings True
End With
Me.sbfrmAffiliations.Requery
ElseIf answer = vbNo Then
MsgBox "Affiliate this Contact", , "Affiliation required"
With DoCmd
.SetWarnings False
.CancelEvent
.SetWarnings True
End With
Me!sbfrmAffiliations.SetFocus
End If
End If
Exit Sub
ErrorHandler:
If Err.Number = 2501 Then
Resume Next
End If
End Sub
---------query: Unaffiliate------------------
INSERT INTO tblAffiliations ( ContactID, OrganizationID )
SELECT [Forms]![Form2]![ContactID] AS ContactID, 1 AS OrgID;
Again, many thanks!!