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
  #11  
Old October 24th, 2006, 06:35 PM posted to microsoft.public.access.forms
sandrao
external usenet poster
 
Posts: 46
Default date field

O.K. Last time...
the Name of my table is "MainSchedule" the name in the main table for the
date is simply "Date" the form in use is called "MainScheduleFrm" that is
connected to a query called "MainSchedule Query" the date field is always the
same name.

Thanks Again
"Klatuu" wrote:

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.

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

Using Date as a field name, a control name, or any other name in Access is a
bad idea. It is a reserved word. No reserved words should be used as names.
You can usually get around it by putting it in brackets, but that is just a
band-aid. I suggest you scout MSDN for some Access or VBA naming convention
information.
I have modified that is use your name.
Private Sub Form_AfterInsert()
Dim rst As Recordset

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


"sandrao" wrote:

O.K. Last time...
the Name of my table is "MainSchedule" the name in the main table for the
date is simply "Date" the form in use is called "MainScheduleFrm" that is
connected to a query called "MainSchedule Query" the date field is always the
same name.

Thanks Again
"Klatuu" wrote:

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.

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

Error message reads after highlighting 'strKey= 'Compile Error Can't find
project or library' then the date disappears fron the field.
sorry it muse be me

"Klatuu" wrote:

Using Date as a field name, a control name, or any other name in Access is a
bad idea. It is a reserved word. No reserved words should be used as names.
You can usually get around it by putting it in brackets, but that is just a
band-aid. I suggest you scout MSDN for some Access or VBA naming convention
information.
I have modified that is use your name.
Private Sub Form_AfterInsert()
Dim rst As Recordset

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


"sandrao" wrote:

O.K. Last time...
the Name of my table is "MainSchedule" the name in the main table for the
date is simply "Date" the form in use is called "MainScheduleFrm" that is
connected to a query called "MainSchedule Query" the date field is always the
same name.

Thanks Again
"Klatuu" wrote:

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.

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

I just realized I wrote this incorrectly.
rst.FindFirst "[Date] = '" & Me.[Date] & "'"
It should be
rst.FindFirst "[Date] = #" & Me.[Date] & "#"

If that doesn't fix it, the error you are getting indicates a missing or
broken reference.

"sandrao" wrote:

Error message reads after highlighting 'strKey= 'Compile Error Can't find
project or library' then the date disappears fron the field.
sorry it muse be me

"Klatuu" wrote:

Using Date as a field name, a control name, or any other name in Access is a
bad idea. It is a reserved word. No reserved words should be used as names.
You can usually get around it by putting it in brackets, but that is just a
band-aid. I suggest you scout MSDN for some Access or VBA naming convention
information.
I have modified that is use your name.
Private Sub Form_AfterInsert()
Dim rst As Recordset

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


"sandrao" wrote:

O.K. Last time...
the Name of my table is "MainSchedule" the name in the main table for the
date is simply "Date" the form in use is called "MainScheduleFrm" that is
connected to a query called "MainSchedule Query" the date field is always the
same name.

Thanks Again
"Klatuu" wrote:

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.

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

It seems you have a reference problem. This is not you, it could be a VBA
problem.
Open your VBA editor. Click on Tools, References. Look for any references
that say Missing. If none say missing, then there is one that needs to be
selected, but is not. Post back with what you have checked.

"sandrao" wrote:

Sorry I guess I am a lost cause I still get the error message No need to reply
Thank for all your help and time spent
sandrao
"Klatuu" wrote:

I just realized I wrote this incorrectly.
rst.FindFirst "[Date] = '" & Me.[Date] & "'"
It should be
rst.FindFirst "[Date] = #" & Me.[Date] & "#"

If that doesn't fix it, the error you are getting indicates a missing or
broken reference.

"sandrao" wrote:

Error message reads after highlighting 'strKey= 'Compile Error Can't find
project or library' then the date disappears fron the field.
sorry it muse be me

"Klatuu" wrote:

Using Date as a field name, a control name, or any other name in Access is a
bad idea. It is a reserved word. No reserved words should be used as names.
You can usually get around it by putting it in brackets, but that is just a
band-aid. I suggest you scout MSDN for some Access or VBA naming convention
information.
I have modified that is use your name.
Private Sub Form_AfterInsert()
Dim rst As Recordset

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


"sandrao" wrote:

O.K. Last time...
the Name of my table is "MainSchedule" the name in the main table for the
date is simply "Date" the form in use is called "MainScheduleFrm" that is
connected to a query called "MainSchedule Query" the date field is always the
same name.

Thanks Again
"Klatuu" wrote:

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.

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

Sorry I guess I am a lost cause I still get the error message No need to reply
Thank for all your help and time spent
sandrao
"Klatuu" wrote:

I just realized I wrote this incorrectly.
rst.FindFirst "[Date] = '" & Me.[Date] & "'"
It should be
rst.FindFirst "[Date] = #" & Me.[Date] & "#"

If that doesn't fix it, the error you are getting indicates a missing or
broken reference.

"sandrao" wrote:

Error message reads after highlighting 'strKey= 'Compile Error Can't find
project or library' then the date disappears fron the field.
sorry it muse be me

"Klatuu" wrote:

Using Date as a field name, a control name, or any other name in Access is a
bad idea. It is a reserved word. No reserved words should be used as names.
You can usually get around it by putting it in brackets, but that is just a
band-aid. I suggest you scout MSDN for some Access or VBA naming convention
information.
I have modified that is use your name.
Private Sub Form_AfterInsert()
Dim rst As Recordset

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


"sandrao" wrote:

O.K. Last time...
the Name of my table is "MainSchedule" the name in the main table for the
date is simply "Date" the form in use is called "MainScheduleFrm" that is
connected to a query called "MainSchedule Query" the date field is always the
same name.

