How can I force a specific field to it's default value on a certain date?

You haven't given us enough information to provide good advice.  On the surface, you have a design issue.    Are you talking about resetting dates in a table?  If so, you would check the current date each time the database opened and on some date or during some period, run the update query.

If you are talking about updating a default value of a field in a table, I generally would not go that way.  I prefer to set values of fields in database tables in the forms that use those tables.  So when you load the form, you could set the default value of a control (the control could be hidden) to whatever value you want it to be.  Or, you could use the Form_BeforeUpdate event to simply set that value if you are on a new record, something like:

Public Sub Form_BeforeUpdate(Cancel as integer)

    if me.NewRecord = false then 
        exit sub
    elseif PassesChecks = false 'this is a function I use to make sure all required fields are filled in and if not, display a message to the user
        then exit sub
    Else
        me!FieldName.Value = fnDefaultValue("SomeField") 'this would call a function that defines your default value based on your criteria
    endif

End Sub

Select allOpen in new window

HTH
Dale

Sorry about the lack of information, I didn't have my project pulled up at the time of my first post.

Currently as I have it set up, there is a form receiving values and placing them into a database table. One of the table fields is a (Yes/No) box for saying whether a user has paid or not. Since our service will be run as a subscription program, we need to set up the database so that when the computer (System Time) reaches certain date(s), it (the Yes/No checkbox) will reset to it's "No" state (this should set the value to "No" if the box is currently in a "Yes" state).

This project is nothing fancy, we are currently just trying to set up a bare-bones project that works for now, as we look for further options.

**EDIT: I'll attach photos when I get the chance

So, it sounds like you don't need to change the default value, but you need to change the actual value, and not just for one record, but potentially many.

It sounds like what you would need is a query that runs, maybe on startup or at some point in the day which updates those records which need to be updated.  Is that really more what you are looking to do?

Yes, that sounds like what I'd want it to do.

This is as I suspected a design flaw.  You don't need a paid flag.  You need a paid through date.  That way, your queries /code won't be checking the flag, they will check for whether or not the paid through date is in the past.  When the customer pays for the service, you update the paid through date.

Pat... What are you talking about? Design flaw? Paid Flag? Please explain what you are referring to.

Using a flag is unnecessary plus it violates third normal form because it is dependent on something other than the primary key of  the record (https://en.wikipedia.org/wiki/Third_normal_form).  The flaw in this case is using a flag to indicate "paid" rather than a paid through date.  The paid through date would be updated only when a client actually pays for the service.  When you need to know whether or not the service is active, you would use the paid through date.  If it is in the future, the service is active.  When you need to find out who is expiring when, you use a query that looks for accounts with paid through dates less than your target.

Could you explain this by an easier explanation? I don't know what "flag" or third normal form violation is (yes, I did look at the Wiki link).

If I am understanding you correctly, this should be done on the (VB/Macro) side?

Also, would you able to provide a code snippet or a link to explain how I'd go about doing so?

One of the table fields is a (Yes/No) box for saying whether a user has paid or not.

I am trying to explain why you should not be using a paid flag.  The paid flag is simply two states.  Paid or not paid and doesn't give you any other information.  At some point in time (which we have no way of knowing), you want to change the state of the flag from paid to unpaid.  However, if instead of using a two state flag, you use a real date field and store the paid through date, there will be a natural process for changing the paid through date.  You will NEVER have to run bulk updates to change a state.  When the customer pays for the service, the process of recording the payment should also update the paid through date.  That makes the flag redundant and therefore a violation of third normal form because it's state does not depend on the customerID but instead depends on some date field that is not the primary key of the record.  Redundant data should not be stored.  The status flag can be derived logically by examining the date field.  When the date is in the future, the status is paid.  Otherwise, the status is not paid.

For your own analysis, you might also want to store the date the payment was made.  That way if you ever want to evaluate how close the customers pay to when their service expires, you can.

Oh, I see. Thanks for that! As far as you know, is there an Event Trigger associated with that or would it be best to write my own?

An Event trigger for what?  You would calculate the Paid Through date at the time the service is entered or when a payment is made.  That calculation would go into the Form's BeforeUpdate event.

I am not sure if I am understanding you correctly.

This is what I have so far. In order to set the Paid field to false, what is the syntax? (I've tried true/false, 1/0/-1, yes/no and nothing seems to work).
 

I'm sorry if I didn't express my full true intentions. I do intend for this to overwrite every record in the database on specific dates.

As far as hardcoding dates; I don't know how to do it otherwise (it will be the same dates, however the years will change from year-to-year).

but since you insist on doing this wrong

Excuse me, there's no need to be rude... I'm still pretty new to Access. I'm much more familiar with Visual Basic, so the thought to hardcode is more natural to me. I understand that there are other ways of doing it; more efficient ways. However, I am not familiar enough with the software to make those conclusions on my own.

I have told you why you shouldn't even be doing this

As I explained above, I am still new to access. I appreciate that you are trying to help, but you are explaining the fix using terminology and methods that I don't fully understand. Even just pointing me towards a resource to clarify would help immensely.

Thank you for your help (and further clarifications). The design flaw you mentioned didn't occur to me until you fully explained it here. For now, I'll implement the flag (as a temporary fix) but I'll look further into using a PaidThroughDT.

You're welcome.  The flaw is quite simple to correct.  Just replace the flag with a paid through date and modify the form that records payments to calculate the new paid through date.  Slightly more difficult is to find any queries that currently reference the paid flag and change them to examine the PaidTrhoughDT instead.  Now is an excellent time to make the change since you are about to reset the paid flag so your data will be in a single state eliminating the need to update any existing data.  If you wait, you will also need to run an update query to transfer the Paid flag setting to a proper PaidThroughDT

I felt the design flaw was significant enough and also common enough to persist in trying to explain it.  I'm glad you now understand.  If you think about it, post back here with your experience on making the suggested change.