moving results from a report (1 Viewer)

[mrdb89]

Hi,
I want the result in my report to go to another database, after I have printed. :---

I got database1 where I store most of the info dept, Date, item, price, description. I have about 10 Depts.
I then have a report that prints the item prices from a Dept, with a sub total and with the VAT added to the Total.
I then have a database2 that I only want to store the results ( ie Total with the VAT, Dept, Date ), but with out having to type the results back in to the computer.

Thanks for your HELP!! in advance.

 

[simongallop]

Dim WrkJet As Workspace
Dim MyDB As Database
Dim MyRS As Recordset

Sub Move_Data()
MyTot = Me.txtboxNameWithTotal
' Carry on assigning the data to variables


' Open Microsoft Jet Database object.
Set WrkJet = CreateWorkspace("NewJetWorkspace", _
"admin", "", dbUseJet)
Set MyDB = WrkJet.OpenDatabase("C:\pathofdb\dbname.mdb", True, False, ";PWD=whatever")
Set MyRS = MyDB.OpenRecordset("TableNameToWriteTo", dbOpenDynaset)

MyRS.AddNew
MyRS("TotalsFiled") = MyTot
MyRS("NextFieldName") = NextVariable
etc....
MyRS.Update

MyRS.Close
MyDB.Close
WrkJet.Close
End Sub

This code ought to sit within the report so perhaps within the OnOpen event of the report.


HTH

 

[mrdb89]

Harry,
I have looked at the reply, but I dont understand much of it.
I know where to put it. I dont know which bits to change to my own database?
Dim WrkJet As Workspace ( No idea ??)
Dim MyDB As Database ( MyDB , I guess means my own db)
Dim MyRS As Recordset ( No idea ??)

MyTot = Me.txtboxNameWithTotal ( My total = What is all that? )


Set WrkJet = CreateWorkspace("NewJetWorkspace", _
"admin", "", dbUseJet) ( Explain Pls ??)
Set MyDB = WrkJet.OpenDatabase("C:\pathofdb\dbname.mdb", True, False, ";PWD=whatever") ( Explain pls??)
Set MyRS = MyDB.OpenRecordset("TableNameToWriteTo", dbOpenDynaset) ( Can you please explain all of this ??)

Please explain the following too:-
MyRS.AddNew
MyRS("TotalsFiled") = MyTot
MyRS("NextFieldName") = NextVariable
etc....
MyRS.Update

Thanks!

 

[Rich]

What's the advantage of storing the results in another db?

 

[mrdb89]

Advantage

Why in 2 databases:-
The first is for the invoices and the second is for the statements, so I only want to store the Totals, date, invoice number, for the statement which is printed on a different day. Which is better like that than to recalculate to print off the statements.