Copy date from previous record, subform

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to copy two fields from the previous record as default values for
the new record. These are contained on a subform on my form. One field is a
date; one field is numeric.

I have tried 2 approaches -- one using DLookUp() as the default value
property. After consulting several books, I never got the terminology right.

My second approach works for the numeric field, but not the date field. In
the After Update property, I have this procedure:

Private Sub Date_AfterUpdate()
Forms![Data Entry Form]![Newspapers]![Date].DefaultValue = Forms![Data
Entry Form]![Newspapers]![Date]
End Sub

This procedure returns the correct date as shown on the date field, but it
is always entered as 30 Dec 1899 in the subform. Is it being treated as a
string?? How can I fix it?? I hope some angel can help, as I have spent 2
frustrating weeks on this. Thanks.
 
Default Value is string property, so we need to create a string and then
assign it to the property

Private Sub Date_AfterUpdate()
With Me.Date
If Not IsNull(.Value) then
.DefaultValue = """" & .Value & """"
End If
End With
End Sub

BTW, if you really do have a field called Date, consider renaming it. That
is a reserved word in VBA (for the system date).
 
Thank you! Thank you! Thank you! It works! I hope your halo isn't too heavy.

Carol

Allen Browne said:
Default Value is string property, so we need to create a string and then
assign it to the property

Private Sub Date_AfterUpdate()
With Me.Date
If Not IsNull(.Value) then
.DefaultValue = """" & .Value & """"
End If
End With
End Sub

BTW, if you really do have a field called Date, consider renaming it. That
is a reserved word in VBA (for the system date).

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Carol said:
I am trying to copy two fields from the previous record as default values
for
the new record. These are contained on a subform on my form. One field is
a
date; one field is numeric.

I have tried 2 approaches -- one using DLookUp() as the default value
property. After consulting several books, I never got the terminology
right.

My second approach works for the numeric field, but not the date field. In
the After Update property, I have this procedure:

Private Sub Date_AfterUpdate()
Forms![Data Entry Form]![Newspapers]![Date].DefaultValue = Forms![Data
Entry Form]![Newspapers]![Date]
End Sub

This procedure returns the correct date as shown on the date field, but it
is always entered as 30 Dec 1899 in the subform. Is it being treated as a
string?? How can I fix it?? I hope some angel can help, as I have spent 2
frustrating weeks on this. Thanks.
Click to expand...
Click to expand...
 
I am trying to copy two fields from the previous record as default values for
the new record. These are contained on a subform on my form. One field is a
date; one field is numeric.

I have tried 2 approaches -- one using DLookUp() as the default value
property. After consulting several books, I never got the terminology right.

My second approach works for the numeric field, but not the date field. In
the After Update property, I have this procedure:

Private Sub Date_AfterUpdate()
Forms![Data Entry Form]![Newspapers]![Date].DefaultValue = Forms![Data
Entry Form]![Newspapers]![Date]
End Sub

This procedure returns the correct date as shown on the date field, but it
is always entered as 30 Dec 1899 in the subform. Is it being treated as a
string?? How can I fix it?? I hope some angel can help, as I have spent 2
frustrating weeks on this. Thanks.
Click to expand...

I'd guess it's interpreting the 9/27/2004 as an arithmetic expression,
evaluating to 1.66333998669e-4, which when expressed as a date/time is
equivalent to #12/30/1899 00:00:14#.

The DefaultValue property of a textbox needs to be a *text string*,
delimited with quotemarks. Try

Me.[Date].DefaultValue = Chr(34) & Me.[Date] & Chr(34)

Me means "the current form" so you don't need to use the full
reference.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Thanks! Now I understand. I never would have figured this out in a million
years.

Carol

John Vinson said:
I am trying to copy two fields from the previous record as default values for
the new record. These are contained on a subform on my form. One field is a
date; one field is numeric.

I have tried 2 approaches -- one using DLookUp() as the default value
property. After consulting several books, I never got the terminology right.

My second approach works for the numeric field, but not the date field. In
the After Update property, I have this procedure:

Private Sub Date_AfterUpdate()
Forms![Data Entry Form]![Newspapers]![Date].DefaultValue = Forms![Data
Entry Form]![Newspapers]![Date]
End Sub

This procedure returns the correct date as shown on the date field, but it
is always entered as 30 Dec 1899 in the subform. Is it being treated as a
string?? How can I fix it?? I hope some angel can help, as I have spent 2
frustrating weeks on this. Thanks.
Click to expand...

I'd guess it's interpreting the 9/27/2004 as an arithmetic expression,
evaluating to 1.66333998669e-4, which when expressed as a date/time is
equivalent to #12/30/1899 00:00:14#.

The DefaultValue property of a textbox needs to be a *text string*,
delimited with quotemarks. Try

Me.[Date].DefaultValue = Chr(34) & Me.[Date] & Chr(34)

Me means "the current form" so you don't need to use the full
reference.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
 
Back
Top