Dates in subform
in [Form]
|
Prev: Display a message while a form loads
Next: Combo ?
From: jubiiab via AccessMonster.com on 26 Feb 2010 03:40 @ Karyl I need to make it in Access and not Excel. This is just a small part of the Form and Database but a very necessary part. I know it can be done somehow in access I just don't know how to do yet. @ Daryl or anyone, You asked for more information. Right now I just made a simple test database: Database name: Sampledb Table name: tblSample [SampleID] [StartDate] [EndDate] [CheckEvery] Form name: frmSample I think I need to make a query and make some kind of crosstable and then add it as subform? I just can't make the code for that. Please help. Daryl S wrote: >Jujiiab - > >Since you didn't give any details, the answer will be general, but you can >take the idea and use your table/field/control names to do it. I guessed you >would have the SampleId and DueDate fields in the table behind your subform, >but you will need to use whatever is required for your table. > >Set up the form like you said, then add a button for the user to click on >when the data is ready. In the code behind the button, you want to check to >make sure the data is valid (e.g. both dates are entered and the end date is >at least [CheckEvery] months after the start date. > >Then append records as needed in a loop, something like this: > >Dim dtDue as Date > >dtDue = DateAdd("m", Me.CheckEvery, Me.StartDate) 'the first due date >Do Until dtDue > Me.EndDate > DoCmd.RunSQL "INSERT Into tablename ([SampleId],[DueDate]) Values (" & _ > Me.SampleID & ", #" & dtDue & "#)" > dtDue = DateAdd("m", Me.CheckEvery, dtDue) 'increment the due date >Loop > >Then requery the subform. >Me.subformname.requery > >> I need to make a Form where the user type: >> >[quoted text clipped - 27 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/201002/1
From: Daryl S on 26 Feb 2010 09:38 Jujiab - You need to create a new table to hold the 'due date' records. Something like this: tblDueDate SampleDueDateID (PK - autonumber) SampleID (FK) DueDate other fields you may need... Then on your main form, you can add a button to create the new due date records using the code I put in the last posting. -- Daryl S "jubiiab via AccessMonster.com" wrote: > @ Karyl I need to make it in Access and not Excel. This is just a small part > of the Form and Database but a very necessary part. > > I know it can be done somehow in access I just don't know how to do yet. > > @ Daryl or anyone, > > You asked for more information. Right now I just made a simple test database: > > Database name: Sampledb > Table name: tblSample [SampleID] [StartDate] [EndDate] [CheckEvery] > Form name: frmSample > > I think I need to make a query and make some kind of crosstable and then add > it as subform? I just can't make the code for that. Please help. > > > Daryl S wrote: > >Jujiiab - > > > >Since you didn't give any details, the answer will be general, but you can > >take the idea and use your table/field/control names to do it. I guessed you > >would have the SampleId and DueDate fields in the table behind your subform, > >but you will need to use whatever is required for your table. > > > >Set up the form like you said, then add a button for the user to click on > >when the data is ready. In the code behind the button, you want to check to > >make sure the data is valid (e.g. both dates are entered and the end date is > >at least [CheckEvery] months after the start date. > > > >Then append records as needed in a loop, something like this: > > > >Dim dtDue as Date > > > >dtDue = DateAdd("m", Me.CheckEvery, Me.StartDate) 'the first due date > >Do Until dtDue > Me.EndDate > > DoCmd.RunSQL "INSERT Into tablename ([SampleId],[DueDate]) Values (" & _ > > Me.SampleID & ", #" & dtDue & "#)" > > dtDue = DateAdd("m", Me.CheckEvery, dtDue) 'increment the due date > >Loop > > > >Then requery the subform. > >Me.subformname.requery > > > >> I need to make a Form where the user type: > >> > >[quoted text clipped - 27 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/201002/1 > > . >
From: jubiiab via AccessMonster.com on 1 Mar 2010 06:18 Hi Daryl Right now I have: 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 dtDue = DateAdd("m", Me.CheckEvery, Me.StartDate) 'the first due date Do Until dtDue > Me.EndDate DoCmd.RunSQL "INSERT Into tblDate ([SampleID],[EveryMonth],[EveryMonthDate]) Values (" & _ Me.SampleID & ", " & Me.CheckEvery & ", #" & dtDue & "#)" dtDue = DateAdd("m", Me.CheckEvery, dtDue) 'increment the due date Loop End Sub When I fill out the the form with this data: SampleID: 6 StartDate: 01-01-2011 EndDate: 01-01-2013 CheckEvery: 3 …and press the "Instert Date" button I get this data in the subform: [SampleID] [EveryMonth] [EveryMonthDate] [Done] 6 3 04-01-2011 (checkbox) 6 3 07-01-2011 (checkbox) 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) Dates are totally wrong and i need the startDate to be in the subform like the endDate. Thx for your help. Daryl S wrote: >Jujiab - > >You need to create a new table to hold the 'due date' records. Something >like this: > >tblDueDate >SampleDueDateID (PK - autonumber) >SampleID (FK) >DueDate >other fields you may need... > >Then on your main form, you can add a button to create the new due date >records using the code I put in the last posting. > >> @ Karyl I need to make it in Access and not Excel. This is just a small part >> of the Form and Database but a very necessary part. >[quoted text clipped - 43 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 1 Mar 2010 06:29 I didnt got this part... I wanted more like this: [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) jubiiab wrote: >Hi Daryl > >Right now I have: > >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 > >dtDue = DateAdd("m", Me.CheckEvery, Me.StartDate) 'the first due date >Do Until dtDue > Me.EndDate > DoCmd.RunSQL "INSERT Into tblDate ([SampleID],[EveryMonth],[EveryMonthDate]) >Values (" & _ > Me.SampleID & ", " & Me.CheckEvery & ", #" & dtDue & "#)" > dtDue = DateAdd("m", Me.CheckEvery, dtDue) 'increment the due date >Loop > >End Sub > >When I fill out the the form with this data: > >SampleID: 6 >StartDate: 01-01-2011 >EndDate: 01-01-2013 >CheckEvery: 3 > >…and press the "Instert Date" button I get this data in the subform: > >[SampleID] [EveryMonth] [EveryMonthDate] [Done] > 6 3 04-01-2011 (checkbox) > 6 3 07-01-2011 (checkbox) > 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) > >Dates are totally wrong and i need the startDate to be in the subform like >the endDate. > >Thx for your help. >>Jujiab - >> >[quoted text clipped - 15 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 1 Mar 2010 10:15
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 -- Daryl S "jubiiab via AccessMonster.com" wrote: > I didnt got this part... > > I wanted more like this: > > [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) > > > jubiiab wrote: > >Hi Daryl > > > >Right now I have: > > > >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 > > > >dtDue = DateAdd("m", Me.CheckEvery, Me.StartDate) 'the first due date > >Do Until dtDue > Me.EndDate > > DoCmd.RunSQL "INSERT Into tblDate ([SampleID],[EveryMonth],[EveryMonthDate]) > >Values (" & _ > > Me.SampleID & ", " & Me.CheckEvery & ", #" & dtDue & "#)" > > dtDue = DateAdd("m", Me.CheckEvery, dtDue) 'increment the due date > >Loop > > > >End Sub > > > >When I fill out the the form with this data: > > > >SampleID: 6 > >StartDate: 01-01-2011 > >EndDate: 01-01-2013 > >CheckEvery: 3 > > > >…and press the "Instert Date" button I get this data in the subform: > > > >[SampleID] [EveryMonth] [EveryMonthDate] [Done] > > 6 3 04-01-2011 (checkbox) > > 6 3 07-01-2011 (checkbox) > > 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) > > > >Dates are totally wrong and i need the startDate to be in the subform like > >the endDate. > > > >Thx for your help. > >>Jujiab - > >> > >[quoted text clipped - 15 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 > > . > |