access form to run query and display results

Question

i have a query in ms access database how can i create a form that has a button to run the query and display the results in the same form ( the result is only 5 records of a two collumn table ) ( so that it looks more user friendly ) i dont need the complete solution by you .. just and advice on what code should be for the button and the space to display result

Answer 1

I suggest using a subform with a continuous form to display the results. I guess the query is a select query of some description, to the record source of the subform can be set to the sql string:

strSQL="SELECT ID, Description, Count(SomeVal) " _
   & "FROM Table " _
   & "GROUP BY ID, Description " _
   & "HAVING SomeVal=" & Me.txtSomeVal

Me.[Subform Control Name].Form.RecordSource = strSQL

Answer 2

Based on the answers, I guess don't I understand the question. It sounds like the OP has a DML query (or "action query" in Access terms) that modifies data and wants to display the results in a form. The current answers explain how to display the results, but not how to run the query.

So, here's an answer based on my interpretation of the question.

First, create a continuous or datasheet form that is bound to the results.

That's the easy part. The "hard" part is executing the SQL to do the updates the results of which you're going to display. You don't give any context for where you're launching this from, nor how you determine which particular records to update, so I'm going to give two fairly generic answers.

Method 1. create a macro with two parts:

1. the first command is OpenQuery and you'd supply the name of your saved query as the argument.

2. the second command is OpenForm that opens the form you created to display the results.

Now, I haven't supplied any method for executing the macro, but that's because you didn't supply any context.

Method 2. on a form from which it is appropriate to initiate this process:

1. create a comand button.

2. use the OnClick event to perform the desired action.

   a. use the macro you wrote with Method 1 as the argument for the OnClick event of the command button.

   b. write VBA code to do both tasks:

   CurrentDB.Execute "MySaveQueryThatUpdatesData", dbFailOneError
   DoCmd.OpenForm "MyFormThatDisplaysTheResults"
   

But this is all really begging the questions, as this is all pretty darned elementary. The hard part of this kind of thing comes about when your SQL update is operating on a subset of records and you need to display only that subset of records.

It is very likely that your original query will be keyed to the original context. Say, for instance, that you want to launch the entire process from a form that displays Companies and your SQL operates on the Employees of the currently displayed Company record. In that case, you'd want an update of the Employees table limited to the Company you're currently viewing. There are two ways to do that:

1. use a reference to the CompanyID in the Company form in the WHERE clause of your saved QueryDef:

   UPDATE Employees
   SET [blah, blah, blah]
   WHERE Employees.CompanyID = Forms!Company!CompanyID
   

2. instead of using a saved QueryDef hardwired to require that your Company form be open for it to work, write the SQL on the fly in the code behind your command button:

   Dim strSQL As String
   strSQL = "UPDATE Employees "
   strSQL = strSQL & "SET [blah, blah, blah] "
   strSQL = strSQL & "WHERE Employees.CompanyID = "
   strSQL = strSQL & Me!CompanyID
   CurrentDB.Execute strSQL, dbFailOneError
   

Now, for the second part of it, you need to open the results form to display just those records that have been updated. That means you want the form opened with the same WHERE clause as was used for the update. There are two methods for this, too.

1. the first is very much like the the first method for performing the update, i.e., hardwiring the reference to the Company form in the WHERE clause of your results form's Recordsource's WHERE clause. So, the Recordsource for your results form would look like this:

   SELECT Employees.*
   FROM Employees
   WHERE Employees.CompanyID = Forms!Company!CompanyID
   

   Then you'd open the results form the same way as originally stated:

   DoCmd.OpenForm "MyFormThatDisplaysTheResults"
   

2. the second approach avoids hardwiring the Recordsource of your results form to require the Company form be open, and instead, you just supply the WHERE clause (without the WHERE keyword) in the appropriate parameter of the OpenForm command:

   DoCmd.OpenForm "MyFormThatDisplaysTheResults", , , "[CompanyID] = " & Me!CompanyID
   

   Learning to do this is one of the most powerful and easy aspects of using Access, since you can create a form that returns all the records in a table, and then open that form and display subsets of data by supplying the appropriate WHERE parameter in the OpenForm command. Keep in mind that Access applies these very efficiently, that is, it doesn't open the form and load the entire recordset and then apply the WHERE argument to it, but applies the WHERE parameter to the recordsource before any records are loaded in the form.

