Clearing all fields with code problem

[Steve Goodrich]

I need to clear nearly all fields of a record without deleting the record
itself , (57 out of 60) I thought a command button on the form would be a
good Idea and save hitting the delete key 57 times.

Some fields are text, some fields are check boxes and one field is an OLE
Picture

I clear the text and check boxes by using the following code:

Check boxes
Me.L18 = False
Me.L18 = False
Me.L19 = False
Me.L20 = False
Me.L21 = False
Me.L22 = False
etc.

Text boxes
me. firstName = null
me.surname = null
etc.

I clear my ole picture field using

DoCmd.GoToControl "Picture"
DoCmd.RunCommand acCmdDelete

This works most of the time!
Sometimes (not all) if the record doesn't have a picture within the record,
I get the following message.

Run Time error 29013
This action will reset the current code in break mode. Do you want to stop
the running code?
To halt the execution of the program so the module window can be closed,
select yes. To leave the code in the current state, select no.
There are no options for Yes/No, There are 2 options, Debug and End, There
is a third option which is greyed out - Continue.

I click Debug and the last line of my code is highlighted
DoCmd.RunCommand acCmdDelete

If I do nothing and close the database down and re-open it, It works fine
again even when clearing records with no picture!!

The next time I use it I could get the error message again.

Can any one suggest a solution, or is there another way to clear the OLE
picture with my command button?

Thanks for any help

 

[Philip Herlihy]

I need to clear nearly all fields of a record without deleting the record
itself , (57 out of 60) I thought a command button on the form would be a
good Idea and save hitting the delete key 57 times.

Some fields are text, some fields are check boxes and one field is an OLE
Picture

I clear the text and check boxes by using the following code:

Check boxes
Me.L18 = False
Me.L18 = False
Me.L19 = False
Me.L20 = False
Me.L21 = False
Me.L22 = False
etc.

Text boxes
me. firstName = null
me.surname = null
etc.

I clear my ole picture field using

DoCmd.GoToControl "Picture"
DoCmd.RunCommand acCmdDelete

This works most of the time!
Sometimes (not all) if the record doesn't have a picture within the record,
I get the following message.

Run Time error 29013
This action will reset the current code in break mode. Do you want to stop
the running code?
To halt the execution of the program so the module window can be closed,
select yes. To leave the code in the current state, select no.
There are no options for Yes/No, There are 2 options, Debug and End, There
is a third option which is greyed out - Continue.

I click Debug and the last line of my code is highlighted
DoCmd.RunCommand acCmdDelete

If I do nothing and close the database down and re-open it, It works fine
again even when clearing records with no picture!!

The next time I use it I could get the error message again.

Can any one suggest a solution, or is there another way to clear the OLE
picture with my command button?

Thanks for any help

Part of the problem is that you're not using your own error-handling
code, which can give you more information about what's going on, and
help pin-point the problem. See the 9 numbered lines in this valuable
summary: http://allenbrowne.com/ser-23a.html

Of course, you should test to see if there is anything there to delete
before trying to do so!

There's an alternative approach: if you're trying to alter a record,
rather than the contents of a form (which will only update the record
when you move to a new record, or explicitly save the values) then you
could kick off an update query and refresh the form to view the changed
values.

Are you clearing (most of) an existing record? Or undoing changes not
yet saved? Or undoing changes just made and saved? If the latter two,
look in Help for "undo changes", and the Undo method on a form.

Phil, London

 

[Ken Sheridan]

Rather than listing all the controls you can loop through the form's Controls
collection. To identify the controls to clear set their Tag property, e.g
for the check boxes to tgCheck, for the text boxes to tgText, for the object
frame to tgOLE. The use the following code:

Dim ctrl As Control

For Each ctrl In Me.Controls
Select Case ctrl.Tag
Case "tgText", "tgOLE"
ctrl = Null
Case "tgCheck"
ctrl = False
End Select
Next ctrl

Ken Sheridan
Stafford, England

 

[Jeff Boyce]

Steve

It isn't clear from your description what your underlying data structure is
like "behind" that form.

Folks will often start with a form they are trying to duplicate, then build
a table that matches the form. I can't tell if this is what you've done.
If it is, one solution might be to move this to Excel ... it is fairly rare
for a well-normalized Access database table to have more than 30 columns ...
and if there are repeating columns (checkbox1, checkbox2, ....), there's a
very good chance the data is not normalized.

So what? you ask... So Access 'expects' well-normalized data for the
features and functions it offers. If your data isn't, both you and Access
will end up working a lot harder to do things that are fairly simple when
the data's structured in a way Access understands.

