Solved How to update default value

[Falcon88]

Hi to all

I have a form to insert some data to orders table , i use default value for OrderDate field , another table tblDefaultDate designed special to store one record only contains a field of date datatype to use it as default value for orders orderdate default value .
but when i change the value of DfltDate on tblDefaultDate while OrdersFrm ( that is based on Orders table) is opened , then when go to new record the default date do not takes the new value.

Q : how to update the default value for a control on the form based on another field on another table ?

 

[June7]

You don't want to just use the current date as default?

Exactly how do you pull the date from table into DefaultValue property?

Refresh the form before moving to new record. Unfortunately, Refresh and Requery put focus on first record.

So alternative is VBA to set value of OrderDate field.

 

[Pat Hartman]

Please post the procedure you are using to change the default for the date control. We can't debug code we can't even see.

 

[LarryE]

Hi to all

I have a form to insert some data to orders table , i use default value for OrderDate field , another table tblDefaultDate designed special to store one record only contains a field of date datatype to use it as default value for orders orderdate default value .
but when i change the value of DfltDate on tblDefaultDate while OrdersFrm ( that is based on Orders table) is opened , then when go to new record the default date do not takes the new value.

Q : how to update the default value for a control on the form based on another field on another table ?

1. Create a query called DefaultDate using the tblDefaultDate table and set it to Unique Values = Yes
2. Open the form containing the OrderDate field you want to set the default value for
3. In the Default Value property type:

=DLookup("[OrderDate]","DefaultDate")

That will look up the value and set it to OrderDate for all new records.

 

[Falcon88]

Please post the procedure you are using to change the default for the date control. We can't debug code we can't even see.

On the default value property of OrderDate :
Dlookup([DfltD];"tblDefaultDate")

 

[Pat Hartman]

This won't change with each new record. Is that your problem? To make the date change for each record, you would need to use the form's Current event. To make it change ONCE when the form is opened, I would use the Form's Load event.

 

[MajP]

I would use in the on current event
Me.ControlName.DefaultValue = "=DLookup('DfltD', 'tblDefaultDate')"
Or
Me.controlName.DefaultValue = "#" & DLookup("DfltD", "tblDefaultDate") & "#"

I would have thought the default value is reevaluated on the new record and your solution would have worked. But I tested it and the way you are @LarryE proposed does not reevaluate the default value on each record.

 

[strive4peace]

@Falcon88

perhaps instead of setting the DefaultValue all the time when you only need it if you create a new record, maybe fill the value on the Form_BeforeInsert event? That happens as the user begins to make a new record