Automaticaly Unchecking CheckBoxes based on Date (1 Viewer)

bobunknown · Jul 6, 2018

Evening All,

I have ben working on this for a little while now trying a few iterations and this is what I have so far.

Code:

Sub Yearly_WTN()
Dim Day As Integer, Month As Integer
Dim CurrentDate As String
Day = Day(Date)
Month = Month(Date)
CurrentDate = Format(DateSerial, dd.mm.yyyy)
Debug.Print CurrentDate

If CurrentDate = "01.04.yyyy" Then
Set Check256 = False
End If
End Sub

It is intended to uncheck a yes/no (True/false) box every year on a set date. however it dose not

Currently I am getting an "Expected Array" Error on this line:

Code:

Day = Day(Date)

Please can anyone provide a bit of direction?

Edit:

its 5pm on a Friday... im going home

Thanks in advance.

Minty · Jul 6, 2018

Day and Month are reserved names and /or functions so using them as variable names is going to highly confusing and troublesome.

All you need is an update query.

UPDATE YourTable
SET YourCheckField = 0
WHERE YourDateField = YourTargetDate

Trying to remember to do this on a form is fraught with issues, what if you forget to open it, or that date is a Sunday and no one is there?

Put this into an automated code and run the query on open of the database if it needs to be automated.

MarkK · Jul 6, 2018

If you declare...

Code:

Dim Day As Integer

...you can no longer use the VBA.Day() function in your code without qualification. Try...

Code:

Day = VBA.Day(Date)

But there are many other problems with that code...

The problem with this approach...

It is intended to uncheck a yes/no (True/false) box every year on a set date.

is that if you happen not to run your code on "a set date," then this approach will fail. Maybe a set date is on a weekend, and nobody comes to the office. I think you need to back up and describe the problem from a greater distance, so maybe we can help not just with code syntax, but with the overall solution design.

Hope you had a good weenend,
Mark

bobunknown · Jul 11, 2018

Thanks for all the advice, I have only just got a chance to look them over.

From what you have said I have been thinking about using a button to uncheck everything in the checkbox on click. To prevent miskicks through the year I have added an error command should the click fall on a month outside the specified range.

Here is the redesign:

Code:

Private Sub Command259_Click()
Dim NewYear As String
NewYear = Date
If NewYear = "dd.04.yyyy" Then
Set Check256 = 0
Else: MsgBox "It's not the time to use that"
End If
End Sub

Currently I get an error on

Code:

Set Check256 = 0

Saying it is an invalid use of a property?
I am working on the problem but would a appreciate some advice.

Minty · Jul 11, 2018

To set the value of a checkbox (and please rename it to something meaningful!) use the Me. prefix to indicate it's a current form control;

Code:

Me.Check256 = 0

SET is used to assign an object reference to a variable or property.

bobunknown · Jul 11, 2018

Code:

Me. prefix to indicate it's a current form control;

Good to know thanks.

I went from a different angle as what I am simply after unchecking this specific checkbox access all my entries at once with this button. Not sure if its the right way but I seem to be getting somewhere.

