Solved

Access Report from form

Posted on 2014-01-10
4
488 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 34

Expert Comment

by:PatHartman
Comment Utility
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
Comment Utility
"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
Comment Utility
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 34

Expert Comment

by:PatHartman
Comment Utility
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

762 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now