Allow duplicates, but not for the same reference

[Jose]

Hi,
I created a database that has a table with contact info for a group of
volunteers, and another table that has the history when volunteers have
participated by YEAR.
I related these two through a query and created a form and subform that
displays these for each volunteer ID. Each volunteer can have several rows
of records in the history table.
The Year column must be allowed to have duplicates because different
volunteers have worked the same year. However I would like to prevent
duplicates on the Year column IF it is for the same volunteer. I used some
help from the Students Template on Office Online to relate the contact info
to the history of each volunteer and it is working fine (as was done with
students and attendance through queries in the template).
I just need to prevent duplicate YEAR values for the same volunteer in my
form/table. Any ideas on how to achieve this would be greatly appreciated!

Thank you!

 

[tina]

set a multi-field unique index in the history table, using the volunteer ID
field and the year field. from Access Help:

1. Open the table in Design view.
2. Click the Indexes on the toolbar.
3. In the first blank row in the Index Name column, type a name for the
index. You can name the index after one of the index fields, or use another
name.
4. In the Field Name column, click the arrow and select the first field for
the index.
(In the Index Properties section at the bottom of the dialog box, set the
Unique property to Yes.)
5. In the next row in the Field Name column, select the second field for the
index. (Leave the Index Name column blank in that row.) Repeat this step
until you have selected all the fields you want to include in this index.

hth

 

[Jose]

This works beautifully!
Is there a way to create a custom error message when duplicates are created
in this case?

Thanks

 

[tina]

yes, at the form level. you can trap the form level error that occurs in the
subform when the unique index is violated, and replace the default error
message with your own. try adding something like the following code to the
subform's OnError event procedure, as

If DataErr = 3022 Then
MsgBox "There is already a record for " _
& "this volunteer for this year.", vbExclamation, _
"UPDATE DENIED"
Me.Undo
Response = acDataErrContinue
End If

the line "Me.Undo" is optional; it erases all changes to the current record.

hth

 

[Jose]

I tried going to the subforms design view, the on the on error box, i select
[event procedure], then click on the (...) Thsi takes me to visual basic and
i copy paste the code below but when i try it out, the same default error
message appears. I am sure I am doing something wrong but it's probably
because this is a little out of my comfort zone. Could you give me a step by
step guide on how to do this exactly? I think I'm missing something.

 

[tina]

hmm, well, from your description, sounds like you put the code in the right
place. maybe the error code is different. trying adding a messagebox to the
error code, so you can see the error number, as

MsgBox DataErr
If DataErr = 3022 Then
<and the rest of the code follows as before>

when the messagebox pops up, write down the number it shows. replace 3022
with the number you wrote down, and then delete the messagebox line.

hth

I tried going to the subforms design view, the on the on error box, i select
[event procedure], then click on the (...) Thsi takes me to visual basic and
i copy paste the code below but when i try it out, the same default error
message appears. I am sure I am doing something wrong but it's probably
because this is a little out of my comfort zone. Could you give me a step by
step guide on how to do this exactly? I think I'm missing something.

yes, at the form level. you can trap the form level error that occurs in the
subform when the unique index is violated, and replace the default error
message with your own. try adding something like the following code to the
subform's OnError event procedure, as

If DataErr = 3022 Then
MsgBox "There is already a record for " _
& "this volunteer for this year.", vbExclamation, _
"UPDATE DENIED"
Me.Undo
Response = acDataErrContinue
End If

the line "Me.Undo" is optional; it erases all changes to the current record.

hth


volunteer
ID field
for set
the for
the done
with volunteer
in my