Dates in subform

I need to make a Form where the user type:

SampleID: 1234

StartDate: 01-01-2010

EndDate: 01-01-2015

CheckEvery: 3

(There will be more fields but these are the most important)

On this form there should be a subForm because in the subForm it will show
all the due dates:

0 3 6 9
12 15
01-01-2010 01-03-2010 01-06-2010 01-09-2010 01-12-2010 01-03-
2012

...and it continues until it reaches the last date 01-01-2015.

In the “checkEvery� textbox the user should be able to write a number. In
this case I wrote 3 – means that the subform should write the date for every
3rd month. I don’t know how to make this subform to add dates automatically?

Is it also possible to make a checkbox for every date it shows? So you can
mark that the sampleID has been analyzed that date?

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/For...forms/201002/1

On this form there should be a subForm because in the subForm it will show
all the due dates:
0 3 6 9
12 15
01-01-2010 01-03-2010 01-06-2010 01-09-2010 01-12-2010 01-03-
2012

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201002/1

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

--
Daryl S


"jubiiab via AccessMonster.com" wrote:

I need to make a Form where the user type:

SampleID: 1234

StartDate: 01-01-2010

EndDate: 01-01-2015

CheckEvery: 3

(There will be more fields but these are the most important)

On this form there should be a subForm because in the subForm it will show
all the due dates:

0 3 6 9
12 15
01-01-2010 01-03-2010 01-06-2010 01-09-2010 01-12-2010 01-03-
2012

...and it continues until it reaches the last date 01-01-2015.

In the “checkEvery� textbox the user should be able to write a number. In
this case I wrote 3 – means that the subform should write the date for every
3rd month. I don’t know how to make this subform to add dates automatically?

Is it also possible to make a checkbox for every date it shows? So you can
mark that the sampleID has been analyzed that date?

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/For...forms/201002/1

.

Hi Daryl

What kind of details do u need more?

I will look at your answer tomorrow at work. thx for your time.


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/For...forms/201002/1

What you are asking to create is better done in Excel with its autofill.
Fill in the date in 3 cells, highlight those three, use cursor to grab the
lower right corner and then drag to the right until you the number of sells
filled you need. A little popup will follow along telling you the date in
the last cell.

--
Build a little, test a little.


"jubiiab via AccessMonster.com" wrote:

On this form there should be a subForm because in the subForm it will show
all the due dates:
0 3 6 9
12 15
01-01-2010 01-03-2010 01-06-2010 01-09-2010 01-12-2010 01-03-
2012

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201002/1

.

@ 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/For...forms/201002/1

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/For...forms/201002/1

.

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/For...forms/201003/1

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/For...forms/201003/1

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/For...forms/201003/1

.