Solved

Access Report from form

Posted on 2014-01-10
4
501 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 36

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 30

Accepted Solution

by:
hnasr earned 500 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 36

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

740 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