Re: Clearing all fields with code problem

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

Steve Goodrich wrote:
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
"John Spencer" <spencer@xxxxxxxxxx> wrote in message news:%23Xg2MugwJHA.1504@xxxxxxxxxxxxxxxxxxxxxxx
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
'====================================================


Steve Goodrich wrote:
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



"Philip Herlihy" <bounceback@xxxxxxx> wrote in message news:OepU00nvJHA.5100@xxxxxxxxxxxxxxxxxxxxxxx
Steve Goodrich wrote:
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

.

Loading