A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

date field



 
 
Thread Tools Display Modes
  #1  
Old October 23rd, 2006, 04:30 PM posted to microsoft.public.access.forms
sandrao
external usenet poster
 
Posts: 46
Default date field

How can I have adate field in a form automatically filled with dates. I have
a scheduling program that schedules events on a weekly basis. The schedule
starts on the first day of the week (vbSunday). I would like to have the date
appear each time a new record is started. The first week would be 15-Oct-06
then when those events are filled in the new record would already have the
date 22-Oct-06. These dates would continue on appearing each time a new
record was started.
  #2  
Old October 23rd, 2006, 04:57 PM posted to microsoft.public.access.forms
kingston via AccessMonster.com
external usenet poster
 
Posts: 620
Default date field

You can set the default value of the field (table) or the control (form) to
be Date().

sandrao wrote:
How can I have adate field in a form automatically filled with dates. I have
a scheduling program that schedules events on a weekly basis. The schedule
starts on the first day of the week (vbSunday). I would like to have the date
appear each time a new record is started. The first week would be 15-Oct-06
then when those events are filled in the new record would already have the
date 22-Oct-06. These dates would continue on appearing each time a new
record was started.


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

  #3  
Old October 23rd, 2006, 06:21 PM posted to microsoft.public.access.forms
Klatuu
external usenet poster
 
Posts: 7,074
Default date field

If what you are saying is you want each new record's date to be one week
after the previous records date, you can use the Default Value property of
the date field control. Since I don't know the names of your objects, the
following example uses made up names. Change them to use your names:

=DateAdd("ww", 1, DMax("[ScheduleDate]", "SchedulteTable"))

"sandrao" wrote:

How can I have adate field in a form automatically filled with dates. I have
a scheduling program that schedules events on a weekly basis. The schedule
starts on the first day of the week (vbSunday). I would like to have the date
appear each time a new record is started. The first week would be 15-Oct-06
then when those events are filled in the new record would already have the
date 22-Oct-06. These dates would continue on appearing each time a new
record was started.

  #4  
Old October 23rd, 2006, 08:01 PM posted to microsoft.public.access.forms
sandrao
external usenet poster
 
Posts: 46
Default date field

I tried the suggested example and I got an #Error message.

You understood my question. Yes each new record should appear one week after
the previous entry. Tlhe date field name is txDate and the form is
MainScheduleFrm.

I am not sure what I did wrong but the suggestion did not work. Do you have
any other advise


Thanks
"Klatuu" wrote:

If what you are saying is you want each new record's date to be one week
after the previous records date, you can use the Default Value property of
the date field control. Since I don't know the names of your objects, the
following example uses made up names. Change them to use your names:

=DateAdd("ww", 1, DMax("[ScheduleDate]", "SchedulteTable"))

"sandrao" wrote:

How can I have adate field in a form automatically filled with dates. I have
a scheduling program that schedules events on a weekly basis. The schedule
starts on the first day of the week (vbSunday). I would like to have the date
appear each time a new record is started. The first week would be 15-Oct-06
then when those events are filled in the new record would already have the
date 22-Oct-06. These dates would continue on appearing each time a new
record was started.

  #5  
Old October 23rd, 2006, 08:03 PM posted to microsoft.public.access.forms
sandrao
external usenet poster
 
Posts: 46
Default date field

I know about using Date() but what I want is for the date to be 7 days after
the last entry in the date field.
Do you have any other suggestions

sandrao

"kingston via AccessMonster.com" wrote:

You can set the default value of the field (table) or the control (form) to
be Date().

sandrao wrote:
How can I have adate field in a form automatically filled with dates. I have
a scheduling program that schedules events on a weekly basis. The schedule
starts on the first day of the week (vbSunday). I would like to have the date
appear each time a new record is started. The first week would be 15-Oct-06
then when those events are filled in the new record would already have the
date 22-Oct-06. These dates would continue on appearing each time a new
record was started.


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


  #6  
Old October 23rd, 2006, 08:23 PM posted to microsoft.public.access.forms
Klatuu
external usenet poster
 
Posts: 7,074
Default date field

There must be a syntax error in what you coded. To break it down:

=DateAdd("ww", 1, DMax("[ScheduleDate]", "SchedulteTable"))

