Solved Run-Time Error 3131 Syntax error in FROM clause (1 Viewer)

Kuvasz

New member
Local time
Today, 05:43
Joined
Jun 17, 2020
Messages
6
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

Banishment Pending
Local time
Today, 05:43
Joined
May 11, 2011
Messages
11,611
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:

 

cheekybuddha

AWF VIP
Local time
Today, 10:43
Joined
Jul 21, 2014
Messages
2,237
>> 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

New member
Local time
Today, 05:43
Joined
Jun 17, 2020
Messages
6
Thank you. You have help me solve the problem.
 

Kuvasz

New member
Local time
Today, 05:43
Joined
Jun 17, 2020
Messages
6
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

Banishment Pending
Local time
Today, 05:43
Joined
May 11, 2011
Messages
11,611
For this solution you needed both. However, as I pointed out, you can accomplish this without coding at all.
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:43
Joined
Sep 21, 2011
Messages
14,038
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?
 

Users who are viewing this thread

Top Bottom