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