Populated field not writing back to table

[gls]

Hi

I have a field on a form called C_Date and another field on the same form called C_Response_Date. Once I enter a date in C_Date, I have the C_Response_Date populated from the control source =C_Date + 12, which automatically enters a date which is 12 days greater than C_Date. However the C_Response_Date will not appear back in the table like the C_Date appears. Can someone help. The form is based on a table.

Garry

 

[wiklendt]

is C_response_date a field in your table (not just a field in the form)?

you may need to make this work via VBA - something in the order of an 'after update' event of the c_date field:

(warning: air-code)

Dim strResponseDate As Date

strResponseDate = txtCDate + 12

txtCDate.Value = strResponseDate

also, i urge AGAINST using special characters (like underscore) or spaces (FYI) in names of your fields or tables/queries/forms/reports...

 

[gls]

Thankyou, I treid that but it didn't work, debug highlighted this code txtCDate.Value = strResponseDate

I find the spaces, underscores etc inconsistent, as many people say don't use them and others say use underscores etc. So I was never really sure whether to use them or not, very confusing.

The field is in the table

Thankyou

 

[wiklendt]

Thankyou, I treid that but it didn't work, debug highlighted this code txtCDate.Value = strResponseDate

I find the spaces, underscores etc inconsistent, as many people say don't use them and others say use underscores etc. So I was never really sure whether to use them or not, very confusing.

The field is in the table

Thankyou

i'm not surprised it didn't work first time around - it was only air code, after all. second, you have to replace the name of the control with whatever you've actually called that control:

is your textbox control called txtCDate? if not, you'll have to replace it with the exact name of the control as you've called it.

i just used that as a probable name. sorry, i should have been more explicit how to use that code. as an after thought, you may need to prefix it with "Me." (to tell the code that control is in the current form)

if you get any more error messages, it's helpful for you to tell us what they are as well as what happens then (or how it doesn't work). in this instance it was obvious to me, but that may not be the case in all instances.

 

[Rich]

You shouldn't store this date, it can be calculated at any time via a query or a simple calculated field on the form

 

[wiklendt]

You shouldn't store this date, it can be calculated at any time via a query or a simple calculated field on the form

ah of course! wise words of wisdom from Rich! i'm so daft sometimes! Thanks for jumping in to correct us both!

 

[missinglinq]

I have to ask you, wiklendt, why the advice against using underscores in names? I've been contributing to four or five Access forums for a number of years now and I have never seen anyone advance this theory!

In point of fact, if you name a control My Textbox, when going to an event of the control in VBA, Access will insert the underscore and call the control My_Textbox.

Having said that, I dislike the practice myself, and prefer using caps for each component of a name, such as MyTextbox, but that's just me! I know of no problem caused by using the underscore.

Spaces in names should, of course, be avoided at all costs, as this does cause problems!

 

[gemma-the-husky]

i suspect its a binding issue

where do you have your code to set

c_response_date

and are you SURE c_response_date is BOUND to a field in your table

in fact is c_response_date a visible field? and do you have option explicit set at the the top of your code?
depending on how this is set up, vba may just be setting up a local variable

---------

 

[missinglinq]

I have the C_Response_Date populated from the control source =C_Date + 12

It's obviously not bound to a field in the table, Dave. If it were, its Control Source couldn't be =C_Date + 12 as stated in the oiginal post.

 

[wiklendt]

I have to ask you, wiklendt, why the advice against using underscores in names? I've been contributing to four or five Access forums for a number of years now and I have never seen anyone advance this theory!
...

Spaces in names should, of course, be avoided at all costs, as this does cause problems!

i actually provided a link to a recent post which had issues with the underscore. until i saw this post i didn't think underscore would be an issue, but having read it, i may need to alter some of my OWN databases! LOL

here is the link again Underscore.

edit: and of course, now that i provided the link, i read the lastest post form the OP - turns out the OP had code to parse through names and change any underscores into spaces! and that ended up being the problem - the spaces, not the underscores - so looks like underscores are indeed ok! there you go.

 

[gls]

Thankyou, i will try this.

 

[gls]

I tried this and the date worked, however it would not write the date back to the table. So now the field is bound to the table.

 

[gls]

i suspect its a binding issue

where do you have your code to set

c_response_date

and are you SURE c_response_date is BOUND to a field in your table

in fact is c_response_date a visible field? and do you have option explicit set at the the top of your code?
depending on how this is set up, vba may just be setting up a local variable

---------

I'm not sure what you mean about where do I have my code set, i guess it's in the form. Yes C_response_date is bound to a table. Yes, it is a visible field on the form, however if i could get the calculation working, i would hide the field off the form. A little confused now.

 

[wiklendt]

I'm not sure what you mean about where do I have my code set, i guess it's in the form. Yes C_response_date is bound to a table. Yes, it is a visible field on the form, however if i could get the calculation working, i would hide the field off the form. A little confused now.

as Rich said, you shouldn't store a calculated field.

and if you don't even need to see it (as you've implied) then all you need to do is have the field calculated in a query of whereever you're going to use the calculation

you don't need a form unless you need to view the calculation - even then the form you are using should have a query as it's control source, and you should be able to just add that calculated field to the query itself (either by referencing the first instance of the field from another query, or by re-calculating it in that query)