Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Append Record (2000)

    Hi All

    I am having a bad hair day <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23> .....

    I have two tables the first tblAll and tblRTB. tblAll has a set of records that will be the final output, and tblRTB has data that needs to be appended to tblAll after a field has been updated. If the field in tblRTB is NULL then it can't be updated to tblALL.


    I have created a continuous form viewing the contents of tblRTB. What I want to do is get my assistant to update the empty field with a reference and then click a command button which append the record to tblAll. I know it is something along the lines of:

    Private Sub cmdSelect_Click()
    Dim db As Database
    Dim rs As Recordset
    Dim strSQL As String



    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblRTB")
    strSQL = ("SELECT * FROM tblRTB where RTBID=" & Me.ID)

    ..........

    But how do I append the record to tblAll?

    Cheers

    Jerry
    Jerry

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append Record (2000)

    Why don't you use an append query. Something like :
    strSQL = "INSERT INTO tblAll SELECT rtbid.* FROM rtbid WHERE rtbid.id = " & [me].[id];
    db.Execute strSQL
    Francois

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Append Record (2000)

    The SQL for an append query is

    INSERT INTO tblALL ( Field1, Field2, Field3 ) SELECT Field1, Field2, Field3 FROM tblRTB

    If the source and target tables have the same structure, and you want to append all fields, you can omit the field list after INSERT INTO tblAll:

    INSERT INTO tblALL SELECT * FROM tblRTB

    If you want to filter the records appended, you can place a WHERE clause at the end.

    Note: You don't open a recordset to execute the SQL for an action query., instead you use

    DoCmd.RunSQL strSQL

    or

    CurrentDb.Execute strSQL.

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Append Record (2000)

    Thanks Hans

    Sorry to be a pain, but I can't see why this won't work

    Private Sub cmdAppend_Click()
    Dim db As Database
    Dim rs As Recordset
    Dim strSQL As String

    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblNoMatch")
    strSQL = "INSERT into tblAll SELECT* from tblNoMatch where RTBID1=" & Me.RTBID1
    db.Execute strSQL

    end sub

    I have done similar exercises before in other databases where I had data outputting to a form and it worked fine. For some reason it bugs out at Set rs = db.OpenRecordset("tblNoMatch") giving me a Run Time error 13, Type Mismatch. I know and I can see I have a table called tblNoMatch... I told you it was a bad hair day.


    Jerry
    Jerry

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Append Record (2000)

    OK OK

    I have found it

    Dim rs As DAO.Recordset

    Cheers

    Jerry
    Jerry

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Append Record (2000)

    You can always use your chainsaw to discipline your hair <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

    I don't think you need to open a recordset here - I don't see it being used in the code. But if you do need it, you must declare it as a DAO recordset explicitly:

    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    You must be explicit because the ADODB library also contains a recordset object.

    I assume you do have a reference to the Microsoft DAO 3.6 Object Library, otherwise the As Database declaration would have caused an error.

  7. #7
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Append Record (2000)

    Hi Hans

    Now I know it is time for lunch...


    Private Sub cmdAppend_Click()

    Dim strSQL As String

    strSQL = "INSERT INTO tblALL SELECT * FROM tblRTB where RTBID1 = " & Me.RTBID1
    DoCmd.RunSQL strSQL
    End Sub

    Now I get a Datatype mismatch in criteria expression

    I have checked the two tables and ensured that the field types have the same properties and are named the same. I really think I have missed the plot here....


    Jerry
    Jerry

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Append Record (2000)

    If RTBID1 is a text field, you need quotes around the value:

    strSQL = "INSERT INTO tblALL SELECT * FROM tblRTB where RTBID1 = " & Chr(34) & Me.RTBID1 & Chr(34)

    Chr(34) is the double quote ".

  9. #9
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Append Record (2000)

    Hans

    My hair is smoothing out as I type, no need for the chain saw to trim it


    Thanks for your patience.

    Jerry
    Jerry

  10. #10
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Append Record (2000)

    Hans

    Right, now my hais IS being pulled out.

    After finally getting this to work another problem has arisen. tblRTB holds information that needs to be changed (Propref), what I wanted to happen is to add the Propref value to to this field and when it has been added for it to be appended to tblAll, hence the discussion above. Now when I click on my command button it appends all the fields apart from the value that I put in the field Propref.

    I have tried Requery, refresh, save but none seem to work. The updated propref data does appear in tblRTB but won't go accross to tblAll,Why?


    Jerry
    Jerry

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Append Record (2000)

    Is there a Propref field in tblAll?

  12. #12
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Append Record (2000)

    Yes

    Both tables mimic each other in every detail, fields the same name, with the same field properties.

    Jerry
    Jerry

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Append Record (2000)

    See if it helps if you insert a line

    RunCommand acCmdSaveRecord

    above the line that executes the SQL string.

  14. #14
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Append Record (2000)

    Nope

    Doesn't do it <img src=/S/help.gif border=0 alt=help width=23 height=15>

    Jerry
    Jerry

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Append Record (2000)

    Sorry, no idea then, without seeing the database.

Page 1 of 2 12 LastLast

Posting Permissions

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