[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Access Report from form

Posted on 2014-01-10
4
Medium Priority
?
533 Views
Last Modified: 2014-01-17
We have an Access DB with 4 tables; investor, borrower, loan and payments.  We have a form with the majority of fields that a user can enter data into to find all loans an investor is related to and they can run a query from this form.  Is it possible to save a particular instance of the query for running in the future from this form?  If so is it possible to list all of these saved queries in a combo box for recall?
0
Comment
Question by:LeonHeyns
  • 2
4 Comments
 
LVL 40

Expert Comment

by:PatHartman
ID: 39771722
Yes but.

If you are talking about having users create queries in the "production" FE, that is a bad idea for a number of reasons.  If you want to allow users to create their own queries, make them a separate database with queries instead of linked tables (create a query for each table and include a dummy 1-row table with no join line so the query returns a Cartesian product) so you can make the data not-updateable.  That way they can have their own environment.  If you let them make design changes to the common FE, you'll have to have a way to deal with each user's changes when you need to distribute a new FE.

You can create queries and save the SQL strings to a table.  You can let the user save queries this way and call them back for use but I also don't think it is a good idea to let users anywhere near raw queries.  Access by default makes every query updateable and so it is quite dangerous to have non-technical people working with them because accidents happen and you don't have any control the way you do with forms.

If the Select clause is always the same and it is only the criteria that changes, just save the criteria.  Then you can create a form and/or a report and use the selected criteria as the WHERE argument of the OpenForm/OpenReport method.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 39772484
"they can run a query from this form"
By that, I think you mean they can cause the form to show a subset of the initial records, rather than they can create and run MS Access queries.

What you want can be fairly straight-forward.
A table to store filter values, shortnames for the filters, and perhaps even who can use and manage them.
A button to say "Save Apply/Manage filters" that opens a popup form
A form that pops up and gives users the opportunity to tag the present filter with some unique name and save it
On that form, some ability to select and delete entries that users no longer like.
And on that form a button that applies a filter to the first form.

Filters are simple in principle: A WHERE statement without the where.
The bugger is in typos and syntax.

The VBA is simple:
Me.Someform.Filter ="Some completely syntactically correct WHERE clause"
Me.SomeForm.FilterOn = True 

Open in new window


Its the filter syntax that can be no fun.
Fields can sometimes need to be fully qualified: [SomeTableorQuery].[SomeField]
String Criteria need to be surrounded in quotes: [SomeTableorQuery].[SomeField] = Chr(34) & "SomeString" & chr(34)   -----> I like this better than
[SomeTableorQuery].[SomeField] = ""SomeString"" ------> is that 2 quotes, four singles or a quote and two singles?  Chr(34) is the quote mark.
Date Criteria must be pounded:  [SomeTableorQuery].[SomeField] = #1-Jan-2014#
Get sloppy with your date formats and evil occurs.
#31/1/2014# is January 31, 2014 but #3/1/2014# can be two different things depending on Regional Settings in the Control Panel.

Throw up a sample.  Let's have a look at what "run a query " means in context.
0
 
LVL 31

Accepted Solution

by:
hnasr earned 2000 total points
ID: 39774290
I assume you have the first part done, which is creating the sql of query from selected form fields.
If not you need to accomplish this first.
Is it possible to save a particular instance of the query for running in the future from this form?
Yes. The query you make from the form, can be saved with a smart name, like UserID-someText-query#
If so is it possible to list all of these saved queries in a combo box for recall?
Yes. The combo can be filled from a query that lists all the relevant queries.
0
 
LVL 40

Expert Comment

by:PatHartman
ID: 39789831
I see that you have accepted hnasr's solution but I think you are making a mistake by creating objects in the FE.  You will end up with each user having a customized FE and you will not be able to automatically replace them.

If you want to save the SQL Strings, save them in a table rather than as querydefs.  Include a column in the table to identify which user created the query.  I would also add a column that is used to track when the query was last run.  So, if the user requests a particular query, you would update the table before you run it.  This would allow you to easily determine which queries were actually being used.  The table method also allows you to include a column which can hold a description so you have some method of remembering what each query is for.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question