Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2005
    Posts
    55

    Unanswered: Appending access data

    Hi I am trying to append data in my access database to an microsoft excel file using the code below.

    DoCmd.OutputTo acOutputQuery, "testquery", "MicrosoftExcel(*.xls)", "C:\SMIS\Appenddata.xls", False, ""

    The code works to an extent however, say that I click on the botton to do the appending for the first time the record being viewed at present is appended to the excel file when I do a second appending of another record it writes over the first record that was appended to the excel file and I don't want that to happen I just want the records to be added to what has already been appended. So that at the end of the day if six records was appended I am to see the six records in the excel file.

    Can anyone help me with this I have been trying and nothing seem to work what is wrong with the code that I am using?

    Thank much for the help!

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Code:
    DoCmd.OutputTo acOutputQuery, "testquery", "MicrosoftExcel(*.xls)", "C:\SMIS\Appenddata.xls", False, ""
    Not sure it's causing your trouble, but the blue part of your code (scroll code to the right) shouldn't be there. Parameters left blank in the middle of a parameter group must have the comma to hold the place, but trailing parameters that are blank should not, much less have a zero length string appended!
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Oct 2005
    Posts
    55
    Missingling you are right! I moved the blue part in the code and nothing changed!

    Thanks any way!

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Is this a report or is the spreadsheet an ongoing concern?

    By this I mean - could you cache all the records within Access and then export them to Excel in one go or do you need to append results whilst preserving the pre-existing data?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Oct 2005
    Posts
    55
    Actually pootle flump what I am trying to do is capture a record when it is deleted. So for example I have a button name 'delete' when that is click I want that record to be appended to an excel file. As I mentioned the code that I used that is working to an extent is

    DoCmd.OutputTo acOutputQuery, "testquery", "MicrosoftExcel(*.xls)", "C:\SMIS\Appenddata.xls", False, ""

    But the problem is that when ever I click the 'delete' button the record in the excel file is replaced or overwrite.

    I don't want that to happen I just want the records to be added to what has already been appended. So that at the end of the day if ten records was deleted when I check the excel file I should see the ten records.

    Hope I answer the question.

    Thanks for the help.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - I still don't understand why Excel. Can the deleted records not be stored in an Access table and exported to excel whenever requested? Personally this is how I would want things to work if I were keeping some sort of audit log (as this sounds it is).

    My point is you can get round this problem but there may be easier ways....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Oct 2005
    Posts
    55
    Ok thanks for the advice pootle flump.

    How to I store the deleted records in an access table and then export it to excel whenever requested?

    I have no Idea where to begin.

    Thanks for the help

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You can export to Excel using the code you had previously (or you can be more fancy and use soemthing like this: http://www.dbforums.com/showpost.php...81&postcount=4)

    To cache the data locally, create a table with your required fields and then simply change your "testquery" into an append query, inserting the data into your new table.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Oct 2005
    Posts
    55
    Thanks pootle flump I did had a look at what you send.

    But I did a little something on my own and low and behold when I run my append query with its given criteria I am able to send the record to an append table. Yet I have a problem.

    Here goes!!!!

    I am using a form "delete form" where I search for a record and when it is found I click the "delete" button so that the record is deleted but before doing the deletion I want that record to be appended to the "append table".

    So what I did was I created a macro for the "delete" button and "apply filter" to the append query(Query1 [name of append query]) and in the where condition I have ([Area]=[Forms]![Deletedata]![CboArea] And [Location]=[Forms]![Deletedata]![Cbolocations]).

    I have that same criteria in my append query.

    This is not working!

    I have not put in the actual command to delete the record just yet cause I want to see that the append action works first but it is not.

    What did I do wrong.

    I have realised that the append query works when I am in the query environment and I click the run icon.

    How do I get it to work via the "delete" button on the form.

    Thanks for the help

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - I am afraid I don't do macros But I can talk you through the VB code
    Quote Originally Posted by Greenway
    This is not working!
    What goes wrong? Error message (details please)? Appends the wrong records? Appends no records? etc
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Oct 2005
    Posts
    55
    Ok thanks very much!

    On my delete form I have two combo boxes and base on the data selected in the two combo boxes information is shown.

    As I said before when a record is deleted I want to append that record to another table before it is actually deleted from its original table.

    So what I did was I created a macro for the "delete" button and "apply filter" to the append query(Query1 [name of append query]) and in the where condition I have ([Area]=[Forms]![Deletedata]![CboArea] And [Location]=[Forms]![Deletedata]![Cbolocations]).

    I also have in criteria row under the Area column in the "append query"[Forms]![Deletedata]![CboArea] And under the Location column[Forms]![Deletedata]![Cbolocations]).

    After selecting the information in the two combo boxes that generates information to be appended before deleted, when I click the delete button on the 'delete form' I get the message "Enter parameter value(form!Deletedata!CboArea)" without entering any thing and I click ok another message ' Enter parameter value(form!Deletedata!Cbolocations) when I click ok the fields on the screen are cleared.

    If I enter information when the "Enter parameter value" message is shown it accepts the information and when I click ok and check to see if the record was appended to the append table, the append has nothing. So therefore it is not working.

    It clear that no records are being appended!
    Last edited by Greenway; 10-11-06 at 08:55.

Posting Permissions

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