Copying record from one table to another table

[akrylik]

I have been trying to solve this issue for several days now with no luck. Here is the situation. I have a database with multiple tables and forms. Of those tables and forms, i have an EmployeeData table, which holds all the information for each employee, an EmployeeDeleteQueue table, which holds records of those employees who have been recently deleted from the EmployeeData table, and an Employee Data Form, used to enter data into the EmployeeData table. On the form i have added a Delete button, which currently only deletes the current record from the EmployeeData table. What i WANT it to do, is not only delete it, but before it deletes the record i want to move the record from EmployeeData to EmployeeDeleteQueue for review before its completely deleted.

I tried using the Macro Builder, since i was instructed to not use VBA if possible, and i can not find any macro that will do what i want. Any ideas?

[pbaldy]

I personally wouldn't use two tables; I'd have a status field in one table. If you really want to move it, you'd execute an append query and then a delete query, each using the form for its criteria.

[akrylik]

The main reason for going this way is because we will have multiple managers accessing the forms and possibly deleting the record from the table. We dont want the deleted employee record viewable, but moved for admins of the db to review before perm. deleting each record.

Here is a sample from the append query that i created, but i get the following error:

INSERT INTO EmployeeDeleteQueue ( HRID, [First Name], [Last Name], [Direct Report], [Job Code Description], Country, [Employee Class], [Sub Org], [Cost Center], [Off-Shore], [Email Address], [Standard Rate], [Physical Location], [Vacation Days], [Relevance Training], [Netcool Training], [Years of Experience] )
SELECT EmployeeData.HRID, EmployeeData.[First Name], EmployeeData.[Last Name], EmployeeData.[Direct Report], EmployeeData.[Job Code Description], EmployeeData.Country, EmployeeData.[Employee Class], EmployeeData.[Sub Org], EmployeeData.[Cost Center], EmployeeData.[Off-Shore], EmployeeData.[Email Address], EmployeeData.[Standard Rate], EmployeeData.[Physical Location], EmployeeData.[Vacation Days], EmployeeData.[Relevance Training], EmployeeData.[Netcool Training], EmployeeData.[Years of Experience]
FROM EmployeeData
WHERE EmployeeData.[Last Name] = Me.Last_Name;

I get a "Syntax error" but i cant seem to find it.

Im new to using access, how do i run the append query without typing in the actual query in the "on click" event from the form?

[pbaldy]

The main reason for going this way is because we will have multiple managers accessing the forms and possibly deleting the record from the table. We dont want the deleted employee record viewable, but moved for admins of the db to review before perm. deleting each record.

I'd still keep it in one table. You can handle the view-ability of the record with the status field. I would never ever delete an employee record. That said, if you want to, you can't use "Me" outside VBA code. In a query you'd have to use the full form reference:

Forms Refer to Form and Subform properties and controls

You can use OpenQuery to run a saved query, either in a macro or VBA.

[akrylik]

Thanks for the suggestion, i will give it a try shortly. working on another aspect of the database right now. Ill let you know how it goes.

[akrylik]

Ok so i put the append query into VBA and its giving me a "Compile Error: Expected: end of statement" on the INSERT INTO line. I copied it straight from the query design view into VBA. Can you see why its throwing the error?

[pbaldy]

Can you see why its throwing the error?

Not without seeing it.

[akrylik]

lol. Ok well here is my VBA code for the delete button:

Private Sub Command35_Click()
Dim Response
Dim SQL As String






Response = MsgBox("Are you sure you would like to delete this record?", vbYesNo + vbCritical, "Delete Record?")
If Response = vbYes Then
SQL = "INSERT INTO EmployeeDeleteQueue ( HRID, [First Name], [Last Name], [Direct Report], [Job Code Description], [Country], [Employee Class], [Sub Org], [Cost Center], [Off-Shore], [Email Address], [Standard Rate], [Physical Location], [Vacation Days], [Relevance Training], [Netcool Training], [Years of Experience]) SELECT EmployeeData.HRID, EmployeeData.[First Name], EmployeeData.[Last Name], EmployeeData.[Direct Report], EmployeeData.[Job Code Description], EmployeeData.Country, EmployeeData.[Employee Class], EmployeeData.[Sub Org], EmployeeData.[Cost Center], EmployeeData.[Off-Shore], EmployeeData.[Email Address], EmployeeData.[Standard Rate], EmployeeData.[Physical Location], EmployeeData.[Vacation Days], EmployeeData.[Relevance Training], EmployeeData.[Netcool Training], EmployeeData.[Years of Experience] FROM EmployeeData WHERE EmployeeData.[Last Name] = Me.Last_Name"


DoCmd.RunSQL SQL
ElseIf Response = vbNo Then
MsgBox ("Action Canceled")
DoCmd.CancelEvent
End If
End Sub

It is working now, however its not grabbing the value from the form. It is asking me to enter the last name for the employee that I want to move instead of getting the value from the form.

[pbaldy]

In VBA you'd have to concatenate the orm reference

"...WHERE EmployeeData.[Last Name] = '" & Me.LastName & "'"

[akrylik]

That worked! Thanks. I have one more question however. When i use the following code, i get the error " Data type mismatch in criteria expression" :

"FROM ResourceAllocation WHERE ResourceAllocation.[Last Name] = '" & Me.Last_Name & "' and ResourceAllocation.[First Name] = '" & Me.First_Name & "' and ResourceAllocation.Project = '" & Me.Project & "' and ResourceAllocation.[Resource End Date] = '" & Me.Resource_End_Date & "'"

the error is coming from after i added and ResourceAllocation.[Resource End Date] = '" & Me.Resource_End_Date & "' to the code. The data type for both are dates, but it doesnt seem to like it. I tried using the ID also, but that didnt work and got the same error. Since these types are not strings, how do i code that in the WHERE part of the query?

[pbaldy]

This should help

http://www.baldyweb.com/ImmediateWindow.htm

Offhand, date/time values need to be surrounded by #, not '.

[akrylik]

Its working beautifully now. Thanks for all your help.

[pbaldy]

Happy to help.