validation to revent duplicate value

[alfonsojansen]

Can somebody help me out, I complete new in this;

I have created a form in access 2003 that monitors Member numbers that with an authorization. The authorization is for a certain period
So I’m working with a:
Member IDnumber
Start date
End date and a
Registration date


I am attempting to stop duplicate entries of Member IDnumbers being entered onto the form without ending the previous authorization when the end date Is Null
In this case I want the system to fill the end date which has to be equal to the registration date of new record. ( =Date() )
It has to be also possible to enter a value different from de default value of the end date by typing it but this can not be < then the new registration date nor < then the start date

The defaultValue controle on registration date is set on =Date()

I want all this to happened before updating



However, I have no idea how to do this, and I'm unsure as to whether or not this is the best way to tackle the problem. Please, someone show me the way step by step!
Alfonso

[TheShabz]

have you tried isNull()?

if isnull(endDate) then
' SQL for update query to set endDate = the desired date
end if

[alfonsojansen]

I'm trying these code but I’m getting “Run-Time error 424 Object Required” I don't understand whi is wrong.

Alfonso

Option Compare Database

Private Sub MemberID_BeforeUpdate(Cancel As Integer)
Dim ID As Long
'Check to see if this is a duplicate value before update
ID = Nz(DLookup("Member_IDNumber", "Lastgever", " Member_IDNumber =" & Lastgever. Member_IDNumber), 0)
If ID <> 0 Then
'Run a query to findout if there is already an autorisation
DoCmd.RunSQL ("select Member_IDNumber From lastgever where Member_IDNumber =" & "textboxvalue" And EndDate Is Null)
Cancel = True
Else: MsgBox "Er bestaat reeds een machtiging dat afgesloten dient te worden"
End If
End Sub

[TheShabz]

Else: MsgBox .....
should be

Else
MsgBox .....

I don't know if thats the issue but try that.