unique record

  • Thread starter Thread starter Elsie M
  • Start date Start date
E

Elsie M

We have set up a photo database.In this database are fields for a schema
number, (which could be the same # on more then one picture), an envelope
number of where the picture is located, and a photo number.

Would like to set it up so if the same schema number, envelope number and
photo number were entered, the inputter would get a message that this is a
duplicate record and would not allow it to be input.

How can we accomplish this.

Thanks for any help
em
 
you can set up a unique index in the table. open the table in design view,
and click on the Indexes button on the toolbar. there may be other indexes
already listed, there certainly will be if you have a primary field in the
table.
go to the first blank line in the Index Name column and enter a name, like
SchemaEnvelopePhoto. in the next column FieldName, add the schema field from
the droplist; then go down to the Index Properties section and change Unique
to Yes.
go back up to the Field Name column, and in the next row *of the same
column* add the envelope field, and in the next row *again of the same
column* add the photo field.
now you have an index made up of three fields, and the combination of the
three fields must always be unique, in each record in the table.
now you'll get an error message if you enter a duplicate-value record in the
form. it's a fairly ugly, user-unfriendly message. if you want to know how
to substitute your own message, post back for details.

hth
 
That worked great. Can you set it to warn you when you enter the last item.
Right now it does not tell you it is a duplicate until you try to save or
move to the next record. Also would like to know how to customize the
warning message . Thank you so much for you help
 
yes, there are ways to validate the entries in all three fields as soon as
all three are entered, but i don't think i'd want to talk you thru writing
that code.
do any of the fields in your table have their Required property set to Yes?
if not, it might be easier to add a simple procedure (in the form, not the
table) to save the record when all three fields have been entered. then the
index rule would be enforced immediately.
 
if you're in A2000 or newer, read the rest of this first paragraph. but if
you're in A97, skip directly to the next paragraph. in the database window,
on the menu bar click Tools, Options, General tab. if the box next to "Track
name AutoCorrect info" is checkmarked, uncheck it. click the Apply button at
the bottom of the dialog box, then click OK.
open your form in design view. on the menu bar, click View, Code. this opens
the VBE window. unless you already have code behind the form (which is
possible, even if you didn't write it yourself), the sheet will be blank
except for the following at the top:

Option Compare Database
Option Explicit

if the second line is missing, type it in. hit Enter a couple times, and
copy/paste the following procedures, as

Private Sub CheckIndex()

On Error GoTo CheckIndex_Error
'change the three fieldnames below to the correct
'names.
If Not IsNull(Me!Schema) And Not IsNull(Me!Envelope) _
And Not IsNull(Me!Photo) Then
DoCmd.RunCommand acCmdSaveRecord
End If

CheckIndex_End:
Exit Sub

CheckIndex_Error:
If Err.Number = 3022 Then
'change the message to whatever you want to say.
'keep the message inside the double quotes.
MsgBox "Duplicate record. Please start over."
Me.Undo
'change the fieldname below to the correct name.
'i assumed that the schema field comes first in the
'tab order. if one of the other fields comes first, then
'change the fieldname below to that field, instead.
Me!Schema.SetFocus
Resume CheckIndex_End
Else
MsgBox Err.Number & " " & Err.Description, , _
"Private Sub CheckIndex()"
Resume CheckIndex_End
End If

End Sub

'change the fieldname below to the correct name.
Private Sub Envelope_AfterUpdate()

CheckIndex

End Sub

'change the fieldname below to the correct name.
Private Sub Photo_AfterUpdate()

CheckIndex

End Sub

'change the fieldname below to the correct name.
Private Sub Schema_AfterUpdate()

CheckIndex

End Sub

after you paste the lines of code above into the VBE window, certain lines
will turn green. those are "comment" lines (they don't affect the way the
code runs) that i put in to give you instructions, so read and follow them.

after you're done making the changes, go to the menu bar (still in the VBE
window) and click Debug, Compile. if there is an error in the code, you will
get an error message; otherwise the Compile was successful.

try it out, and post back if problems. btw, i may not check back on this
thread until sometime tomorrow (5/22).
 
Hi Tina,

This is what I did. However three lines turned red and gave me an error.
Last section of the code. Could you take a look and see what I did wrong.
Here are the field names in the order they should check.

Subseries#
Env#
Photo#


Thanks for any help.





Option Compare Database
Option Explicit


Private Sub CheckIndex()

On Error GoTo CheckIndex_Error
'change the three fieldnames below to the correct
'names.
If Not IsNull(Me!subseries#) And Not IsNull(Me!Env#) _
And Not IsNull(Me!Photo#) Then
DoCmd.RunCommand acCmdSaveRecord
End If

CheckIndex_End:
Exit Sub

CheckIndex_Error:
If Err.Number = 3022 Then
'change the message to whatever you want to say.
'keep the message inside the double quotes.
MsgBox "Duplicate record. Please start over."
Me.Undo
'change the fieldname below to the correct name.
'i assumed that the schema field comes first in the
'tab order. if one of the other fields comes first, then
'change the fieldname below to that field, instead.
Me!subseries#.SetFocus
Resume CheckIndex_End
Else
MsgBox Err.Number & " " & Err.Description, , _
"Private Sub CheckIndex()"
Resume CheckIndex_End
End If

End Sub

'change the fieldname below to the correct name.
Private Sub Env#_AfterUpdate() (Turned red)
CheckIndex

End Sub

'change the fieldname below to the correct name.
Private Sub Photo#_AfterUpdate() (turned red)

CheckIndex

End Sub

'change the fieldname below to the correct name.
Private Sub subseries#_AfterUpdate() ( turned red)

CheckIndex

End Sub
 
your problem is your field names. don't use any special characters in object
names (tables, fields, forms, controls, etc) - and this is a perfect example
of why. see the following link for more information.
http://www.mvps.org/access/tencommandments.htm

if it were me, i would go back and fix the field names in the tables - even
though it means also fixing them in every query, form, report, expression
and code where they're used. if you haven't built a lot of objects yet
(queries, forms, etc), it will be a pain, but not impossible.
here's an alternative: delete all the code i gave you, then close the code
window and the form window, clicking Yes on the Save Changes? question. then
open your form in design view. do the following to the controls that are
named
Subseries#
Env#
Photo#
Click to expand...
leave the ControlSource alone. change the Name of each control, removing the
# sign. close the form, saving your changes. now open the form's code window
again and paste in the code i gave you, fresh. this time when you update the
control references, make sure you leave out those # signs - because now
they're not part of the control names.
but i really urge you to make a copy of your db, open it, and go thru and
fix those field names in every object they're used in - rather than using
the alternative solution. otherwise you'll just keep running into problems
referencing the fields with # in the name.

hth
 
Back
Top