Update field in current record after drop down changed..

[Coatezy]

Hi,

Im putting together a database at the moment which hold a lot of confidential details.. The info is submitted to the database and once used yes is selected from a drop down to confirm complete. What I would then like it to do is update a specific field(s) with a for example 4 *'s so the original data in the field no longer exists and therefore makes the record useless if anyone tries to use the data.. How can I go about doing this? I have tried using the update query function but is there anyway to set it just to update the current record within the form.. Or is it possible to do it via VBA??? I'm completely stuck here...

Thanks in advance

Tom

 

[dkinley]

If you do not care to keep the confidential data - overwriting it completely, then on the AfterUpdate event of the combo box use something like ...

If cboComboBoxName = "Yes" Then
     Me.txtControlNametoModify = "****"
End If

This is only for text fields. I am not sure what you mean by making the record totally useless ...

-dK

 

[Coatezy]

If you do not care to keep the confidential data - overwriting it completely, then on the AfterUpdate event of the combo box use something like ...

If cboComboBoxName = "Yes" Then
     Me.txtControlNametoModify = "****"
End If

This is only for text fields. I am not sure what you mean by making the record totally useless ...

-dK

Thanks for the quick reply!

So when they select Yes in the drop down.. The above will update another field within the record to ****? This specified field is required to make the other info within the record usable.

Thanks
Tom

 

[Coatezy]

Hmmm cant seem to work out the above... This is how my database is structured

I have a main database which shall we say is called database1. I then have a form linked to a query like looks for all incomplete records with No in the field (field name "event complete?") Once the user is done with the data in the record they select yes from a drop down in a form which changed it to complete.. At this point I would like it to update one of the other fields within the database.. Lets call it "security code" from what is currently in there to as suggested above "****"

Hope that makes it a little clearer to understand what I'm trying to do?

Thanks
Tom

 

[dkinley]

I think I get it ...

What I proposed was something 'as you go'. If you want to go through and do a mass-update, then you could do the query bit that searches all the complete records (instead of a No in the criteria field, it would be a Yes). Ensure you have this security field in the query. At that point, switch the query type to an Update query and in the UpdateTo row for the security field only, type in "****" (with quotations). Leave the "Yes" in the criteria of the completed field. Run the query, all of the records with Yes in the completed field will have the security field updated to ****.

I think you would want to do this mass update, and then keep the 'as you go' solution incorporated to handle the future records.

Now, just to put the information out there ... if you DO NOT want to dispose of the information and simply hide it. Then on the OnCurrent event of the form you could use something like the following ...

If cboComboBoxName = "Yes" Then
     Me.txtControlNametoModify.InputMask = "Password"
Else 
     Me.txtControlNametoModify.InputMask = "Normal"
End If

Keep in mind, that this will merely hide the information - it will still be in plain language in the underlying tables.

-dK

 

[Coatezy]

Hi,

I am currenly using the update query method which works but not exactly how I would like it to perform the task... The as you go method you suggested is how I would like to do it but cant get my head around it.. What would I need to do with the code that you supplied n your first post, so when I select yes from the drop down the security code field is updated with "****" on the current record..? How do I make your original as you go code point to my security code field...

Thanks for your help dkinley!

Tom

 

[dkinley]

Oh, I see ... My apologies, Tom. Welcome to VBA!!!

Go to your form in design view. Get to the properties of the combo box (right-click > properties). The first row is the Name property. Give it a meaningful name - let's say cboComplete for the sake of this post.

Now, click the event tab of the properties. Click in the AfterUpdate row, you will see an arrow and ellipses to the right on the row. Click the ellipses. On the Builder dialogue box, choose Code Builder and click OK.

A whole new screen will open. This is the Visual Basic Editor (VBE). It will go ahead and put in a wrapper for the event like ...

Private Sub cboComplete_AfterUpdate()
End Sub

This tells you it is a private subroutine for the combo box named cboComplete that runs on the AfterUpdate event. If you have familiarity with functions in other languages it includes the () because this function does not require passed variables. The End Sub statement marks the end of the function.

In between these two lines is what will 'fire' on the AfterUpdate event of the combo box. Modify it to look like ...

Private Sub cboComplete_AfterUpdate()
 
If cboComplete= "Yes" Then
     Me.txtControlNametoChange = "****"
End If
 
End Sub

Note, you will need to change the names as appropriate (combo box name, the security control name) so they will refer to the correct controls on the form.

Save and close the VBE. You will notice in the properties window now that the AfterUpdate event now has: [Event Procedure] in it. That is a signal that there is some code behind the event.

Save form and go to form view to test. Since this is your first stab, here is a link that will help you become better familiar with VBA moving forward.

http://www.functionx.com/vbaccess/

Hope that helps.
-dK

 

[Coatezy]

Oh, I see ... My apologies, Tom. Welcome to VBA!!!

Go to your form in design view. Get to the properties of the combo box (right-click > properties). The first row is the Name property. Give it a meaningful name - let's say cboComplete for the sake of this post.

Now, click the event tab of the properties. Click in the AfterUpdate row, you will see an arrow and ellipses to the right on the row. Click the ellipses. On the Builder dialogue box, choose Code Builder and click OK.

A whole new screen will open. This is the Visual Basic Editor (VBE). It will go ahead and put in a wrapper for the event like ...

Private Sub cboComplete_AfterUpdate()
End Sub

This tells you it is a private subroutine for the combo box named cboComplete that runs on the AfterUpdate event. If you have familiarity with functions in other languages it includes the () because this function does not require passed variables. The End Sub statement marks the end of the function.

In between these two lines is what will 'fire' on the AfterUpdate event of the combo box. Modify it to look like ...

Private Sub cboComplete_AfterUpdate()
 
If cboComplete= "Yes" Then
     Me.txtControlNametoChange = "****"
End If
 
End Sub

Note, you will need to change the names as appropriate (combo box name, the security control name) so they will refer to the correct controls on the form.

Save and close the VBE. You will notice in the properties window now that the AfterUpdate event now has: [Event Procedure] in it. That is a signal that there is some code behind the event.

Save form and go to form view to test. Since this is your first stab, here is a link that will help you become better familiar with VBA moving forward.

http://www.functionx.com/vbaccess/

Hope that helps.
-dK

Thanks! I have been racking my brains all night over that! And I cant believe how simple it is now! I will be taking a look at the above URL! Thanks for your help dK! I'm not in my Partners good books tonight because I have been sat in front of the pc since I got home from work trying to get it to do what I wanted it to do!

Thanks again!
Tom

 

[dkinley]

Don't show your partner how easy it was once you figured it out - just click and say "LOOK AT IT!" and make em oooh and ahhh over the automation.

Good luck with your project!
-dK

 

[Coatezy]

Don't show your partner how easy it was once you figured it out - just click and say "LOOK AT IT!" and make em oooh and ahhh over the automation.

Good luck with your project!
-dK

Thanks!