By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,998 Members | 2,773 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,998 IT Pros & Developers. It's quick & easy.

Forms not updating tables

P: 14
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.
Jan 15 '07 #1
Share this Question
Share on Google+
24 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Jan 15 '07 #2

P: 14
Thanks for the quick reply - I will try that this morning and let you know how it turns out.
Jan 16 '07 #3

P: 14
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.
Jan 16 '07 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Jan 16 '07 #5

P: 14
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.
Jan 16 '07 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Jan 16 '07 #7

P: 14
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.
Jan 17 '07 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
(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?
Jan 17 '07 #9

P: 14
FutureDate Textbox has "Futuredate".
Jan 17 '07 #10

MMcCarthy
Expert Mod 10K+
P: 14,534
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?
Jan 17 '07 #11

P: 14
Yes, and I have it as the selected field
Jan 17 '07 #12

MMcCarthy
Expert Mod 10K+
P: 14,534
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?
Jan 17 '07 #13

P: 14
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.
Jan 17 '07 #14

MMcCarthy
Expert Mod 10K+
P: 14,534
So the calculated value of futuredate shows on the form but doesn't get stored in the table?

Mary
Jan 17 '07 #15

P: 14
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
Jan 17 '07 #16

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Jan 17 '07 #17

P: 14
Mary - here's the "Test" database -- good luck -- Dave
Jan 17 '07 #18

P: 14
Well I'll try it again with a zip file.
Attached Files
File Type: zip test.zip (25.3 KB, 115 views)
Jan 17 '07 #19

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Jan 18 '07 #20

P: 14
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.
Jan 18 '07 #21

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Jan 18 '07 #22

P: 14
Sorry! Here you go. Dave
Attached Files
File Type: zip test2.zip (12.2 KB, 117 views)
Jan 18 '07 #23

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Attached Files
File Type: zip test2_Rev.zip (21.3 KB, 139 views)
Jan 18 '07 #24

P: 14
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
Jan 18 '07 #25

Post your reply

Sign in to post your reply or Sign up for a free account.