Archiving record from form to a table.

[puzzledinsc]

Reference Shannon88, This is a similar problem. I have an employee database and the user wants to be able to archive a record from time to time after a promotion, etc. So I'm trying to take the record in the form and write it to another table (archive_emp) at the push of a button. The tables are on a SQL server connected by an ODBC driver in Access. I'm using the following code and what happens is that it prompts me for the local variables (insted of lifting them off the Me! form), then it gives me an error that it can't open the output table - archive_emp. I seem to be missing a fundamental grasp on writing from one table to another in Access.
Code:

Private Sub Archive_Record_Click()
On Error GoTo Err_Archive_Record_Click

Dim l_emp_id, l_last_name, l_first_name As String
Dim db As Database
l_emp_id = Me.EMP_ID
l_last_name = Me.LAST_NAME
l_first_name = Me.FIRST_NAME

Set db = CurrentDb
DoCmd.RunSQL "insert into archive_emp (emp_id, last_name, first_name) values (l_emp_id, l_last_name, l_first_name)"


Exit_Archive_Record_Click:
Exit Sub

Err_Archive_Record_Click:
MsgBox Err.Description
Resume Exit_Archive_Record_Click

End Sub

PS: I tried to change Dim db as DataBase to AcDataObjectType but it failed at Set db = Current and said "Object required"

Thanks

 

[scking]

You have enclosed the variable names in quotes so it uses the names rather than the value of the variable.

DoCmd.RunSQL "insert into archive_emp (emp_id, last_name, first_name) values (" & l_emp_id & ", '" & l_last_name & "', '" & l_first_name & "')"

In this example I have assumed the names are text and the emp_id is a numeric value.

-------------------------------------
scking@arinc.com
Try to resolve problems independently
Then seek help among peers or experts
But TEST recommended solutions
-------------------------------------

 

[jbehrne]

Hi puzzledinsc,

Here are a few of my observations about your code:

1. You don't need to declare db as a database
2. You don't need to assign the values from your form to temporary values in your code
3. When you are declaring your temporary values in your code it won't find the fields because you are declaring the location wrong. Instead of:
4. I am also using SQL Server and connecting via an ODBC - so make sure that your data types in your archive table are the same data types that you are passing!


l_emp_id = Me.EMP_ID
l_last_name = Me.LAST_NAME
l_first_name = Me.FIRST_NAME


Try: (notice the change from the '.' to the '!')

l_emp_id = Me!EMP_ID
l_last_name = Me!LAST_NAME
l_first_name = Me!FIRST_NAME

So, first things first. Lets try and send one value over to make sure just one value can get through... If EMP_ID is an autonumber we won't pass that value yet.

Try this as your code (without declaring the variables or the db):

DoCmd.RunSQL "INSERT INTO archive_emp (last_name) VALUES (LAST_NAME);"

If you pass this value then go ahead and try this:

DoCmd.RunSQL "INSERT INTO archive_emp (last_name, first_name) VALUES (LAST_NAME, FIRST_NAME);"

If you couldn't pass the values then you need to remove the spaces or other signs (I.E. '_') from your table and field names. I have run across problems with code not working due to spaces and other characters in field and table names.


HTH,

jbehrne