Solved Run-Time Error 3131 Syntax error in FROM clause

[Kuvasz]

I am basically self taught in MS Access, Excel and VBA in general. I am attempting to get a form to auto populate an unbound Text box when the value in Combo Box is changed. I have set the following code in the AfterUpdate section of that combo box.

When I select the combo box and change the value, I get Run-Time Error '3131' Syntax error in FROM clause

My code is below. Any help would be greatly appreciated
.
Private Sub Striker_1_SN_AfterUpdate()
Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Select [Description] FROM db_tbl_Astea Tups" & "Where [Serial No#]= '" & Me.[Striker 1 SN] & "'")
Me.[txt_Striker 1 Description] = rs![Description]
rs.Close

Set rs = Nothing
Set db = Nothing

End Sub

 

[plog]

Classic naming error. You should only use alphanumeric characters when naming tables/fields in Access. That means you should not use the # nor spaces. When you do use those weird characters you must escape your tables/fields with brackets:

db_tbl_astea Tups = [dbl_tblAstea Tups]

With that said, you can have a combo box show one value, but use another which would make your coding moot:

https://www.techonthenet.com/access/comboboxes/bind_index.ph

 

[cheekybuddha]

>> Set rs = db.OpenRecordset("Select [Description] FROM db_tbl_Astea Tups" & "Where [Serial No#]= '" & Me.[Striker 1 SN] & "'") <<

You need a space between the table name and WHERE:
Set rs = db.OpenRecordset("Select [Description] FROM db_tbl_Astea Tups" & " Where [Serial No#]= '" & Me.[Striker 1 SN] & "'")

 

[Kuvasz]

Thank you. You have help me solve the problem.

 

[Kuvasz]

This too was helpful. Maybe I should have troubleshot the problem more to see if I needed just one or both of the changes, but I applied both this and the other recommendations and it now works. Thank you!

 

[plog]

For this solution you needed both. However, as I pointed out, you can accomplish this without coding at all.

 

[Gasman]

I am basically self taught in MS Access, Excel and VBA in general. I am attempting to get a form to auto populate an unbound Text box when the value in Combo Box is changed. I have set the following code in the AfterUpdate section of that combo box.

When I select the combo box and change the value, I get Run-Time Error '3131' Syntax error in FROM clause

My code is below. Any help would be greatly appreciated
.
Private Sub Striker_1_SN_AfterUpdate()
Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Select [Description] FROM db_tbl_Astea Tups" & "Where [Serial No#]= '" & Me.[Striker 1 SN] & "'")
Me.[txt_Striker 1 Description] = rs![Description]
rs.Close

Set rs = Nothing
Set db = Nothing

End Sub

I tend to put all the SQL into a string when I need to concatenate variables, and then Debug.Print that string to check the syntax, then use that in the function?

It saves a lot of time?