[ScheduleDate] should actually be the name of the date field in the table.
ScheduleTable should be the name of the table.
The DMax function will return the hightest date in the table.
The DateAdd function will add one week to that date.

You can test your syntax in the VBA immediate window by typing in the above
formula with the correct field and table name. Just replace the = with ? so
it will print in the window. Once you get a correct return, you have the
syntax correct and can paste it into the Default Value property of the date
field with the =

I did create a form and test it, so it does work.

"sandrao" wrote:

I tried the suggested example and I got an #Error message.

You understood my question. Yes each new record should appear one week after
the previous entry. Tlhe date field name is txDate and the form is
MainScheduleFrm.

I am not sure what I did wrong but the suggestion did not work. Do you have
any other advise


Thanks
"Klatuu" wrote:

If what you are saying is you want each new record's date to be one week
after the previous records date, you can use the Default Value property of
the date field control. Since I don't know the names of your objects, the
following example uses made up names. Change them to use your names:

=DateAdd("ww", 1, DMax("[ScheduleDate]", "SchedulteTable"))

"sandrao" wrote:

How can I have adate field in a form automatically filled with dates. I have
a scheduling program that schedules events on a weekly basis. The schedule
starts on the first day of the week (vbSunday). I would like to have the date
appear each time a new record is started. The first week would be 15-Oct-06
then when those events are filled in the new record would already have the
date 22-Oct-06. These dates would continue on appearing each time a new
record was started.

  #7  
Old October 24th, 2006, 12:43 AM posted to microsoft.public.access.forms
sandrao
external usenet poster
 
Posts: 46
Default date field

I tested out the code and it works In my case it put 17-dec-06 in the date
field, however when I went to and an a new record it did not add 7 to the
previous date which would have been 24-dec-06. What I need is at each new
record it will continue to add 7 the the previous or the last date entered in
the date field.

Is that Possible?

"Klatuu" wrote:

There must be a syntax error in what you coded. To break it down:

=DateAdd("ww", 1, DMax("[ScheduleDate]", "SchedulteTable"))

[ScheduleDate] should actually be the name of the date field in the table.
ScheduleTable should be the name of the table.
The DMax function will return the hightest date in the table.
The DateAdd function will add one week to that date.

You can test your syntax in the VBA immediate window by typing in the above
formula with the correct field and table name. Just replace the = with ? so
it will print in the window. Once you get a correct return, you have the
syntax correct and can paste it into the Default Value property of the date
field with the =

I did create a form and test it, so it does work.

"sandrao" wrote:

I tried the suggested example and I got an #Error message.

You understood my question. Yes each new record should appear one week after
the previous entry. Tlhe date field name is txDate and the form is
MainScheduleFrm.

I am not sure what I did wrong but the suggestion did not work. Do you have
any other advise


Thanks
"Klatuu" wrote:

If what you are saying is you want each new record's date to be one week
after the previous records date, you can use the Default Value property of
the date field control. Since I don't know the names of your objects, the
following example uses made up names. Change them to use your names:

=DateAdd("ww", 1, DMax("[ScheduleDate]", "SchedulteTable"))

"sandrao" wrote:

How can I have adate field in a form automatically filled with dates. I have
a scheduling program that schedules events on a weekly basis. The schedule
starts on the first day of the week (vbSunday). I would like to have the date
appear each time a new record is started. The first week would be 15-Oct-06
then when those events are filled in the new record would already have the
date 22-Oct-06. These dates would continue on appearing each time a new
record was started.

  #8  
Old October 24th, 2006, 02:39 PM posted to microsoft.public.access.forms
Klatuu
external usenet poster
 
Posts: 7,074
Default date field

Okay, I know what the problem is. I should know better.
What is happening is that when you enter a new record, it is in the form's
recordset, but is not yet in the table. So, here is what we need to do. Use
the form's After Insert event to requery the form which will immediately
update the table with the new record. We will also have to make sure we stay
on the current record, so here is an example of how to do this.

Private Sub Form_AfterInsert()
Dim rst As Recordset

strKey = Me.txtPrimaryKeyField
Me.Requery
Set rst = Me.RecordsetClone
rst.FindFirst "[KeyField] = '" & Me.txtKeyControl & "'"
Me.Bookmark = rst.Bookmark
Set rst = Nothing
End Sub


