change table input through form

[access17401]

Hi guys

is there a way to create a button on a form that if clicked will change data in a table.

For example i am inputting credit cards numbers, which then get exported in format to a text file to be uploaded to the bank. I would then like a button on my form which when clicked changes the number from say 1234123412341234 to 1234**********34 in the whole table column that it is stored in.

 

[NauticalGent]

In short, yes there is a very easy way to do,this with an update query. However, if encryption is what your after, there is a better way to achieve it.

If you are certain that you will NEVER need to use the numbers again then an update query would be perfect. As a rule, I am always reluctant to dispose of data...

 

[arnelgp]

yes it can:

LEFT([CardNumberField],4) & STRING(10,"*") & RIGHT([CardNumberField],2)

but you don't need to change it in the table.
you will lose those numbers forever.
instead create a query and use this query as input
to your export code, as an example (say name it qryBankCard):

SELECT CustomerName, LEFT([CardNumber],4) & STRING(10,"*") & RIGHT([CardNumber],2) As CardNumber
FROM yourTable;


now to export it:

DoCmd.TransferText acExportDelim,,"qryBankCard","d:\BankCard.txt",True

 

[NauticalGent]

As Arnelgp has provided,

LEFT([CardNumberField],4) & STRING(10,"*") & RIGHT([CardNumberField],2

Place that in the update field of your update query grid and it will work.

 

[access17401]

That worked a treat. But I have also worked out the export code, thanks to your (plural) help.

I will also look into the encryption option too, i really want to learn as much as possible. I might be able to help others then in return.

In addition to this say I want to create an export report with I dont know a range of details say personal and transactional.

But in this report I want to exclude certain info say if a persons name starts with the letter A the name field is blank BUT the other fields for that person still populates.

I have a query name - Export_info (which is for the report) would I under the field - name, in criteria. Be able to put the same kind of script in to exclude names starting with A?, or this might be an if statement?.

 

[Galaxiom]

Everyone working with credit card data aught to be aware of the industry standards.

NauticalGent's advice to prefer storage is incorrect. Card data should not be stored unless absolutely necessary. "Might need it again" is not a good enough reason to store. Moreover the cardholder must give explicit authorisation for you to store it.

Data must not be stored on a PC or other unprotected device. It can only be stored encrypted on a server with locked access.

Having this data in an Access database at all, especially unencrypted would be a very serious breach. It is too easy for someone to copy the database and take it offsite to crack any passwords. Card data should only ever be on a database server.

 

[NauticalGent]

NauticalGent's advice to prefer storage is incorrect.

Does this mean I don't get to go outside for recess?!?

You are of course correct Galaxiom and I should have addressed that issue first and foremost. I was more concerned about the principal of disposing of data rather than the big picture.

 

[access17401]

Yes definitely understand that.

Did you have any thoughts on my follow up question? Or is it too different?

 

[Galaxiom]

I have a query name - Export_info (which is for the report) would I under the field - name, in criteria. Be able to put the same kind of script in to exclude names starting with A?, or this might be an if statement?.

It can be controlled by input from a form. In the example you would put a condition on the name field:

 Not Like Forms!formname.textboxname & "*"

There are ways to make it ignore the textbox when it is empty.
For example, add another field:

Len(Forms!formname.textboxname)= 0

with the condition True on a separate row in the designer from the other condition. Then all records are returned of there is nothing in the box.

Alternatively you can add a parameter to the query.

 

[Galaxiom]

BTW. Don't use the word Name as a field name. Best to avoid reserved words.

 

[access17401]

Firstly thanks Galaxiom I was way off on the coding thought, and can not wait to develop some of this Monday.

I need to be more precise with my responses the field name i could not remember exactly it is not actually name, however I had not thought about reserve words before and will have a read through.

 

[access17401]

So I have tried to alter your code and make it fit but I am having a little trouble, could you please guide me using a basic example? attached.

If you open the report and enter the date 28/08/2017, what I am trying to accomplish is where records 3, 6, and 8 appear with the name 'Anonymous', I want those records to appear but replacing the world Anonymous with an empty cell.

I have spent a couple hours trying now and looked through access websites but it seems when I think I have it the record just removes all together.

 

[arnelgp]

i haven't download your db
but here is what you need to do:

open your report in design view.
click on the textbox where you want it blank.
on it's Property, change its name to: txt+[name of field].
so for example, the name is FirstName, you
rename it to txtFirstName.

next, again on Property->Data->Control Source:

=Iif([FirstName]="Anonymous","",[FirstName])

save your report and youre done.