A select query based on two fields of a form selects the proper record however when it is converted to a delete query it always deletes the first record in the table.
Sounds simple. I have used this method before with no problems.
Any ideas?
A select query based on two fields of a form selects the proper record however when it is converted to a delete query it always deletes the first record in the table.
Sounds simple. I have used this method before with no problems.
Any ideas?
Post the SQL
I have a form with a list box bound to a query. The form is bound to a table. When the list box is clicked I populate four text boxes. A button is provided for the delete where it opens the delete query But only the first record is deleted not the one selected. When looking at it in spreadsheet view the proper record is selected.
Open the delete query in design view. Click on the view icon top left and select SQL view, copy it and paste it here.
DELETE [Requirement Table].[Job Nbr], [Requirement Table].[Course Nbr]
FROM [Requirement Table]
WHERE ((([Requirement Table].[Job Nbr])=[Forms]![FrmRequirementDelete]![JobNumber]) AND (([Requirement Table].[Course Nbr])=[Forms]![FrmRequirementDelete]![CourseNumber]));
That looks good.
How are you populating them? The surest way is to filter the form on the primary key of the listbox selection.When the list box is clicked I populate four text boxes
Debug it by: just prior to running the query, display a msgbox with those two values so that you can see what is about to be deleted. If they are the incorrect values then you know that your form is not showing the correct data/record.
The msgbox shows the selected record but it is still deleting the first record. How can I tell which record my form is showing?
The msgbox showed the correct values in job number and contract and yet the query was still deleting some other record?
Test it out by changing it to a SELECT Query first:How can I tell which record my form is showing?
By the way, in a DELETE query, you do not need to list individual fields after the DELETE word - as it deletes the WHOLE record, not just specific fields in your record.Code:SELECT [Requirement Table].[Job Nbr], [Requirement Table].[Course Nbr] FROM [Requirement Table] WHERE ((([Requirement Table].[Job Nbr])=[Forms]![FrmRequirementDelete]![JobNumber]) AND (([Requirement Table].[Course Nbr])=[Forms]![FrmRequirementDelete]![CourseNumber]));
The primary key in this table is Job Nbr and Course Nbr. Does this have any effect?
When changed to a select query it selects the proper record.
It should delete the exact same records as the select query returns.
It shouldn't matter, but what if you tried like this:
Also, your Form is open when you are running this, correct?Code:DELETE * FROM [Requirement Table] WHERE ((([Requirement Table].[Job Nbr])=[Forms]![FrmRequirementDelete]![JobNumber]) AND (([Requirement Table].[Course Nbr])=[Forms]![FrmRequirementDelete]![CourseNumber]));