"sandrao" wrote:

I tested out the code and it works In my case it put 17-dec-06 in the date
field, however when I went to and an a new record it did not add 7 to the
previous date which would have been 24-dec-06. What I need is at each new
record it will continue to add 7 the the previous or the last date entered in
the date field.

Is that Possible?

"Klatuu" wrote:

There must be a syntax error in what you coded. To break it down:

=DateAdd("ww", 1, DMax("[ScheduleDate]", "SchedulteTable"))

[ScheduleDate] should actually be the name of the date field in the table.
ScheduleTable should be the name of the table.
The DMax function will return the hightest date in the table.
The DateAdd function will add one week to that date.

You can test your syntax in the VBA immediate window by typing in the above
formula with the correct field and table name. Just replace the = with ? so
it will print in the window. Once you get a correct return, you have the
syntax correct and can paste it into the Default Value property of the date
field with the =

I did create a form and test it, so it does work.

"sandrao" wrote:

I tried the suggested example and I got an #Error message.

You understood my question. Yes each new record should appear one week after
the previous entry. Tlhe date field name is txDate and the form is
MainScheduleFrm.

I am not sure what I did wrong but the suggestion did not work. Do you have
any other advise


Thanks
"Klatuu" wrote:

If what you are saying is you want each new record's date to be one week
after the previous records date, you can use the Default Value property of
the date field control. Since I don't know the names of your objects, the
following example uses made up names. Change them to use your names:

=DateAdd("ww", 1, DMax("[ScheduleDate]", "SchedulteTable"))

"sandrao" wrote:

How can I have adate field in a form automatically filled with dates. I have
a scheduling program that schedules events on a weekly basis. The schedule
starts on the first day of the week (vbSunday). I would like to have the date
appear each time a new record is started. The first week would be 15-Oct-06
then when those events are filled in the new record would already have the
date 22-Oct-06. These dates would continue on appearing each time a new
record was started.

  #9  
Old October 24th, 2006, 06:14 PM posted to microsoft.public.access.forms
sandrao
external usenet poster
 
Posts: 46
Default date field

You are probably going to tired of my questions

I did as you said but it still not working so, should some of the code be
altered to include names in my form. e.g. should the 'Me.txtPrimaryKeyField'
be the name of my date field or is there anything else that has to change co
comply with my form
I am just new at all this and really appreciate your help

Thanks


"Klatuu" wrote:

Okay, I know what the problem is. I should know better.
What is happening is that when you enter a new record, it is in the form's
recordset, but is not yet in the table. So, here is what we need to do. Use
the form's After Insert event to requery the form which will immediately
update the table with the new record. We will also have to make sure we stay
on the current record, so here is an example of how to do this.

Private Sub Form_AfterInsert()
Dim rst As Recordset

strKey = Me.txtPrimaryKeyField
Me.Requery
Set rst = Me.RecordsetClone
rst.FindFirst "[KeyField] = '" & Me.txtKeyControl & "'"
Me.Bookmark = rst.Bookmark
Set rst = Nothing
End Sub


"sandrao" wrote:

I tested out the code and it works In my case it put 17-dec-06 in the date
field, however when I went to and an a new record it did not add 7 to the
previous date which would have been 24-dec-06. What I need is at each new
record it will continue to add 7 the the previous or the last date entered in
the date field.

Is that Possible?

"Klatuu" wrote:

There must be a syntax error in what you coded. To break it down:

=DateAdd("ww", 1, DMax("[ScheduleDate]", "SchedulteTable"))

[ScheduleDate] should actually be the name of the date field in the table.
ScheduleTable should be the name of the table.
The DMax function will return the hightest date in the table.
The DateAdd function will add one week to that date.

You can test your syntax in the VBA immediate window by typing in the above
formula with the correct field and table name. Just replace the = with ? so
it will print in the window. Once you get a correct return, you have the
syntax correct and can paste it into the Default Value property of the date
field with the =

I did create a form and test it, so it does work.

"sandrao" wrote:

I tried the suggested example and I got an #Error message.

