ACCESS FEED: RE: [MS_AccessPros] Validation to SQL server field

Kamis, 20 Oktober 2011

RE: [MS_AccessPros] Validation to SQL server field

Troy-

If Job Number is the Primary Key of the table, then SQL Server will prevent the
save. If you want to catch it before that, you could do a lookup in the
BeforeUpdate event of the form.

' If on a new record
If Me.NewRecord Then
' See if this job already exists
If Not IsNull(DLookup("[Job Number]", "[linked table name]", "[Job Number] =
" & Me.Job_Number)) Then
MsgBox "You are trying to add a new record, but the Job Number already
exists."
Cancel = True
End If
End If

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/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Troy Sherven
Sent: Thursday, October 20, 2011 5:27 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Validation to SQL server field

I have an MS SQL server table with 2 fields: Job number, and reason
code. I built an Access form that saves data to the SQL table (through
a linked table). I'm pretty new to this, and am trying to figure out
how to prevent users from entering the same job# twice? I have a save
record button on the form. When I click it, If the job# already exists
in the table, I'd like to show a message saying "job# already exists".
What's the best approach to this?

Thanks,

Troy Sherven

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links