coding to take current record on form and copy record to another .mdb 3

[jimbo62]

Access 2000-I need help coding to select the current record on my form and copying it to another access 2000 database. I have looked at paste append( only takes it to the current table) and an append query(Which I can send Queried records to another .MDB But not necessarily the current record ). I would like to make it part of a save record Sub Procedure. Maybe doing a query(SQL?) or just plain code to get the record into memory but how do I get it into the other DB Table.

fI have taken a course in VBA but they did not cover record operations very much. any suggestions would be appreciated.

Thanks Jim

 

[Rolliee]

Open the 'foreign' database with either dao or ado and if the structure is the same write a do loop to add the new record to the foreign database. This could be triggered by a command button on the form.

rollie@bwsys.net

 

[wemeier]

First, create a query that selects all records from the foreign database table (we'll call it ForeignTable):

SELECT * FROM ForeignTable IN "D:\Databases\ForeignDatabase.mdb";

Save the query (we'll call it qryForeignTable).

Next, in VBA code in the form where you want to export the current record, do the following:

Dim db as Database
Dim rst as Recordset

' Open the foreign table
Set db = CurrentDB()
Set rst = db.OpenRecordset("qryForeignTable&quot

' Add a new record to the foreign table, move the
' fields from the current record on the form to
' the foreign table and save the row
rst.AddNew
rst!Field1 = Me.Field1
rst!Field2 = Me.Field2
rst!Field3 = Me.Field3
.
.
.
rst!Fieldn = Me.Fieldn
rst.Update

' Close the foreign database
rst.Close
Set db = Nothing

This coding is slightly inefficient in that it retrieves all rows from the foreign table in order to have a recordset to append to, but it will work.

 

[jimbo62]

This is what I have so fat I believe I am missing something though. I get a compile error(user defined type not found error) on the dim db as database statement can you clairify this for me?

Thanks for the resonse I believe I am close...

Jim

'First, create a query that selects all records from the foreign database table (we'll call it Expired):

'SELECT * FROM Expired IN "C:\lddata\ExpiredCauseDB.mdb";

'Save the query (we'll call it ExpiredQry).

'Next, in VBA code in the form where you want to export the current record, do the following:

Dim db As Database
Dim rst As Recordset

' Open the foreign table
Set db = CurrentDb("C:\lddata\ExpiredCauseDB.mdb&quot
Set rst = db.OpenRecordset(qryEpireddb)

' Add a new record to the foreign table, move the
' fields from the current record on the form to
' the foreign table and save the row
rst.AddNew
rst![Item] = Me.[Item]
rst![Decription] = Me.[Decription]
rst![WHSE] = Me.[WHSE]
rst![Month] = Me.[Month]
rst![Cause] = Me.[Cause]
rst![Expired] = Me.[Expired]
rst! [Serial Lot] = Me.[Serial Lot]
rst![QTY] = Me.[QTY]
rst![Amount] = Me.[Amount]
rst![Storage Code] = Me.[Storage Code]
rst![NOD] = Me.[NOD]
rst![IPF Code] = Me.[IPF Code]
rst![IPF Desc] = Me.[IPF Desc]
rst![Storage Desc] = Me.[Storage Desc]
rst![Primary] = Me.[Primary]
rst![Primary Desc] = Me.[Primary Desc]
rst![Second] = Me.[Second]
rst![Second Desc] = Me.[Second Desc]
rst![Third] = Me.[Third]
rst![Third Desc] = Me.[Third Desc]
rst![Buyer Code] = Me.[Buyer Code]
rst![Vendor Number] = Me.[Vendor Number]
rst![Full Product Type] = Me.[Full Product Type]

rst.Update

' Close the foreign database
rst.Close
Set db = Nothing

Exit_paste_append_Click:
Exit Sub

Err_paste_append_Click:
MsgBox Err.Description
Resume Exit_paste_append_Click

End Sub

 

[jimbo62]

Ok I referened the dao 3.6 library and have eliminated the compile error. Now I have a type mismatch, all of the fields and tables are an exact copy of the structure of my main database. Two fields are number and I need to keep them that way? I am getting closer. Please any more ideas?

Thanks as always....
Jim

 

[jimbo62]

I got it... I needed to define both dim statements as dao objects and add the library reference...

Thanks so much for all of your help..

success is sooo sweeet

Jim