Delete query not working..why?? 1

[ttellis]

Let me start by saying that I am new to Access and prior to this current project, have only developed database for school. My knowledge of coding is basic. Here's the problem:

I've been handed to task of creating a database that holds security badge info. BadgeData is the active badge data, but once an employee is terminated, I need to have the data moved to Termed table and deleted from BadgeData. The delete query selects the CardNum from the form IndivData and previews the correct record, but does not delete it. The message I received is: "Could not delete from specified tables. (Error 3086)". HELP says the error means the database was opened in Read-only or that I don't have permission to modify the table. Neither of these are true.

Here is the SQL for the query:
[/code]DELETE BadgeData.*, TempTermed.CardNum
FROM TempTermed INNER JOIN BadgeData ON TempTermed.CardNum = BadgeData.CardNum;
[/code]

Any help that can be provided would be great!I'm all ears...

 

[randy700]

Actually, you need 2 queries to accomplish what you're trying to do.

First create an append query that copies the required information from BadgeData to TermedTable. Something like...
"INSERT INTO TermedTable(CardNum, other fields) SELECT CardNum, other fields FROM BadgeData WHERE CardNum = " & Forms!IndivData!txtCardNum

Once that has been accomplished, you can run your delete query. Something like...
"DELETE * FROM BadgeData WHERE CardNum = " & Forms!IndivData!txtCardNum

Another possible option would be to modify the BadgeData table by adding a Yes/No field, maybe called "ActiveEmp". This would eliminate the need for the second table and you could simply run an update query like...
"UPDATE BadgeData SET ActiveEmp = False WHERE CardNum = " & Forms!IndivData!txtCardNum


Randy

 

[ttellis]

Thanks randy700!! Your suggestions were a great help!