By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,581 Members | 2,023 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,581 IT Pros & Developers. It's quick & easy.

dates and msflexgrid problem trying to loop dates on a grid control for 2 weeks

P: 14
I have this code which puts correct dates on msflexgrid control for weekly rotas on my program. What it currently does is checks which day is monday and the loops it through creating dates accordingly to as monday, tuesday etc.

now I have a problem trying to create a rota for 2 weeks instead of one. if i just copy the code it will work for the time being but as soon as that week is over both rotas just skip a whole week instead of running like 14 days. 1 , 2 , 3 , 4 .... 14 and then loop both rotas on to staring 15 (monday) 16, 17 , 28 etc.

Expand|Select|Wrap|Line Numbers
  1. Dim f As Integer
  2. dim tDate as Date
  3. tDate = now
  4.     For f = 1 To 7
  5.        tDate = Format(tDate, "d/m/yy")
  6.        grdRota.TextMatrix(1, f) = DateAdd("d", f - 1, tDate)
  7.  
  8.    Next f
grdRota is msflexgrid1.
Jan 29 '13 #1
Share this Question
Share on Google+
19 Replies


Rabbit
Expert Mod 10K+
P: 12,374
I'm not sure I understand the question. Some sample data would help.
Jan 29 '13 #2

P: 14
Basically my program generates rota from access database. But I want my dates to be added automatically. Which that what the code does. To see create a new form and add msflexgrid control on it set cols to 8 and paste the code in form initialize event. Also need to declare tdate as date. My problem is I want to create rota for 2 weeks either using one msflexgrid control or 2. What happens now is when dates are looped on the grid it changes as soon as the week over automatically. I need to automatically change the dates only after 2 weeks are lapsed. If system date is 1st of Feb I want it to loop for whole14 days and only change to next 14 days on the 15th of feb. I hope I make sense. Thanks for support
Jan 29 '13 #3

Rabbit
Expert Mod 10K+
P: 12,374
I see nothing in your code that resets the values after each week. I only see code that sets values based on the current date. There's nothing in there to rerun the code when a week is over.
Jan 29 '13 #4

P: 14
I tried to reset the values putting in an if statement in the loop to check if it's a new week but it is not working. How would you reset the counter?
Jan 29 '13 #5

P: 14
The problem is I don't know What value could be used to determine that the week is over.
Jan 29 '13 #6

Rabbit
Expert Mod 10K+
P: 12,374
What you need to do is check if the current date is greater the last date in the grid. If it is, repopulate the grid.
Jan 29 '13 #7

P: 14
Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim*f*As*Integer
  3. dim*tDate*as*Date
  4. tDate*=*now
  5. ****For*f*=*1*To*7
  6. *******tDate*=*Format(tDate,*"d/m/yy")
  7. *******grdRota.TextMatrix(1,*f)*=*DateAdd("d",*f*-*1,*tDate)
  8. ***Next*f
  9. tdate = now
  10. Dim dtcheck as date
  11. dtCheck = grdrota.textmatrix(1,7)
  12. If tdate>dtcheck then
  13.       tdate=now -7
  14. ****For*f*=*1*To*7
  15. *******tDate*=*Format(tDate,*"d/m/yy")
  16. *******grdRota.TextMatrix(1,*f)*=*DateAdd("d",*f*-*1,*tDate)
  17. *
  18. ***Next*f
  19.  
  20.  
Something like that?
Jan 29 '13 #8

P: 14
Not sure why all these stars come from, sorry about that.
Jan 29 '13 #9

Rabbit
Expert Mod 10K+
P: 12,374
Not a problem, it happens sometimes when you copy and paste on this forum.

That looks almost right to me. I just don't understand why you're running the population code twice on the same row.
Jan 29 '13 #10

P: 14
Because I am repopulating the dates on the rota. So now hopefully it will do a check and if it returns true then it should repopulate and the dates should be staying the same. Then i can do another check when current date is greater than it is on the second Row like day 14. Then loop it through to add dates starting from 15th. What do you exactly mean by running it twice on the same row?
Jan 29 '13 #11

