Creating a form to modify query

[peterdavid]

Hi - I'm new to Access, but not programming or SQL. I will be using Access as a report writer by connecting to a corporate Unix RDBMS. Pretty good so far.. and cheap! I have created a bunch of queries: they work fine.

I want to be able to create a window, with a form that will allow the user to enter data (date, date range, state, user name) that will modify the query and then allow the query to run. At this time, I only know how to create the query in design mode.

Thanks

 

[Cagliostro]

Create a new form, select it(menu edit->selectForm) and in the properties look up 'record source' and put he name of your query, table or the query text. John Fill
ivfmd@mail.md

 

[MichaelRed]

Make all of the queries "Parameter" queries. To the extemt pratical, make your parameters have the same name for the same data (e.g. [StrtDt] for all starting (or only) dates.

Go through each of the queries and list the parameters which they require.

Create a Frame for each parameter necessary. In that frame. place each of the parameter input controls.

Create one combo box which lists all of the queries/report record sources.

When a User has selected a specifis report, make the frames for the relevant parameters visible, and the other frmes not-visible.

Include a command button to "Execute" the report. In the click event, validate the parameters. If the validation fails, do the msg box to tell the user where they went awry and set the focus to that control. If the validation 'passes' open the report.

I usually employ a tab control for this process, placing various groups or steps on different tabs. For instance, the reports and any common (to ALL reports) parameters go on a single Tab, with the various optional (depending on the report) go on a seperate tab. Further, I generally default the parameters to certain values, based on the report. If it it is a 'weekly' report, I would default the start date th the Monday (perhaps Sunday) of the PREVIOUS week, and the End Date to 7 days later.

Another time saver is to generally provide 'calculated' parameters through check boxes or option groups (again, the example is for dates. Suppose that the report could be a daily/weekly/monthly or 'selected'. Providing an option gropup (again defaulted to the most common) lets the user get away with only entering a single date/item. Providing a second set of option buttons for current, previous, selected also simplfies the Users task - and more or less assures the correct date input (since YOU will then provide the date values).


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[Terpsfan]

I'd create an unbound form in design view and add the necessary fields you need for your query. Then add a command button to run the query. Then add a fully qualified reference of the form field to you query criteria.
For instance, say one of your query fields is LastName. On your form, you would name the field txtLastName. Let's say you name your form FORM. In the criteria of your query for LastName enter =Forms!FORM!txtLastName. This creates a parameterized query based on the value you enter in the txtLastName field on FORM. Just add additional fields as needed to the form for your criteria. Some other options are to use the Calendar Control activeX control. It's a graphical utility to select dates as criteria for running queries and reports. Go into the help for more info.