Get a SQL query to display table entries so that I can edit the entries

Question

I'm working with Microsoft Access forms. My objective is to be able to enter text into a box, then click a search button so that I can perform a SQL query based on this text. My goal is to have entries that this SQL query produces be displayed in a subform so that I can edit the information present in the table entries.

So far I have been able to use the Query feature in Access to pretty much do what I want Me.Browse.SourceObject = "Query.Find Item in Parts Query" Browse is my subform and Find Item in Parts Query is my Query. However, what I don't like about this is that the query comes from a separate Access query and the text from the text box is unused.

I would instead like to be able to do a SQL query based on text I entered into the text box and be able to easily edit these results in a subform. If my question is unclear I could specify details. This question is somewhat open ended as I couldn't find anything online that made this process clear.

Answer 1

You idea is a good one.

So, assuming you build a form (continues items). Set that up - get it looking all nice. That form of course will be your sub form. You now create that main form. This main form only needs that text box at the top. The bottom part can be the sub form.

So, if this is for say a invoice? Then we use a exact match.

In the after update event of the text box (or your "search" button), you can use this code:

Dim strSQL     As String

strSQL = "select * from tblInvoices WHERE InvoiceNumber = " & Me.Text26

Me.MySubForm.Form.RecordSource = strSQL

Now, the above is for a number column. However, if it is for say a company Name? then you have to write it like this:

Dim strSQL     As String

strSQL = "select * from tblCustomers WHERE CompanyName LIKE '" & Me.Text26 & "*'"

Me.MySubForm.Form.RecordSource = strSQL

Note close, how you have to surround the results with ' (single quotes).

But, in palce of a exact match, I used "like". So the above will match the start of the company name. (quite a bit better for a search). So

If you typed in North

Then it would match North Creek Beer Northen Lights Theater etc.

And you can get quite fancy. Here is a form and sub form, and I used above approach. The user types in Smi (for smith), and I display the results of the search like this:

And on the multiple items sub form, I also palced a button (glasses icon). When the user clicks on that button, I then launch the main edit form to that ONE reocrd.

The code behind that button?

I use this:

docmd.OpenForm "frmCustomer",,,"ID = " & me!id

So, you can REALLY improve the ease of use by building a nice search form. The other really nice feature is that this works VERY fast - even on very large tables. So, if you have 500,000 or even 1 million rows? The search will occur VERY fast if the result set is small. And then with the click button idea, then you have a nice UI.

Most applications - even google? You search You display results You let user pick. From the web, to accounting packages, or just about ANY software you will use? they all follow this design pattern.

Also, once you build that nice sub form (say a continues items form). I would as the VERY last step remove the data source (the table) that this sub form is based on. That way, when you start out, the form (which is now a sub form on your main search form) will not show or have any data - so the whole form/sub form will load fast before you start searching.