How to create a button that returns the following Monday of the current date. (1 Viewer)

HangoutGuy

Registered User.
Local time
Today, 13:12
Joined
Aug 6, 2019
Messages
28
Hi all,

I feel like this is a simple question with a direct answer I'm just not finding.

Can I make a button that when I click it, the dates in my database are all updated to the next Monday of the current date. Like today is Thursday the 15th, if I clicked the button the dates would be updated to Monday the 19th.

Thanks!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:12
Joined
Oct 29, 2018
Messages
21,358
Hi. To update multiple records, you can use an UPDATE query. But if you're doing frequent updates of all records to a date for next Monday, maybe you're not supposed to store that date at all. Just a thought...
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:12
Joined
May 7, 2009
Messages
19,169
Code:
Public Function FindNextDOW(dte As Date, whatDay As VbDayOfWeek) As Date
   FindNextDOW = dte + 7 - Weekday(dte + 7 - whatDay)
   If FindNextDOW = dte Then FindNextDOW = FindNextDOW + 7
End Function

debug.print FindNextDOW(Date, vbMonday)

result: 19-aug-2019
 

HangoutGuy

Registered User.
Local time
Today, 13:12
Joined
Aug 6, 2019
Messages
28
Thanks so much! I just would like some guidance implementing this. I have the button created and I have the code posted in the vba window. I don't know how to get the code to run without errors. All I need is to have the button update all the dates in one of my tables and I'm unsure how to.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:12
Joined
Feb 19, 2002
Messages
42,973
I'm with theDBGuy. If you are updating ALL records to a specific date, there is something wrong with your logic. We can help with that if you tell us what you are trying to do.
 

HangoutGuy

Registered User.
Local time
Today, 13:12
Joined
Aug 6, 2019
Messages
28
I'm with theDBGuy. If you are updating ALL records to a specific date, there is something wrong with your logic. We can help with that if you tell us what you are trying to do.

I have a table with projects with a start date and an end date. I have the ability to use a date picker to change the start date to whatever I want to be able to see how much time is left in a project. The start date will always be the same for every project. I just want a button that I can click that updates that start date to the following Monday.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:12
Joined
Oct 29, 2018
Messages
21,358
I have a table with projects with a start date and an end date. I have the ability to use a date picker to change the start date to whatever I want to be able to see how much time is left in a project. The start date will always be the same for every project. I just want a button that I can click that updates that start date to the following Monday.
If the bolded part above is true, then one approach is to not store this information in the projects table. Perhaps it's better stored in a "settings" or "preferences" table instead.
 

HangoutGuy

Registered User.
Local time
Today, 13:12
Joined
Aug 6, 2019
Messages
28
If the bolded part above is true, then one approach is to not store this information in the projects table. Perhaps it's better stored in a "settings" or "preferences" table instead.

Yes I realize there are far and away better approaches to what I am currently doing. This is my first access database that I will be building and I just need the simple work arounds to get done what I need done.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:12
Joined
Oct 29, 2018
Messages
21,358
Yes I realize there are far and away better approaches to what I am currently doing. This is my first access database that I will be building and I just need the simple work arounds to get done what I need done.
Hi. No offense intended, but one of our responsibilities here, I think, is to help guide new database developers, such as yourself, avoid making mistakes and prevent pitfalls as much as possible. If you do some actions now towards that goal, the less likely you'll have to come back here every now and then asking for more workarounds. For example, we could give you a quick and simple code to do what you're asking to do (in fact, Arnel already gave you one), but it's just as simple to also create a separate table to store the project start date. That way, this particular part of the design is already fixed, and you won't have to deal with other problems you might encounter later on if you didn't fix it now.
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:12
Joined
May 7, 2009
Messages
19,169
put the function i gave in a Module.
on the click of your button, add code:
Code:
Private Sub buttonName_Click()
    Currentdb.Execute "Update yourTable Set StartDate = #" & Format(FindNextDOW(Date, 2), "mm/dd/yyyy") & "#"
End Sub
 

Users who are viewing this thread

Top Bottom