Adding to a code

[enriquemellado]

This is the Code I'm using for a notinlist event.
I know it works

Private Sub cboCity_NotInList(NewData As String,
Response As Integer)
On Error GoTo coboCity_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("The City " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "Item not in the list")
If intAnswer = vbYes Then
strSQL = "INSERT INTO tblLocation([City]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new job title has been added to the list." _
, vbInformation, "Item not in the List"
Response = acDataErrAdded
Else
MsgBox "Please choose a city from the list." _
, vbInformation, "Item not in the List"
Response = acDataErrContinue
End If
cboCity_NotInList_Exit:
Exit Sub
cboCity_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume cboCity_NotInList_Exit
End Sub


I want to add to this part:
strSQL = "INSERT INTO tblLocation([City]) " & _
"VALUES ('" & NewData & "');"

What do I want to add:
my table tblLocation has the fields COUNTRY, STATE, CITY.
This code only add the New City to the CITY Field in the table tblLocation

In the form I also have 2 more combo boxes for Country and State.
I want to add all 3 as a new record.

I understand INSERT INTO
works by writting INSERT INTO table_name (field1, field2, field3)
VALUES (value1, value2, value3)

I tried
strSQL = "INSERT INTO tblLocation ([Country], [State], [City])" & _
"VALUES (cboCountry.Value, cboState.Value, "' & NewData & "'); "

but it says there is an error on it.
can anyone help?
thanks

 

[pbaldy]

The same kind of concatenation required for NewData must be done for the other values (plus you need a space between the text on different lines). Try this:

" VALUES ( '" & Me.cboCountry & "', '" & Me.cboState "', "' & NewData & "'); "

 

[enriquemellado]

thanks for the reply

i tried the lines:
strSQL = "INSERT INTO tblLocalizacion ([Pais], [Estado], [Ciudad]) " & _
VALUES ('" & Me.PaisNac & "', '" & Me.EstadoNac "', '" & NewData & "');

it says bad expression on the first ' (single cuotation)

 

[pbaldy]

You didn't restart the quotes before VALUES, as I included in my example. That plus I had a little typo in mine, so try this:

strSQL = "INSERT INTO tblLocalizacion ([Pais], [Estado], [Ciudad]) " & _
" VALUES ('" & Me.PaisNac & "', '" & Me.EstadoNac & "', '" & NewData & "');

 

[enriquemellado]

thank you very much
it works perfectly
i really appreciate it