Cancel adding record to sub-form

[fredk]

I have a sub-form - the primary key is the main group number nad effective date - when someone adds a record that already exists, I want to use my own message but I also want to cancel out the record they were adding. I know how to isolate the error, however, how do I cancel the record when they click ok ??? thanks!!!

Here is what I have in the error section

If Err.Number = 3022 Then
MsgBox "There is already a record in the quality table with the same effective" _
& "date and main group number - please click cancel to remove this entry"

How do I cancel the operation???
Else
MsgBox Err.DESCRIPTION
Resume Exit_Command46_Click

 

[Remou]

Are you using the Before Insert event? It has a Cancel:

Private Sub Form_BeforeInsert(Cancel As Integer)
'If Record Found

Cancel = True

'Else
'Whatever
'End if
End Sub

 

[MajP]

look at the "undo" property and the "acCmdUndo" constant of the DoCmd.runCommand.

 

[fredk]

I tried the cancel = true but it is not working because the code is in a save button event.

Looks like acmdundo is what I need - however, how do I also clear the form? - after I use the accmdundo it leaves the data in the record - Also, if you go to the next record it leaves the data in the sub-form - I guess I would just reset the fields one by one by doing me.fieldname = null

Is there something to clear out the entire form? thanks!!!!!

Fred

 

[Remou]

Once the user has clicked into the subform from the main form, the main form record is committed, as far as I know. I think you will have to look at a different set up if you wish to undo a main record and a subform record. It may be that you will need to use unbound forms and only add the record if everything is ok, or do your check earlier, or in the Before Insert event.

 

[fredk]

I am sorry Remou - I mean the sub-form does not clear - I am ok with the main form - it is just the sub-form I want to clear - any ideas? thanks!!!!

Can I run my check in the before update event to see if there is already the same record out there? If so, would I just use the dlookup? thanks!!!

Fred

 

[Remou]

Ok. I though about this more carefully. Forget the Before Insert stuff, it won't work, I think. You have an index set for unique and you want a custom message, so try this:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
      Const INDEX_VIOLATION = 3022
      Select Case DataErr
      Case INDEX_VIOLATION
         MsgBox "There was an index violation!"
         Me.Undo
         Response = acDataErrContinue
      End Select
End Sub

Put the above code on your sub form. What should happen is that when the index is violated, the record will be undone.

 

[fredk]

I will try this first thing tomorrow - is it ok to put this in the save button? thanks!!!!

Fred

 

[Remou]

No I think a save button would involve an unbound form. It is a little procedure in itself that just gets pasted into the module for the subform. It may not suit, but it is worth a try before going about something a bit longer.