Duplicate Records Loop

[Guest]

Hello,

I am using Access 2003. I have continuous 3 subforms, all bound to the same
table. I am filtering each subform on a hidden field, EyeColor, in the table:

TABLE:

OccupationID Name EyeColor
1 Jim Blue
2 Joe Green
3 Sally Blue
4 Brigid Brown

Of my three subforms, one subform filters to show only Blue EyeColor,
another only Green EyeColor, and the third only Brown EyeColor

So I have something like:

Continuous Subform - Blue:

1 Jim <delete button>
3 Sally <delete button>

Continuous Subform - Green:

2 Joe <delete button>

Continuous Subform - Brown:

4 Brigid <delete button>

Occupation is set up as a combobox on each of the 3 subforms, and is
pre-populated with a list of all possible OccupationIDs, 1-10. This means I
can only have a total of 10 records in the table. OccupationID is my primary
key in the table, and has to remain that way, as there can only be one
person per occupation. However, you can change occupation.

My problem is, on any subform, I can add a new row, since they are all
continuous subforms. When I do this, if I pick an OccupationID from the
combobox that already exists in tthe table (I already have 10 records in the
table), the application warns me of a primary key violation and that I must
pick another OccupationID. But if all Occupation IDs are taken, and the
OccupationID combobox only has 1-10 in its list, I cannot set the combobox to
any value that doesn't violate the primary key integrity, and I get stuck in
a loop where clicking the <delete button> does not work, and trying to leave
the subform just prompts the primary key violation pop-up.

In a nutshell (a large nutshell), I already have 10 records. I add another
one (shouldn't be able to but it's a continuous form so i can). The current
record, the one I just added, cannot be set to any Occupation ID that does
not violate the primary key rule, and when I'm in this state, since they're
all "taken", and clicking the delete button has no effect (not sure why).

Sorry if this is confusing.

Any ideas?

Thanks,

Scott

 

[OldPro]

Hello,

I am using Access 2003. I have continuous 3 subforms, all bound to the same
table. I am filtering each subform on a hidden field, EyeColor, in the table:

TABLE:

OccupationID Name EyeColor
1 Jim Blue
2 Joe Green
3 Sally Blue
4 Brigid Brown

Of my three subforms, one subform filters to show only Blue EyeColor,
another only Green EyeColor, and the third only Brown EyeColor

So I have something like:

Continuous Subform - Blue:

1 Jim <delete button>
3 Sally <delete button>

Continuous Subform - Green:

2 Joe <delete button>

Continuous Subform - Brown:

4 Brigid <delete button>

Occupation is set up as a combobox on each of the 3 subforms, and is
pre-populated with a list of all possible OccupationIDs, 1-10. This means I
can only have a total of 10 records in the table. OccupationID is my primary
key in the table, and has to remain that way, as there can only be one
person per occupation. However, you can change occupation.

My problem is, on any subform, I can add a new row, since they are all
continuous subforms. When I do this, if I pick an OccupationID from the
combobox that already exists in tthe table (I already have 10 records in the
table), the application warns me of a primary key violation and that I must
pick another OccupationID. But if all Occupation IDs are taken, and the
OccupationID combobox only has 1-10 in its list, I cannot set the combobox to
any value that doesn't violate the primary key integrity, and I get stuck in
a loop where clicking the <delete button> does not work, and trying to leave
the subform just prompts the primary key violation pop-up.

In a nutshell (a large nutshell), I already have 10 records. I add another
one (shouldn't be able to but it's a continuous form so i can). The current
record, the one I just added, cannot be set to any Occupation ID that does
not violate the primary key rule, and when I'm in this state, since they're
all "taken", and clicking the delete button has no effect (not sure why).

Sorry if this is confusing.

Any ideas?

Thanks,

Scott

Set the forms' .AllowAdditions property to false if you don't want to
allow new records to be added in your contirnuous forms. If you do
want to allow this, then create a blank profession, and assign it to
the original in the .BeforeUpdate event of the combobox.
The .BeforeUpdate event can also be used for validation.

 

[Guest]

Thanks for the reply OldPro.

I do want to be able to add records to the subforms. I have created a blank
profession on the BeforeUpdate event, but the problem is I would need to
delete that record, which I would need to do in case the same scenerio
happened again at a later time. Otherwise I would end up with a duplicate
record with a blank OccupationID.

Here is my code:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim db As Database, rs As Recordset, qryCount As QueryDef, intCount As
Integer

