Add two values into Combobox items (1 Viewer)

[Alhakeem1977]

I have got a code to fire in (not in list) event of a combobox to add two values in a table called: tblCompanies but it's not working getting error: Run-time error '3134' Syntax error in INSERT INRO.

Any help is highly appreciated?

Private Sub cboComp_NotInList(NewData As String, Response As Integer)
    Dim strTmp As String
    'Get confirmation that this is not just a spelling error.
    strTmp = "Add '" & NewData & "' as a new company?"
    If MsgBox(strTmp, vbYesNo + vbDefaultButton2 + vbQuestion, "Not in list") = vbYes Then

        'Append the NewData as a record in the Categories table.
        strTmp = "INSERT INTO tblCompanies ( Company, CompCountry ) " & _
            "SELECT """ & NewData & """ AS cboComp, AS cboCountryID;"

        DBEngine(0)(0).Execute strTmp, dbFailOnError

        'Notify Access about the new record, so it requeries the combo.
        Response = acDataErrAdded
    End If
End Sub

 

[June7]

Because NewData is a single value for 1 field, not 2 fields.

If the second value is country ID from combobox cboCountryID, try

strTmp = "INSERT INTO tblCompanies (Company, CompCountry) " & _
"VALUES('" & NewData & "'," & cboCountryID & ")"

 

[theDBguy]

Hi. Do a Debug.Print strTmp and tell us what you get.

 

[Micron]

I've never tried to use 2 aliases for field names (AS) without explicitly aliasing each of them separately, like fld1 AS foo, fld2 AS bar. Maybe you're missing your 2nd field (or it is just missing from your post).

Always output sql to a variable or immediate window when trouble shooting, then copy/paste into sql view of a new query and attempt to switch to datasheet view. Access will usually highlight the offending part in sql view. I seldom triple up double quote marks like that BTW, preferring to nest singles within doubles (" ' ") without the spaces I added for clarity. AFAIK, that could be your issue as I would expect

""" & NewData & """ should be

"""" & NewData & """" or

" ' " & NewData & " ' " (again, without spaces)

 

[The_Doc_Man]

        strTmp = "INSERT INTO tblCompanies ( Company, CompCountry ) " & _
            "SELECT """ & NewData & """ AS cboComp[COLOR="Red"],[/COLOR] AS cboCountryID;"

Find the red comma. Immediately following it is what looks like is an alias without a primary field name. In other words, you didn't specify the ACTUAL field name of the second part of the insert, you just specified an alias for it. That is probably the source of the 3134 error.

Strictly speaking, for an INSERT INTO's SELECT portion, you don't usually need an alias anyway unless you are doing some kind of WHERE filtering. And I don't see that in your presentation.

 

[Alhakeem1977]

Because NewData is a single value for 1 field, not 2 fields.

If the second value is country ID from combobox cboCountryID, try

strTmp = "INSERT INTO tblCompanies (Company, CompCountry) " & _
"VALUES('" & NewData & "'," & cboCountryID & ")"

Thank you so much, it's done as my desired .

 

[Alhakeem1977]

Hi. Do a Debug.Print strTmp and tell us what you get.

Thank you so much the DBguy you're always so active, I am waiting for your help in providing a demo on:

Re: Pass Data from MS Access to AS400
Hi. I wish I could create a demo for you, but I am not sure I can. If you search for Windows APIs, you should be able to find the pair that identifies the currently available windows and set a specific one as active. You should be able to switch back and forth between windows using these APIs. You can then either use Sendkeys or other APIs to copy and paste data as you move from one window to another. Hopefully, someone else would have a better idea for you. What I am recommending is very crude and nothing more than automating a manual process for you. Good luck!

I am not insisting if you could get time to do so.
Thanks again!