AddRecord button on form - additional functionality

[Mary Jane Smith]

Can someone please help me? I have a table that hold territory assignments with a start dt and end dt. This table will help us determine when a certain user was assigned to a territory and should receive credit for work done on that territory during the date range based on the start and end dates. If an end dt is null then that record is the current existing assignment. it will only be end dated when the assignment is ending. I do not want any new records added until all records for that territory are end dated. I created a Add Records button on the form with the following script, but it isn't working. I have limited knowledge of VB. Any help would be appreciated.


Private Sub AddRecord_Click()
On Error GoTo Err_AddRecord_Click

Dim SQL As String

SQL = "Select [EndDt] from [tRCAAssignments] where [Territory] = [Forms]![fTerritoryAssignments]![Territory] and [EndDt] is not null"

If SQL Is Null Then
MsgBox "There are records that need to be end dated"
Else
Me.AllowAdditions = True
DoCmd.GoToRecord , , acNewRec
End If

Exit_AddRecord_Click:
Exit Sub

Err_AddRecord_Click:
MsgBox Err.Description
Resume Exit_AddRecord_Click

End Sub

 

[edtab]

Try this code:

Dim intCount as integer
intCount = DCount("[EndDt]","tRCAAssignments","[Territory] = [Forms]![fTerritoryAssignments]![Territory] AND Not IsNull([EndDt])"
If intCount>0 then
msgbox "Can not add new record..",vbinformation
Exit Sub
EndIf

Note: code not tested, but I think will work; if it doesn't work, let me know.

 

[Mary Jane Smith]

Thank you so much. I had to change the count to count the rowid because the end date was null (that was my fault in my original SQL query), but once I did that it worked great. Thank you again.