Require one of two fields to be filled in unless user decides to cancel the record (1 Viewer)

[eee333]

Hi all, I'm having a problem that I would think would be quite common but I've had trouble finding the answer I need.
I need to accomplish two things: 1) make it so the user can't save a record without entering information in one of two fields
2) allow the user to delete the record if they change their mind

The code I currently have is this:

 Private Sub Form_AfterUpdate()
'requires some sort of ID before moving on'
If IsNull(SurveyID) And IsNull(InsightID) Then
    Beep
    MsgBox ("Please enter either a Survey ID or generate an Insight ID.")
    Me.q1 = Me.q1.OldValue
    SurveyID.SetFocus
End If
End Sub

However, there's one specific instance that messes this up. If the user opens a new record, enters a piece of information, then changes their mind and deletes the information, and tries to navigate back, there's no way to tell Access that the record is blank. (I think it may be because I collect some data not visible on the form, like the date where the default value is Now(), so it's not actually blank).
Since my form is kind of crowded, and I don't want to add a button to delete a record, I thought I would set it up so that if SurveyID and InsightId and user and language were null, and you tried to navigate away from the form, then it would give you a dialogue box asking you to confirm that you'd like to delete a blank record.

 Private Sub Form_AfterUpdate()
'requires some sort of ID before moving on'
If IsNull(SurveyID) And IsNull(InsightID) And IsNull(user) And IsNull(Language) Then
    pineapple = MsgBox("Would you like to delete this blank record? If the record is not blank, hit cancel!", vbOKCancel)
    Select Case pineapple
        Case 1
            DoCmd.RunCommand acCmdDeleteRecord
        Case 2
        Cancel = True
    End Select
ElseIf IsNull(SurveyID) And IsNull(InsightID) Then
    Beep
    MsgBox ("Please enter either a Survey ID or generate an Insight ID.")
    Me.q1 = Me.q1.OldValue
    SurveyID.SetFocus
End IF
End Sub

However, in this case, if the user selects okay first, and then when they see the final ask from access about deleting the record, and they hit cancel, it gives me a runtime error 2501. I tried adding the following bit of code,

errhandler:
Select Case Err.Number
Case 2501
'ignore
End Select

Which sort of works, EXCEPT, if they go through that and hit cancel, and then try to navigate away from the current record again, it allows them to do so without filling in either of those two fields.

Any suggestions?

 

[sneuberg]

I think you want to put this in the form's before update. Cancel = True doesn't do anything in the afterupdate, but in the before update it won't allow the record to be added until the fields are completed. Rather than delete you can offer Me.Undo as an option. to clear the form.

 

[eee333]

Hmm, I originally added it in after update because I wanted to do the setfocus to the relevant field, and it won't let you do setfocus before update. However, given the massive headache figuring this out has been, I might just let the user figure that one out on their own.
To make sure I'm understanding you right, are you suggesting to keep the same basic code structure that i'm doing in the second example, but to replace the delete command with me.undo?

 

[eee333]

Wow, this worked out great! Now I just need to figure out how to add ANOTHER message box when case=1 so they can double confirm they want to delete it.

 

[sneuberg]

To make sure I'm understanding you right, are you suggesting to keep the same basic code structure that i'm doing in the second example, but to replace the delete command with me.undo?

Well you can't delete a record that hasn't been added so Case 1 can't be DoCmd.RunCommand acCmdDeleteRecord. Maybe just give them the message and see if that works for you.

But with this in the before update if the user enters any data in the new record they won't be able to leave the record until they fill in these required fields even if they just want to abandon the entry and close the form. The only other way out is to clear the form hence the suggestion to offer the user a chance just to scrape whatever he entered

 

[eee333]

I had some initial difficulties with this allowing a user to go to an older record, delete the id number, and then move on, but I added cancel=true to the ELSEIF it took care of that. If I had more time I would like to find a more bulletproof way of preventing them from accidentally deleting an old record's ID number (but also allowing people to purposely change them if necessary), but I think I'm more likely to do that in the general database design than through coding in this instance.