Limit table

[ladybug via AccessMonster.com]

I have a table with four fields:
SupervisorID
EmployeeID
StartDate
EndDate

I want to set it where an employee can have multiple entries (supervisors),
but can only have one entry without an end date.

For example:

SupervisorID EmployeeID StartDate EndDate
Adams1 Smith1 06/01/07 07/01/07
Miller1 Smith1 07/02/07
08/01/07
Thompson1 Smith1 08/02/07

 

[John W. Vinson]

I have a table with four fields:
SupervisorID
EmployeeID
StartDate
EndDate

I want to set it where an employee can have multiple entries (supervisors),
but can only have one entry without an end date.

For example:

SupervisorID EmployeeID StartDate EndDate
Adams1 Smith1 06/01/07 07/01/07
Miller1 Smith1 07/02/07
08/01/07
Thompson1 Smith1 08/02/07

I don't think a table validation rule will work here. What you can do is use a
BeforeUpdate event on the Form in which you enter this data; if the new record
has a null EndDate, search for another record for this employee and cancel the
update if it finds one (or, perhaps better, ask if its EndDate should be set
to today's date).


John W. Vinson [MVP]

 

[ladybug via AccessMonster.com]

I do have a form that will be used for this table. Can you explain more in
how i can do this on a form?

I have a table with four fields:
SupervisorID

[quoted text clipped - 12 lines]

08/01/07
Thompson1 Smith1 08/02/07

I don't think a table validation rule will work here. What you can do is use a
BeforeUpdate event on the Form in which you enter this data; if the new record
has a null EndDate, search for another record for this employee and cancel the
update if it finds one (or, perhaps better, ask if its EndDate should be set
to today's date).

John W. Vinson [MVP]

 

[John W. Vinson]

I do have a form that will be used for this table. Can you explain more in
how i can do this on a form?

The BeforeUpdate event on the form fires when the user does something to write
the record to disk (e.g. moves to another record, or explicitly saves the
record, or closes the form). It can be cancelled. You could check to see if
there is an already-existing record with a NULL EndDate and warn the user and
cancel the addition:

Private Sub Form_BeforeUpdate(Cancel as Integer)
If IsNull(Me!txtEndDate) Then
If Not IsNull(DLookUp("[EmployeeID]", "[tablename]", _
"[EndDate] IS NULL") Then
MsgBox "There is already a record with a blank EndDate", vbOKOnly
Cancel = True
End If
End If
End Sub

You could have it go to that record, or even fill in today's date in that
record if you want to get fancy - without knowing more about your table
structure I hesitate to suggest code though.

John W. Vinson [MVP]

 

[ladybug via AccessMonster.com]

I tried putting in your code and correcting the field names like below:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me!dtmEndDate) Then
If Not IsNull(DLookUp("[chrEmployeeUserID]", "[tblEmployeeSupervisorJctn]",
_
"[dtmEndDate] IS NULL") Then
MsgBox "There is already a record with a blank EndDate", vbOKOnly
Cancel = True
End If
End Sub

It keeps highlighting the "Then" before the MsgBox and giving the error:
Compile Error: Expected: list separator or )
Sorry, my code knowledge is very limited. Can you tell me why that is
happening?
Thank you so much for all your help!

I do have a form that will be used for this table. Can you explain more in
how i can do this on a form?

The BeforeUpdate event on the form fires when the user does something to write
the record to disk (e.g. moves to another record, or explicitly saves the
record, or closes the form). It can be cancelled. You could check to see if
there is an already-existing record with a NULL EndDate and warn the user and
cancel the addition:

Private Sub Form_BeforeUpdate(Cancel as Integer)
If IsNull(Me!txtEndDate) Then
If Not IsNull(DLookUp("[EmployeeID]", "[tablename]", _
"[EndDate] IS NULL") Then
MsgBox "There is already a record with a blank EndDate", vbOKOnly
Cancel = True
End If
End If
End Sub

You could have it go to that record, or even fill in today's date in that
record if you want to get fancy - without knowing more about your table
structure I hesitate to suggest code though.

John W. Vinson [MVP]

 

[John W. Vinson]

I tried putting in your code and correcting the field names like below:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me!dtmEndDate) Then
If Not IsNull(DLookUp("[chrEmployeeUserID]", "[tblEmployeeSupervisorJctn]",
_
"[dtmEndDate] IS NULL") Then
MsgBox "There is already a record with a blank EndDate", vbOKOnly
Cancel = True
End If
End Sub

It keeps highlighting the "Then" before the MsgBox and giving the error:
Compile Error: Expected: list separator or )
Sorry, my code knowledge is very limited. Can you tell me why that is
happening?
Thank you so much for all your help!

Sorry - that was my typo. Put a second ) before the word THEN.

If you in fact have the line Private Sub Form_BeforeUpdate(Cancel as Integer)
twice, delete one of them.

John W. Vinson [MVP]

 

[ladybug via AccessMonster.com]

THANK YOU SO MUCH!!!!!!!!!!!!!!!!

I tried putting in your code and correcting the field names like below:

[quoted text clipped - 14 lines]

happening?
Thank you so much for all your help!

Sorry - that was my typo. Put a second ) before the word THEN.

If you in fact have the line Private Sub Form_BeforeUpdate(Cancel as Integer)
twice, delete one of them.

John W. Vinson [MVP]