John -
Thanks so much!! You're the best! Tested out the code and I received the msgbox.
Thanks again!
Katrina
--- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@...> wrote:
>
> Katrina-
>
> Private Sub Citation_Number_BeforeUpdate(Cancel As Integer)
>
> If Not IsNull(DLookup("[Citation Number]", "tblCitations", _
> "[Citation Number] = " & Me.Citation_Number & _
> " And ID <> " & Me.ID)) Then
> MsgBox "The Citation Number you entered has already been " & _
> "to another record. Correct your entry and try again.",
> vbInformation
> Cancel = True
> End If
> End Sub
>
> I just guessed at the name of the table (tblCitations) and the name of the field
> ([Citation Number]).
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Nashua, NH)
>
>
>
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of ka0t1c_ang3l
> Sent: Tuesday, March 06, 2012 1:56 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: Re: [MS_AccessPros] Duplicates in a field
>
> Doyce and John,
>
> Citation Number is not the primary key. I left the ID as the autonumber;
> however, I can make it the primary key. Once I do that what do I need to do? I
> know you said to do the DLookup in the Before Update, I have no idea how to do
> that. Do I just put the (Me.NewRecord = True) in the Event Procedure?
>
> Thanks!
>
> Katrina
>
> --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@> wrote:
> >
> > Katrina-
> >
> > Is Citation Number the Primary Key of the table?
> >
> > In any case, you can use DLookup in the Before Update event of the field on a
> > form to check to see if the value already exists. It's easy to do when you're
> > on a new record (Me.NewRecord = True), but it's a bit more difficult to check
> a
> > change on an existing record - depending on what is the Primary Key.
> >
> > John Viescas, author
> > Microsoft Office Access 2010 Inside Out
> > Microsoft Office Access 2007 Inside Out
> > Building Microsoft Access Applications
> > Microsoft Office Access 2003 Inside Out
> > SQL Queries for Mere Mortals
> > http://www.viescas.com/
> > (Nashua, NH)
> >
> >
> >
> > -----Original Message-----
> > From: MS_Access_Professionals@yahoogroups.com
> > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of ka0t1c_ang3l
> > Sent: Tuesday, March 06, 2012 12:10 PM
> > To: MS_Access_Professionals@yahoogroups.com
> > Subject: [MS_AccessPros] Duplicates in a field
> >
> > Hello everyone!
> >
> > I'm creating a new database for our Public Safety Department. In my database
> I
> > have a Citation Number that is issued. Is there a way to code this field to
> > prompt me that the number entered has already been used to avoid duplicate
> > numbers being issued?
> >
> > Any help is appreciated!
> >
> > Thanks!
> >
> > Katrina
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
Tidak ada komentar:
Posting Komentar