ADP unique value form validation

[Aad via AccessMonster.com]

Hi,

For my ADP I need to show a (alert) message box when a user tries to add a
reference number (field: reject_ref) which already exist in de db.
In other words the entry for this field must be unique otherwise the user
must be informed about it by means of the message box.

My code so far (adapting from similar threat):

Private Sub reject_ref_Exit(Cancel As Integer)
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM [reject] WHERE [reject].
[reject_ref]='" & Me![reject_ref].Value & "';")
If Not rs.EOF Then
MsgBox "The reference number you entered already exists. Please enter
a unique value"
Cancel = True
Me![reject_ref].Value = Null
Me![reject_ref].SetFocus
End If
rs.Close
End Sub

However it’s not working. (Object variable not set etc…)

Can someone please help with this?

Very thanks,

Aad

 

[Sylvain Lafontaine]

Unless you explicitely create one, there is no CurrentDB object in an ADP
project. Use CurrentProject.Connection.Execute() method or the DLookup()
function.

There is also the remote possibility of having a race condition here, where
your code might fail if two users are trying to enter the same code at the
same time.

 

[ro via AccessMonster.com]

Sylvain,

Thanks for your respons. This is the final code that works for me:

Private Sub reject_ref_BeforeUpdate(Cancel As Integer)
Dim rs As ADODB.Recordset
Set rs = Application.CurrentProject.Connection.Execute("SELECT * FROM [reject]
WHERE [reject].[reject_ref]='" & Me![reject_ref].Value & "';")
If Not rs.EOF Then
MsgBox "Reference number already exist."
Cancel = True
End If
rs.Close
End Sub

Unless you explicitely create one, there is no CurrentDB object in an ADP
project. Use CurrentProject.Connection.Execute() method or the DLookup()
function.

There is also the remote possibility of having a race condition here, where
your code might fail if two users are trying to enter the same code at the
same time.

Hi,

[quoted text clipped - 28 lines]

Aad