Dates in subform

  • Thread starter jubiiab via AccessMonster.com
  • Start date
J

jubiiab via AccessMonster.com

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.
 
J

jubiiab via AccessMonster.com

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
 
D

Daryl S

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
 
J

jubiiab via AccessMonster.com

Hi Daryl

What kind of details do u need more?

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


Daryl said:
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.
 
K

KARL DEWEY

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.
 
J

jubiiab via AccessMonster.com

@ 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 said:
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.
 
D

Daryl S

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 said:
@ 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 said:
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.

--



.
 
J

jubiiab via AccessMonster.com

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 said:
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]
 
J

jubiiab via AccessMonster.com

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)

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.
[quoted text clipped - 15 lines]
 
D

Daryl S

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 said:
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)

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.
[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.

--



.
 
J

jubiiab via AccessMonster.com

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 said:
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]
 
D

Daryl S

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 said:
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 said:
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.

--



.
 
J

jubiiab via AccessMonster.com

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 said:
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]
 
J

jubiiab via AccessMonster.com

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.


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]
 
J

jubiiab via AccessMonster.com

I just realized that if I have these values:

StartDate: 30-01-2011
EndDate: 30-01-2013
CheckEvery: 3

I get the correct date format results (dd/mm/yyyy):
[SampleID] [EveryMonth] [EveryMonthDate] [Done]
6 6 30-01-2011 (checkbox)
6 6 30-04-2012 (checkbox)
6 6 30-07-2012 (checkbox)
6 6 30-10-2012 (checkbox)
6 6 30-01-2012 (checkbox)
6 6 30-04-2013 (checkbox)
6 6 30-07-2013 (checkbox)
6 6 30-10-2013 (checkbox)
6 6 30-01-2013 (checkbox)

