Default property for a control bounded to a Date/Time field (1 Viewer)

Mihail

Registered User.
Local time
Today, 13:27
Joined
Jan 22, 2011
Messages
2,373
Sorry if this will be a duplicate thread. Yesterday I started a thread with exact the same title but, somehow, it disappear from the forum :confused:

So:
Access 2007

In a table I have 2 fields:
D1 , Date/Time , DefaultValue: Date()
D2 , Date/Time , DefaultValue: Date()+1

In a form (bounded to this table) I have, of course, 2 controls bounded to this fields:
txtD1 and txtD2

If the user change the value in txtD1 (using the Date picker) I like to change the DefaultValue for txtD1 to the new date and the DefaultValue for txtD2 to the new date + 1 day.
Something like this:
Code:
Private Sub txtD1_AfterUpdate()
  Me.txtD1.DefaultValue = Me.txtD1
  Me.txtD2.DefaultValue = Me.txtD1 + 1 day
End Sub
I tried whatever crossed my stupid head... with the same "result" : #Name?

My Regional settings:
Short date format: dd.MM.yyyy
Date separator: Dot
Simple: 05.12.2013

Can you help me ?
Thank you !
 

namliam

The Mailman - AWF VIP
Local time
Today, 12:27
Joined
Aug 11, 2003
Messages
11,696
Me.txtD2.DefaultValue = Me.txtD1 + 1 day
should be
Me.txtD2.DefaultValue = Me.txtD1 + 1

however, this looks like a calculated field and calculated fields should not be stored
 

Mihail

Registered User.
Local time
Today, 13:27
Joined
Jan 22, 2011
Messages
2,373
@namliam
Hi and thank you for the answer.
I know your skills, so I'm very sure that you don't read very carefully my post

My problem is NOT how to calculate that date.
My problem is that that damned control (text box) DO NOT ACCEPT a DefaultValue.

And NO. It isn't a calculate data. It is a help for user but he (the user) is allowed to change the Date.

Can you take, please, a closer look ? This burned me :banghead:

Thank you !
 

namliam

The Mailman - AWF VIP
Local time
Today, 12:27
Joined
Aug 11, 2003
Messages
11,696
Me.txtD2.DefaultValue = Me.txtD1 + 1 day
should be
Me.txtD2.DefaultValue = Me.txtD1 + 1

Hmz, I think I thought I made a suggestion on how to fix it as well?
 

namliam

The Mailman - AWF VIP
Local time
Today, 12:27
Joined
Aug 11, 2003
Messages
11,696
Odd, testing the assigning of date+1 to a defaultvalue seems to cause "some sort of problem" in that the value filled isnt proper-like....

Assigning Now() + 1, causes the #Name error

How about just putting the default value of Date() + 1 into the control hard coded?
Or simply doing:
Me.txtD2 = Date + 1
 

Mihail

Registered User.
Local time
Today, 13:27
Joined
Jan 22, 2011
Messages
2,373
No. I tried even this:

Dim dt As Date
dt = CDate(Me.txtD1)
Debug.Print dt ->>>> OK. The expected result

Me.txtD1.DefaultValue = dt ->>>>> Failed
Me.txtD1.DefaultValue = "#" & dt & "#" ->>>>> Failed
Me.txtD1.DefaultValue = "05.12.2013" ->>>> Failed
Me.txtD1.DefaultValue = "#05.12.2013#" ->>>>> Failed
 

Mihail

Registered User.
Local time
Today, 13:27
Joined
Jan 22, 2011
Messages
2,373
How about just putting the default value of Date() + 1 into the control hard coded?

I do this at table level and works OK but...

Each day will be more than 100 records that will have the same date (not necessary "today"). So, if the user will change the date in txtD1 I like to keep this date for the rest of records: I'll set this date as default and the Tab Stop to false for that control.

More, if the user change this date (in txtD1) I like to do a calculation:
If txtD1 + 1 day is an working day then... OK => the Default for txtD2 will be txtD1 + 1 if not... first working day.
But, if the user also will change the date in txtD2 I like to keep this date as default for txtD2.

Can you understand my English ? Hope so.
 

namliam

The Mailman - AWF VIP
Local time
Today, 12:27
Joined
Aug 11, 2003
Messages
11,696
I think ( some*frigging*how ) the date of 12/6/2013 is getting turned into a number as when I assign this it is getting turned into a time only thing which means a decimal number which might be the 12/6/2013 getting re-calculated....

