Dates in subform
in [Form]
|
Prev: Display a message while a form loads
Next: Combo ?
From: jubiiab via AccessMonster.com on 2 Mar 2010 03:52 The start date and the end date is now included but I still have few more problems. 1st: The table keep showing the date format like this: mm-dd-yyyy. Can I change it to dd-mm-yyyy? I have tried to change the input mask in the table but it didn't work?? 2nd the iCounter is not working. I need [EveryMonth] field to start with 0 and then add the [checkEvery] value on the next. Like if the checkEvery value is 3 then it should count like this: 0 3 6 9 12…..and so on. [SampleID] [EveryMonth] [EveryMonthDate] [Done] 6 0 01-01-2011 (checkbox) 6 3 01-04-2011 (checkbox) 6 6 01-07-2011 (checkbox) 6 9 01-10-2012 (checkbox) 6 12 01-01-2012 (checkbox) 6 15 01-04-2012 (checkbox) 6 18 01-07-2012 (checkbox) 6 21 01-10-2012 (checkbox) 6 24 01-01-2013 (checkbox) Below you can see the new code for the mcdInsertDate button Private Sub cmdInsertDate_Click() Dim dtDue As Date Dim iCounter As Integer dtDue = Me.StartDate 'the first due date iCounter = Me.CheckEvery.Value Do Until dtDue > Me.EndDate DoCmd.SetWarnings (False) DoCmd.RunSQL "INSERT Into tblDate ([SampleID],[EveryMonth],[EveryMonthDate]) Values (" & _ Me.SampleID & ", " & (Me.CheckEvery + iCounter) & ", #" & dtDue & "#)" dtDue = DateAdd("m", Me.CheckEvery, dtDue) 'increment the due date DoCmd.SetWarnings (True) Loop Me.subDueDate.Requery End Sub Daryl S wrote: >Jujiiab - > >I see from your sample you want the original date, and the 'every date' to >be incremented. Also the start date. I don't know if you want the start >date incremented weekly or always the original start date. In this code, I >have the start date fixed. If you want, you can set up a variable and >increment the same way the end date is incremented. > >Anyway, here goes (untested, plus check the field names): > >Private Sub cmdInsertDate_Click() >Dim dtDue As Date >Dim iCounter as Integer > >dtDue = Me.StartDate 'the first due date >iCounter = 0 > >Do Until dtDue > Me.EndDate > DoCmd.RunSQL "INSERT Into tblDate >([SampleID],[EveryMonth],[EveryMonthDate], [EveryStartDate]) >Values (" & _ > Me.SampleID & ", " & (Me.CheckEvery * iCounter) & ", #" & dtDue & "#, #" & >Me.StartDate & "#)" > dtDue = DateAdd("m", Me.CheckEvery, dtDue) 'increment the due date >Loop > >End Sub > >> I didnt got this part... >> >[quoted text clipped - 65 lines] >> >>> >> I am sorry about my English. I know it's not good but I hope you understand >> >>> >> the question or please ask. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/201003/1
From: Daryl S on 2 Mar 2010 10:25 Jubiiab - The default date format is controlled by your Windows settings (language, date, and regional options). You can have it display any way you want on forms, queries, etc. by formatting the control that the date will show up in. The results you are showing in your code look like what you are asking for. Or are you showing what you want? If so, please show what you are getting so we can narrow down the problem. Remember if you are testing with the same SampleID to delete the old data from tblDate for that SampleID before adding the new data, as you only want to see what the new code adds. You indicated you were getting both the start and end date now, but it looks like the startdate is removed from the code? Can you please run the code you have and post both what was selected on the form and the results, then tell us what the problem is with the results? -- Daryl S "jubiiab via AccessMonster.com" wrote: > The start date and the end date is now included but I still have few more > problems. > 1st: The table keep showing the date format like this: mm-dd-yyyy. Can I > change it to dd-mm-yyyy? I have tried to change the input mask in the table > but it didn't work?? > > 2nd the iCounter is not working. I need [EveryMonth] field to start with 0 > and then add the [checkEvery] value on the next. Like if the checkEvery value > is 3 then it should count like this: 0 3 6 9 12…..and so on. > > [SampleID] [EveryMonth] [EveryMonthDate] [Done] > 6 0 01-01-2011 (checkbox) > 6 3 01-04-2011 (checkbox) > 6 6 01-07-2011 (checkbox) > 6 9 01-10-2012 (checkbox) > 6 12 01-01-2012 (checkbox) > 6 15 01-04-2012 (checkbox) > 6 18 01-07-2012 (checkbox) > 6 21 01-10-2012 (checkbox) > 6 24 01-01-2013 (checkbox) > > > Below you can see the new code for the mcdInsertDate button > > Private Sub cmdInsertDate_Click() > > Dim dtDue As Date > Dim iCounter As Integer > > dtDue = Me.StartDate 'the first due date > iCounter = Me.CheckEvery.Value > > Do Until dtDue > Me.EndDate > > DoCmd.SetWarnings (False) > > DoCmd.RunSQL "INSERT Into tblDate ([SampleID],[EveryMonth],[EveryMonthDate]) > Values (" & _ > Me.SampleID & ", " & (Me.CheckEvery + iCounter) & ", #" & dtDue & "#)" > dtDue = DateAdd("m", Me.CheckEvery, dtDue) 'increment the due date > > DoCmd.SetWarnings (True) > > Loop > > Me.subDueDate.Requery > > End Sub > > > Daryl S wrote: > >Jujiiab - > > > >I see from your sample you want the original date, and the 'every date' to > >be incremented. Also the start date. I don't know if you want the start > >date incremented weekly or always the original start date. In this code, I > >have the start date fixed. If you want, you can set up a variable and > >increment the same way the end date is incremented. > > > >Anyway, here goes (untested, plus check the field names): > > > >Private Sub cmdInsertDate_Click() > >Dim dtDue As Date > >Dim iCounter as Integer > > > >dtDue = Me.StartDate 'the first due date > >iCounter = 0 > > > >Do Until dtDue > Me.EndDate > > DoCmd.RunSQL "INSERT Into tblDate > >([SampleID],[EveryMonth],[EveryMonthDate], [EveryStartDate]) > >Values (" & _ > > Me.SampleID & ", " & (Me.CheckEvery * iCounter) & ", #" & dtDue & "#, #" & > >Me.StartDate & "#)" > > dtDue = DateAdd("m", Me.CheckEvery, dtDue) 'increment the due date > >Loop > > > >End Sub > > > >> I didnt got this part... > >> > >[quoted text clipped - 65 lines] > >> >>> >> I am sorry about my English. I know it's not good but I hope you understand > >> >>> >> the question or please ask. > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/201003/1 > > . >
From: jubiiab via AccessMonster.com on 3 Mar 2010 03:43 Hi daryl, First I want to thank you for your time – you are truly a great person. :) Below you see the database, tables, forms and cmdButton code I am using right now: Database name: Sampledb Table name: tblSample [SampleID_PK] [StartDate] [EndDate] [CheckEvery] Table name: tblDate [DateID_PK] [SampleID_FK] [EveryMonth] [EveryMonthDate] [Done] Form name: frmSample Subform name: subDueDate I have created a button with caption: “Insert Date” and the code is: Private Sub cmdInsertDate_Click() Dim dtDue As Date Dim iCounter As Integer dtDue = Me.StartDate 'the first due date iCounter = Me.CheckEvery.Value Do Until dtDue > Me.EndDate DoCmd.SetWarnings (False) DoCmd.RunSQL "INSERT Into tblDate ([SampleID],[EveryMonth],[EveryMonthDate]) Values (" & _ Me.SampleID & ", " & (Me.CheckEvery + iCounter) & ", #" & dtDue & "#)" dtDue = DateAdd("m", Me.CheckEvery, dtDue) 'increment the due date DoCmd.SetWarnings (True) Loop Me.subDueDate.Requery End Sub Below you see the frmSample fields and data I inter and what the result I get. SampleID: [6] StartDate: [01-10-2011] EndDate: [01-10-2013] CheckEvery: [3] 'Results I get right now from the Data above in the subDate form. Notice, I do get the [StartDate] and [EndDate] in the table. Only [EveryMonth] is wrong: SampleID] [EveryMonth] [EveryMonthDate] [Done] 6 3 10-01-2011 (checkbox) 6 3 01-01-2012 (checkbox) 6 3 04-01-2012 (checkbox) 6 3 07-01-2012 (checkbox) 6 3 10-01-2012 (checkbox) 6 3 01-01-2013 (checkbox) 6 3 04-01-2013 (checkbox) 6 3 07-01-2013 (checkbox) 6 3 10-01-2013 (checkbox) I want it to look more like the table below. Notice [EveryMonth]. It starts with 0 and adds with 3 because the value in [CheckEvery] is 3. If the value was 6 it had to add like this 0 6 12 18….: [SampleID] [EveryMonth] [EveryMonthDate] [Done] 6 0 01-10-2011 (checkbox) 6 3 01-01-2012 (checkbox) 6 6 01-04-2012 (checkbox) 6 9 01-07-2012 (checkbox) 6 12 01-10-2012 (checkbox) 6 15 01-01-2013 (checkbox) 6 18 01-04-2013 (checkbox) 6 21 01-07-2013 (checkbox) 6 24 01-10-2013 (checkbox) I have been in the Controlpanel Regional and language Options. The Date format is set to dd-mm-yyyy but I get in the tblDate mm-dd-yyyy. Daryl S wrote: >Jubiiab - > >The default date format is controlled by your Windows settings (language, >date, and regional options). You can have it display any way you want on >forms, queries, etc. by formatting the control that the date will show up in. > >The results you are showing in your code look like what you are asking for. >Or are you showing what you want? If so, please show what you are getting so >we can narrow down the problem. Remember if you are testing with the same >SampleID to delete the old data from tblDate for that SampleID before adding >the new data, as you only want to see what the new code adds. > >You indicated you were getting both the start and end date now, but it looks >like the startdate is removed from the code? > >Can you please run the code you have and post both what was selected on the >form and the results, then tell us what the problem is with the results? > >> The start date and the end date is now included but I still have few more >> problems. >[quoted text clipped - 78 lines] >> >> >>> >> I am sorry about my English. I know it's not good but I hope you understand >> >> >>> >> the question or please ask. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/201003/1
From: jubiiab via AccessMonster.com on 3 Mar 2010 03:54 PLEASE NOTICE that in the eksample above I made a mistake in the result table I get. I wrote in [EveryMonth] 3 3 3 3 3 3 3... but I get 6 6 6 6 6 6..... becasue of this code in the cmdInsert button: (Me.CheckEvery + iCounter) Sorry. jubiiab wrote: >Hi daryl, >First I want to thank you for your time – you are truly a great person. :) > >Below you see the database, tables, forms and cmdButton code I am using right >now: > >Database name: Sampledb >Table name: tblSample [SampleID_PK] [StartDate] [EndDate] [CheckEvery] >Table name: tblDate [DateID_PK] [SampleID_FK] [EveryMonth] [EveryMonthDate] >[Done] >Form name: frmSample >Subform name: subDueDate > >I have created a button with caption: “Insert Date” and the code is: > >Private Sub cmdInsertDate_Click() > >Dim dtDue As Date >Dim iCounter As Integer > >dtDue = Me.StartDate 'the first due date >iCounter = Me.CheckEvery.Value > >Do Until dtDue > Me.EndDate > >DoCmd.SetWarnings (False) > >DoCmd.RunSQL "INSERT Into tblDate ([SampleID],[EveryMonth],[EveryMonthDate]) >Values (" & _ >Me.SampleID & ", " & (Me.CheckEvery + iCounter) & ", #" & dtDue & "#)" >dtDue = DateAdd("m", Me.CheckEvery, dtDue) 'increment the due date > >DoCmd.SetWarnings (True) > >Loop > >Me.subDueDate.Requery > >End Sub > >Below you see the frmSample fields and data I inter and what the result I get. > >SampleID: [6] >StartDate: [01-10-2011] >EndDate: [01-10-2013] >CheckEvery: [3] > >'Results I get right now from the Data above in the subDate form. Notice, I >do get the [StartDate] and [EndDate] in the table. Only [EveryMonth] is wrong: > >SampleID] [EveryMonth] [EveryMonthDate] [Done] > 6 3 10-01-2011 (checkbox) > 6 3 01-01-2012 (checkbox) > 6 3 04-01-2012 (checkbox) > 6 3 07-01-2012 (checkbox) > 6 3 10-01-2012 (checkbox) > 6 3 01-01-2013 (checkbox) > 6 3 04-01-2013 (checkbox) > 6 3 07-01-2013 (checkbox) > 6 3 10-01-2013 (checkbox) > >I want it to look more like the table below. Notice [EveryMonth]. It starts >with 0 and adds with 3 because the value in [CheckEvery] is 3. If the value >was 6 it had to add like this 0 6 12 18….: > >[SampleID] [EveryMonth] [EveryMonthDate] [Done] > 6 0 01-10-2011 (checkbox) > 6 3 01-01-2012 (checkbox) > 6 6 01-04-2012 (checkbox) > 6 9 01-07-2012 (checkbox) > 6 12 01-10-2012 (checkbox) > 6 15 01-01-2013 (checkbox) > 6 18 01-04-2013 (checkbox) > 6 21 01-07-2013 (checkbox) > 6 24 01-10-2013 (checkbox) > >I have been in the Controlpanel Regional and language Options. The Date >format is set to dd-mm-yyyy but I get in the tblDate mm-dd-yyyy. > >>Jubiiab - >> >[quoted text clipped - 19 lines] >>> >> >>> >> I am sorry about my English. I know it's not good but I hope you understand >>> >> >>> >> the question or please ask. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/201003/1
From: jubiiab via AccessMonster.com on 4 Mar 2010 04:19
*bump -- Message posted via http://www.accessmonster.com |