By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,889 Members | 1,373 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,889 IT Pros & Developers. It's quick & easy.

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

ollyb303
P: 74
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
Dec 7 '07 #1
Share this Question
Share on Google+
6 Replies


Jim Doherty
Expert 100+
P: 897
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

Expand|Select|Wrap|Line Numbers
  1.  IF Me!Status="Refund Email Sent" Then 
  2. 'do nothing because an email has been sent for a refund
  3. ELSE
  4. 'the code that fires to send the mail and if sent sucessfully
  5. 'update the status field to read email sent and then save
  6. END IF
  7.  
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 :)
Dec 7 '07 #2

ollyb303
P: 74
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
Dec 10 '07 #3

ollyb303
P: 74
Thanks Jim,

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

Olly :)
Dec 10 '07 #4

100+
P: 114
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!
Dec 10 '07 #5

ollyb303
P: 74
Expand|Select|Wrap|Line Numbers
  1. If RefundStatus = "sent" Then
  2.     MsgBox "THIS REFUND HAS ALREADY BEEN SENT - LOG NOT SAVED"
  3.     Exit Sub
  4. Else
  5.     End If
  6.  
  7. If Confirmation = -1 Then
  8.     strCC = "xxxx@xxxx.com" 
  9. Else:
  10.     strCC = ""
  11. End If
  12.     strTo = "xxxx@xxxx.com"     strSubject = "Refund"
  13.     strBody = "Refund for authorisation"
  14.     FirstFile = "h:\financereport.rtf"
  15.  
  16.     DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
  17.  
  18.  
  19.  
  20.     If Refund_Amount > 0 Then
  21.  
  22.          If fIsAppRunning = False Then
  23.                 MsgBox "PLEASE LOG ON TO LOTUS NOTES BEFORE TRYING TO SEND REFUND REQUEST" & Chr$(10) & "                                              *REFUND NOT SENT*"
  24.                 Exit Sub
  25.         Else
  26.         End If
  27.  
  28.  
  29.         DoCmd.OutputTo acOutputReport, "FinanceReport", acFormatRTF, "H:\financereport.rtf"
  30.  
  31.         SendNotesMail strTo, strCC, strSubject, strBody, FirstFile
  32.         RefundStatus.SetFocus
  33.         RefundStatus.Text = "sent"
  34.         DoCmd.GoToRecord , , acNext
  35.         MsgBox "REFUND REQUEST HAS BEEN SENT TO FINANCE" & Chr$(10) & "              * LOG SAVED, THANK YOU *"
  36.  
  37.     Else
  38.         RefundStatus.SetFocus
  39.         RefundStatus.Text = "N/A"
  40.         DoCmd.GoToRecord , , acNext
  41.         MsgBox "* LOG SAVED, THANK YOU *"
  42.     End If
  43.  
Dec 10 '07 #6

ollyb303
P: 74
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!
Dec 11 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.