.... something of a 'pay now or pay later' situation!

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Steve Goodrich]

Thanks for your replies gents.

The database is for 1285 cards. That value never changes. The cards are
assigned to staff in our building, so the card is either blank, I.e no one
has that card or someone has the card and the record is filled in. When a
member of staff leaves, the record is cleared apart from 3 fields (one of
which is the card number) The record is not deleted. When a new member of
staff starts they are assigned one of the free cards and the record is
completed.
There is only one table (first db I built, would probably do it different
now) apart from several small tables that populate the drop down boxes. The
form we use to edit records is bound to the one table.

The problem is when someone leaves, on occasions not all the fields are
"cleared" by the user which causes wrong information when running queries.

All I want is a simple button that when clicked will delete all but 3 of the
fields on my form (and of course in the table bound to it.)

What code should I enter that will check if there is any thing to delete in
my OLE picture field
And where should I enter it?

DoCmd.GoToControl "Picture"
DoCmd.RunCommand acCmdDelete

Thank again for your time.
Steve

 

[John Spencer]

Why not use an update query that sets ALL except the three fields to NULL?

UPDATE TheTable
SET FieldA = Null
, FieldB = Null
, FieldC = Null
, FieldE = Null
WHERE TheTable = CardNo

Simpler might be to Store the three values in variables, delete the
record, and create a new record with the three values

Dim vCard 'Assume Number field
Dim vFieldA 'Assume Text field
Dim vFieldX 'Assume Text field

Dim strSQL as String

VCard = Me.CardNo
vFieldA = Me.SomeField
vFieldX = Me.SomeOtherField

strSQL= "DELETE FROM TheTable WHERE CardNo = " & vCard
CurrentDb().Execute strSQL

strSQL = "INSERT Into TheTable (CardNo, FieldA, FieldB)" & _
"Values(" & vCard & ", """ & vFieldA & """, """ & vFieldB & """
CurrentDb().Execute strSQL

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[AccessVandal via AccessMonster.com]

I agree with Jeff Boyce.

These records should not be deleted or modified just because the Staff is not
using the cards anymore. How do you keep the information of previous Staff
whom was using it?

As for the last 3 controls, use the combo box to autofill the card (unused
card) information from a table (you have a list of card records in a table
right?).

You should avoid pictures in database tables. Link them using the folders in
your harddisk. MS Access has a limit file size of 2GB.

 

[Steve Goodrich]

How do I enter the text in the criteria for my card no when building an
update query.
so only the record showing on my form is deleted
Thanks

 

[Philip Herlihy]

Thanks for your replies gents.

The database is for 1285 cards. That value never changes. The cards are
assigned to staff in our building, so the card is either blank, I.e no one
has that card or someone has the card and the record is filled in. When a
member of staff leaves, the record is cleared apart from 3 fields (one of
which is the card number) The record is not deleted. When a new member of
staff starts they are assigned one of the free cards and the record is
completed.
There is only one table (first db I built, would probably do it different
now) apart from several small tables that populate the drop down boxes. The
form we use to edit records is bound to the one table.

The problem is when someone leaves, on occasions not all the fields are
"cleared" by the user which causes wrong information when running queries.

All I want is a simple button that when clicked will delete all but 3 of the
fields on my form (and of course in the table bound to it.)

What code should I enter that will check if there is any thing to delete in
my OLE picture field
And where should I enter it?

DoCmd.GoToControl "Picture"
DoCmd.RunCommand acCmdDelete

Thank again for your time.
Steve

Use the IsNull function to test if the OLE field is empty (although I
agree with AccessVandal that it's better to store these outside the
database and use a link instead.

I can't help thinking that something is really wrong with your data
structure. To get the best out of Access you should think "through" the
forms to the underlying tables, and if you have three fields which have
to be preserved each time and umpteen others which are repeatedly
cleared, then these groups of attributes surely belong to different
entities? (Entity is just a posh word for thingumyjig, and different
thingumyjigs should live in different tables.

Sounds to me as if you want a table for cards (4 fields, including a
foreign key to the "Person" table) and a Person table. New assignment
means you replace the foreign key in the Card table (so linking to a
different Person). If you want to keep a history of these assignments,
you need a "junction" table, which has foreign keys for both Card and
Person, and one or more date fields, the assignment being current if the
"end-date" field is null.

Phil

 

[John Spencer MVP]

Are you running code from a form?
Are you calling a delete query from a form?

In a saved query you could use the following as criteria

Field: [Card No]
Criteria: [Forms]![NameOftheForm]![Name of the control that has Card No]

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County