Rabbit
Expert Mod 10K+
P: 12,374
You populate it on grdRota.TextMatrix(1,f) before the check. Then you populate it again in the same place after the check.
Jan 29 '13 #12

P: 14
I assume if I don't repopulate it then the dates will change automatically but I don't want that to happen, I want it to stay the same for 14 days but as soon as 8 days lapses then it assumes its a new week and starts to loop it again
Jan 29 '13 #13

Rabbit
Expert Mod 10K+
P: 12,374
I know, that's what the check is for.

But it still doesn't make sense why you would populate anything before the check and why when you populate it after the check, you replace the original population.
Jan 29 '13 #14

P: 14
Because I would not have any values to do a check from. If I do a check from msflexgrid.textmatrix(1,7) before the loop I would have nothing in that row to do a check from.
Jan 30 '13 #15

P: 14
I think I am going to need a text file or something like that to store that date and the compare it and at some point rewrite it again
Jan 30 '13 #16

Rabbit
Expert Mod 10K+
P: 12,374
I had assumed you were storing the values somewhere and repopulating it on load. Otherwise, the problem you would be having is that the values get repopulated every time the program is opened. Instead, the problem you're having is that the values change after one week instead of two. This implied that you were storing off the dates somewhere. If that is not the case, then that's what you need to do. Otherwise, you're going to run into the first problem in this post.
Jan 30 '13 #17

P: 14
how would you implement this? I do not want the user to be typing it, i want it to be added there automatically. any sample code? thank you
Jan 30 '13 #18

Rabbit
Expert Mod 10K+
P: 12,374
Implement what? Storing the dates in between runs of the program? You have a few options. You can store it in a text file, or in the registry, or in a database. How you do it depends on which option you prefer.

If you need further help with that, you will need to create a new thread. We only allow one question per thread and that is an entirely different question than what was first asked in this thread.

Also, you should be aware that this forum is not a code writing service. You will have to post some code showing that you attempted a solution. If you are unsure where to start, we can offer some general advice but we generally do not write code unless the poster has shown that they have already attempted to do some of that coding work themselves.
Jan 30 '13 #19

P: 14
finally! thanks for all your help i got it cracked, just needs loads of testing now but looks good, by the way I didn't come here to ask people to write me code.

here is what I wrote and it works. no loops just simple logic