You understood my question. Yes each new record should appear one week after
the previous entry. Tlhe date field name is txDate and the form is
MainScheduleFrm.

I am not sure what I did wrong but the suggestion did not work. Do you have
any other advise


Thanks
"Klatuu" wrote:

If what you are saying is you want each new record's date to be one week
after the previous records date, you can use the Default Value property of
the date field control. Since I don't know the names of your objects, the
following example uses made up names. Change them to use your names:

=DateAdd("ww", 1, DMax("[ScheduleDate]", "SchedulteTable"))

"sandrao" wrote:

How can I have adate field in a form automatically filled with dates. I have
a scheduling program that schedules events on a weekly basis. The schedule
starts on the first day of the week (vbSunday). I would like to have the date
appear each time a new record is started. The first week would be 15-Oct-06
then when those events are filled in the new record would already have the
date 22-Oct-06. These dates would continue on appearing each time a new
record was started.

  #10  
Old October 24th, 2006, 06:19 PM posted to microsoft.public.access.forms
Klatuu
external usenet poster
 
Posts: 7,074
Default date field

Yes, all the names have to be names you use. Since I don't know what names
you use, I can only offer examples.

txtPrimaryKeyField should be the control on your form where you have the
primary key for the record bound.

PrimaryKeyField should be the name of the field in your table that is bound
to txtPrimaryKeyField on your form.

"sandrao" wrote:

You are probably going to tired of my questions

I did as you said but it still not working so, should some of the code be
altered to include names in my form. e.g. should the 'Me.txtPrimaryKeyField'
be the name of my date field or is there anything else that has to change co
comply with my form
I am just new at all this and really appreciate your help

Thanks


"Klatuu" wrote:

Okay, I know what the problem is. I should know better.
What is happening is that when you enter a new record, it is in the form's
recordset, but is not yet in the table. So, here is what we need to do. Use
the form's After Insert event to requery the form which will immediately
update the table with the new record. We will also have to make sure we stay
on the current record, so here is an example of how to do this.

Private Sub Form_AfterInsert()
Dim rst As Recordset

strKey = Me.txtPrimaryKeyField
Me.Requery
Set rst = Me.RecordsetClone
rst.FindFirst "[KeyField] = '" & Me.txtKeyControl & "'"
Me.Bookmark = rst.Bookmark
Set rst = Nothing
End Sub


"sandrao" wrote:

I tested out the code and it works In my case it put 17-dec-06 in the date
field, however when I went to and an a new record it did not add 7 to the
previous date which would have been 24-dec-06. What I need is at each new
record it will continue to add 7 the the previous or the last date entered in
the date field.

Is that Possible?

"Klatuu" wrote:

There must be a syntax error in what you coded. To break it down:

=DateAdd("ww", 1, DMax("[ScheduleDate]", "SchedulteTable"))

[ScheduleDate] should actually be the name of the date field in the table.
ScheduleTable should be the name of the table.
The DMax function will return the hightest date in the table.
The DateAdd function will add one week to that date.

You can test your syntax in the VBA immediate window by typing in the above
formula with the correct field and table name. Just replace the = with ? so
it will print in the window. Once you get a correct return, you have the
syntax correct and can paste it into the Default Value property of the date
field with the =

I did create a form and test it, so it does work.

"sandrao" wrote:

I tried the suggested example and I got an #Error message.

You understood my question. Yes each new record should appear one week after
the previous entry. Tlhe date field name is txDate and the form is
MainScheduleFrm.

I am not sure what I did wrong but the suggestion did not work. Do you have
any other advise


Thanks
"Klatuu" wrote:

If what you are saying is you want each new record's date to be one week
after the previous records date, you can use the Default Value property of
the date field control. Since I don't know the names of your objects, the
following example uses made up names. Change them to use your names:

=DateAdd("ww", 1, DMax("[ScheduleDate]", "SchedulteTable"))

"sandrao" wrote:

How can I have adate field in a form automatically filled with dates. I have
a scheduling program that schedules events on a weekly basis. The schedule
starts on the first day of the week (vbSunday). I would like to have the date
appear each time a new record is started. The first week would be 15-Oct-06
then when those events are filled in the new record would already have the
date 22-Oct-06. These dates would continue on appearing each time a new
record was started.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:40 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.