(For context it is to do with contracts expiring at the start of every financial year. So at the start I wasn't all boxes unchecked and then as the renewal forms come in I will tack them off one by one so I have a list of the non returns etc)

Code:

Private Sub Command259_Click()
Dim NewYear As String
Dim SLQ As String
NewYear = Date
If NewYear = "dd.07.yyyy" Then
SLQ = " UPDATE Trade_Waste_Address, SET Column28 = False"
Else: MsgBox "It's not the time to use that!"
End If
End Sub

As of now the above will only return a message box as I dictated in the code. I changed the date to today to try get it to uncheck some boxes but no luck so far. Ideally I want it to allow for the unchecking of the box on click only for the 4th month of the year and for the rest to return the message box.

Sorry for the run around, I took your advice on taking a step back.

Minty · Jul 11, 2018

You are still either under thinking or over complicating this I feel. And I would also not have a renewal tick box.
I have dealt with maintenance contracts over periods of time, and if you want accurate reporting and the ability to really see what happens and when, you need some fields to help you.

ContracCreateDate, ContStartDate, ContEndDate are the bare minimum required to decide what is New, Expired or renewed.

You should really have a separate table with

ContractDatesID (PK), ContractID(FK), BusinessType (New, Renewal, Addition etc), TransactionStartDate, TransactionEndDate. (maybe add Invoice Value, Invoice number etc)

This allows you to query the contract and see all the transactions associated with it.
Slightly more complicated initially but much more useful long term.

bobunknown · Jul 11, 2018

We do keep track of all the start dates and returns etc, this box is intended to give me a field to sue as a variable come the start of each financial year so that I can use the database to generate a list of non returns and chase them up.

To give you an idea (and this is simplified as I have subforms on subfoms here

) my structure, with regards to this specific form:

ID (Associated with the customer and all their other info)
Date Returned (as it says on the tin)
WTN (Hyperlink field for an ecopy of their document)
WTN Received (The Checkbox in question)

If you have an idea of how I could achieve what I explained above in a simpler way im all ears as im a simple person at my core

Minty · Jul 11, 2018

Why not have a LastSubmission date, if it's before 1st April of the current year you know you haven't had a current submission, and you keep track of how long it took them to return it?

Checkboxes as flags are almost always better handled as an event date, as it is automatically telling you not only if but when.

And you can easily reset them all with a date based query, that can be run every time someone opens the database.

bobunknown · Jul 11, 2018

Sounds simple enough

Ill see about working something in.

I still need to update my knowledge on how to select a date or a range of dates using VBA as it seems to come up often. Have you got any recommended reading?

Minty · Jul 11, 2018

By far the simplest way is to use Between in the criteria.

Code:

SELECT CustID , Address, PostCode, CreationDate 
FROM tblCustomers
WHERE CreationDate Between  DateAdd("yyyy",-1,Date()) AND Date()

This would find all customers created in the last year for instance.

Mark_ · Jul 11, 2018

Just to add to Minty's comments, what happens when you get your first contract that doesn't expire on the beginning of YOUR fiscal year?

Knowing when the contract expires and running a report based on the lead time to get it renewed is far more useful. I'm dealing with a small system to do this. We are reworking it to add "Lead time" as some facilities respond quickly and others... Don't. Default is going to be one month, but for some government run organizations it will need to be more like 6.

bobunknown · Jul 12, 2018

Mark,

We only write contracts to the end of the financial year, so if you were to contact me half way thought I would write you a 6 month contract with and adjusted price. keeps it simple.

Minty

Code:

SELECT CustID , Address, PostCode, CreationDate 
FROM tblCustomers
WHERE CreationDate Between  DateAdd("yyyy",-1,Date()) AND Date()

With regards to the above im guessing that VBA recognises the field "date" as it is formatted to my computer e.g im uk so dd,mm,yyyy not mm,dd,yyyy.

Thanks.

Minty · Jul 12, 2018

Yes, the Date() function returns today in the correct format to work wherever you use it.
The same as the Now() function which does the same but with the current time component displayed.

The_Doc_Man · Jul 12, 2018

I would write you a 6 month contract with and adjusted price. keeps it simple.

Conceptually, you are still "cruising for a bruising" because Access doesn't so easily do things in the mind-set you have built for your approach to the problem. That "button click on a given day of the year" approach presumes that something specific will happen on that given day. What do you do if you have a day-long power failure? What do you do if the given day is a weekend and nobody is in the office? You have to scramble to play catch-up with that lost event. The design itself is imposing difficulties. This is the point many of our members are making.

If you track start date and either end date or contract duration, then you don't need to "reset" anything. The right query will TELL you which contracts are up for renewal or are past due or are expired - or whatever it is that you wanted to know. No button clicks needed. Nicklaus Wirth, the "Father of the PASCAL language" once said that 80% of all programming problems start with bad data design. I often think he was an optimist.

bobunknown · Jul 12, 2018

Conceptually, you are still "cruising for a bruising" because Access doesn't so easily do things in the mind-set you have built for your approach to the problem. That "button click on a given day of the year" approach presumes that something specific will happen on that given day. What do you do if you have a day-long power failure? What do you do if the given day is a weekend and nobody is in the office? You have to scramble to play catch-up with that lost event. The design itself is imposing difficulties. This is the point many of our members are making.

If you track start date and either end date or contract duration, then you don't need to "reset" anything. The right query will TELL you which contracts are up for renewal or are past due or are expired - or whatever it is that you wanted to know. No button clicks needed. Nicklaus Wirth, the "Father of the PASCAL language" once said that 80% of all programming problems start with bad data design. I often think he was an optimist.

Yes I agree you make excellent points and queries are how I have been generating my lists so far. But the people I am around are so... "paper centric" they don't even know how to make a fairly simple query.

Simply up my initial approach was:

Once a year (on a set month) admin press button....
Button unchecks FormsRtn box...
Query (linked to Excell) instantly shows everyone as no contract....
As admin do their job in put in data list shrinks....
Month after year start you have list of non returns....
(minimal contact with the IT illiterate so they can do their job without shouting down my ear :banghead

However as you said there are many ways to skin a cat and the suggestions posted here have me thinking. I could use a button, or a start end date... lots of things to think on.

Minty:

Yes, the Date() function returns today in the correct format to work wherever you use it.
The same as the Now() function which does the same but with the current time component displayed.

Thanks good bit of info to add to me scrapbook

Automaticaly Unchecking CheckBoxes based on Date (1 Viewer)

bobunknown

Registered User.

Minty

AWF VIP

MarkK

bit cruncher

bobunknown

Registered User.

Minty

AWF VIP

bobunknown

Registered User.

Minty

AWF VIP

bobunknown

Registered User.

Minty

AWF VIP

bobunknown

Registered User.

Minty

AWF VIP

Mark_

Longboard on the internet

bobunknown

Registered User.

Minty

AWF VIP

The_Doc_Man

Immoderate Moderator

bobunknown

Registered User.

Similar threads

Users who are viewing this thread