A deletion query

[Pwyd]

I have a macro that opens up a form, hidden, datasheet, edit mode, runs a
delete query which includes all of the fields in two seperate tables, then
closes the form it was deleting from.

When running the macro, all i get as a response is:
Could not delete from specified tables.
then
Action failed
DeleteMacro

Condition: True

Action Name OpenQuery

Arguments clearallrecords, datasheet, edit

what am i doing wrong?


in a second query problem, i cannot get a query to properly insert data into
a table.
the query uses data imported from an excel spreadsheet. which works fine.
then the query runs, which properly formats the data and pushes it into the
fields on the form. The problem is, when the query runs, it rejects several
thousand records, supposedly because they violate key or validation rules. I
can tell you with certainty, that the data it is retrieving (which only
includes SOME of the fields on the main form) is using exactly the same
validation, key, and other rules. In fact, the fields in the table and the
fields in the query are completley identical. in all settings. I'm lost on
this one. please help

 

[John Spencer]

A delete query can only delete from ONE table.

Also, I see no reason to open a form in order to run a delete query.

Post the SQL (view: SQL from Menu) of your delete query and someone should
be able to suggest how to modify it to delete the requisite records. Unless
you have a relationship set up that uses cascade delete option, you will
need two queries to delete the records.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Pwyd]

Yeah i disocvered that just now by trial and error. all right, i'll write
two queries. What about my second problem? Why is it telling me there are
possible violations of data integrity? I've been told i can turn those
warnings off. I would MUCH rather make the query NOT violate those rules,
than force it through the meat grinder, so to speak.

 

[John W. Vinson]

in a second query problem, i cannot get a query to properly insert data into
a table.
the query uses data imported from an excel spreadsheet. which works fine.
then the query runs, which properly formats the data and pushes it into the
fields on the form. The problem is, when the query runs, it rejects several
thousand records, supposedly because they violate key or validation rules. I
can tell you with certainty, that the data it is retrieving (which only
includes SOME of the fields on the main form) is using exactly the same
validation, key, and other rules. In fact, the fields in the table and the
fields in the query are completley identical. in all settings. I'm lost on
this one. please help

Correct the error in your query or in your validation rules. Since we can't
see either one, it's more than a bit difficult to help you do so!

Please post the SQL view of the query and indicate which table fields have
primary keys, or are being used as foreign keys, or are required, or have
validation rules - any of which will trigger this kind of error.

I'll agree with John Spencer that popping up another form to do this is
neither necessary nor a good idea. An Append query based on the linked
spreadsheet is the appropriate tool.

John W. Vinson [MVP]

 

[Pwyd]

INSERT INTO AuditerTable ( [Emp Id], [Report Paid Date], [Report Name],
[Total Claimed Amount], [Total Posted Amount], [SAP Company Code], [Employee
Name] )
SELECT TableAppend.[Emp Id], TableAppend.[Report Paid Date],
TableAppend.[Report Name], TableAppend.[Total Claimed Amount],
TableAppend.[Total Posted Amount], TableAppend.[SAP Company Code],
TableAppend.[Employee Name]
FROM TableAppend;
There are no imput masks.
the field lengths are the same.
The table i'm appending FROM has no primary key.
the table i'm appending TO has an autonumber id primary key, and no other.

every single setting, down the entire list in design mode, is identical for
the fields being transferred from, and on the table being transferred to.
absolutely identical. Access doesn't give me any specifics on the error.
All it claims is that such and such records were rejected for possible field
violations, key violations, etc, etc, a general error it gives in response to
several other problems. Looking at the help files has only a response to
record locks, which doesn't help me.

what else do you need a copy of in order to help?

here's the table its taking the data from

 

[Pwyd]

Okay maybe i can't paste the table. unless someone tells me how to do it

INSERT INTO AuditerTable ( [Emp Id], [Report Paid Date], [Report Name],
[Total Claimed Amount], [Total Posted Amount], [SAP Company Code], [Employee
Name] )
SELECT TableAppend.[Emp Id], TableAppend.[Report Paid Date],
TableAppend.[Report Name], TableAppend.[Total Claimed Amount],
TableAppend.[Total Posted Amount], TableAppend.[SAP Company Code],
TableAppend.[Employee Name]
FROM TableAppend;
There are no imput masks.
the field lengths are the same.
The table i'm appending FROM has no primary key.
the table i'm appending TO has an autonumber id primary key, and no other.

every single setting, down the entire list in design mode, is identical for
the fields being transferred from, and on the table being transferred to.
absolutely identical. Access doesn't give me any specifics on the error.
All it claims is that such and such records were rejected for possible field
violations, key violations, etc, etc, a general error it gives in response to
several other problems. Looking at the help files has only a response to
record locks, which doesn't help me.

what else do you need a copy of in order to help?

here's the table its taking the data from

Correct the error in your query or in your validation rules. Since we can't
see either one, it's more than a bit difficult to help you do so!

Please post the SQL view of the query and indicate which table fields have
primary keys, or are being used as foreign keys, or are required, or have
validation rules - any of which will trigger this kind of error.

I'll agree with John Spencer that popping up another form to do this is
neither necessary nor a good idea. An Append query based on the linked
spreadsheet is the appropriate tool.

John W. Vinson [MVP]