Automatically update a field when a box is checked

[strangebiscuit]

Hi, I'm fairly new to using Access for any serious purposes. I'm trying to replicate some database functionality I setup in Salesforce and just trying to get my head around a few core concepts in Access, so pardon if this question seems silly.

I'm messing around in the Contact Management template and I'm wondering how I can make it so when so when a yes/no checkbox field (called "Active") is ticked a date/time field (called "Last Date") is automatically set to today's date + 60 days.

In Salesforce I would create a Workflow Action triggered by an if Active = true statement, with a Field Update something like TODAY() + 60.

I can tell I need to create an After Insert/After Update Data Macro but can't quite figure out what to put in.

Any tips?

Thanks for your advice and patience with the simplistic question.

[alansidman]

Create an after update event for the checkbox, then insert this code

If me.Active = True then
me.[Last Date] = Date + 60
End if

[strangebiscuit]

Create an after update event for the checkbox, then insert this code

If me.Active = True then
me.[Last Date] = Date + 60
End if

Hey, thanks for the reply! Greatly appreciated.

When I click the "After Update" button I don't have any place to drop in complete code like that...just this kind of macro builder screen where I have to select commands and fill in the values...the only way I can seem to do it is to use these Edit Record > Set Field commands. Screen shot below:



But even with this when I go to edit a contact and check the active box I get the error: "Edit record failed because the default alias represents a record which is read only". The Last Date field is not set to read-only as far as I can tell.

I'm I going about this in a totally incorrect way? Should I not be clicking the "After Update" button in the "Table Tools" tab?

Sorry again if this is painfully obvious and I'm just missing it. Thanks again for your help!

[John_G]

What Alan showed you was VBA, not a macro. You need to create an On Click event procedure for the checkbox, and put the code in there.

If you are not familiar with how to do that, post back.

HTH

John

[strangebiscuit]

What Alan showed you was VBA, not a macro. You need to create an On Click event procedure for the checkbox, and put the code in there.

If you are not familiar with how to do that, post back.

HTH

John

OK...that helped. Think I got it...didn't realize I had to do this on the form rather than the table. Went into Design View on the form that the checkbox is on, clicked the box and then went to the Event tab in the Properties section on the right. Found the On Click box, selected [Event Procedure] and then clicked the litter "..." button. This opened up the VBA editor, pasted in the code and it seems to work. Thanks so much.

Didn't seem inherently obvious to me, but I get it now...though not sure I fully understand the difference between an Event Procedure and a Data Macro. Hate to ask for more, but just curious...could you tell me what's the purpose of the "me." in front of the field names and why "[Last Date]" needs brackets but "Active" does not?

Thanks again for the help you've already given though. I greatly appreciate it and it's definitely helping me understand how things work.

[June7]

Saving calculated data is usually a bad idea and there are few justifications for it. Your date value can probably be calculated when needed.

Macros (of any variety) are not VBA. [Event Procedure] means VBA code.

The Me is VBA shorthand reference for the form or report object the code is behind.

[Last Date] requires brackets because of the space. This is why spaces and special characters/punctuation (underscore is exception) should be avoided in naming convention, as well as reserved words as names.

[strangebiscuit]

Saving calculated data is usually a bad idea and there are few justifications for it. Your date value can probably be calculated when needed.

Macros (of any variety) are not VBA. [Event Procedure] means VBA code.

The Me is VBA shorthand reference for the form or report object the code is behind.

[Last Date] requires brackets because of the space. This is why spaces and special characters/punctuation (underscore is exception) should be avoided in naming convention, as well as reserved words as names.

Hmm...I find the terminology confusing because when I create macros in in Excel they are most certainly VBA.

When you say saving calculated data is a bad idea and the date can probably be calculated when needed...what kind of change are you suggesting exactly? Is doing it with the on click event procedure not the proper way to go about it?

[June7]

Yes, what Excel calls macro is really VBA code. The macro recorder actually creates a VBA procedure but the syntax of much of the generated code is often quite different.

I just reread your first post and gained some new understanding. I originally thought the +60 was being added to a date field in db, such DateSubscribed or DateRenewed or some such initializing date in the record. I presume you are editing an existing record with this checkbox update? What is purpose of [Last Date]? Perhaps with your data structure saving the calculated date is the most convenient immediate solution. Would need to know more about db. Just keep in mind that saving calculated data has risk of data getting 'out of sync'.

[strangebiscuit]

Yes, what Excel calls macro is really VBA code. The macro recorder actually creates a VBA procedure but the syntax of much of the generated code is often quite different.

I just reread your first post and gained some new understanding. I originally thought the +60 was being added to a date field in db, such DateSubscribed or DateRenewed or some such initializing date in the record. I presume you are editing an existing record with this checkbox update? What is purpose of [Last Date]? Perhaps with your data structure saving the calculated date is the most convenient immediate solution. Would need to know more about db. Just keep in mind that saving calculated data has risk of data getting 'out of sync'.

The purpose of the event between the checkbox and [Last Date] is simply that when the box is checked the [Last Date] field is set to 60 days from the current day. Like imagine if a contact has a subscription and when they cancel it's marked "inactive", but the subscription always remains technically active for 60 days from the date of the cancellation. So [Last Date] gets automatically populated with the last date of activity.

I'm just kind of conceptualizing how I'd port certain aspects of a Salesforce customization into an Access database. How likely is it that this kind of data would go "out of sync"? I've not heard of that...

[June7]

If someone managed to get to the table or query and did edits there, the code would not execute. Saved aggregate calcs (summary data) are especially at risk to get out-of-sync with the raw data.

I should think a cancellation date would be more informative than a checkbox. The +60 could be added to the DateCancel field 'on-the-fly' as needed in textbox calculation.

[strangebiscuit]

If someone managed to get to the table or query and did edits there, the code would not execute. Saved aggregate calcs (summary data) are especially at risk to get out-of-sync with the raw data.

I should think a cancellation date would be more informative than a checkbox. The +60 could be added to the DateCancel field 'on-the-fly' as needed in textbox calculation.

Huh, interesting. I don't want to make this thread huge asking lots of questions and favors, but I do really appreciate the tips... These are not factors I ever had to worry about in Salesforce.

The [Last Date] field is a requirement that the users requested...but do you mean it'd be safer to store the cancellation date in a field and use that date + 60 to populate [Last Date] rather than using today's date? I guess I'm not entirely sure what "on-the-fly" textbox calculation is and how it differs from what I'm trying to do.

Again, in Salesforce when someone asked for a date to be populated/calculated when a checkbox is checked, I just create a workflow action and put in the formula and it's done. Obviously it's not really comparable to Access and they are quite different systems, but I assumed I could create similar functionality without too much trouble and risk of corruption.

[June7]

On-the-fly means the calculation is performed whenever needed, such as when a report is opened. An expression in textbox or query would do the calc so it is always fresh (current). If the CancelDate is changed or removed (maybe subscriber comes back) the calculated LastDate would automatically adjust, without relying on user to force the recalc.

A basic principle of relational database is to enter raw data and do calcs in reports - whenever possible. Saving calculated values (data dependent on other data) has risks.

Having said all that, I will confess to having a database that violates all sorts of principles to accomplish what I want. It is for managing lab test data. The test calcs are so involved and complex we must save the results (much more complex than Quantity * Price or DateValue + 60) and we don't want existing results to be altered if a formula is changed. In essence, the calculated results become the 'raw data' used for reports and data analysis.