how do I delete every field on a form without deleting the record

[steve goodrich]

We have a database with 1285 records which need to be amended, never deleted
I want to create a button on the form that will delete all fields but one
This is what I've used so far
Me.FIRST_NAME = Null
Me.INIT = Null
Me.SURNAME = Null
and so on
Me.L1 = False
Me.L2 = False

this works ok for text (null) and Check boxes (false)

I also have a photograph (ole) on the form.

My question is - What do I need to type to delete the photograph?

many thanks

Steve Goodrich

 

[steve goodrich]

Just found out that the Me.field_name = Null doesn't work on drop down combo
boxes. What do I type to delete those?

 

[Jeff Boyce]

Steve

Might I ask why you wish to delete (not the fields but) the values
displayed? It could make a difference in what folks offer in the way of
suggestions...

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[steve goodrich]

Yes
each one of the records represents an access card for our building - 1285 in
total.
When a member of staff leaves all the fields in the record are deleted
except the access card number (This will be available to a new member of
staff)
The record selector at the bottom of the form is the same as the access card
number, so if for example I want to go to card 185, I would enter this as
the record number and it would open the record of card 185. It's been
working great for 5 years now.
The reason for my question is - when a member of staff leaves a command
button would be useful to delete all the values in every field except the
card number, instead of tabbing through all the fields and hitting delete.
There are 40 fields per record. Hope this make sense
Steve

 

[John Vinson]

The reason for my question is - when a member of staff leaves a command
button would be useful to delete all the values in every field except the
card number, instead of tabbing through all the fields and hitting delete.
There are 40 fields per record. Hope this make sense

Run an Update query using the ID as a criterion, updating all forty
fields to NULL.

John W. Vinson[MVP]

 

[steve goodrich]

do I use [enter no] in the criteria of the access no. or is there a way to
clear the CURRENT record without being prompted for a number?
Steve

 

[tina]

set the criteria in the Update query to

[Forms]![NameOfForm]![NameOfPrimaryKeyField]

replace NameOfForm with the correct name of the form you're working in, and
replace NameOfPrimaryKeyField with the correct name of the primary key field
in the table that the form is bound to.

hth

do I use [enter no] in the criteria of the access no. or is there a way to
clear the CURRENT record without being prompted for a number?
Steve

Run an Update query using the ID as a criterion, updating all forty
fields to NULL.

John W. Vinson[MVP]

 

[steve goodrich]

Thanks for all your help. The update query worked for all but one of my
fields.
I have a picture on my form (ole) and when I include that in the update
query and set it to update to null like the rest, I get the following error
message.
"couldn't update, currently locked by another session on this machine"
If I remove the picture field from the query, it works great.
Do I need to set the update criteria to something other than null for this
ole field?
many thanks and a happy Christmas to all
Steve

set the criteria in the Update query to

[Forms]![NameOfForm]![NameOfPrimaryKeyField]

replace NameOfForm with the correct name of the form you're working in,
and
replace NameOfPrimaryKeyField with the correct name of the primary key
field
in the table that the form is bound to.

hth

do I use [enter no] in the criteria of the access no. or is there a way
to
clear the CURRENT record without being prompted for a number?
Steve

On Sat, 23 Dec 2006 21:48:13 -0000, "steve goodrich"

The reason for my question is - when a member of staff leaves a command
button would be useful to delete all the values in every field except the
card number, instead of tabbing through all the fields and hitting delete.
There are 40 fields per record. Hope this make sense

Run an Update query using the ID as a criterion, updating all forty
fields to NULL.

John W. Vinson[MVP]

 

[missinglinq via AccessMonster.com]

Don't know where my response went; left it here last night.

YourOLEControl.Value = ""

should do the job (tested in AC2000 simply put behind a button.)

Happy Holidays!

 

[steve goodrich]

Tried setting the update criteria to "" as you suggested but am still
getting the same error message.
Thanks for replying anyway
Steve

 

[missinglinq via AccessMonster.com]

Why not simply try

YourOLEControl.Value = ""

behind your button BEFORE you run the update query, instead of as part of the
update query?

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000

Message posted via AccessMonster.com

 

[steve goodrich]

How do I do that?
sorry I'm still learning, and finding the learning curve difficult to say
the least
Steve

 

[missinglinq via AccessMonster.com]

I assume from your previous posts that you have a button on your form that
you're using to run your query. Simply replace YourOLEControl with the actual
name of your control the place

YourOLEControl.Value = ""

in the code immediatley before the code to run the query. Remember to remove
whatever reference to the OLEcontrol you currently have in the query.