Use Access VBA to change the record source for a form

Question

I am creating a search form and I am unable to see the search results in the subform. The query runs fine, I have set the subforms record source to the query. I donot get any errors. When I click the search button the query runs and it shows the number of rows in the record selector at the bottom of the subform, but i cannot see the rows.

Here is my code for the OnClick event of the button:

Private Sub cmdSearch_Click()

Dim tableName As String
Dim colName As String
Dim keyword As String
Dim strSQL As String

tableName = Me.cmbTableNames.Value
colName = Me.cmbColumnNames.Value
keyword = Me.txtKeyword.Value
strSQL = "Select * from [" & [tableName] & "] where [" & [colName] & "] like '*" &  [keyword] & "*';"
Debug.Print strSQL
Me.searchResultsForm.Visible = True

Forms![F_SearchForm]![searchResultsForm].Form.RecordSource = "Select * from [" &   [tableName] & "] where [" & [colName] & "] like '*" & [keyword] & "*';"
Forms![F_SearchForm]![searchResultsForm].Form.Requery
End Sub

Can someone tell me what I am doing wrong.

Thank You

This is what shows up in the Immediate Window for Debug.Print

Select * from [dbo_Internal Contacts] where [First Name] like '*Amy*';

My Form looks like this in the form view:

enter image description here

I have added some text boxes to my subform (around 35). Now if I run the query my form looks like this: enter image description here

How can I link these text boxes on the subform to the columns in the recordsource using vba?

Please help

Is there a reason you're not using the native query builder and design mode properties to set the Record Source for the form? Embedding SQL in code is bad practice and (as you have discovered) makes debugging more difficult. — Tim, Apr 08 '14 at 15:08
@Tim I am trying to create a search form and the table name and column name are the values from a combo box selected by the user. I tried to use this query (strSQL) as it is. But access is not letting me save the query, it gives me the error invalid bracketing at tablename. Thats the reason i chose to go this way. — ksagar, Apr 08 '14 at 15:26
You can't paste that code into a query; just start from scratch in design mode of the query builder, then select the tables you need, and create joins between the fields as required. — Tim, Apr 08 '14 at 15:32
Debug.Print strSQL gives this in the Immediate Window: ------> Select * from [dbo_Internal Contacts] where [First Name] like '*Amy*'; — ksagar, Apr 08 '14 at 15:32
@Tim I am sorry if I was not clear earlier. I want to create a search form, the form has a combo box that lists all the table names in my db. After I select a table name, another combo box comes up that lists all the columns in the table selected above. Then when the user selects a column a text field appears, here a keyword is typed. Now my query should take the selected table name, col name and keyword from the form and run the select query and show the results in a subform. — ksagar, Apr 08 '14 at 15:37
@HansUp Oh it does, i dont know y the * vanished when I pasted the query here in the comment box. This is what Debug.Print gives me: 'Select * from [dbo_Internal Contacts] where [First Name] like '*Amy*''; I am sorry I cant get the * to show up in this comment box but they exist in the query. — ksagar, Apr 08 '14 at 15:39
My guess is your subform does not include data controls which are bound to the fields returned by the `SELECT` statement. For example, if `ID` is the first column returned by `SELECT *`, and you want that column displayed in the subform, the subform should include a control (such as a text box) whose Control Source property is the `ID` field. Does that make sense, and does that describe your subform's design? — HansUp, Apr 08 '14 at 15:49
@HansUp I understood what u r saying. My subform is basically empty, I donot have any texfields or recordsource or controlsource for my subform. Is there a way to dynamically set the recordsource of my subform using vba — ksagar, Apr 08 '14 at 15:55
one more thing, I actually created a blank form and used it as a subform on my main form. — ksagar, Apr 08 '14 at 15:56
A *blank* subform will not show you any values from the record source. This is a critical point ... and is not limited to subforms. — HansUp, Apr 08 '14 at 15:59
Ya you are right. But I want the subform to be dynamic based on the table selected from the first combobox. The number of columns in each table vary and I cannot have a set number of text fields in the subform. Is there any other way to show the result of this query, if not on a subform? — ksagar, Apr 08 '14 at 16:02

score 1 · Answer 1 · answered Apr 08 '14 at 17:09

I figured it out. Here's what I did

I modified the query to

Select * into tmpSearchResults from [" & [tableName] & "] where [" & [colName] & "] like '*" & [keyword] & "*';

And I gave the tmpSearchResults as recordsource to my subform.

I drop the tmpSearchResults table everytime the the table name combobox is updated.

It works just as I wanted it to.

Use Access VBA to change the record source for a form

1 Answers1