Text boxes = Date + 1 (Sunday to Saturday)

[djspod]

Hi,

I have a main form and a sub form rota plan.



The Combo box is fed from a field selection of W/C Sunday date values.

How do I get the selected date value of the Combo to populate/update the first textbox under Sunday in the Subform?

From there my plan is for the remaining text boxes under the days to auto feed from the first textbox value + 1, textbox value + 2, textbox value + 3 etc etc

Can anyone help






Sent from my SM-G935F using Tapatalk

[ssanfu]

Maybe something like this?

[djspod]

Maybe something like this?

Hi,

Thanks for taking the time to answer my post and providing a great example.

The example was absolutely spot on!! and exactly what I was after.

Thanks again Darren

[ssanfu]

You are very welcome. Happy Holidays!

[djspod]

Hi Ssanfu,

Hope you don't mind, just a quick question on the code in module 1.

I'm just learning some VBA to use in spreadsheets and Access, a long way to go yet but I'm getting there.
I understand the logic from your neat bit of code, a For loop 1 to 200 times, based on the date (#01/01/2017#) + 7 on each loop and cleverly inserting into the Sunday commencing table, I usually copy and paste this from Excel, this method is a lot better.

What I don't understand is: the dates are increasing by 7 days each time but also seem to randomly miss a few on the beginning of some months, added a date 3 days away instead of 7days?

The code makes sense to me but not why it inserts a few random dates.

If you explain what I am missing or understanding that would be great towards my learning of VBA coding.

Thanks

Darren



Sent from my SM-G935F using Tapatalk

[djspod]

Sent from my SM-G935F using Tapatalk

[ssanfu]

<snip> What I don't understand is: the dates are increasing by 7 days each time but also seem to randomly miss a few on the beginning of some months, added a date 3 days away instead of 7days?

The code makes sense to me but not why it inserts a few random dates.

If you explain what I am missing or understanding that would be great towards my learning of VBA coding. <snip>

I've looked at the dates in the table, but don't see where the dates (Sundays) are being skipped??? Which dates are you referring to?

This site gives an explanation of how dates are stored.

Since the date format is a double precision floating-point number, for simple date calculations you can add of subtract integers from a date to get a new date.
For more advanced date calculations, use the built-in Access date functions.

Here is an example of using the DateAdd function, adding 7 days:

Public Sub CalcSundays()
    Dim i As Integer
    Dim dTmp As Date

    'beginning date (mm/dd)/yyyy)
    dTmp = #1/1/2017#

    For i = 1 To 200
        CurrentDb.Execute "INSERT INTO tblSundayDates2 ( SundayDates ) VALUES (#" & dTmp & "#);"
        dTmp = DateAdd("d", 7, dTmp)
        '      dTmp = dTmp + 7
    Next

    MsgBox "Done"

End Sub

[djspod]

Hi SSanfu,

Happy New Year!

Appologies as i have only just seen your message, thanks for replying.

I have took a screenshot from the Sunday Dates table just after I have ran the code, I emptied table beforehand.

200 entries of dates, sorted them by descending. (The code was set for starting from this Sunday just gone 01/01/2017)

You will notice the second date record jumps to 10th Jan, the next 15th.

The dates then run in 7 days again but go weird again at the beginning of February and so on.

Thanks for posting the other piece of code and the links though, I'll have a look at them.

I am now on my next problem query issue that is missing certain records (posted today in the Query Section) https://www.accessforums.net/showthread.php?t=63662

Thanks for your help

Darren

[djspod]

Just a quick update..

I have now tried the DateAdd code too.

Bizzareley it is doing the same werid thing with the dates, starts with Sunday 01/01/2017, jumps to Tuesday the 10th, then to the 15th, 22nd and 29th which is great but the next date is 04/02/2017 (Saturday)

Option Compare DatabaseOption Explicit


Public Sub CalcSundays()
    Dim i As Integer
    Dim dTmp As Date


    'beginning date (mm/dd)/yyyy)
    dTmp = #1/1/2017#


    For i = 1 To 200
        CurrentDb.Execute "INSERT INTO tblSundayDates2 ( SundayDates ) VALUES (#" & dTmp & "#);"
        dTmp = DateAdd("d", 7, dTmp)
        '      dTmp = dTmp + 7
    Next


    MsgBox "Done"


End Sub

[ssanfu]

This behavior is very strange! I don't see it, but I use American formatting.

I added code to (or tried) use international formatting to do the insert. Run the code to create the Sunday dates.

[djspod]

Thanks Steve,

That worked a treat.

It looks like you have been coding a long time, I understand most of it.

My learning of VBA is coming on but the next big jump for myself is to be presented with a problem like this and know which code to write, run or loop to get one answer or solution.

I guess it's all about breaking it down into chunks and putting it all together at the end or testing the data.

I have a few VBA courses with tips and tricks but I learn better from real live examples like this.

Thanks again for your help.

Darren

[ssanfu]

You are welcome.... glad to help.

I guess it's all about breaking it down into chunks and putting it all together at the end or testing the data.

Very true!

The "Old Programmer's Rule" is this: If you can't do it on paper then you can't do it in Access. Until you can draw out your tables AND rules for how they change for each function you want to perform, you are going nowhere fast.

This applies to writing code also. If you cannot write the process down on paper (even in pseudo code), you can't write effective code to accomplish solving the problem.

Good luck on your project...

[djspod]

Thanks, sounds like good logic

Sent from my SM-G935F using Tapatalk