Formatting SQL statement to run in VBA

[cer5yc]

Can someone tell me how to format the below so that it runs in VBA ..

INSERT INTO [Supplier Information] ( [Tax ID], [Supplier Name], Address, City, State, [Zip Code], [Duns #], [M/WBE Owned] )
SELECT [Enter Tax ID #] AS TaxID, [Forms]![frmContractData]![txtSUSupplierName] AS SupplierName, [Forms]![frmContractData]![txtSUSAddress] AS Address, [Forms]![frmContractData]![txtSUCity] AS City, [Forms]![frmContractData]![txtSUState] AS State, [Forms]![frmContractData]![txtSUZipCode] AS ZipCode, [Forms]![frmContractData]![txtSUDuns] AS Duns, [Forms]![frmContractData]![txtSUMWBEOwned] AS MWBE;

Thanks!

 

[dinger2121]

currentdb.execute("INSERT INTO [Supplier Information] ( [Tax ID], [Supplier Name], Address, City, State, [Zip Code], [Duns #], [M/WBE Owned] )
SELECT [Enter Tax ID #] AS TaxID, [Forms]![frmContractData]![txtSUSupplierName] AS SupplierName, [Forms]![frmContractData]![txtSUSAddress] AS Address, [Forms]![frmContractData]![txtSUCity] AS City, [Forms]![frmContractData]![txtSUState] AS State, [Forms]![frmContractData]![txtSUZipCode] AS ZipCode, [Forms]![frmContractData]![txtSUDuns] AS Duns, [Forms]![frmContractData]![txtSUMWBEOwned] AS MWBE")

that should work. If there are line breaks, you will have to put " & _ and start then next line with

 

[TheAceMan1]

. . . or perhaps:

[blue]   Dim db As DAO.Database, frm As Form, SQL As String
   
   Set db = CurrentDb
   Set frm = Forms!frmContractData
   
   
   SQL = "INSERT INTO [Supplier Information] ([Tax ID], " & _
                                             "[Supplier Name], " & _
                                             "Address, " & _
                                             "City, " & _
                                             "State, " & _
                                             "[Zip Code], " & _
                                             "[Duns #], " & _
                                             "[M/WBE Owned]) " & _
         "SELECT [Enter Tax ID #] AS TaxID, " & _
                 frm!txtSUSupplierName & " AS SupplierName, " & _
                 frm!txtSUSAddress & " AS Address, " & _
                 frm!txtSUCity & " AS City, " & _
                 frm!txtSUState & " AS State, " & _
                 frm!txtSUZipCode & " AS ZipCode, " & _
                 frm!txtSUDuns & " AS Duns, " & _
                 frm!txtSUMWBEOwned & " AS MWBE;"
   db.Execute SQL, dbFailOnError

   Set frm = Nothing
   Set db = Nothing[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997

 

[SkipVought]

... and taking it one step further for clarity and ease of maintenance...

    Dim sSQL As String
    sSQL = "INSERT INTO [Supplier Information]"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "("
    sSQL = sSQL & "  [Tax ID]"
    sSQL = sSQL & ", [Supplier Name]"
    sSQL = sSQL & ", Address"
    sSQL = sSQL & ", City"
    sSQL = sSQL & ", State"
    sSQL = sSQL & ", [Zip Code]"
    sSQL = sSQL & ", [Duns #]"
    sSQL = sSQL & ", [M/WBE Owned]"
    sSQL = sSQL & ")"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "SELECT"
    sSQL = sSQL & "  [Enter Tax ID #] AS TaxID"
    sSQL = sSQL & ", [Forms]![frmContractData]![txtSUSupplierName] AS SupplierName"
    sSQL = sSQL & ", [Forms]![frmContractData]![txtSUSAddress] AS Address"
    sSQL = sSQL & ", [Forms]![frmContractData]![txtSUCity] AS City"
    sSQL = sSQL & ", [Forms]![frmContractData]![txtSUState] AS State"
    sSQL = sSQL & ", [Forms]![frmContractData]![txtSUZipCode] AS ZipCode"
    sSQL = sSQL & ", [Forms]![frmContractData]![txtSUDuns] AS Duns"
    sSQL = sSQL & ", [Forms]![frmContractData]![txtSUMWBEOwned] AS MWBE;"

    currentdb.Execute sSQL

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[cer5yc]

Thank you!

 

[cer5yc]

Can anyone tell me how to format this one as well - thanks!

SELECT [Supplier Information].[Tax ID], [Supplier Information].[Supplier Name], [Supplier Information].Address, [Supplier Information].City, [Supplier Information].State, [Supplier Information].[Zip Code], [Supplier Information].[Duns #], [Supplier Information].[M/WBE Owned], [Supplier Information].[FEIN/SSN]
FROM [Supplier Information]
WHERE ((([Supplier Information].[Tax ID])=[Enter Tax ID #]));