Help needed "SQL Insert into..."

[sakthivels]

Greetings to everyone,

I have a table tblCountryState with the following structure
pkID : Autonumber
Country : Text
State : Text

Besides this I also have other tables
tblStateCiry,
pkID : Autonumber
State : Text
City : Text

tblCountry.
pkID : Autonumber
Country : Text

I have created a data capture form, where the user fills in the details. When he selects the Country from a ComboBox, the State list, another combobox is filled in with the criteria where the field Country in the tblCountryState matches that of the Country ComboBox.Value.

Now I also present an option "[OTHERS]" in the State ComboBox, added using a UNION Query. So, when the "[OTHERS]" option is selected I ask the user to give the name of New Data using a InputBox. Once I get the value, I try to INSERT the value in the tblCountryState table. Since I have a AutoNumber Filed, my SQL doesn't work.

My SQL Statement is:

"Insert into tblCountryState values ('" & Country.Value & "','" & strValue & "')"

Here strValue is the input gathered from InputBox.

Can some one please point me where the error is?

Thanks and regards,
Sakthivel

I am using Access 2003 on Windows XP Service Pack 3

 

[khawar]

Try this

"Insert into tblCountryState values (" & Country.Value & ", " & strValue & ")"

 

[sakthivels]

Try this

"Insert into tblCountryState values (" & Country.Value & ", " & strValue & ")"

Tried but got a runtime error of 3075, Syntax error (Missing Operator) in expression "New Value"

"New Value" is what I typed as input.

Another ideas ?

 

[khawar]

try to insert using field names

 

[sakthivels]

try to insert using field names

Tried this

"Insert into tblCountryState (Country, State) values ('" & Country.Value & "', '" & strValue & "')"

and works.

But the problem is after updating, the ComboBox doesn't update with the latest values.

Sorry to bug you again, Can u please help.

I also attach the Zipped mdb file with this.

 

[khawar]

use this after inserting value in table
me.mycombo.requery