Search form with datasheet display

Question

I have a form that is a search form. The form has drop down lists of field names and a text box where they can type search keywords. For each search field/value there is a drop down list of booleans (and, or, not). When the user hits the search button, the selections generate a query string that is then run:

Set qdf = CurrentDb.QueryDefs("temp_query")
qdf.SQL = SQL_query_string

DoCmd.Close acQuery, "temp_query"
DoCmd.OpenQuery "temp_query", acViewNormal, acReadOnly

SQL_query string is generated based on the field/value combinations.

The code above creates a new query, temp_query, which runs SQL_query string. Rather than opening a new query pane, I would like to display the query results in the form as a datasheet (table). I thought a subform might be the answer, but there doesn't seem to be a datasheet format. How do I do this?

This seems to be the best alternative, but I'd like something that looks/acts more like an actual datasheet.

enter image description here

What about your current form? Can't you use that as a split form? — Mark C.
That's a good idea. Then, how do I populate the datasheet form from the query string generated by the search entry form? — abalter
I tried the solution here, but Me!DatasheetView.Form doesn't seem to have a RecordSource property. — abalter
I think you should try this method. I actually think split forms must use the same data source. I thought you could supply a separate recordsource. — Mark C.

abalter abalter · Accepted Answer · 2014-09-16T20:23:11

Aha! The correct syntax is:

Me.DatasheetView.Form.RecordSource = "temp_query"

I had gotten the ! from somewhere online, but clearly the dot is the correct way.

Here is what I did:

I already had the main search form (see image above). I created a new form called DatasheetView. Then I added a subform in the main search form that I linked (using the wizard that comes up) to the new DatasheetView form. Then, when the user clicks the "Search" button, the VBA callback runs which pieces together the query string based on the search form input. After piecing together the query string (SQL_query_string), I have:

CurrentDb.QueryDefs("temp_query").SQL = SQL_query_string
Me.DatasheetView.Form.RecordSource = "temp_query"

I would have thought that I would need to explicitly update the DatasheetView query with the generated SQL, but it seems to happen just by setting the SQL.

Basically, the form DatasheetView acts as a dummy form, that is never viewed except through the subform.

Search form with datasheet display

1 Answers