INSERT INTO statement to save a field

[KingBroil]

Hi,

When the user enters a new city I used an after update event on the text box and the statement INSERT INTO to add the newly entered city to tblUniqLoc . Here's the code:

Private Sub btnAddLocation_Click()

Me.txtGeoName.Visible = True
Me.txtGeoName.SetFocus
DoCmd.GoToRecord , , acNewRec

End Sub

Private Sub txtGeoName_AfterUpdate()

'These next lines are working if I remove everything in orange: It adds every records from tblGeoLoc. However, I just need the ID of the newly entered city in the text box. With the WHERE statement as below, Access prompts to add "0" lines to tblUniqLoc
DoCmd.RunSQL ("INSERT INTO tblUniqLoc(GeoLocID) SELECT ID FROM tblGeoLoc WHERE GeoName = ' & Me.txtGeoName & '")

Me.cboGeoLoc.SetFocus

End Sub

Private Sub cboGeoLoc_GotFocus()

DoCmd.RefreshRecord
Me.cboGeoLoc.Dropdown

End Sub

Anything an expert eye can spot here? Let me know if you need more details, any suggestions are very welcomed!

KB

 

[plog]

2 tips, one for the problem, 1 for solving problems like this:

Whenever you are executing an SQL statement in VBA and are having trouble--spit it out so you can see with your own eyes the actual SQL being executed. That means instead of directly running the query inside the RunSQL method, you set the statement to a variable which will let you spit it out (i.e. MsgBox(strSQL)) if you are having difficulty with it.

Now your actual problem has to do with your lack of double quotes prior to your variable:

WHERE GeoName =' &

You need double quotes after that single quote and then again before the next single quote, like this:

WHERE GeoName='" & Me.txtGeoName & "'")

 

[KingBroil]

Thanks Plog for the super fast and usefull reply!

The double quotes didn't solve the problem by itself. Setting the SQL statement in a variable did help me solve my problem though. Here's the modified code:

Private Sub txtGeoName_AfterUpdate()

Dim strSQL As String

DoCmd.RefreshRecord

strSQL = "INSERT INTO tblUniqLoc(GeoLocID)"
strSQL = strSQL & "SELECT ID FROM tblGeoLoc"
strSQL = strSQL & " WHERE GeoName ='" & Me.txtGeoName & "'"

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

'MsgBox strSQL

Me.cboGeoLoc.SetFocus
Me.txtGeoName.Visible = False

End Sub

As I thought, part of my problem was with the update of the table bound to my text box, along with the syntax error. Putting the refreshRecord comand did the trick.

Very usefull reply, thanks again Plog!

KB