Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2004
    Location
    Atlanta, GA
    Posts
    58

    Unanswered: Insert Into Statement

    I have a main form named frmDeal. I also have 2 tables name tblInventory & tblTrade. I have a button on frmDeal that when I click it I want to insert some values from tblTrade into tblInventory. I have a field on the frmDeal named Transaction_ID that is also on the tblTrade as a foreign key. Here is the code I have so far but I don't know how to code the values.

    Code:
    Dim strSQL as string
    strSQL = "INSERT INTO [tblInventory]([VIN_Nbr],[Year],[Make],[Model] Values (This is where I need help).
    I want to insert the fields (VIN_Nbr, Year, Make, Model) from tblTrade where Transaction_ID = forms!frmdeal!transaction_id.

    Thanks for your help in advance.

  2. #2
    Join Date
    Feb 2004
    Posts
    142
    if the form is still open then pull the values from the form controls

    Dim strSQL as string
    strSQL = "INSERT INTO [tblInventory]([VIN_Nbr],[Year],[Make],[Model] Values ('" & me!txtVIN_Nbr & "', " & me!txtYear & ", '" & me!txtMake & "', '" & me!txtModel & "')".

    Otherwise
    Dim strSQL as string
    strSQL = "INSERT INTO [tblInventory]([VIN_Nbr],[Year],[Make],[Model] Values (SELECT VIN_Nbr, YEAR, MAKE, MODEL from tblTrade WHERE Transaction_ID = " & forms!frmdeal!transaction_id & ")"
    KC

  3. #3
    Join Date
    Mar 2004
    Location
    Atlanta, GA
    Posts
    58
    It is messing up at the insert statement. Am I missing something?

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    probably it's the mushrooms i eat, but....
    "INSERT INTO (field1, field2, field3) VALUES (val1, val2, val3);"

    works fine for me.

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Jan 2004
    Posts
    492
    If you are going to do a select statement as the data source for your values (instead of hardcoding the values) take out the VALUES keyword.

    EX:

    Insert into test_table(col1, col2, col3) Select col1, col2, col3 from other_table

  6. #6
    Join Date
    Mar 2004
    Location
    Atlanta, GA
    Posts
    58
    Thanks I will give it a try.

  7. #7
    Join Date
    Mar 2004
    Location
    Atlanta, GA
    Posts
    58
    I gave it a try and I get the following error message:

    Incorrect syntax near the keyword 'Select'

    Here is my string...

    Code:
    strSQLCopyTrade = "INSERT INTO [tblInventory]([VIN_Nbr],[Year],[Make],[Model] SELECT VIN_Nbr, YEAR, MAKE, MODEL from tblTrade WHERE Transaction_ID = " & Forms!frmdeal!Transaction_ID & ")"

  8. #8
    Join Date
    Mar 2004
    Location
    Adelaide, Australia
    Posts
    32
    Originally posted by odamsr
    I gave it a try and I get the following error message:

    Incorrect syntax near the keyword 'Select'

    Here is my string...

    Code:
    strSQLCopyTrade = "INSERT INTO [tblInventory]([VIN_Nbr],[Year],[Make],[Model] SELECT VIN_Nbr, YEAR, MAKE, MODEL from tblTrade WHERE Transaction_ID = " & Forms!frmdeal!Transaction_ID & ")"
    The ")" at the end of your string may cause a problem. Try putting it between [Model] and SELECT, ie. [Model]) SELECT.

    So your string should look something like:

    Code:
    strSQLCopyTrade = "INSERT INTO [tblInventory]([VIN_Nbr],[Year],[Make],[Model]) SELECT VIN_Nbr, YEAR, MAKE, MODEL from tblTrade WHERE Transaction_ID = " & Forms!frmdeal!Transaction_ID
    HTH.

  9. #9
    Join Date
    Mar 2004
    Location
    Atlanta, GA
    Posts
    58

    Talking

    Thanks. That worked.

Posting Permissions

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