INSERT INTO for Not In List

[NISMOJim]

This is the beginning of what will probably be a two-part question. I've tried several variations on working code I've used in the past, and scoured the internet until my eyes fell out (I'm better now), but I still can't get it working.
I have a combobox (cboVstrName) on a form using the field "VstrName" as the row source from tblVstr (other fields in this table are "VstrID {PK, autonumber} and VstrCompany). Limit To List is set to "Yes". I'm trying to add the following code to the "Not In List" to add the name to the table, so it will show up in the dropdown box the next time...

Private Sub cboVstrName_NotInList(NewData As String)
'Adds visitor name to tblVstr if not already on dropdown list
Dim strSQL As String

DoCmd.SetWarnings False
strSQL = "INSERT INTO tblVstr (VstrName)"
strSQL = strSQL & "Select """ & NewData & """;"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
Response = acDataErrAdded

End Sub



but I get this error...


How can I make this work?

[NISMOJim]

Now that I've looked at things, I'm getting this error on events for every control on the form. Everything else worked great yesterday, now things that weren't even changed are sending errors. What happened?

[ItsMe]

That error message does not look familiar to me but I would start to look for code in my form's module that is out of place. I would look for code that is not within a Sub or a Function or a misplaced/duplicate End Sub or End Function termination line. Have you tried Compiling your code?

[Missinglinq]

Are you entering this code freehand? I ask because when using the ellipsis in the Property Pane to go to the code window,

Private Sub cboVstrName_NotInList(NewData As String)

would be generated as

Private Sub cboVstrName_NotInList(NewData As String, Response As Integer)

The NotInList parameters the Access Gnomes expect are NewData As String and Response As Integer, and that missing second parameter is what they're balking at; the parameters you have don't match the parameters that Access expects, hence the error message!

Experience has shown that, at least in 2007/2010 and 2013, some of the things in code that Access checks for are only checked for when the Form is opened directly, in Form View, not when it is opened in Design View and then changed to Form View.

An example of this involves the 'Trusted' status; Access will run code, when going from Design View to running it, in Form View, even with the Folder not 'trusted,' but will balk when opened directly in Form View, under the same conditions.

Linq ;0)>

[CJ_London]

when trying to debug code, it always helps to comment out setwarnings and other error handling so you can see the actual errors

In addition to Linq's comment, it looks like you are missing a space before Select

[orange]

Ajax,

I don't think access sql will have a problem with no space before the Select

"INSERT INTO tblVstr (VstrName)Select """ ....

I would put the space, but I don't think 'no space' violates syntax.

[CJ_London]

you're right - never knew that

[NISMOJim]

I won't get a chance to try this out until Wednesday, but thanks for starting me somewhere. When I first tried the code I typed "Me.cboVstrName" instead of "NewData". I thought that might be where the error came from, but I guess not. I removed the "Response as integer" part because I thought it referred to a yes/no answer to a message box that I'm not using. Some of the controls are throwing this error on "OnClick" events that don't even have a "NotInList" event. I did look through all of the code for errors, but nothing I noticed looked wrong. Not sure if I would recognize an error or not at this point.

[NISMOJim]

Mr. Linq, you've done it again! As soon as I added "Response as Integer", everything on the form worked perfectly. As for the second part of my question, I figured it out through other posts. Thanks again, you guys are priceless!

[Missinglinq]

Glad we could help!

Linq ;0)>