Valdidation rule

[alfonsojanse]

I have created a form in access 2003 base on the table “Lastgever” that monitors Member IDnumbers 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!


The code is as follows and I’m getting “Run-Time error 424 Object Required”

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

[OBP]

alfonsojanse,welcome to the forum.
Personally I would use a Combo box to select the member and have the code in the Combo's After Update event procedure.
I would use a Recordset set to check if the there were already any records for that member and if the Enddate is null.
What do you want to happen if the there is a Record?