Now, a consideration of what is the best way out of all the alternatives:

I would write the SQL on the fly for the update and use the WHERE parameter of the OpenForm command to do the filtering. So, in one of my apps, the code behind the OnClick event of your command button on the Company form would look like this:

  Dim strSQL As String

  strSQL = "UPDATE Employees "
  strSQL = strSQL & "SET [blah, blah, blah] "
  strSQL = strSQL & "WHERE Employees.CompanyID = "
  strSQL = strSQL & Me!CompanyID
  CurrentDB.Execute strSQL, dbFailOneError
  DoCmd.OpenForm "MyFormThatDisplaysTheResults", , , "[CompanyID] = " & Me!CompanyID

Now, because of the dbFailOnError argument for CurrentDB.Execute, you'd need an error handler. And if you want to know how many records where changed, you'd need to use a database object other than CurrentDB, so more likely, I'd do it like this:

On Error GoTo errHandler
  Dim strSQL As String
  Dim db As DAO.Database

  strSQL = "UPDATE Employees "
  strSQL = strSQL & "SET [blah, blah, blah] "
  strSQL = strSQL & "WHERE Employees.CompanyID = "
  strSQL = strSQL & Me!CompanyID
  Set db = CurrentDB
  db.Execute strSQL, dbFailOneError
  Debug.Print "Updated " & db.RecordsAffect & " Employee records."
  DoCmd.OpenForm "MyFormThatDisplaysTheResults", , , "[CompanyID] = " & Me!CompanyID

exitRoutine:
  Set db = Nothing
  Exit Sub

errHandler:
  MsgBox Err.Number & ": " & Err.Description, _
    vbExclamation, "Error in Forms!Company!cmdMyButton.OnClick()"
  Resume exitRoutine

My reason for constructing the SQL on the fly in the command button's OnClick event is so that it's very easy to add in more criteria should they become necessary. I like to avoid overloading my saved QueryDefs with dependencies on UI objects, so I will tend to write SQL like this on the fly in the place where it is being used.

Some people worry that this degrades performance because on-the-fly SQL is not optimized by your database engine's query optimizer. This may or may not be true. Many server database engines cache optimization plans of on-the-fly SQL commands, and because of the way Jet/ACE parses a SQL command like this and hands it off to the server, it is likely to be sent as a generic stored procedure. Because of that, a server like SQL Server will cache that query plan and be able to re-use it each time you execute the on-the-fly SQL, even if each time it has a different CompanyID value.

With a Jet/ACE back end, there is no caching like this, but the difference in execution time between the optimized and unoptimized SQL is going to be very small in all cases where you're not operating on really large datasets. And even updating, say, 1000 employee records is not something that counts as a large dataset for Jet/ACE. So I think there is seldom enough of performance hit from writing SQL on the fly to justify moving it to a saved QueryDef. However, on a case-by-case basis, I might very well choose to do so -- it would just not be my first choice.

The more significant objection, though, is that you'll have a bunch of SQL strings littered throughout your code, and this can become a maintenance nightmare. I don't know what to say about that, except that there are ways to handle that such that you eliminate as much duplication as possible, either by saving a base SELECT query as a saved QueryDef and using that such that the SQL you construct in code is unique only the parts specific to the action being taken in that particular case, or by using defined constants in your code that hold the base SQL statements that you use (such that you only have to change the definition of the constant to change the results anywhere it is used).

That's fairly weak, but with Access, I don't see any alternative. If you save every SQL statement as a QueryDef you end up with a different kind of unmanageable mess with too many saved queries, each slightly different from the other, and it can be just as duplicative as SQL repeated in code.

But that's another issue, and I probably shouldn't make this any longer by trying to resolve it here!