Prevent a new record by checking the last record

L

Lyn

Hi
I have a main form (tblsurvey) with a subform (bound to tblpendings).
I would like to prevent a user from creating a new record if the (if they
hit * on the main form)
previous record still has a "pending" status.

on the before insert event (main form), code snip as follows:


If Me.RecordsetClone.RecordCount > 0 Then
Dim rsSCP As Recordset
Dim strsql As String

strsql = "Select Pending_resolved from TblPendings inner join
TblSurvey on " & _
" TblPendings.SCP_seqno = Tblsurvey.SCP_seqno " & _
" where TblPendings.log_no = " & Me.txtLog & " order by
TblPendings.create_dt desc"
Set rsSCP = CurrentDb.OpenRecordset(strsql, dbOpenSnapshot,
dbSeeChanges)
If rsSCP.RecordCount > 0 Then
rsSCP.MoveFirst
If rsSCP("Pending_resolved") = 0 Then
MsgBox "You cannot create a NEW SURVEY page unless the previous
Pending has been resolved."
cancel = True
DoCmd.RunCommand acCmdRecordsGoToPrevious

End If
End If
Else
Me.AllowAdditions = True
Me.AllowEdits = True
End If
Set rsSCP = Nothing

This works and I do get the message box that I can't create a new record.
But the error is --- it still allows me to create a new record and does not
go back to the previous record.
Can this be done differently???
Am I using the correct event?
Pls. advise ASAP!
 
Top