I tried doing
Me.Field2.DefaultValue = CDbl(#12/31/2013#)

That seemed to work
 

namliam

The Mailman - AWF VIP
Local time
Today, 12:27
Joined
Aug 11, 2003
Messages
11,696
You should be able to subst the date for any date variable? The key part is sending the date as a double to the defaultvalue instead of "as a date"
 

namliam

The Mailman - AWF VIP
Local time
Today, 12:27
Joined
Aug 11, 2003
Messages
11,696
From MSDN
To assign the defaultvalue "Cash"
Forms!frmInvoice!PaymentMethod.DefaultValue = """Cash"""

So perhaps (stupid as it sounds) you need to do something like:
Forms!frmInvoice!PaymentMethod.DefaultValue = "#SomeDate#"
or
Forms!frmInvoice!PaymentMethod.DefaultValue = ##SomeDate##
 

CazB

Registered User.
Local time
Today, 11:27
Joined
Jul 17, 2013
Messages
309
My approach would be:

put an unbound control on your form, which again defaults to today's date but into which the user can put the date they want...my theory being that if the control is unbound then it should just remember whatever the last value was that was used, when you move to a new record?
Then copy that into your bound field behind the scenes, whenever a new record is added...
 

Mihail

Registered User.
Local time
Today, 13:27
Joined
Jan 22, 2011
Messages
2,373
Thank you !!!
Finally it work:

Code:
'In the form module
Private Sub DataComanda_AfterUpdate()
    Me.DataComanda.DefaultValue = EnglishDate2(Me.DataComanda)
End Sub

'and in a regular module:

Public Function EnglishDate2(UzualData) As Double
Dim dt As Date
    dt = Month(UzualData) & "/" & Day(UzualData) & "/" & Year(UzualData)
    EnglishDate2 = CDbl(dt)
End Function

Never mind to convert to double.
 

Mihail

Registered User.
Local time
Today, 13:27
Joined
Jan 22, 2011
Messages
2,373
@namliam and CazB
I write my last post while you post here so, is now when I see.
As I said, the thread is SOLVED but thank you for information that you added.

@CazB
I think that your suggestion is not doable with a Continuous Form.
Even for a Single Form involve more other code in order to check if "under" the mask is not an older record with an older date and needed to update the mask... and, if I think more (hard work for me :) ) I can find more other reasons to not like the idea.

Anyway, be sure that I appreciate that you try to help me.

Thank you (both of you) !!!!!!!
 

CazB

Registered User.
Local time
Today, 11:27
Joined
Jul 17, 2013
Messages
309
Ahh, ok, didn't realise it was a continuous form. Glad you got it working, anyway :)
 

missinglinq

AWF VIP
Local time
Today, 07:27
Joined
Jun 20, 2003
Messages
6,423
...If the user change the value in txtD1 (using the Date picker) I like to change the DefaultValue for txtD1 to the new date and the DefaultValue for txtD2 to the new date + 1 day...
I know that this has been marked as 'solved,' but the problem fascinated me, so I noodled with it and this works for the initial question above:

Code:
Private Sub txtD1_AfterUpdate()
  Me.txtD1.DefaultValue = """" & Me.txtD1 & """"
  Me.txtD2.DefaultValue = """" & DateAdd("d", 1, Me.txtD1) & """"
End Sub

...But, if the user also will change the date in txtD2 I like to keep this date as default for txtD2...
So this would simply be

Code:
Private Sub txtD2_AfterUpdate()
  Me.txtD2.DefaultValue = """" & Me.txtD2 & """"
End Sub

Basically, you cannot assign a Default Value using syntax like this

Code:
Me.ControlName.DefaultValue = Me.ControlName
It has to be like this

Code:
Me.ControlName.DefaultValue = [COLOR="Red"]"""" &[/COLOR] Me.ControlName [COLOR="Red"]& """"[/COLOR]

And this works regardless of the Datatype of the associatedField.

The only thing I did not test was how the syntax

Code:
Me.txtD2.DefaultValue = """" & DateAdd("d", 1, Me.txtD1) & """"

performs when using non-US Format Dates; this can sometimes be problematic.

Linq ;0)>
 

Mihail

Registered User.
Local time
Today, 13:27
Joined
Jan 22, 2011
Messages
2,373
@missinglinq
Thank you for this. Good to know.

No problem with "SOLVED" mark.
Any new solution is welcome.

Never mind that a "simple" task like this can cause so much troubles for me :)
 

missinglinq

AWF VIP
Local time
Today, 07:27
Joined
Jun 20, 2003
Messages
6,423
It's always 'simple' after you've done a few times!:D

Linq ;0)>
 

Users who are viewing this thread

Top Bottom