Re: unique record

From: Elsie M (emiller_at_fslf.org)
Date: 05/24/04 

Date: Mon, 24 May 2004 16:34:12 -0500

Thanks, I will go and fix all the field names and go from there.
"tina" <nospam@address.com> wrote in message
news:02tsc.60753$hH.1103730@bgtnsc04-news.ops.worldnet.att.net...
> 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#
> 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
>
>
> "Elsie M" <emiller@fslf.org> wrote in message
> news:O%23KrhxZQEHA.3264@TK2MSFTNGP10.phx.gbl...
> > 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.
> >
>
>
> >
> >
> > 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
> > "tina" <nospam@address.com> wrote in message
> > news:oAyrc.13740$fF3.342116@bgtnsc05-news.ops.worldnet.att.net...
> > > 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 *** 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).
> > >
> > >
> > > "Elsie M" <emiller@fslf.org> wrote in message
> > > news:OrTAfx3PEHA.2404@TK2MSFTNGP12.phx.gbl...
> > > > that sounds like an option. How would we go about doing that?
> > > > "tina" <nospam@address.com> wrote in message
> > > > news:E4urc.12984$fF3.320321@bgtnsc05-news.ops.worldnet.att.net...
> > > > > 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.
> > > > >
> > > > >
> > > > > "Elsie M" <emiller@fslf.org> wrote in message
> > > > > news:uNyo3t2PEHA.624@TK2MSFTNGP11.phx.gbl...
> > > > > > 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
> > > > > >
> > > > > > "tina" <nospam@address.com> wrote in message
> > > > > >
news:qFsrc.12707$fF3.314790@bgtnsc05-news.ops.worldnet.att.net...
> > > > > > > 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
> > > > > > >
> > > > > > >
> > > > > > > "Elsie M" <emiller@fslf.org> wrote in message
> > > > > > > news:OOmUhS2PEHA.3220@TK2MSFTNGP09.phx.gbl...
> > > > > > > > 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
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>