Thanks Again
"Klatuu" wrote:

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.

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

Dim rst As Recordset

strKey = Me.txtScheduleDate
Me.Requery
Set rst = Me.RecordsetClone
rst.FindFirst "[txtScheduleDate] = #" & Me.[txtScheduleDate] & "#"
Me.Bookmark = rst.Bookmark
Set rst = Nothing

I changed my Date fields name in the table to the above the date seems to be
going into the table but I still get the error related to 'strKey = ' thought
you would like to know I think we are on the right tract

Thought you would like to know

"Klatuu" wrote:

I just realized I wrote this incorrectly.
rst.FindFirst "[Date] = '" & Me.[Date] & "'"
It should be
rst.FindFirst "[Date] = #" & Me.[Date] & "#"

If that doesn't fix it, the error you are getting indicates a missing or
broken reference.

"sandrao" wrote:

Error message reads after highlighting 'strKey= 'Compile Error Can't find
project or library' then the date disappears fron the field.
sorry it muse be me

"Klatuu" wrote:

Using Date as a field name, a control name, or any other name in Access is a
bad idea. It is a reserved word. No reserved words should be used as names.
You can usually get around it by putting it in brackets, but that is just a
band-aid. I suggest you scout MSDN for some Access or VBA naming convention
information.
I have modified that is use your name.
Private Sub Form_AfterInsert()
Dim rst As Recordset

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


"sandrao" wrote:

O.K. Last time...
the Name of my table is "MainSchedule" the name in the main table for the
date is simply "Date" the form in use is called "MainScheduleFrm" that is
connected to a query called "MainSchedule Query" the date field is always the
same name.

Thanks Again
"Klatuu" wrote:

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.

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

I just noticed there is an error in the code. It is my fault, I wrote it
wrong. The way it is, it will not go to be current record, but back to the
first record. Here is the correction:

Dim rst As Recordset
Dim strKey As String

strKey = Me.txtScheduleDate
Me.Requery
Set rst = Me.RecordsetClone
rst.FindFirst "[txtScheduleDate] = #" & strKey & "#"
Me.Bookmark = rst.Bookmark
Set rst = Nothing

Notice I added an Dim statement for strKey. If it doesn't work as a string,
change it to

Dim strKey as Date

Not a Lost cause at all, we are almost there!

"sandrao" wrote:

Dim rst As Recordset

strKey = Me.txtScheduleDate
Me.Requery
Set rst = Me.RecordsetClone
rst.FindFirst "[txtScheduleDate] = #" & Me.[txtScheduleDate] & "#"
Me.Bookmark = rst.Bookmark
Set rst = Nothing

I changed my Date fields name in the table to the above the date seems to be
going into the table but I still get the error related to 'strKey = ' thought
you would like to know I think we are on the right tract

Thought you would like to know

"Klatuu" wrote:

I just realized I wrote this incorrectly.
rst.FindFirst "[Date] = '" & Me.[Date] & "'"
It should be
rst.FindFirst "[Date] = #" & Me.[Date] & "#"

If that doesn't fix it, the error you are getting indicates a missing or
broken reference.

"sandrao" wrote:

Error message reads after highlighting 'strKey= 'Compile Error Can't find
project or library' then the date disappears fron the field.
sorry it muse be me

"Klatuu" wrote:

Using Date as a field name, a control name, or any other name in Access is a
bad idea. It is a reserved word. No reserved words should be used as names.
You can usually get around it by putting it in brackets, but that is just a
band-aid. I suggest you scout MSDN for some Access or VBA naming convention
information.
I have modified that is use your name.
Private Sub Form_AfterInsert()
Dim rst As Recordset

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


"sandrao" wrote:

O.K. Last time...
the Name of my table is "MainSchedule" the name in the main table for the
date is simply "Date" the form in use is called "MainScheduleFrm" that is
connected to a query called "MainSchedule Query" the date field is always the
same name.

Thanks Again
"Klatuu" wrote:

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.

  #19  
Old October 25th, 2006, 07:41 PM posted to microsoft.public.access.forms
sandrao
external usenet poster
 
Posts: 46
Default date field

Final note.

I decided to ignore the recordset seeing that it caused me to many problems
instead I entered this simple code:
Private Sub Form_AfterInsert()
Me.txtScheduleDate.Requery
End Sub
I ran the code severals times and the table is being updated

thought you would like to know

Thanks again for all your help The information you gave me for the default
in the date field works like a charm

sandrao

"sandrao" wrote:

Sorry I guess I am a lost cause I still get the error message No need to reply
Thank for all your help and time spent
sandrao
"Klatuu" wrote:

I just realized I wrote this incorrectly.
rst.FindFirst "[Date] = '" & Me.[Date] & "'"
It should be
rst.FindFirst "[Date] = #" & Me.[Date] & "#"

If that doesn't fix it, the error you are getting indicates a missing or
broken reference.

"sandrao" wrote:

Error message reads after highlighting 'strKey= 'Compile Error Can't find
project or library' then the date disappears fron the field.
sorry it muse be me

"Klatuu" wrote:

Using Date as a field name, a control name, or any other name in Access is a
bad idea. It is a reserved word. No reserved words should be used as names.
You can usually get around it by putting it in brackets, but that is just a
band-aid. I suggest you scout MSDN for some Access or VBA naming convention
information.
I have modified that is use your name.
Private Sub Form_AfterInsert()
Dim rst As Recordset

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


"sandrao" wrote:

O.K. Last time...
the Name of my table is "MainSchedule" the name in the main table for the
date is simply "Date" the form in use is called "MainScheduleFrm" that is
connected to a query called "MainSchedule Query" the date field is always the
same name.

Thanks Again
"Klatuu" wrote:

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 12:02 PM.


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