SQL Insert Into Select help.

[JamesHalko]

I have been trying to figure our why this code wont work for hours so now it is time to ask for some help. I have exhausted every debugging tool I know of and obviously can;t find the problem. A user adds information into a form and clicks a button to add a new record, however the record never gets added to the table.

Private Sub cmdSearch_Click()
    On Error GoTo Err_cmdsearch_Click
    Dim strSQL As String
    If CEASE_SEARCHING Then
        strSQL = "INSERT INTO tblpbtinformation"
        strSQL = strSQL & "( Organizer, PrometricID, CASNumber, City, State, Country, SiteCode, TestDate, PMIGTN, DeadlineDate, MaxCan, OpenClosed, SpecialAcc, Confirm, Notes, RosterReconcile, ReconcileDate, ConfirmationDate, Results, SpecialAccNotes )"
        strSQL = strSQL & "SELECT [Forms]![fdlgPBTSearch]![IfOrganizer] AS Organizer,"
        strSQL = strSQL & "[Forms]![fdlgPBTSearch]![IfProID] AS PrometricID,"
        strSQL = strSQL & "[Forms]![fdlgPBTSearch]![IfCAS] AS CASNumber,"
        strSQL = strSQL & "[Forms]![fdlgPBTSearch]![IfCity] AS City,"
        strSQL = strSQL & "[Forms]![fdlgPBTSearch]![IfState] AS State,"
        strSQL = strSQL & "[Forms]![fdlgPBTSearch]![IfCountry] AS Country,"
        strSQL = strSQL & "[Forms]![fdlgPBTSearch]![IfSiteCode] AS SiteCode,"
        strSQL = strSQL & "[Forms]![fdlgPBTSearch]![IfTestDate] AS TestDate,"
        strSQL = strSQL & "[Forms]![fdlgPBTSearch]![IfPMIGTN] AS PMIGTN,"
        strSQL = strSQL & "[Forms]![fdlgPBTSearch]![IfDeadline] AS DeadlineDate,"
        strSQL = strSQL & "[Forms]![fdlgPBTSearch]![IfMaxCan] AS MaxCan,"
        strSQL = strSQL & "[Forms]![fdlgPBTSearch]![IfOpen] AS OpenClosed,"
        strSQL = strSQL & "[Forms]![fdlgPBTSearch]![IfSpecialAcc] AS SpecialAcc,"
        strSQL = strSQL & "[Forms]![fdlgPBTSearch]![IfConfirm] AS Confirm,"
        strSQL = strSQL & "[Forms]![fdlgPBTSearch]![IfNote] AS Notes,"
        strSQL = strSQL & "[Forms]![fdlgPBTSearch]![IfReconcile] AS RosterReconcile,"
        strSQL = strSQL & "[Forms]![fdlgPBTSearch]![IfReconcileDate] AS ReconcileDate,"
        strSQL = strSQL & "[Forms]![fdlgPBTSearch]![IfConfirmDate] AS ConfirmationDate,"
        strSQL = strSQL & "[Forms]![fdlgPBTSearch]![IfResults] AS Results,"
        strSQL = strSQL & "[Forms]![fdlgPBTSearch]![IfSpecialAccNotes] AS SpecialAccNotes;"
        If IsNull(Me!IfProID) Then
        FALLS_SHORT = True
        Else
        FALLS_SHORT = False
        End If
        If FALLS_SHORT Then
        DoCmd.Beep
        MsgBox "You must provide at least the Prometric ID", vbExclamation, "System Message"
        Exit Sub
        End If
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL

[Rabbit]

You can't reference form values in a string when using RunSQL. You will have to concatenate the literal values outside the string.

[JamesHalko]

Thanks for the info Rabbit.

Can you give a short example of the changes I would have to make. Or the process to do that.

[Rabbit]

strSQL = strSQL & "'" & [Forms]![fdlgPBTSearch]![IfCity] & "' AS City,"

[JamesHalko]

For the Select statement would it read as such...

strSQL = strSQL & "SELECT'" & [Forms]![fdlgPBTSearch]![IfOrganizer] & "' AS Organizer,"

[JamesHalko]

At this point after all the code my sql statement is as such.
INSERT INTO tblpbtinformati on( Organizer, PrometricID, CASNumber, City, State, Country, SiteCode, TestDate, PMIGTN, DeadlineDate, MaxCan, OpenClosed, SpecialAcc, Confirm, Notes, RosterReconcile , ReconcileDate, ConfirmationDat e, Results, SpecialAccNotes )SELECT 'TestTestTest' AS Organizer,'1231 23123123' AS PrometricID,'' AS CASNumber,'' AS City,'' AS State,'' AS Country,'' AS SiteCode,'' AS TestDate,'' AS PMIGTN,'' AS DeadlineDate,'' AS MaxCan,'' AS OpenClosed,'' AS SpecialAcc,'' AS Confirm,'' AS Notes,'' AS RosterReconcile ,'' AS ReconcileDate,' ' AS ConfirmationDat e,'' AS Results,'' AS SpecialAccNotes ,

I do not receive any type of error. The new record just never appears in the table.

[jforbes]

I've never seen a SQL INSERT INTO SELECT work without Selecting from a Table. I would either include a table or switch to using SQL INSERT INTO VALUES syntax.

[Rabbit]

You turn off the warnings so you're not going to get error messages. You should never turn it off until you know the code is working.

Also, you should test it in a query by pasting the SQL it's going to run into the SQL view to make sure you have everything formatted correctly.

One thing I can say right now is that you have no FROM clause in your SELECT. In Access SQL, you can't have a SELECT without a FROM. If you don't have a table as the source of the data, then you should be using the INSERT INTO () VALUES () syntax instead.

Also, there could be other errors that I can't see and you won't see unless you turn warnings back on. For example, if you misspelled a reference, or have the wrong number of inputs.

[JamesHalko]

Yeah that's the conclusion I made as well. I'm just wondering do I need to keep the "AS XXXXX" in the code? Would it just take blanks for the values not provided by the user.

[JamesHalko]

I got it to work! There were problems with my original table as well. If anyone wants the finished code just let me know.