Can I "lock" a record after saving to prevent future changes?

[ollyb303]

Hello,

I have created an access database to log complaint calls to my company. Sometimes these will result in a refund request being sent to our finance dept.

I have an event procedure which will send an email via lotus notes to the finance dept. when the Save button on the form is clicked.

I also have a Search (Find) button on the form so that users can look up previous complaints if necessary (which it sometimes is).

My problem is this:

If a user uses the search facility to find a record which has resulted in a refund request and clicks the Save button again, the refund request would be sent through to finance again (they shouldn't, but I'm trying to make this idiot-proof!).

Is there any way to prevent this from happening?

Many thanks,

Olly

[Jim Doherty]

Hello,

I have created an access database to log complaint calls to my company. Sometimes these will result in a refund request being sent to our finance dept.

I have an event procedure which will send an email via lotus notes to the finance dept. when the Save button on the form is clicked.

I also have a Search (Find) button on the form so that users can look up previous complaints if necessary (which it sometimes is).

My problem is this:

If a user uses the search facility to find a record which has resulted in a refund request and clicks the Save button again, the refund request would be sent through to finance again (they shouldn't, but I'm trying to make this idiot-proof!).

Is there any way to prevent this from happening?

Many thanks,

Olly

It seems to me Olly that you simply need to add an additional piece of logic there and that is this:

If the 'status' of the record is flagged as 'refund email sent' either in a hidden field (if you think this is more appropriate for you) or a field 'visible' to the user and they understand the relevance of. Then a test could be applied to that value within the 'on click' event for the mail send.

I don't know what you have used there specifically for sending the mail via lotus notes but within that code if you are able to say that the email was 'actually sent' at the end of its program flow you could then simply update the status field of your table to 'email sent' and test against that value when the user clicks the 'Save' button something like this

 IF Me!Status="Refund Email Sent" Then 
'do nothing because an email has been sent for a refund
ELSE
'the code that fires to send the mail and if sent sucessfully
'update the status field to read email sent and then save
END IF
 

I cannot see your program flow so obviously this is pure method guesswork and might even be more appropriate in the forms 'before update' event, it depends on what program flow you have there. It assumes of course you can add an extra field to your table to act merely as a status flag for the record.

Regards

Jim :)

[ollyb303]

Thanks Jim,

That makes perfect sense. I was thinking down completely different lines, but I think your idea will work just fine. Thanks for your help.

Olly

[ollyb303]

Thanks Jim,

This works just great. Problem is solved with 5 or 6 lines of code and I'm a happy man.

Olly :)

[martin DH]

Thanks Jim,

This works just great. Problem is solved with 5 or 6 lines of code and I'm a happy man.

Olly :)

Good you post your final code? Thanks!

[ollyb303]

If RefundStatus = "sent" Then
    MsgBox "THIS REFUND HAS ALREADY BEEN SENT - LOG NOT SAVED"
    Exit Sub
Else
    End If
 
If Confirmation = -1 Then
    strCC = "[e-mail]" 
Else:
    strCC = ""
End If
    strTo = "[e-mail]"     strSubject = "Refund"
    strBody = "Refund for authorisation"
    FirstFile = "h:\financereport.rtf"
 
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
 
 
 
    If Refund_Amount > 0 Then
 
         If fIsAppRunning = False Then
                MsgBox "PLEASE LOG ON TO LOTUS NOTES BEFORE TRYING TO SEND REFUND REQUEST" & Chr$(10) & "                                              *REFUND NOT SENT*"
                Exit Sub
        Else
        End If
 
 
        DoCmd.OutputTo acOutputReport, "FinanceReport", acFormatRTF, "H:\financereport.rtf"
 
        SendNotesMail strTo, strCC, strSubject, strBody, FirstFile
        RefundStatus.SetFocus
        RefundStatus.Text = "sent"
        DoCmd.GoToRecord , , acNext
        MsgBox "REFUND REQUEST HAS BEEN SENT TO FINANCE" & Chr$(10) & "              * LOG SAVED, THANK YOU *"
 
    Else
        RefundStatus.SetFocus
        RefundStatus.Text = "N/A"
        DoCmd.GoToRecord , , acNext
        MsgBox "* LOG SAVED, THANK YOU *"
    End If
 

[ollyb303]

N.B. I posted that at 5pm yesterday on my way out of work and didn't get chance to tidy up the indents etc. Sorry if it looks a bit messy, but it works!