Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    May 2010
    Location
    Melbourne, Australia
    Posts
    13
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Need to insert data to table from report

    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.

  2. #2
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,397 Times in 1,220 Posts
    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.

  3. #3
    New Lounger
    Join Date
    May 2010
    Location
    Melbourne, Australia
    Posts
    13
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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!

  4. #4
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,397 Times in 1,220 Posts
    Do you have code in the report to determine whether the page should be displayed or not?

  5. #5
    New Lounger
    Join Date
    May 2010
    Location
    Melbourne, Australia
    Posts
    13
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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.

  6. #6
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,397 Times in 1,220 Posts
    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.
    Last edited by ruirib; 2012-04-22 at 07:25.

  7. #7
    New Lounger
    Join Date
    May 2010
    Location
    Melbourne, Australia
    Posts
    13
    Thanks
    1
    Thanked 0 Times in 0 Posts
    As simple as that?

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

    Will use your suggestion at work tomorrow.

    Thanks!

  8. #8
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,397 Times in 1,220 Posts
    That's the way to access a report's control value. The VB code, however, needs to be different:
    Code:
    dbs.execute "Insert into tblMonthlyJournals ([Organisation]) values (" & Me.txtOrg & ")"
    You don't want the value written to be "Me.txtOrg" .

  9. #9
    New Lounger
    Join Date
    May 2010
    Location
    Melbourne, Australia
    Posts
    13
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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

  10. #10
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,397 Times in 1,220 Posts
    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:

    Code:
    dbs.execute "Insert into tblMonthlyJournals ([Organisation]) values ('" & [Reports]![Invoices]![txtOrg] & "')"
    Take notice of the single quote in red.

  11. The Following User Says Thank You to ruirib For This Useful Post:

    aardvarck (2012-05-08)

  12. #11
    New Lounger
    Join Date
    May 2010
    Location
    Melbourne, Australia
    Posts
    13
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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.

  13. #12
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,397 Times in 1,220 Posts
    Great .

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •