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

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

P: n/a
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!

Sep 19 '06 #1
Share this Question
Share on Google+
10 Replies


P: n/a
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!
Sep 20 '06 #2

P: n/a
sa********@aol.com wrote in
news:11*********************@i42g2000cwa.googlegro ups.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

Sep 20 '06 #3

P: n/a
Bob Quintal wrote:
sa********@aol.com wrote in
news:11*********************@i42g2000cwa.googlegro ups.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!!!!!

Sep 20 '06 #4

P: n/a
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
Sep 20 '06 #5

P: n/a
<sa********@aol.comwrote in message
news:11**********************@i42g2000cwa.googlegr oups.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 "appropriate" 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
Sep 20 '06 #6

P: n/a
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.googlegr oups.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 "appropriate" 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
Sep 21 '06 #7

P: n/a
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*********************@b28g2000cwb.googlegro ups.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.googleg roups.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 "appropriate" 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

Sep 21 '06 #8

P: n/a
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*********************@b28g2000cwb.googlegro ups.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.googlegr oups.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 "appropriate" 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
Sep 21 '06 #9

P: n/a
sa********@aol.com wrote in
news:11**********************@k70g2000cwa.googlegr oups.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

Sep 21 '06 #10

P: n/a
Thank you so much!! I guess it is time to take a class in VB code!
I love Access and I am so glad I found this website where there are so
many nice people willing to help.

Many blessings to you - thanks again!
Bob Quintal wrote:
sa********@aol.com wrote in
news:11**********************@k70g2000cwa.googlegr oups.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
Sep 22 '06 #11

This discussion thread is closed

Replies have been disabled for this discussion.