Hi,

I'm new to VBA and am building an ACCESS form that when a Button is clicked:
1. Save the current changes made to record in Table A.
2. Copies the current record from Table A to Table B (appending it)
3. Deletes the current record in Table A.

The purpose of this is to allow a user to click a button to archive the record in Table A to the archive table, Table B.

Hope this makes sense?

Thanks in advance for your help!

Why would you save a record to table A, and then delete it? Why not just bind the form to table B and forget table A?


mmcdonal

I'm sorry all, I probably should have told you "why" I want to do this, and it will either make more sense, or someone will come up with a better solution.

Table A is the current "active" database, where as Table B is the "archive" database.

The form that is going to do this record move, first allows the user to add closing information to the record in Table A.

Then when they click on the "save" button, I want the record to then transfer over to the archive table (Table B).

I do not want the record in both places, but do want it in the active file (Table A) and then want it removed from there and moved to the archive file (Table B) once the user clicks on the button.

Hope that makes more business sense?

Thanks!

Is table B linked to the database with table A, or is it totally disconnected?

mmcdonal

They are totally disconnected. Separate tables, but the fields are set up exactly the same.

I would use an append query to copy the data to the other table, then a delete query to remove the data from the current table.

This is how I've done it when I needed to.

-- Steve Olson

Wasn't this posted somewhere else and we did append and delete queries? Otherwise, Append and delete queries are problematic since you need to take the PK from table A, then move off the record, then add the record to table B, then remove the record from table A while not on the record, so a parameter query generally won't work.

Did you get this done or do you need help with the ADO?



mmcdonal

Hi mmcdonal,

Yes, I would love some help! I can do this in many other languages, on many different platforms, but I haven't learned enough about ACCESS or VBA.

My goal is to take a record from the table where teh active tables are (eg. Table A) and move it to the archive table (eg. Table B). That would mean, removing it from Table A and adding it to Table B.

It seems simple enough, but I'm clueless how to do it in ACCESS! I'm sure it's very simple, but I just haven't "got it" yet.


Thanks in advance!

I use ADO since I do mostly Access?SQL development. Many of the posters here use DAO since it works slightly better in Access Jet.

To move the record from one table to another, I would assume you have some way of retrieving the PK of the record in TableA you want to move to TableB. How are you doing that? If it is on the form (hidden) you can do this:

I am also assuming for right now that you are in the same database, but you would want to use an ADODB.Connection object if TableB is in another database.

Dim rs1, rs2 As ADODB.Recordset
Dim sSQL1, sSQL2 As String
Dim iPK As Integer

iPK = Me.PKField

sSQL1 = "SELECT * FROM TableA WHERE [PKField] = " & iPK
sSQL2 = "SELECT * FROM TableB"

Set rs1 = New ADODB.Recordset
rs1.Open sSQL1, CurrentPorject.Connection, adOpenDynamic, adLockOptimistic

Set rs2 = New ADODB.Recordset
rs2.Open sSQL2, CurrentPorject.Connection, adOpenDynamic, adLockOptimistic

rs2.AddNew
rs2("FirstField") = rs1("FirstField")
...
rs2.Update

rs1.Close
rs2.Close

sSQL1 = "DELETE FROM TableA WHERE [PKFIeld] = " & iPK
Set rs1 = New ADODB.Recordset
rs1.Open sSQL1, CurrentPorject.Connection, adOpenDynamic, adLockOptimistic

'Don't close the recordset here

Did that work?


mmcdonal

Hi,

That looks very do-able, and I will try it tonight. Thank you very much! I've used SQL elsewhere (eg. PHP, mainframe, etc.), but hadn't delved into how to embed it into ACCESS! I'll let you know tomorrow if it works, but from looking at it, it looks like it's doing exactly what I need to do!

Thanks again!