Expand|Select|Wrap|Line Numbers
  1. Public Sub newWeekCheck()
  2. Dim res As Integer
  3. Dim tDate As Date
  4. Dim NumD As Integer
  5. Dim adddate As String
  6. adddate = str(DateAdd("d", 1, Format(Now, "dd-mm-yyyy")))
  7. NumD = Weekday(Now)
  8.  dtcheck.DatabaseName = App.Path & "\check97.mdb"
  9.  
  10.   Select Case NumD
  11.     Case 1
  12.  
  13.  
  14.  
  15.     grdRota.TextMatrix(1, 1) = dtcheck.Recordset!mon
  16.     grdRota.TextMatrix(1, 2) = dtcheck.Recordset!tue
  17.     grdRota.TextMatrix(1, 3) = dtcheck.Recordset!wed
  18.     grdRota.TextMatrix(1, 4) = dtcheck.Recordset!thu
  19.     grdRota.TextMatrix(1, 5) = dtcheck.Recordset!fri
  20.     grdRota.TextMatrix(1, 6) = dtcheck.Recordset!sat
  21.     grdRota.TextMatrix(1, 7) = dtcheck.Recordset!sun
  22.  
  23.     grdweek2.TextMatrix(1, 1) = dtcheck.Recordset!mon1
  24.     grdweek2.TextMatrix(1, 2) = dtcheck.Recordset!tue1
  25.     grdweek2.TextMatrix(1, 3) = dtcheck.Recordset!wed1
  26.     grdweek2.TextMatrix(1, 4) = dtcheck.Recordset!thu1
  27.     grdweek2.TextMatrix(1, 5) = dtcheck.Recordset!fri1
  28.     grdweek2.TextMatrix(1, 6) = dtcheck.Recordset!sat1
  29.     grdweek2.TextMatrix(1, 7) = dtcheck.Recordset!sun1
  30.  
  31.     Dim dtchecks As Date
  32.     dtchecks = dtcheck.Recordset!sun
  33.     tDate = Now
  34.     tDate = Format(tDate, "d/m/yy")
  35.  
  36.     Dim newweek As Date
  37.     Dim intnewweek As Integer
  38.     newweek = dtcheck.Recordset!sat1
  39.     tDate = Now
  40.     tDate = Format(tDate, "d/mm/yy")
  41. If newweek < tDate Then
  42.     intnewweek = MsgBox("Do you want to create a new rota commencing " & tDate + 1, vbYesNo, "New Week?")
  43.     If intnewweek = 6 Then
  44.  
  45.         tDate = Format(tDate, "d/m/yy")
  46.         dtcheck.Recordset.MoveFirst
  47.         dtcheck.Recordset.Edit
  48.         dtcheck.Recordset.Fields("mon") = newweek + 2
  49.         dtcheck.Recordset.Fields("tue") = newweek + 3
  50.         dtcheck.Recordset.Fields("wed") = newweek + 4
  51.         dtcheck.Recordset.Fields("thu") = newweek + 5
  52.         dtcheck.Recordset.Fields("fri") = newweek + 6
  53.         dtcheck.Recordset.Fields("sat") = newweek + 7
  54.         dtcheck.Recordset.Fields("sun") = newweek + 8
  55.  
  56.         dtcheck.Recordset.Fields("mon1") = newweek + 9
  57.         dtcheck.Recordset.Fields("tue1") = newweek + 10
  58.         dtcheck.Recordset.Fields("wed1") = newweek + 11
  59.         dtcheck.Recordset.Fields("thu1") = newweek + 12
  60.         dtcheck.Recordset.Fields("fri1") = newweek + 13
  61.         dtcheck.Recordset.Fields("sat1") = newweek + 14
  62.         dtcheck.Recordset.Fields("sun1") = newweek + 15
  63.         dtcheck.Recordset.Update
  64.         grdRota.TextMatrix(1, 1) = dtcheck.Recordset!mon
  65.         grdRota.TextMatrix(1, 2) = dtcheck.Recordset!tue
  66.         grdRota.TextMatrix(1, 3) = dtcheck.Recordset!wed
  67.         grdRota.TextMatrix(1, 4) = dtcheck.Recordset!thu
  68.         grdRota.TextMatrix(1, 5) = dtcheck.Recordset!fri
  69.         grdRota.TextMatrix(1, 6) = dtcheck.Recordset!sat
  70.         grdRota.TextMatrix(1, 7) = dtcheck.Recordset!sun
  71.  
  72.         grdweek2.TextMatrix(1, 1) = dtcheck.Recordset!mon1
  73.         grdweek2.TextMatrix(1, 2) = dtcheck.Recordset!tue1
  74.         grdweek2.TextMatrix(1, 3) = dtcheck.Recordset!wed1
  75.         grdweek2.TextMatrix(1, 4) = dtcheck.Recordset!thu1
  76.         grdweek2.TextMatrix(1, 5) = dtcheck.Recordset!fri1
  77.         grdweek2.TextMatrix(1, 6) = dtcheck.Recordset!sat1
  78.         grdweek2.TextMatrix(1, 7) = dtcheck.Recordset!sun1
  79.     End If
  80. End If
  81.  
  82.  
  83.  
  84.  
  85.     Case 2
  86.     grdRota.TextMatrix(1, 1) = dtcheck.Recordset!mon
  87.     grdRota.TextMatrix(1, 2) = dtcheck.Recordset!tue
  88.     grdRota.TextMatrix(1, 3) = dtcheck.Recordset!wed
  89.     grdRota.TextMatrix(1, 4) = dtcheck.Recordset!thu
  90.     grdRota.TextMatrix(1, 5) = dtcheck.Recordset!fri
  91.     grdRota.TextMatrix(1, 6) = dtcheck.Recordset!sat
  92.     grdRota.TextMatrix(1, 7) = dtcheck.Recordset!sun
  93.  
  94.     grdweek2.TextMatrix(1, 1) = dtcheck.Recordset!mon1
  95.     grdweek2.TextMatrix(1, 2) = dtcheck.Recordset!tue1
  96.     grdweek2.TextMatrix(1, 3) = dtcheck.Recordset!wed1
  97.     grdweek2.TextMatrix(1, 4) = dtcheck.Recordset!thu1
  98.     grdweek2.TextMatrix(1, 5) = dtcheck.Recordset!fri1
  99.     grdweek2.TextMatrix(1, 6) = dtcheck.Recordset!sat1
  100.     grdweek2.TextMatrix(1, 7) = dtcheck.Recordset!sun1
  101.     Case 3
  102.     grdRota.TextMatrix(1, 1) = dtcheck.Recordset!mon
  103.     grdRota.TextMatrix(1, 2) = dtcheck.Recordset!tue
  104.     grdRota.TextMatrix(1, 3) = dtcheck.Recordset!wed
  105.     grdRota.TextMatrix(1, 4) = dtcheck.Recordset!thu
  106.     grdRota.TextMatrix(1, 5) = dtcheck.Recordset!fri
  107.     grdRota.TextMatrix(1, 6) = dtcheck.Recordset!sat
  108.     grdRota.TextMatrix(1, 7) = dtcheck.Recordset!sun
  109.  
  110.     grdweek2.TextMatrix(1, 1) = dtcheck.Recordset!mon1
  111.     grdweek2.TextMatrix(1, 2) = dtcheck.Recordset!tue1
  112.     grdweek2.TextMatrix(1, 3) = dtcheck.Recordset!wed1
  113.     grdweek2.TextMatrix(1, 4) = dtcheck.Recordset!thu1
  114.     grdweek2.TextMatrix(1, 5) = dtcheck.Recordset!fri1
  115.     grdweek2.TextMatrix(1, 6) = dtcheck.Recordset!sat1
  116.     grdweek2.TextMatrix(1, 7) = dtcheck.Recordset!sun1
  117.     Case 4
  118.     grdRota.TextMatrix(1, 1) = dtcheck.Recordset!mon
  119.     grdRota.TextMatrix(1, 2) = dtcheck.Recordset!tue
  120.     grdRota.TextMatrix(1, 3) = dtcheck.Recordset!wed
  121.     grdRota.TextMatrix(1, 4) = dtcheck.Recordset!thu
  122.     grdRota.TextMatrix(1, 5) = dtcheck.Recordset!fri
  123.     grdRota.TextMatrix(1, 6) = dtcheck.Recordset!sat
  124.     grdRota.TextMatrix(1, 7) = dtcheck.Recordset!sun
  125.  
  126.     grdweek2.TextMatrix(1, 1) = dtcheck.Recordset!mon1
  127.     grdweek2.TextMatrix(1, 2) = dtcheck.Recordset!tue1
  128.     grdweek2.TextMatrix(1, 3) = dtcheck.Recordset!wed1
  129.     grdweek2.TextMatrix(1, 4) = dtcheck.Recordset!thu1
  130.     grdweek2.TextMatrix(1, 5) = dtcheck.Recordset!fri1
  131.     grdweek2.TextMatrix(1, 6) = dtcheck.Recordset!sat1
  132.     grdweek2.TextMatrix(1, 7) = dtcheck.Recordset!sun1
  133.     Case 5
  134.     grdRota.TextMatrix(1, 1) = dtcheck.Recordset!mon
  135.     grdRota.TextMatrix(1, 2) = dtcheck.Recordset!tue
  136.     grdRota.TextMatrix(1, 3) = dtcheck.Recordset!wed
  137.     grdRota.TextMatrix(1, 4) = dtcheck.Recordset!thu
  138.     grdRota.TextMatrix(1, 5) = dtcheck.Recordset!fri
  139.     grdRota.TextMatrix(1, 6) = dtcheck.Recordset!sat
  140.     grdRota.TextMatrix(1, 7) = dtcheck.Recordset!sun
  141.  
  142.     grdweek2.TextMatrix(1, 1) = dtcheck.Recordset!mon1
  143.     grdweek2.TextMatrix(1, 2) = dtcheck.Recordset!tue1
  144.     grdweek2.TextMatrix(1, 3) = dtcheck.Recordset!wed1
  145.     grdweek2.TextMatrix(1, 4) = dtcheck.Recordset!thu1
  146.     grdweek2.TextMatrix(1, 5) = dtcheck.Recordset!fri1
  147.     grdweek2.TextMatrix(1, 6) = dtcheck.Recordset!sat1
  148.     grdweek2.TextMatrix(1, 7) = dtcheck.Recordset!sun1
  149.     Case 6
  150.     grdRota.TextMatrix(1, 1) = dtcheck.Recordset!mon
  151.     grdRota.TextMatrix(1, 2) = dtcheck.Recordset!tue
  152.     grdRota.TextMatrix(1, 3) = dtcheck.Recordset!wed
  153.     grdRota.TextMatrix(1, 4) = dtcheck.Recordset!thu
  154.     grdRota.TextMatrix(1, 5) = dtcheck.Recordset!fri
  155.     grdRota.TextMatrix(1, 6) = dtcheck.Recordset!sat
  156.     grdRota.TextMatrix(1, 7) = dtcheck.Recordset!sun
  157.  
  158.     grdweek2.TextMatrix(1, 1) = dtcheck.Recordset!mon1
  159.     grdweek2.TextMatrix(1, 2) = dtcheck.Recordset!tue1
  160.     grdweek2.TextMatrix(1, 3) = dtcheck.Recordset!wed1
  161.     grdweek2.TextMatrix(1, 4) = dtcheck.Recordset!thu1
  162.     grdweek2.TextMatrix(1, 5) = dtcheck.Recordset!fri1
  163.     grdweek2.TextMatrix(1, 6) = dtcheck.Recordset!sat1
  164.     grdweek2.TextMatrix(1, 7) = dtcheck.Recordset!sun1
  165.     Case 7
  166.     grdRota.TextMatrix(1, 1) = dtcheck.Recordset!mon
  167.     grdRota.TextMatrix(1, 2) = dtcheck.Recordset!tue
  168.     grdRota.TextMatrix(1, 3) = dtcheck.Recordset!wed
  169.     grdRota.TextMatrix(1, 4) = dtcheck.Recordset!thu
  170.     grdRota.TextMatrix(1, 5) = dtcheck.Recordset!fri
  171.     grdRota.TextMatrix(1, 6) = dtcheck.Recordset!sat
  172.     grdRota.TextMatrix(1, 7) = dtcheck.Recordset!sun
  173.  
  174.     grdweek2.TextMatrix(1, 1) = dtcheck.Recordset!mon1
  175.     grdweek2.TextMatrix(1, 2) = dtcheck.Recordset!tue1
  176.     grdweek2.TextMatrix(1, 3) = dtcheck.Recordset!wed1
  177.     grdweek2.TextMatrix(1, 4) = dtcheck.Recordset!thu1
  178.     grdweek2.TextMatrix(1, 5) = dtcheck.Recordset!fri1
  179.     grdweek2.TextMatrix(1, 6) = dtcheck.Recordset!sat1
  180.     grdweek2.TextMatrix(1, 7) = dtcheck.Recordset!sun1
  181.   End Select
  182. End Sub
  183.  
Jan 31 '13 #20

Post your reply

Sign in to post your reply or Sign up for a free account.