none
Add data from a query to a table. RRS feed

  • Question

  •  I have a query that gives a detailed summary of all the Employees salaries. Detail include Tax deduction, UIF, Pension, Medical aid etc. My aim is to print the summary on payday, and when approved I would like to click a button that add the data from the query to the salaries table.

    If it is a lot to describe here, I will appreciate if someone refer me to a video or script that may teach me how.

    Thank you.

    Friday, April 15, 2016 4:10 AM

Answers

  • I managed to invoke the query through VBA thanks. I will continue to try to get the insert function correct as well Thank you very much.
    Friday, April 15, 2016 4:09 PM

All replies

  • I know how to do an append query manually. Just tested it and it does add the info correct. I wondered if it is possible to add a command button on the report, and as an event under code builder what would be the code that can do the append query?
    Friday, April 15, 2016 5:52 AM
  • Hi, Hans van Niekerk

    You can place the following code in the click event of command button on report.

    Private Sub Command0_Click()
    Dim db As Database
    Dim tdf As TableDef
    Dim x As Integer
    Set db = CurrentDb
    db.Execute " insert into Table1 (name,address) select name1,address1 from Table2;"
    MsgBox "Data inserted succesfully"
    End Sub
    

    This is just a demo code. You can change it as per your data.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, April 15, 2016 8:18 AM
    Moderator
  • Thank you Deepak.
    Friday, April 15, 2016 10:07 AM
  • The query name is q02SalariesCurrent and the table is t02Salaries. I named all the fields in the query exactly the same as in the table. On above insert I have only added 3 of the 10 fields involved, but you can see the error message.
    Friday, April 15, 2016 10:12 AM
  • The query name is q02SalariesCurrent and the table is t02Salaries. I named all the fields in the query exactly the same as in the table. On above insert I have only added 3 of the 10 fields involved, but you can see the error message.

    Hi Hans,

    I see two times  "End Sub". You can remove one.

    Imb.

    Friday, April 15, 2016 12:32 PM
  • Hi, good to hear from you! I removed one "End Sub". Result same. Something else has been bugging me. When I add a command button to a report, it disappears in report view, but is always there in design view? Visible is set to yes.

    If you look at the insert, does it look right to append the query to the table. As I said when I do it manually it works exactly correct. Do I understand Deepak correct that all ten fields from both sides should be added to the expression?

    Friday, April 15, 2016 1:24 PM
  • If you look at the insert, does it look right to append the query to the table. As I said when I do it manually it works exactly correct. Do I understand Deepak correct that all ten fields from both sides should be added to the expression?

    Hi Hans,

    Another try. Is there a missing space between t02Salaries and the field list?

    Imb.

    Friday, April 15, 2016 1:38 PM
  • There is one space.

    I saved an Append query as  q02SalariesAdd. If I run it manually the result is good. I am searching for the correct expression in VBA that will invoke the query. Surely that will work as well. If you know give me the exact expression using my query name.

    Friday, April 15, 2016 2:19 PM
  • Hi Hans,

    it seems that you have not add an reference to the "Microsoft Access Object Library".

    Go to the "Tools" menu.

    Click on the "References"

    select the "Microsoft Access Object Library"

    click on "OK"

    then try to run the code.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, April 15, 2016 2:22 PM
    Moderator
  • Hi, Hans

    • all ten fields from both sides should be added to the expression?

    no need to add all the fields. add fields only that you want to append.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, April 15, 2016 2:26 PM
    Moderator
  • Deepak. I have Access 2013. I don't find the page you are referring to. I find Database Tools, Object dependencies but nothing close to your insert.
    Friday, April 15, 2016 3:02 PM
  • Sorry I found the right menu Microsoft Access Object Library was ticked.
    Friday, April 15, 2016 3:52 PM
  • I managed to invoke the query through VBA thanks. I will continue to try to get the insert function correct as well Thank you very much.
    Friday, April 15, 2016 4:09 PM