Solved

Access Report from form

Posted on 2014-01-10
4
495 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 35

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 35

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VBA Access 2016 syntax 6 44
How can I Flash a mandatory field in Access Form? 13 48
Calculation in Access 5 26
Create macro from runcode 30 26
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of utilizing SQL Server functions 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 Ac…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

809 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