Need to insert data to table from report

[aardvarck]

I have been given an Access 2003 database which produces a report and then attaches that page to an email, based on complex criteria.

I have found the area in the code where it creates the rtf file and opens the mail client and attaches the file.

I have been asked to create a table and populate it with some details from all of the report pages which are attached as individual files to emails.

I can, in the immediate window, see the data which I need to write to the table, by using
? [Reports]![Invoices]![txtOrg] and ? [Reports]![Invoices]![CostCentre] for example.

I can add records to the table by using the following
set dbs = CurrentDb
dbs.execute "Insert into tblMonthlyJournals ([Organisation]) values ('testOrg')"

This adds a new record and enters testOrg into the Organisation field.

I need to replace the literal 'testOrg' with the contents of [Reports]![Invoices]![txtOrg], but have not been able to find a way of doing it.

Any help is gratefully appreciated.

[ruirib]

You can always add code to one of the report's events and use that code to add the records you need. The On Load event should be adequate for that purpose.

[aardvarck]

Thanks for the suggestion, I'll try from within the report. The way the process works is the user steps through a query one record at a time, stopping at each record and clicking the report button. If the content meets the criteria, the page of the report is displayed and also opens up Lotus Notes and attaches that page otherwise it shows a message stating this record does not meet the criteria. The user then goes back to the query and continues to step through one record at a time. Thanks!

[ruirib]

Do you have code in the report to determine whether the page should be displayed or not?

[aardvarck]

Yes, there is code in the report and I have stepped through to the point where it generates the rtf attachment for Lotus Notes. It is at this point that I can see the subset of data which I need to write to the table by using the Immediate Window. From here in the code I propose to insert the subset of data to a table, if I can get the syntax correct. This is what I type in the Immediate Window ? [Reports]![Invoices]![txtOrg] and it returns a four character string like 7002 or 7301 I can add records to the table by using the following set dbs = CurrentDb dbs.execute "Insert into tblMonthlyJournals ([Organisation]) values ('7002')" This adds a new record and enters 7002 into the Organisation field. I need to replace the literal '7002' with the contents of [Reports]![Invoices]![txtOrg], but have not been able to find a way of doing it. I have tried a variety of combinations eg. dbs.execute "Insert into tblMonthlyJournals ([Organisation]) values ([Reports]![Invoices]![txtOrg])" dbs.execute "Insert into tblMonthlyJournals ([Organisation]) values ('[Reports]![Invoices]![txtOrg]')" but all I get are errors. What is the correct syntax for this insert? Is there another way of achieving this from within the report on the required page? (Sadly, the original author is no longer with the company and this app is now an important component of the business processes of the business unit) Thanks in advance.

[ruirib]

From report event code you can simply use:

Me.txtOrg

This, of course, presumes txtOrg is the name of the report control that has the value you want.

[aardvarck]

As simple as that?

dbs.execute "Insert into tblMonthlyJournals ([Organisation]) values (Me.txtOrg)"

Will use your suggestion at work tomorrow.

Thanks!

[ruirib]

That's the way to access a report's control value. The VB code, however, needs to be different:

dbs.execute "Insert into tblMonthlyJournals ([Organisation]) values (" & Me.txtOrg & ")"

You don't want the value written to be "Me.txtOrg" .

[aardvarck]

Thanks for your advice.

I have used your suggestion and been able to insert most of the values into the table, but am being presented with errors on others.


I can do this in the immediate window and get the correct result

? [Reports]![Invoices]![MatterName]
Real Estate and Facility Management SPC

however when I try this I get the below error
dbs.execute "Insert into tblMonthlyJournals ([LineDescription]) VALUES("&[Reports]![Invoices]![MatterName]&")"
---------------------------
Microsoft Visual Basic for Applications
---------------------------
Run-time error '3075':
Syntax error (missing operator) in query expression 'Real Estate and Facility Management SPC'.

The LineDescription field in tblMonthlyJournals is Text and has length of 255


Again, I can do this in the immediate window and get the correct result

? [Reports]![Invoices]![Project]
PSR00139

however when I try this I get the below error
dbs.execute "Insert into tblMonthlyJournals ([Project]) VALUES("&[Reports]![Invoices]![Project]&")"
---------------------------
Microsoft Visual Basic for Applications
---------------------------
Run-time error '3061':
Too few parameters. Expected 1.

The Project field in tblMonthlyJournals is Text and has length of 8


I can do this in the immediate window and get the correct result

? [Reports]![Invoices]![txtOrg]
7002
dbs.execute "Insert into tblMonthlyJournals ([Organisation]) VALUES("&[Reports]![Invoices]![txtOrg]&")"
I can see the field contains 7002
however when I do this
dbs.execute "Insert into tblMonthlyJournals ([Organisation]) VALUES("&Me.txtOrg&")"
I get this error
---------------------------
Microsoft Visual Basic for Applications
---------------------------
Compile error:
Expected: end of statement

The report has code only against the On No Data event.

Obviously I do not have the requisite understanding of VB and Access coding, but if you could advise me of what I can do to stop these errors (should teh remediation be simple enough), I would appreciate it.

Regards

[ruirib]

It seems the value you are trying to insert is a text string, so you need to delimit it with quotes. You can use single quotes for that:

dbs.execute "Insert into tblMonthlyJournals ([Organisation]) values ('" & [Reports]![Invoices]![txtOrg] & "')"

Take notice of the single quote in red.

[aardvarck]

I have implemented all of your suggestions and it all works now.
I have given the database back to the business unit for useability testing and verifying results of the coding changes, with initial feedback being very positive.
Many thanks for your help.

[ruirib]

Great .