SQL and updating text question

[Guest]

I am trying to devlop an sql statement that gets the short description from a
table (routes) and updates the forms text field (Notes) with the value
displayed in the lstRoute listbox. I get an error on the docmd.runsql
statement. Also, I am not sure that the Me.Notes statement is correct to
update the Notes field. Would anyone care to help with correcting my problem?
Thank you.

Private Sub LstRoute_Change()
Dim strRteNum As String
strRteNum = Val(Me.LstRoute)
'Displays form to enter a new route
If Me.LstRoute = 1 Then
Me.LstRoute = ""
DoCmd.OpenForm "AddNewRoute", acNormal, , , acFormAdd
End If

If Me.LstRoute > 1 Then
Dim strSQL1st As String, strSQL2nd As String, strSQL3rd As String,
strSQLFull As String
strSQL1st = "SELECT Routes.[Route#], Routes.[Short Description]FROM
Routes"
strSQL2nd = strSQL1st & " WHERE (((Routes.[Route#])=" & strRteNum
strSQL3rd = strSQL2nd & " ));"
Debug.Print strSQL3rd

DoCmd.RunSQL strSQL3rd
'sets note field with short description field of query
Me.Notes = [Short Description]
End If

Me.Recalc

End Sub

 

[Duane Hookom]

It does no good to run your SQL. Also, you are missing a space to the left
of "FROM". Try:

Me.Notes = DLookup("[Short Description]", "Routes","[Route#]=" & strRteNum )

This assumes Rout# is numeric. If it is text:

Me.Notes = DLookup("[Short Description]", "Routes","[Route#]=""" & strRteNum
& """")

I'm not sure why you are storing the short description in more than one
place. I assume you have a good reason for this.