Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160

    Unanswered: INSERT INTO (a variable that holds the table to append into)

    Hi there,

    I am trying to append records to a table. I'm using the INSERT INTO and it works fine if I reference the table directly.

    INSERT INTO tbl_Purchases ( etc ....)

    However, I have combo box which lists different tables. I would like it to append records to which ever table is selected.

    My code..

    TableRef = Form.main.TableRef this points to the combo box value which lists the various tables

    it works fine if i use SQLtext = " INSERT INTO tbl_Purchases ( etc....

    i would like it to work when i use a pointer

    SQLtest = " INSERT INTO TableRef ( etc....

    I'm guessing my TableRef needs to be in quotes of something. I've tried () and [] and "" but none of them worked.

    thanks
    Mark
    Last edited by marcusmacman; 10-01-10 at 13:15.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You can't parametrise the table name - you will need to concatenate the name into your SQL statement. Note also that more often than not requiring the user to select a destination table is the sign of a problem with the design.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    trust me it's not a problem with the design.... I'm anchoring my unique fields to hidden text boxes which has put me in control ... kinda!!

    Could you elaborate a bit more regarding concatenating the name? maybe show me a bit a VB magic???

    thanks
    Mark

  4. #4
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    could anyone help me with a bit of VB for my problem? be very grateful.

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I'm with Pootle, but:

    SQLtest = " INSERT INTO " & TableRef & " ( etc...."
    Paul

  6. #6
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    thanks for that

    I'm trying to add parts to a quote, but once in the quote I can change the wording without it affecting the original data. The parts often go under different heading within the quote. I'm allowing the combo box to choose which table / heading the data will go to. People who use the database then have a choice.

    Decided to ditch the relational bit and use a unbound text box as reference to the quote I'm viewing. It seems to work well, and I'm not getting tied up with relationships etc...
    thanks again
    marcus

  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Happy to help...but I still question the design. What's in the different tables?
    Paul

  8. #8
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    it's a quotation database. Each table will hold parts depending on the Sub title of the quotation. Eg I'll put some parts under the Hardware heading so that will go into the tblHardware, others will go under Extras heading so again that will go into the tblExtras and so on. Each part picks up the Quote number and when the user is finish it pulls everything together to produced a nice report with parts under their correct heading. well that's the idea.

    The user would like the option of putting parts in any of the headings depending on who the quote is going out to. hence using the combo box to direct the append query.

    The other good thing is once the data has been added to the relevant tables I can modify the price and wording of some of the items without affect the master product list. hope that makes sense? thanks again for your help

    marcus

    P.S Any recommendations of a decent book to help me understand access VBA? cheers
    Last edited by marcusmacman; 10-01-10 at 20:18.

Posting Permissions

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