Run-time error 3075 syntax error (missing operator)

I am working in MS Access 2010 and get a run time error message when the SQL statement runs. The SQL code is pulling data from a combo box (named Combo11) and then I want to use the selection to populate a subform which is part of another form. The SQL code is as follows:

 

Private Sub Combo11_AfterUpdate()
Dim sTestSQL As String

sTestSQL = "SELECT * FROM PerftoGoal2013_tbl WHERE [MSA Code] = " & Me.Combo11.Column(2)

Me.RecordSource = sTestSQL
Me.PerftoGoal2013_subform.Requery
End Sub

 

The Me.RecordSource = StestSQL is highlighted

 
Question Info

Last updated June 25, 2018 Views 7,673 Applies to:

* Please try a lower page number.

* Please enter only numbers.

* Please try a lower page number.

* Please enter only numbers.

Is the value in the 3rd column of Combo11's RowSource a numeric datatype? If it is try:

sTestSQL = "SELECT * FROM PerftoGoal2013_tbl WHERE [MSA Code] = " & Me.Combo11.Column(2) & ";"


Also is Combo11 on the subform?


Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Microsoft Access MVP since 2007

Did this solve your problem?

Sorry this didn't help.

Scott, the combo box has two columns and they are both text fields. The combo box is on the header of the main form and I want to use the selection to drive the results in the subform below. I am pulling the data from a table (PerftoGoal2013) and it contains the MSA Code along with some other information which will appear in the subform.

Did this solve your problem?

Sorry this didn't help.

OK, well that makes two problems with your code. First, when using the column property, the column count begins with zero. So if you want to reference the second column you have to use a 1 not a 2. 

Second, if you are concatenating a text value into your SQL statement you need to surround it with single quotes. Try:

sTestSQL = "SELECT * FROM PerftoGoal2013_tbl WHERE [MSA Code] = '" & Me.Combo11.Column(1) & "';"


You also didn't answer where the controls are. The code you have, refers to Combo11 being on the active form and you are changing the Recordsource of the active form. But then you are requery a subform. 


If your goal is to change the Recordsource of the subform, then your code should be:


Me.PerftoGoal2013_subform.Form.Recordsource = sTestSQL


I'm assuming that combo11 is still on the main form.




Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Microsoft Access MVP since 2007

Did this solve your problem?

Sorry this didn't help.

Ok, I think we are getting closer. The combo box has two columns. Column 1 contains a msa name and column 2 has the msa code that I am using as my filter to change the Recordsource of the sub form. This is why I chose column 2. Combo 11 is on the main form.

 

I changed the code and am now getting the following error message:

run-time error 2580:

 

The record source 'SELECT * FROM PerftoGoal2013_tbl WHERE [MSA Code] = '42F';' specified on this form or report does not exist.

 

Here is the current SQL code:

 

Dim sTestSQL As String

sTestSQL = "SELECT * FROM PerftoGoal2013_tbl WHERE [MSA Code] = '" & Me.Combo11.Column(2) & "';"

Me.PerftoGoal2013_subform.Form.RecordSource = sTestSQL
Me.PerftoGoal2013_subform.Requery
End Sub

Did this solve your problem?

Sorry this didn't help.

Again, Using Column(2) should not work if there are only 2 columns in the RowSource. The column count starts at zero so Column(2) is actually the THIRD column. 

So I don't know where it got the 42F from. 

Do you have a table or query named PerftoGoal2013_tbl? That's the only thing I can think of that would give that message.
Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Microsoft Access MVP since 2007

Did this solve your problem?

Sorry this didn't help.

Yes, there is a table named PerftoGoal2013 and that is probably where the value 42F is coming from. I will play with the column reference to see if I can get the subform to update correctly.

 

Thanks for your help and let me know if you have any other suggestions. 

Did this solve your problem?

Sorry this didn't help.

Here's my problem. The message says that it can't find the Recordsource. No I don't know if it is the default Recordsource or what?

If you you try to reference a column in a Combo's RowSource that doesn't exist, it should return a Null. That's why the error message showing 42F bothers me. 
Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Microsoft Access MVP since 2007

1 person was helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

The row source for the data in the combo box is a table consisting of three fields: (i) id; (ii) MSA Name; and (iii) MSA Code in that order. The combo box is sorted by the MSA Name and it is the first column of data. I tried switching the Me.Combo11.Column(3) to column 3 and it returned a null and errored out. When I changed it back to column 2, it returned a value and also errored out.

Did this solve your problem?

Sorry this didn't help.

You said the rowsource only had TWO columns. But now you are saying it has three. So using Column(2) IS appropriate. 

So now I'm at a loss. Is there a record in the query with a MSA Code of 42F?


Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Microsoft Access MVP since 2007

Did this solve your problem?

Sorry this didn't help.

The combo box has two columns of data and it is pointing to a MSA table for the row source which has three fields of information. The MSA code of 42F appears in the main MSA table and the PerftoGoal2013 table. I was hoping to link these fields to filter and populate the information on the subform.

 

Thanks again for your help and let me know if you have any other suggestions.

Did this solve your problem?

Sorry this didn't help.

* Please try a lower page number.

* Please enter only numbers.

* Please try a lower page number.

* Please enter only numbers.