Move data from 1 table to another

G

Guest

Hi,

I have a Invoice table that is imported into Access from Oracle
that has multiple entries for the same invoices. Everytime an
adjustment is made to the invoice in Oracle a new record is
created. What I am trying to do is to move only the most current
invoice row to another table using fields call "Record Complete
Date" and Invoice#.

This is my code and I'm not getting anywhere with it. Would
someone please offer some guidance please.

Function DIRTRoll()

Dim StoreInvoice As String
Dim StoreDate As Date
Dim intCntr As Integer

DoCmd.ShowAllRecords
DoCmd.GoToControl ("Invoice#")

Set db = CurrentDb()
Set ToTable = db.OpenRecordset("DIRT_Table")
Set FromTable = db.OpenRecordset("DIRT_ImportTable")

StoreInvoice = FromTable![Invoice#]
StoreDate = FromTable![Record Completion Date]

FromTable.MoveFirst

'ToTable.MoveLast
'FromTable.MoveLast

If FromTable.EOF = False Then StoreDate = FromTable![Record
Completion Date]


Do Until FromTable.EOF
If StoreInvoice = FromTable![Invoice#] Then
If StoreDate <= FromTable![Record Completion Date] Then
FromTable.MoveNext
Else
If StoreDate > FromTable![Record Completion Date]
Then
'CopyRecord(,"DIRT_Table",,,,,)

End If
End If
Else

End If

FromTable![Invoice#].SetFocus
FromTable.MoveNext
Loop


ToTable.Close
FromTable.Close

End Function


Thank you,

Melody
 
G

Guest

Mel,

Maybe this piece of code will do the trick for you:

'First open recordset with all invoice numbers (unique values only)
dim rsInvoice as recordset
dim AppendSQL as string

set rsInvoice = currentdb.openrecordset(SELECT DISTINCT INVOICE# FROM
DIRT_importtable)

'Loop through all the invoices in the recordset and retreive the most
current records for that invoice

while not rsInvoice.eof
'Query the invoice table and only retreive the most current record
(based on date and append to the new table
'The Field1, Field2 etc should be the names of your table columns.
'Remember to have the fields from the INSERT INTO in exactly the same order
as the SELECT clause
AppendSQL = "INSERT INTO DIRT_Table (Field1, Field2, Field3, Field4) "
& _
"SELECT TOP 1 (Field1, Field2, Field3, Field4)
FROM DIRT_Importtable where Dirt_importtable.invoice# =
rsInvoice.fields("Invoice#") ORDER BY [DirtRecord Completion Date] Desc"

'Uncomment this line if you want to suppress warnings
'Docmd.setwarnings false

'Execute the SQL statement
docmd.runSQL AppendSQL
rsInvoice.movenext
wend

'If you have suppressed the errors run this code to set warnings back on
'docmd.setwarnings true

HTH
 
G

Guest

Brotha I Thank you,

Melody



Brotha Lee said:
Mel,

Maybe this piece of code will do the trick for you:

'First open recordset with all invoice numbers (unique values only)
dim rsInvoice as recordset
dim AppendSQL as string

set rsInvoice = currentdb.openrecordset(SELECT DISTINCT INVOICE# FROM
DIRT_importtable)

'Loop through all the invoices in the recordset and retreive the most
current records for that invoice

while not rsInvoice.eof
'Query the invoice table and only retreive the most current record
(based on date and append to the new table
'The Field1, Field2 etc should be the names of your table columns.
'Remember to have the fields from the INSERT INTO in exactly the same order
as the SELECT clause
AppendSQL = "INSERT INTO DIRT_Table (Field1, Field2, Field3, Field4) "
& _
"SELECT TOP 1 (Field1, Field2, Field3, Field4)
FROM DIRT_Importtable where Dirt_importtable.invoice# =
rsInvoice.fields("Invoice#") ORDER BY [DirtRecord Completion Date] Desc"

'Uncomment this line if you want to suppress warnings
'Docmd.setwarnings false

'Execute the SQL statement
docmd.runSQL AppendSQL
rsInvoice.movenext
wend

'If you have suppressed the errors run this code to set warnings back on
'docmd.setwarnings true

HTH
MyMel said:
Hi,

I have a Invoice table that is imported into Access from Oracle
that has multiple entries for the same invoices. Everytime an
adjustment is made to the invoice in Oracle a new record is
created. What I am trying to do is to move only the most current
invoice row to another table using fields call "Record Complete
Date" and Invoice#.

This is my code and I'm not getting anywhere with it. Would
someone please offer some guidance please.

Function DIRTRoll()

Dim StoreInvoice As String
Dim StoreDate As Date
Dim intCntr As Integer

DoCmd.ShowAllRecords
DoCmd.GoToControl ("Invoice#")

Set db = CurrentDb()
Set ToTable = db.OpenRecordset("DIRT_Table")
Set FromTable = db.OpenRecordset("DIRT_ImportTable")

StoreInvoice = FromTable![Invoice#]
StoreDate = FromTable![Record Completion Date]

FromTable.MoveFirst

'ToTable.MoveLast
'FromTable.MoveLast

If FromTable.EOF = False Then StoreDate = FromTable![Record
Completion Date]


Do Until FromTable.EOF
If StoreInvoice = FromTable![Invoice#] Then
If StoreDate <= FromTable![Record Completion Date] Then
FromTable.MoveNext
Else
If StoreDate > FromTable![Record Completion Date]
Then
'CopyRecord(,"DIRT_Table",,,,,)

End If
End If
Else

End If

FromTable![Invoice#].SetFocus
FromTable.MoveNext
Loop


ToTable.Close
FromTable.Close

End Function


Thank you,

Melody
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

3034 You tried to commit or rollback a transaction 1
Move data from 1 sheet to another 1
Automatically move data from one worksheet to another 2
Lookup value from another table ado. 3
how to move to another cell to input values from a textbox 1
update one table from another table 1
Using a VBA Variable function from Access table data in Access que 5
Copy data from one SubForm to another 3

Top