set field to empty upon condition

  • Thread starter Thread starter Jean-Paul
  • Start date Start date
J

Jean-Paul

I created a continuous form based upon a query
There is a true/false checkbox.
The checkbox is related to a field in a table.
When I check the box I want an update query to run.
The idea is that a date-field is emptied when the checkbox is checked.
What is the code to delete the content of a field?

I know how to change it into a new dat... but not how to empty it:

UPDATE [Adressen metaal] SET [Adressen metaal].Datum_bezoek = #1/1/2008#
WHERE ((([Adressen metaal].Bezoeken)=True));
 
you just need to say

= null

rather than

=adate

make a note the problem with that query is that it will alter all
values that have bezoeken = true unless that is what you want because
you should be targeting the unique identifier in the form and updating
it like that. even if that is what you want it will take longer to
update all records that are set to true than it will to update the 1.

to execute the update query from the after update event on the
checkbox

hope this helps

Regards
Kelvan
 
yes you are right... how to only update THAT record I changed the value
of the checkbox?
Thanks
JP
 
Thanks for your kind help...
What I have until now is:

I have a continuous form with records displayed based upon a query..
works great.

Each record has a true/false field: "delete"

When I make this field true, the date field of that record should be set
to null
This does not work for the moment.
Suppose I have 2 records where the "delete-checkbox" is true

In the "after update" property of the "delete" field I wrote:

DoCmd.RunSQL "UPDATE [Adressen metaal] SET [Adressen
metaal].Datum_bezoek = null WHERE ((([Adressen metaal].Bezoeken)=True));"

I changed one checkbox to false

I still get a warning 2 records will be updated.... while I hove only
one checked.
When I close the continuous form and open it again... THEN everyting is
correct

Until now it isn't

Any idea??
Thanks

Jean-Paul said:
I created a continuous form based upon a query
There is a true/false checkbox.
The checkbox is related to a field in a table.
When I check the box I want an update query to run.
The idea is that a date-field is emptied when the checkbox is checked.
What is the code to delete the content of a field?

I know how to change it into a new dat... but not how to empty it:

UPDATE [Adressen metaal] SET [Adressen metaal].Datum_bezoek = #1/1/2008#
WHERE ((([Adressen metaal].Bezoeken)=True));
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Same thing, except substitute NULL for the date:

SET [Adressen metaal].Datum_bezoek = NULL
Click to expand...
 
yeah my problem is i dont understand what the fields you have mean

i do hope you have a primary key in your table and it is also
accessible for that record in the form.


DoCmd.RunSQL "UPDATE [Adressen metaal] SET [Adressen
metaal].Datum_bezoek = null WHERE [Adressen metaal].[primary key
field] = " & textboxcontainginprimarykey.value

is how you would update that specific record. as i said you cna only
do this if you have a unique identifier.

Regards
Kelvan
 
Back
Top