This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
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
A family of Microsoft relational database management systems designed for ease of use.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(89.60000000000001, 51%, 18%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ES%3C/text%3E%3C/svg%3E)
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.
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?
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
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.
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.