Access Expressions

[Justforloving]

I have written:

=[Date of Entry] + 3

These are date fields in Forms ControlSource. I have also used this format in several of my fields again the FORMS ControlSource. My problem: These values are NOT written to the TABLE. In addition to Forms I have several queries that require the valueof these fields. How can i get these expression into my table. I am new to Access and VBA is foreign language to me (smile). But I am learning anyway.

 

[Lightning]

If you have set the Control's RecordSource Property to these expressions, then the Control is longer BOUND to the Field in the Table, and the values displayed on the form will not be written to the table when the record is saved.

Try setting the Default Value property to the expressions, and ensure that the Recordsource property is Bound to the correct field in the table

HTH
Lightning

 

[sweetp]

Dear Lightning,

I am also having the same problem as JustforLoving. I tried your suggestion, but my expression:

=DateAdd("y",+[Days_Due_To_Expire],[Date])

works in ControlSource, but not when I moved it to DefaultValue. If I change [Days_Due_To_Expire] to a number (I just type a number in its place), it works. But why can't I reference 2 controls????

Thanks for any help!

 

[Lightning]

Any Default values defined are placed in the field when a new record is created, BEFORE any other data is entered by the users. So your [Days_Due_To_Expire] field would at that stage have a NULL value, thereby giving the whole expression a value of Null. When you simply use a number value instead of the field name, your expression returns a non-Null value because it has values to work with.

The solution for your problem is to put the expression into code that runs when the [Days_Due_To_expire] field is updated.

Private Sub Days_Due_To_Expire_AfterUpdate()
    Me.YourFieldToUpdate = DateAdd("y", [Days_Due_To_Expire], [Date])
End Sub

By the way, it is not good design practice to give fields the same name as Access keywords, such as your [Date] field. This can cause problems at runtime. I suggest you re-name the field to something else, such as [EntryDate] or [CurrentDate] or any other meaningful in terms of your application. Search the On-line Help file for Reserved Words for a list of words that should not be used as field names.

HTH
Lightning

 

[sweetp]

Thanks for the reminder on the Date name. I knew that. Don't know what I was thinking. Anyway, I've changed it to EntryDate.

But when I substituted YourFieldToUpdate with my field name which is ExpirationDate, it gave me an error "Compile Error - Method or Data Not Found". So what am I doing wrong? the debugger highlights the .ExpirationDate = part. Below is what my statement:

Private Sub Days_Due_To_Expire_AfterUpdate()
Me.ExpirationDate = DateAdd("y", [Days_Due_to_Expire], [EntryDate])
End Sub

I really appreciate your help!!!

 

[Lightning]

I set up a test table and form using your field names, cut and pasted your code into it, and it worked perfectly.

Basic question: Did you change the [Date] field name to [EntryDate] everywhere? ie Table's field name, the Form control's Name and Controlsource?

HTH
Lightning

 

[willir]

Hmmm

Long shot...

=[Date of Entry] + 3
...
=DateAdd("y",+[Days_Due_To_Expire],[Date])

I have to wonder... Are these calcuated, unbound fields on a form???

If so, this would explain why your values are not being stored at the table level.

If so, do you have a specific field within your table to capture this data?

If so, then you need to set your control source for the data to the name of the field, and these use Lightning's code to set the calcuated value to the field variable.

SweetP As indicated by Lightning, this appears to be donw correctly in your example. The question here, which may different than JustForLoving, is - does the update event occur for the Days_Due_To_Expire ? It almost seems like you have set the calcuation to kick-in after changing the value.

If I am right, try entering a number in the Days_Due_to_Expire field, and then see if it changes after leaving the field.

You can buy me a (cyber) coffee if I am right. ;-)

Richard

 

[sweetp]

Thanks Lightning,

I redid the form from scratch (ExpirationDate wasn't a member) and now it works like a charm! I really appreciate your help.

And thanks to willir for your input, too.

sweetp