I have an expression in the query under my form to autofill the date but it wont write to the underlying table

[sandraz444]

I have an expression in the query under my form to autofill the date
under a certain condition but it wont write to the underlying table??
The date shows in the form but not the table. Does anyone know a
solution for this?

Thanks!

[Tom van Stiphout]

On 19 Sep 2006 16:50:40 -0700, sa********@aol. com wrote:

Perhaps the control is not bound. Check the ControlSource property.
-Tom.

>I have an expression in the query under my form to autofill the date
under a certain condition but it wont write to the underlying table??
The date shows in the form but not the table. Does anyone know a
solution for this?

Thanks!

[Bob Quintal]

sa********@aol. com wrote in
news:11******** *************@i 42g2000cwa.goog legroups.com:

I have an expression in the query under my form to autofill
the date under a certain condition but it wont write to the
underlying table?? The date shows in the form but not the
table. Does anyone know a solution for this?

Thanks!

We are missing too many pieces of your puzzle to answer you.
If you provide us with the query's sql, and the name of the control
on the form and the structure of the table you are trying to
update, and information how you have bound the three together, then
we might have a chance to help.

However, on general principles, I question whether you even need
the expression in a query, it's usually easier to generate the
expression in the form, and simply set the value into the textbox
that's tied to the table.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[sandraz444]

Bob Quintal wrote:

sa********@aol. com wrote in
news:11******** *************@i 42g2000cwa.goog legroups.com:

I have an expression in the query under my form to autofill
the date under a certain condition but it wont write to the
underlying table?? The date shows in the form but not the
table. Does anyone know a solution for this?

Thanks!

We are missing too many pieces of your puzzle to answer you.
If you provide us with the query's sql, and the name of the control
on the form and the structure of the table you are trying to
update, and information how you have bound the three together, then
we might have a chance to help.

However, on general principles, I question whether you even need
the expression in a query, it's usually easier to generate the
expression in the form, and simply set the value into the textbox
that's tied to the table.

--
Bob Quintal

PA is y I've altered my email address.

Hi Bob,

Thanks for your response. When I write the expression: IIf([Case
Status]="Closed", Date()) in the form instead of the query, it still
isn't writing to the table. Do I write it to the Control source for
the field or somewhere else? You mentioned to simply set the value
into the textbox? could you expound on that? I am at home now and
this is a work related problem so I don't have the structure of the
table and query to send but could you give me any further tips so I
could try it in the morning? Thanks!!!!!

[sandraz444]

Hi Again Bob,

Maybe a clearer question would be: how do I write an IIf statement in
a form control that will write to the underlying table or actually
where do I write it? I have tried directly in the control source and
it only shows in the form not on the table.

Can you help?

thanks

We are missing too many pieces of your puzzle to answer you.
If you provide us with the query's sql, and the name of the control
on the form and the structure of the table you are trying to
update, and information how you have bound the three together, then
we might have a chance to help.

However, on general principles, I question whether you even need
the expression in a query, it's usually easier to generate the
expression in the form, and simply set the value into the textbox
that's tied to the table.

--
Bob Quintal

[Larry Linson]

<sa********@aol .comwrote in message
news:11******** **************@ i42g2000cwa.goo glegroups.com.. .

Hi Again Bob,

Maybe a clearer question would be: how do I write an IIf statement in
a form control that will write to the underlying table or actually
where do I write it? I have tried directly in the control source and
it only shows in the form not on the table.

If you use an expression in the Control Source of a Control, how do you
expect to bind that Control to a Field in the Form's Record Source? It is
illogical to expect either the Query or the Form to _just know_ the Field
into which you want to store the information.

Two things you can do:

(1) Write the VBA code to do the calculation in an
appropriate event -- what is "appropriat e" depends
on your particular requirements.