But If the day is lower then 12 I get the wrong date format mm/dd/yyyy. I
always want it two show this format dd/mm/yyyy. I think the code in the
cmdInsertDate button makes the wrong date format. Must be the add function
dtDue = DateAdd("m", Me.CheckEvery, dtDue. How can I get the correct date
format….PLEASE HELP. You can see the cmdInsertDate button code in the
previous post.
 
J

John W. Vinson

I just realized that if I have these values:

StartDate: 30-01-2011
EndDate: 30-01-2013
CheckEvery: 3

I get the correct date format results (dd/mm/yyyy):
[SampleID] [EveryMonth] [EveryMonthDate] [Done]
6 6 30-01-2011 (checkbox)
6 6 30-04-2012 (checkbox)
6 6 30-07-2012 (checkbox)
6 6 30-10-2012 (checkbox)
6 6 30-01-2012 (checkbox)
6 6 30-04-2013 (checkbox)
6 6 30-07-2013 (checkbox)
6 6 30-10-2013 (checkbox)
6 6 30-01-2013 (checkbox)

But If the day is lower then 12 I get the wrong date format mm/dd/yyyy. I
always want it two show this format dd/mm/yyyy. I think the code in the
cmdInsertDate button makes the wrong date format. Must be the add function
dtDue = DateAdd("m", Me.CheckEvery, dtDue. How can I get the correct date
format….PLEASE HELP. You can see the cmdInsertDate button code in the
previous post.

I think you're running into a builtin feature of Access. It was programmed by
Americans, who use the mm/dd/yyyy format; as a result, a date literal MUST be
either in that format, or an unambiguous format such as yyyy-mm-dd.

Don't confuse data DISPLAY with data STORAGE. Your code needs to use the
mm/dd/yyyy format if you're converting text strings to dates, but you can set
the Format property of a Date/Time field to dd-mm-yyyy with no difficulties.

Your original message has scrolled off my list; if you could repost the code
in your cmdInsertDate button perhaps someone can show you what needs to be
tweaked.
 
J

John W. Vinson

But If the day is lower then 12 I get the wrong date format mm/dd/yyyy. I
always want it two show this format dd/mm/yyyy. I think the code in the
cmdInsertDate button makes the wrong date format. Must be the add function
dtDue = DateAdd("m", Me.CheckEvery, dtDue. How can I get the correct date
format….PLEASE HELP. You can see the cmdInsertDate button code in the
previous post.

OK... I went back to Google Groups and found your code. A one-line fix should
solve the problem:

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) & ", #" & _
Format(dtDue, "mm/dd/yyyy") & "#)"
dtDue = DateAdd("m", Me.CheckEvery, dtDue) 'increment the due date
DoCmd.SetWarnings (True)
Loop
Me.subDueDate.Requery
End Sub

This just formats your date into the Access mm/dd/yyyy form, rather than using
your computer's Regional setting.
 
D

Daryl S

Jubiiab -

This will fix the [EveryMonth] issue - start iCounter at zero; insert
iCounter into the record; and increment it by Me.CheckEvery in the loop
(replace these lines in your code):

iCounter = 0

Do Until dtDue > Me.EndDate

DoCmd.SetWarnings (False)

DoCmd.RunSQL "INSERT Into tblDate ([SampleID],[EveryMonth],[EveryMonthDate])
Values (" & _
Me.SampleID & ", " & (iCounter) & ", #" & dtDue & "#)"
dtDue = DateAdd("m", Me.CheckEvery, dtDue) 'increment the due date
iCounter = iCounter + Me.CheckEvery 'increment iCounter

As for the date format - is it only this table that is formatted as
dd-mm-yyyy? Or is it all date fields? If it is only this table, then check
the format of that field in the table. You may be right in that it is based
on the StartDate and EndDate controls. Are these controls dates or text? If
they are text, switch them to date.

Let us know!
 
J

jubiiab via AccessMonster.com

Hi guys – thx for your responses.

@Daryl
The iCounter works perfect now but I have the main problem – date formation.
If we can’t fix this, I will not be able to use this so please help. :0(

Let me try to explain again what the problem is right now with the date
formation.

If the dd > 12 I get the correct date format as I want: dd/mm/yyyy
If the dd < 12 I get the wrong date format : mm/dd/yyyy

I don’t understand why its keep changing the date format based on the value
of dd??

In my tables all [date] fields has the data type “date/timeâ€. Also [startDate]
and [EndDate]

This is what I get in the subform when I enter these values:

StartDate: [12-10-2011]
EndDate: [12-10-2013]
CheckEvery: [3]

SampleID] [EveryMonth] [EveryMonthDate] [Done]
6 0 10-12-2011 (checkbox)
6 3 01-12-2012 (checkbox)
6 6 04-12-2012 (checkbox)
6 9 07-12-2012 (checkbox)
6 12 10-12-2012 (checkbox)
6 15 01-12-2013 (checkbox)
6 18 04-12-2013 (checkbox)
6 21 07-12-2013 (checkbox)
6 24 10-12-2013 (checkbox)

StartDate: [13-10-2011]
EndDate: [13-10-2013
CheckEvery: [3]

[SampleID] [EveryMonth] [EveryMonthDate] [Done]
6 0 13-10-2011 (checkbox)
6 3 13-01-2012 (checkbox)
6 6 13-04-2012 (checkbox)
6 9 13-07-2012 (checkbox)
6 12 13-10-2012 (checkbox)
6 15 13-01-2013 (checkbox)
6 18 13-04-2013 (checkbox)
6 21 13-07-2013 (checkbox)
6 24 13-10-2013 (checkbox)
Notice the change of the date formation based on the value of dd?? (The last
example is the correct date formation: dd/mm/yyyy.)

@John
Hi John. You can follow this thread here:
http://www.accessmonster.com/Uwe/Forum.aspx/access-forms/62237/Dates-in-subform#a47f5f23cf49duwe


I used Format(dtDue, "dd/mm/yyyy") in the cmdInserteDate button but it didn’t
work. I also did the other way around Format(dtDue, "mm/dd/yyyy") but that
didn’t work either. :(

I also did this:

Private Sub cmdInsertDate_Click()

Dim dtDue As Date
Dim iCounter As Integer

dtDue = Format(Me.StartDate, "dd/mm/yyyy") 'the first due date
iCounter = 0

Do Until dtDue > Format(Me.EndDate, "dd/mm/yyyy")

DoCmd.SetWarnings (False)

DoCmd.RunSQL "INSERT Into tblDate ([SampleID],[EveryMonth],[EveryMonthDate])
Values (" & _
Me.SampleID & ", " & (iCounter) & ", #" & Format(dtDue, "dd/mm/yyyy") & "#)"
dtDue = DateAdd("m", Me.CheckEvery, Format(dtDue, "dd/mm/yyyy")) 'increment
the due date
iCounter = iCounter + Me.CheckEvery 'increment iCounter

DoCmd.SetWarnings (True)

Loop

Me.subDueDate.Requery

End Sub

….Still not working. Can’t understand why this date format making such a huge
problem??

I have all the information of my database and tables in this thread. It’s
just a test example so it is not that big. Maybe you guys could make the
database and the form on your computer and see if you get the same problem?
It will only take 5 min. to create it or I can also email my version to you
if you like?
Daryl said:
Jubiiab -

This will fix the [EveryMonth] issue - start iCounter at zero; insert
iCounter into the record; and increment it by Me.CheckEvery in the loop
(replace these lines in your code):

iCounter = 0

Do Until dtDue > Me.EndDate

DoCmd.SetWarnings (False)

DoCmd.RunSQL "INSERT Into tblDate ([SampleID],[EveryMonth],[EveryMonthDate])
Values (" & _
Me.SampleID & ", " & (iCounter) & ", #" & dtDue & "#)"
dtDue = DateAdd("m", Me.CheckEvery, dtDue) 'increment the due date
iCounter = iCounter + Me.CheckEvery 'increment iCounter

As for the date format - is it only this table that is formatted as
dd-mm-yyyy? Or is it all date fields? If it is only this table, then check
the format of that field in the table. You may be right in that it is based
on the StartDate and EndDate controls. Are these controls dates or text? If
they are text, switch them to date.

Let us know!
I just realized that if I have these values:
[quoted text clipped - 20 lines]
format….PLEASE HELP. You can see the cmdInsertDate button code in the
previous post.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top