Delete Query Problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a delete query that delete all the record from a table. This query is
ran from a command button on a form through a macro.

When the process is completed. It show "# DELETED" in all the fields of all
the records. But the records are physically still there. When I exit the
program and re-start the program the record are no longer there.


how can I correct this problem?

Thank YOu
 
Did you requery your Form after you run the Delete Query?

HTH
Van T. Dinh
MVP (Access)
 
Think is a bug is Access. I check in Access KB.
The Recordsource in a query and the Delete CommandButton runs a macro.
The macro run a append query that appends the record to be deleted to a
achive table. Then runs a the delete query.
 
I haven't seen this error. However, I can't pinpoint the problem if you
don't post what I asked.

If you use Macros, post the details of the actions in the Macro.
 
Do you want the SQL of the queries. ?

Van T. Dinh said:
I haven't seen this error. However, I can't pinpoint the problem if you
don't post what I asked.

If you use Macros, post the details of the actions in the Macro.
Click to expand...
 
This is the main record source query on the form.
SELECT tblFileFolderLabels.LastName, tblFileFolderLabels.FirstName,
tblFileFolderLabels.HealthID
FROM tblFileFolderLabels;

The form as three input fields [LastName],[FirsName] and [HealthID]
and 2 unbounded text box that calculated the number of labels to
This is the append query:
INSERT INTO tblArchive ( LastName, FirstName, HealthID, PrintDate )
SELECT tblFileFolderLabels.LastName, tblFileFolderLabels.FirstName,
tblFileFolderLabels.HealthID, tblFileFolderLabels.PrintDate
FROM tblFileFolderLabels;

This is the delete query
DELETE tblFileFolderLabels.LastName, tblFileFolderLabels.FirstName,
Trim([LastName])+", "+Trim([FirstName]) AS Name, tblFileFolderLabels.HealthID
FROM tblFileFolderLabels;

A macro runs both queries back to back from a command button on the form.
 
The Field names (including the calculated Field) in your DELETE Query in not
necessary. However, I am looking for the Requery (Macro) action since if
you don't requery, the Form's Recordset is out of date after deletion and
will show "#DELETED# for rows that have been deleted and in your case all
rows!
 
This is the delete macro

SetWarning - No
OpenQuery - qryAppend
OpenQuery - qrydelete
Requery - [FirstName]
Requery - [LastName]
Requery - [HealthID]

The requery are the control name of the fields on that form.
And I still have the "#DELETED"
Am I requery the correct control name??



Van T. Dinh said:
The Field names (including the calculated Field) in your DELETE Query in not
necessary. However, I am looking for the Requery (Macro) action since if
you don't requery, the Form's Recordset is out of date after deletion and
will show "#DELETED# for rows that have been deleted and in your case all
rows!

--
HTH
Van T. Dinh
MVP (Access)


iholder said:
This is the main record source query on the form.
SELECT tblFileFolderLabels.LastName, tblFileFolderLabels.FirstName,
tblFileFolderLabels.HealthID
FROM tblFileFolderLabels;

The form as three input fields [LastName],[FirsName] and [HealthID]
and 2 unbounded text box that calculated the number of labels to
This is the append query:
INSERT INTO tblArchive ( LastName, FirstName, HealthID, PrintDate )
SELECT tblFileFolderLabels.LastName, tblFileFolderLabels.FirstName,
tblFileFolderLabels.HealthID, tblFileFolderLabels.PrintDate
FROM tblFileFolderLabels;

This is the delete query
DELETE tblFileFolderLabels.LastName, tblFileFolderLabels.FirstName,
Trim([LastName])+", "+Trim([FirstName]) AS Name, tblFileFolderLabels.HealthID
FROM tblFileFolderLabels;

A macro runs both queries back to back from a command button on the form.
Click to expand...
Click to expand...
 
You want to requery the Active Data Object (i.e. Form), not Controls.

Use one Requery action and leave the argument blank.

Since you delete all Records, your Form will show no (remaining) Records ...
 
Place the following code:

TheNameOfYourForm.Form.RecordSource = "SELECT * FROM TheDataSource;"


This actually requeries the data source of you form and works every time for
me.

Lincoln

Van T. Dinh said:
You want to requery the Active Data Object (i.e. Form), not Controls.

Use one Requery action and leave the argument blank.

Since you delete all Records, your Form will show no (remaining) Records ...

--
HTH
Van T. Dinh
MVP (Access)



iholder said:
This is the delete macro

SetWarning - No
OpenQuery - qryAppend
OpenQuery - qrydelete
Requery - [FirstName]
Requery - [LastName]
Requery - [HealthID]

The requery are the control name of the fields on that form.
And I still have the "#DELETED"
Am I requery the correct control name??
Click to expand...
Click to expand...
 
Back
Top