record deletion

[Me.Howard]

I have a table whose key is two fields. I am having no luck in selecting a given record from a form and deleting it. No problem in a table with a single field key.
Any ideas.

[orange]

Show us some detail.

Show the record to delete. Its compound key, and the query you have tried.

[Me.Howard]

The table has two fields JobNbr which has multiple CourseNbr associated with it. The combination of the two is the compound key. (No Duplicates)
A form with two text boxes, where JobNbr and CourseNbr are entered for selection of the record to be deleted.
The delete query is as follows:
JobNbr CourseNbr
Requirement Table Requirement Table
Where Where
[Forms]![FrmRequirementDelete]![JobNbr] [Forms]![FrmRequirementDelete]![CourseNbr]

When the query executes it deletes the first record that matches the JobNbr ignoring the matching CourseNbr
I hope this explains the situation.

[orange]

Please show
-the table design with the compound PK
-the sql of the delete query.

[Me.Howard]

Please show
-the table design with the compound PK
-the sql of the delete query.

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]));

Job Nbr = Number
Course Nbr = Number

Job
Field Size = Long Integer
Validation Rule = Between 1000 and 10000
Required = Yes
Indexed = Yes (No Duplicates)

Course
Field Size = Long Integer
Validation Rule = Between 100 and 1000
Required = Yes
Indexed = No

Could the index on the course be an issue?

[orange]

A DELETE query DELETES records not fields.

Format is DELETE FROM table_name
WHERE some_column=some_value;

http://www.w3schools.com/sql/sql_delete.asp


Can you show a jpg of your table design?

[Me.Howard]

There are only 2 fields in the requirements record.
How do I get a jpeg of the query design?

[orange]

Go to table design view and do a printscrn. That should save a copy of the screen to a copy/paste buffer.
Then go to a graphics image program (like Paint/irfanView...) and paste a new image. Then save the file, and attach to a post.

[Me.Howard]

I have the jpeg but I see no wY TO ATTACH IT TO THE POST. iT WONT CLICK AND DRAG OR COPY AND PASTE.

[ssanfu]

Click on the "Go Advanced" button at the bottom of your post. Scroll down until you see the manage attachments button. Click the button. Follow prompts..

Or you can click on the "Insert Image" icon in the message tool bar. Follow the prompts...

[Me.Howard]

Thanks I appreciate the help.

[orange]

Your design shows an inconsistency to me.
I see a Primary Key indicator on each of Job Nbr and Course Nbr. But I also see that Job Nbr shows Indexed duplicates OK (that is not consistent with a PK) and Course Nbr is not indexed.

If you go to table design, what does it show when you click on Indexes?

[Me.Howard]

Index shown

[orange]

Can you post a copy of your database (zip format)?

[Me.Howard]

I cant share the database as it contains personal information. It is a hospital application and subject to HIPA rules.