I am looking for a code to delete a LastName, FirstName and Date of Birth in a rowe and insert "removed" in the field, can anyone help? I am using Microsoft Access 2010 in a Desktop Database.
I am looking for a code to delete a LastName, FirstName and Date of Birth in a rowe and insert "removed" in the field, can anyone help? I am using Microsoft Access 2010 in a Desktop Database.
Although not difficult to do using an update query, this sounds like a bad idea.
Please explain why you want to do this as there may well be a better solution
Pray forgive my errant request, I should have said I wanted to remove these details on exiting the shared Database when the DateReturned Field =Date()+28, this is to remove the persons name and date of birth to conform to European Data Protection Regulations as I help to run a league. I can access these details through the players registration number. I would simply like to remove these personal details after a number of days. I have never used If Function, I did do a two day course on Command Button but this was too fast for me to keep up with the class. Hope this helps you Ridder?
I'm somewhat confused. The new GDPR law requires organisations to remove personal data unless 'clients' agree for this to continue to be stored.
For players who are involved in a league and whose registration details are maintained for administration, consent can easily be obtained
Also, if you can still access the name and date of birth using the players' registration numbers, why do you store the info somewhere else as well?
It shouldn't matter about being >= 28 days. The data should only be stored once.
Am I getting the wrong end of the stick here?
Yes, the way the administration runs some members send out letters and this is done by members who do not have access to the registration details. Once a set number of days is past I'd like to remove the information automatically that is all irrespective of the reason, besides it would help me in using VBA.
OK - the update query below assumes you have a table called Members with fields LastName, FirstName, DOB & ReturnedDate
It will replace the 2 name fields with 'Removed' but the DOB is a date field so it has to be changed to null
The only records that will be changed are those where the returned date is exactly 28 days in the future (see previous post)
I suspect you may want to modify this date+28 requirement as it doesn't make sense to me
Suggest you test it on a copy of your data to ensure it does what you actually wantCode:UPDATE Members SET Members.LastName = 'Removed', Members.FirstName = 'Removed', Members.DOB = Null WHERE (((Members.DateReturned)=Date()+28));
If you meant where DateReturned is 28 days or more in the past then:
Code:UPDATE Members SET Members.LastName = 'Removed', Members.FirstName = 'Removed', Members.DOB = Null WHERE (((Members.DateReturned)<=Date()-28));
Thank you so much for your welcome reply. I was in London for a few days (great weather) now I am back in Glasgow and returning to my quest.Yes, the way the administration runs some members send out letters and this is done by members who do not have access to the registration details. Once a set number of days is past I'd like to remove the information automatically that is all irrespective of the reason, besides it would help me in using VBA.
I have inserted the code for the minus formula, your second one, into the Form I am using, at the conclusion I get the message: -
"Expected: end of statement."
Could be we are almost there? The double quotes above are mine.
I thought you wanted a query
If you are using in form VBA, did you include line continuations?
This works for me from a form:
The recalc line updates the display (and stays on the current record).Code:Private Sub Command1_Click() CurrentDb.Execute "UPDATE Members SET Members.LastName = 'Removed', Members.FirstName = 'Removed', Members.DOB = Null" & _ " WHERE (((Members.DateReturned)<=Date()-28));" Me.Recalc End Sub
Me.Requery would also work but go back to the first record if you have a single form
Last edited by isladogs; 06-05-2018 at 04:45 AM.
I have used the Code you sent me checking and re-checking my entry. at conclusion the De-Bug offers me, Compile error: Sub or Function not defined. I notice at the beginning of my module I have Private Sub Form_Close() as I would like the code to apply on exit. Could the Private sub be affecting the outcome?I thought you wanted a query
If you are using in form VBA, did you include line continuations?
This works for me from a form:
The recalc line updates the display (and stays on the current record).Code:Private Sub Command1_Click() CurrentDb.Execute "UPDATE Members SET Members.LastName = 'Removed', Members.FirstName = 'Removed', Members.DOB = Null" & _ " WHERE (((Members.DateReturned)<=Date()-28));" Me.Recalc End Sub
Me.Requery would also work but go back to the first record if you have a single form
I have tried Me.Requery and Me.Recalc
The word 'Private' isn't the issue. That just means the code can only be called from within the form.
If you are trying to do this in the form close event, you don't need the recalc line as you don't want to see the update.
However, doing it there may be the problem.
As a test, move the code to a command button on the form. It should work as it does for me.
If not, check your code compiles.
Perhaps a reference issue (unlikely here ) or corruption?
Thank you so much for all your help for this Novice. I see a course is available at Oxford University (I attended the last one so if I choose to go this would be repitition) but I felt it was too fast for me [I am in my sixties] and it was all centered around a button.The word 'Private' isn't the issue. That just means the code can only be called from within the form.
If you are trying to do this in the form close event, you don't need the recalc line as you don't want to see the update.
However, doing it there may be the problem.
As a test, move the code to a command button on the form. It should work as it does for me.
If not, check your code compiles.
Perhaps a reference issue (unlikely here ) or corruption?
I cant find the Star to end this thread but great you were able to help me. Forgive delay as I travelled to London for Trooping the Colour and had a terrific day!
No problem John
For info, I'm also in my sixties, taking up Access fairly late in life & have never done any training in Access - completely self taught.
Its never too late to learn with both forums & you tube videos being particularly useful.
There are 2 excellent video courses by Steve Bishop & Richard Rost that both cover everything from beginner to expert
Glad you had a great day in London - much better than sitting in front of a computer all day
To mark as solved, click the Thread Tools drop down - its bottom of the list.
Thank you for swift reply Colin, I have purchased up to Expert 25 with Richard Rost and find his tutorials the best. I am currently on Expert 6 with Richard and suspended his sending tutorials until I catch up but I will return to Access Learning Zone, I treat this as a hobby therefore less stress. I see Richard has continued to create Developer lessons so I will get around to this - eventually.
But for now I'll chip away at it...
Steve Bishop's videos are free and I think at least as good but it's personal preference. Depends whose voice you find least irritating and whether you want to pay.