(2) Create another Control, bound to the Field where
you want the information stored (that Field name
in the Control's ControlSource property), set its
Visible property to "No," and in an appropriate
event, write VBA code to move the contents of the
Calculated Control to the invisible one. In your
case, this _might_ be the AfterUpdate event of
the control in which CaseStatus can be entered,
(and possibly, the OnCurrent event)

Larry Linson
Microsoft Access MVP

[sandraz444]

Hi Larry,
Thank you so much for your response. Unfortunately I don't know how to
write VBA. Everything I do has to be in query builder, forms, or
reports directly. I really need to get this done, any other
suggestions for me?

Larry Linson wrote:

<sa********@aol .comwrote in message
news:11******** **************@ i42g2000cwa.goo glegroups.com.. .

Hi Again Bob,

Maybe a clearer question would be: how do I write an IIf statement in
a form control that will write to the underlying table or actually
where do I write it? I have tried directly in the control source and
it only shows in the form not on the table.

If you use an expression in the Control Source of a Control, how do you
expect to bind that Control to a Field in the Form's Record Source? It is
illogical to expect either the Query or the Form to _just know_ the Field
into which you want to store the information.

Two things you can do:

(1) Write the VBA code to do the calculation in an
appropriate event -- what is "appropriat e" depends
on your particular requirements.

(2) Create another Control, bound to the Field where
you want the information stored (that Field name
in the Control's ControlSource property), set its
Visible property to "No," and in an appropriate
event, write VBA code to move the contents of the
Calculated Control to the invisible one. In your
case, this _might_ be the AfterUpdate event of
the control in which CaseStatus can be entered,
(and possibly, the OnCurrent event)

Larry Linson
Microsoft Access MVP

[Larry Linson]

That's such simple code that it is likely that someone will write it for you
if you provide the names of the Controls involved, the name of the Field to
which the invisible Control is bound, and the circumstances under which you
want to insert today's date. If it is just on creating a new record, you
can use =Date() as the Default Value for a Field (e.g., DateCreated) but
that only works on creating a new Record, not on updating existing Records.

Larry Linson
Microsoft Access MVP
<sa********@aol .comwrote in message
news:11******** *************@b 28g2000cwb.goog legroups.com...

Hi Larry,
Thank you so much for your response. Unfortunately I don't know how to
write VBA. Everything I do has to be in query builder, forms, or
reports directly. I really need to get this done, any other
suggestions for me?

Larry Linson wrote:

><sa********@ao l.comwrote in message
news:11******* *************** @i42g2000cwa.go oglegroups.com. ..

Hi Again Bob,

Maybe a clearer question would be: how do I write an IIf statement in
a form control that will write to the underlying table or actually
where do I write it? I have tried directly in the control source and
it only shows in the form not on the table.

If you use an expression in the Control Source of a Control, how do you
expect to bind that Control to a Field in the Form's Record Source? It
is
illogical to expect either the Query or the Form to _just know_ the Field
into which you want to store the information.

Two things you can do:

(1) Write the VBA code to do the calculation in an
appropriate event -- what is "appropriat e" depends
on your particular requirements.

(2) Create another Control, bound to the Field where
you want the information stored (that Field name
in the Control's ControlSource property), set its
Visible property to "No," and in an appropriate
event, write VBA code to move the contents of the
Calculated Control to the invisible one. In your
case, this _might_ be the AfterUpdate event of
the control in which CaseStatus can be entered,
(and possibly, the OnCurrent event)

Larry Linson
Microsoft Access MVP

[sandraz444]

Hi Larry,

I actually started out by making the field default to =Date() but the
problem showed up when I realized all the records I append to the table
(which I have to do daily) automatically populated with the default
date.

I only want the date to populate the field when the record is closed.
Which is why I wrote the query to populate the "Closed Date" field when
the "Status" field has "CLOSED" entered.

But it doesn't write to the table and on requerying it only shows the
current date instead of the date it was actually closed....

Any other suggestions would be highly appreciated. Thanks again for
taking the time for simple explanations.
Larry Linson wrote:

That's such simple code that it is likely that someone will write it for you
if you provide the names of the Controls involved, the name of the Field to
which the invisible Control is bound, and the circumstances under which you
want to insert today's date. If it is just on creating a new record, you
can use =Date() as the Default Value for a Field (e.g., DateCreated) but
that only works on creating a new Record, not on updating existing Records.

Larry Linson
Microsoft Access MVP
<sa********@aol .comwrote in message
news:11******** *************@b 28g2000cwb.goog legroups.com...

Hi Larry,
Thank you so much for your response. Unfortunately I don't know how to
write VBA. Everything I do has to be in query builder, forms, or
reports directly. I really need to get this done, any other
suggestions for me?

Larry Linson wrote:

<sa********@aol .comwrote in message
news:11******** **************@ i42g2000cwa.goo glegroups.com.. .
Hi Again Bob,

Maybe a clearer question would be: how do I write an IIf statement in
a form control that will write to the underlying table or actually
where do I write it? I have tried directly in the control source and
it only shows in the form not on the table.

If you use an expression in the Control Source of a Control, how do you
expect to bind that Control to a Field in the Form's Record Source? It
is
illogical to expect either the Query or the Form to _just know_ the Field
into which you want to store the information.

Two things you can do:

(1) Write the VBA code to do the calculation in an
appropriate event -- what is "appropriat e" depends
on your particular requirements.

(2) Create another Control, bound to the Field where
you want the information stored (that Field name
in the Control's ControlSource property), set its
Visible property to "No," and in an appropriate
event, write VBA code to move the contents of the
Calculated Control to the invisible one. In your
case, this _might_ be the AfterUpdate event of
the control in which CaseStatus can be entered,
(and possibly, the OnCurrent event)

Larry Linson
Microsoft Access MVP

[Bob Quintal]

sa********@aol. com wrote in
news:11******** **************@ k70g2000cwa.goo glegroups.com:

Hi Larry,

I actually started out by making the field default to =Date()
but the problem showed up when I realized all the records I
append to the table (which I have to do daily) automatically
populated with the default date.

I only want the date to populate the field when the record is
closed. Which is why I wrote the query to populate the "Closed
Date" field when the "Status" field has "CLOSED" entered.

But it doesn't write to the table and on requerying it only
shows the current date instead of the date it was actually
closed....

Any other suggestions would be highly appreciated. Thanks
again for taking the time for simple explanations.

Ok, you are going to write a little bit ov Visual Basic code.
I promise you it won't hurt a bit.

First put the "closed date" field on the form. Make sure the
..control Source property is set to the field in the query or
table's name.

Second, in the properties for the "status" field find the "After
Update" box on the events tab. If you already have a macro
there, stop, otherwise click in the box, and pick "Event
Procedure" from the dropdown. You'll see an ellipse(three
periods) button to the right.Click it.

The Visual Basic (VB) editor.will open with the cursor between
two lines of code that the editor has written for you.
The upper line will start "private sub status". The lower line
will be "end sub"

In between those two lines, type:
If me.[status] = "CLOSED" then
me.[Closed Date] = date()
end if

Pretty painless? And now you can add writing Visual Basic
programs to your resumé.
..
Using the After Update event of the "Status" box is so the date
will only get written when you change the status.
--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com