Forms not updating tables

[xlr8sion]

Using this string in forms control source "=DateSerial(year([StartDate]), month([StartDate])+6, day([StartDate]))". Works correctly in the the form, but does not update the fields in the table. New to Access -- any help would be appreciated. Thanks in advance.

[MMcCarthy]

Using this string in forms control source "=DateSerial(year([StartDate]), month([StartDate])+6, day([StartDate]))". Works correctly in the the form, but does not update the fields in the table. New to Access -- any help would be appreciated. Thanks in advance.

By putting that in a textboxes control source you are not storing it anywhere. The control source should be set to the field. Put the calculation in the default value and it will set the field to the correct value when the form opens.

[xlr8sion]

Thanks for the quick reply - I will try that this morning and let you know how it turns out.

[xlr8sion]

Mac, Tried entering the string into the default value box -- still no change/update to the table fields. I must have a bracket upside down or something. I'll keep working with it. Think of any other suggestions please let me know.

[MMcCarthy]

Mac, Tried entering the string into the default value box -- still no change/update to the table fields. I must have a bracket upside down or something. I'll keep working with it. Think of any other suggestions please let me know.

Did you also set the control source to the appropriate field?

This default value will only trigger when you open the form to the appropriate record.

[xlr8sion]

The control source is set to the default field where I'm adding the string to the default value. Should it be set to another field? Thanks for your patience.

[MMcCarthy]

The control source is set to the default field where I'm adding the string to the default value. Should it be set to another field? Thanks for your patience.

I don't really understand what you're saying.

Set the Control Source to the field in the table or query that you want to store the value in and set the default value to the previously mentioned statement. These are two separate properties.

[xlr8sion]

Mac, I created a form using the info listed below from a Microsoft site. I changed the control source value for "futuredate" to futuredate. Also added the DatesSerial string to the default value as you prescribe. I can't figure out what I'm doing wrong. Again thanks for your help.

(Microsoft Site) ---- In Access, you can calculate a future or past date from a starting date by using an expression. For example, you can enter a date in a control on a form and calculate a date that is six months in the future from the original date. This article demonstrates this technique by using the DateSerial function to calculate the future date in the controls on a form. The calculated date can then be used in other calculations.

To calculate a future date by using the DateSerial function

In the Database window, click Forms under Objects, and then click New.
In the New Form dialog box, click Design View, and then click OK.
Using the Text Box tool in the toolbox, add two unbound text boxes to your form.
Set the Name property for one text box to StartDate and the other to FutureDate.
Set the Format property for the StartDate text box to Short Date.
Set the Control Source property for the FutureDate text box to the following expression: =DateSerial(year([StartDate]), month([StartDate])+6, day([StartDate]))


Switch to Form view.
In the StartDate text box, type a date (mm/dd/yy), and then press the TAB key.
The value displayed in the FutureDate text box is six months after the date in the StartDate text box.
Inserting a plus (+) followed by a number after the year, month, or day in the appropriate part of the function calculates a date that number of units in the future. Inserting a minus (-) followed by a number in the appropriate part of the function calculates a date that number of units in the past.

[MMcCarthy]

(Microsoft Site) ---- In Access, you can calculate a future or past date from a starting date by using an expression. For example, you can enter a date in a control on a form and calculate a date that is six months in the future from the original date. This article demonstrates this technique by using the DateSerial function to calculate the future date in the controls on a form. The calculated date can then be used in other calculations.

To calculate a future date by using the DateSerial function

In the Database window, click Forms under Objects, and then click New.
In the New Form dialog box, click Design View, and then click OK.
Using the Text Box tool in the toolbox, add two unbound text boxes to your form.
Set the Name property for one text box to StartDate and the other to FutureDate.
Set the Format property for the StartDate text box to Short Date.
Set the Control Source property for the FutureDate text box to the following expression: =DateSerial(year([StartDate]), month([StartDate])+6, day([StartDate]))


Switch to Form view.
In the StartDate text box, type a date (mm/dd/yy), and then press the TAB key.
The value displayed in the FutureDate text box is six months after the date in the StartDate text box.
Inserting a plus (+) followed by a number after the year, month, or day in the appropriate part of the function calculates a date that number of units in the future. Inserting a minus (-) followed by a number in the appropriate part of the function calculates a date that number of units in the past.

What you have here will work fine but it doesn't store the FutureDate anywhere.

Mac, I created a form using the info listed below from a Microsoft site. I changed the control source value for "futuredate" to futuredate. Also added the DatesSerial string to the default value as you prescribe. I can't figure out what I'm doing wrong. Again thanks for your help.

What is showing in the FutureDate textbox. Is it the correct value?

[xlr8sion]

FutureDate Textbox has "Futuredate".

[MMcCarthy]

FutureDate Textbox has "Futuredate".

OK, open the properties window for the FutureDate textbox and click the dropdown box beside the Control Source property. Is futuredate a field on the list?

[xlr8sion]

Yes, and I have it as the selected field

[MMcCarthy]

Yes, and I have it as the selected field

Then I don't understand why the value is not being stored in the field. If you close the form and open the table to the same record is the value still not stored there?

[xlr8sion]

Yes, after closing the form, the table field for "Futuredate" has not been updated/changed, in fact it is blank. Table field for "StartDate"is doing what it is suppose to do. Still baffled by this somewhat simple procedure.

[MMcCarthy]

So the calculated value of futuredate shows on the form but doesn't get stored in the table?

Mary

[xlr8sion]

Yes, that is correct. I have just about given up on this. Can't figure out what the problem could be. I have deleted the database and created new, just to see if that made a difference. The current (test) database at this point only has the table and form for three fields -- ID, Startdate, and Futuredate. Dave

[MMcCarthy]

Yes, that is correct. I have just about given up on this. Can't figure out what the problem could be. I have deleted the database and created new, just to see if that made a difference. The current (test) database at this point only has the table and form for three fields -- ID, Startdate, and Futuredate. Dave

Can you try to attach the test database to your last post Dave. If you click the Edit/Delete link it should give you the option.

Mary

BTW, post a reply once it's done so I'll know.

[xlr8sion]

Mary - here's the "Test" database -- good luck -- Dave

[xlr8sion]

Well I'll try it again with a zip file.

[MMcCarthy]

Well I'll try it again with a zip file.

What version of Access are you using and why have you got an accdb extension on the file.

Mary

[xlr8sion]

That would be 2007 Beta -- free trial and download -- had the same problem with version 2000 that I was using previuosly. I guess Microsoft has gone to a new extension for the databases.

[MMcCarthy]

That would be 2007 Beta -- free trial and download -- had the same problem with version 2000 that I was using previuosly. I guess Microsoft has gone to a new extension for the databases.

OK, I haven't gotten that version so can't open the file. Can you try to convert the database to previous version. Normally an option under the Tools menu probably in Utilities.

[xlr8sion]

Sorry! Here you go. Dave

[MMcCarthy]

OK Dave the default value will only work with new records not on existing ones. I've removed the code from there and added to simple vba event procedures one of which will run if the start date is changed or a new startdate is added and the other of which will run when the form record is opened which will work for all existing records.

Mary

[xlr8sion]

Thanks Mary, When I opened the rev database in 2007 -- it didn't show the form ob ject -- only the table. I will try it in another computer with earlier version of Access. Thanks again, Dave