Delete Query Not Working

[Lamb2087]

I have a command button that runs an append and delete query. The append query works, but the delete query does not work.

I currently have this working in another form and duplicated the query's but changed the form name to it would work on this form.

At a loss as to why the delete query tells me you are about to delete 0 record(s).

 

[jfgambit]

Is there some kind of Criteria in the query? Can you post the SQl of the Delete Query?

 

[Lamb2087]

Criteria info and sql

SQL:

DELETE NMPHY.ACQ_DATE, NMPHY.ACQ_YYYY, NMPHY.BUILDING_NUM, NMPHY.BUILDING, NMPHY.COST, NMPHY.NMMI_NUM, NMPHY.DEPARTMENT, NMPHY.DEPARTMENT_NUM, NMPHY.FDN_PURCHASE, NMPHY.ITEM_DESC, NMPHY.NON_TECH_DES, NMPHY.PO_NUM, NMPHY.SERIAL_NUM, NMPHY.DELETEREASON
FROM NMPHY
WHERE (((NMPHY.NMMI_NUM)=[Forms]![NMPHYAdd1]![NMMI_NUM]));


Criteria:

[Forms]![NMPHYAdd1]![NMMI_NUM]

NMPHYAdd1 is the form name
NMMI_NUM is the field name

 

[jfgambit]

Are you deleting all the information from the table?

DELETE NMPHY.*, NMPHY.NMMI_NUM
FROM NMPHY
WHERE (((NMPHY.NMMI_NUM)=[Forms]![NMPHYAdd1]![NMMI_NUM]));

 

[Lamb2087]

Just deleting 1 record

This query only deletes 1 record.

 

[jfgambit]

Lamb:

Maybe I am confused here, but what you say you have is a command button that runs a query that deletes records from a table based on the information in a textbox. If you have "DOG" in the textbox, all the records that contain the word "DOG" in the NMMI_NUM field will be deleted.

So, since 1 record was deleted there must have been 1 record that had the criteria you entered in the textbox ([Forms]![NMPHYAdd1]![NMMI_NUM]). If you want to delete all the records regardless of what is in the textbox then you should not have the criteria of the query point to the textbox:

DELETE NMPHY.*
FROM NMPHY <--this will delete all records from the table.

 

[Lamb2087]

Yes you are confused

I am only deleting the record that is currently on the form.

My problem now is that when I hit the button it appends the record and jumps directly inot the table that it is appended to.


I need to fix this mess.

 

[Lamb2087]

Got it solved

I got it solved and works like a charm. I have the delete query pointing to the wrong query.