Set db = Application.CurrentDb
Set qryCount = db.QueryDefs("qry_Select_Occupations_All")
Set rs = qryCount.OpenRecordSet

If Not rs.EOF Then
rs.MoveFirst
While Not rs.EOF
If rs.Fields("OccupationID").Value = Me.cmbOccupationID.Value Then
Me.cmbJobGrade.Value = ""
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdDeleteRecord <--- this line is not
deleting the new, OccupationID = "", record
DoCmd.SetWarnings True
End If
rs.MoveNext
Wend
End If
End Sub

Any ideas?

Thanks,

-Scott

 

[George Nicholson]

You're looking in the wrong place. The record would have violated pk
integrity, so the "new" record was never created. (That's sort of the point
of integrity rules, to prevent bad data from contaminating your tables.)

What you need to do is clear the wannabe record that is currently hogging
the form but hasn't (and won't ever) be saved/committed.

In the BeforeUpdate event:
If Dcount(something that will return 1 if pk integrity would be
violated) <>0 Then
MsgBox "Cancelling: Tell user why entry is invalid, yada, yada"
Cancel = True
End If

For your "delete" button:
If Me.Newrecord = True Then Me.Undo

HTH,

 

[OldPro]

You're looking in the wrong place. The record would have violated pk
integrity, so the "new" record was never created. (That's sort of the point
of integrity rules, to prevent bad data from contaminating your tables.)

What you need to do is clear the wannabe record that is currently hogging
the form but hasn't (and won't ever) be saved/committed.

In the BeforeUpdate event:
If Dcount(something that will return 1 if pk integrity would be
violated) <>0 Then
MsgBox "Cancelling: Tell user why entry is invalid, yada, yada"
Cancel = True
End If

For your "delete" button:
If Me.Newrecord = True Then Me.Undo

HTH,













- Show quoted text -

George is correct, if you want to prevent the addition of a
duplicate. If you want to allow it, then you have to choose what to
do with the original. Assigning a blank profession only delays the
inevitable; either delete the original or force the user to select a
new profession. So far this seems more like a theoretical exercise
than a real world application. Is this a school project? Everything
has to be designed with the end in mind. What is the ultimate goal
for this app?

 

[Guest]

George,

That helped a lot, thanks!

The form's Before_Update solves my problem with your suggestion below when
the DB is full (i.e. no Occupations left). Me.Undo was what I was missing.
Also not sure what Cancel = True does?

If you're still into helping me out, I still having this issue: If I have
available Occupations, and I still choose an Occupation this is already
chosen (PK violation), instead of hitting the Before_Update event and
handling it smoothly, Access throws this error message of its own:

The changes you requested on the table were not successful because they
would create duplicate values in the index, primary key, or relationship.
Change the data in the field or fields that contain duplicate data, remove
the index, or redefine the index to permit duplicate entries and try again.


Is there a way to bypass this Access-initiated message, and let
Before_Update handle all PK violations?

Thanks,

Scott

 

[Guest]

Nevermind my last post....I didn't add a Before_Update event to all of my
subforms yet.

Thanks again for the help,

-Scott

 

[George Nicholson]

not sure what Cancel = True does?
BeforeUpdate events have a Cancel property. When set it to True, it prevents
the Update from occuring, leaving you on the same record with the same data.
The user would then need to fix or ESC/undo, so they'll probably need
instructions... (Afaik, you can't use Me.Undo within BeforeUpdate because
any change of the record data at that point will raise an error).

To avoid the built-in error message when Access tries to add the record, you
need to do your own test during BeforeUpdate and then Cancel the event if
necessary. That was the idea behind the Dcount test I suggested.

HTH,

 

[Guest]

Thanks for the help. I am calling Me.Undo from with Before _Update and it's
not throwing an error:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim db As Database, rs As Recordset, qryCount As QueryDef, intCount As
Integer

Set db = Application.CurrentDb
Set qryCount = db.QueryDefs("qry_Select_Occupations_All")
Set rs = qryCount.OpenRecordSet

If Not rs.EOF Then
rs.MoveFirst
While Not rs.EOF
If rs.Fields("OccupationID").Value = Me.cmbOccupationID.Value Then
MsgBox "The Occupation already exists, please delete it
first."
Cancel = True
If Me.NewRecord = True Then Me.Undo
End If
rs.MoveNext
Wend
End If

End Sub