Parameter Dialog (2000)

Topic

New Reply

I have a continuous form generated from a Select Query requesting date parameters.

Field: ShCondate1
Source: qryLoadBoardDateParameter
Criteria: Between [Enter beginning load date:] And [Enter ending load date:]

This works just fine, but for the fact that the pop-up has no input mask which makes it the only place in the db that requires the operator to enter the “/” between the digits (there is an input mask for all date entries elsewhere). When I was first asked to make this change, I thought I could just create a custom dialog form and be done with it.

I tried creating the custom form with a text box bound to the ShCondate1 field from the same table source as the query. Right away I realized that I would need two of these text boxes (beginning and ending dates) and how could they both have the same source? I had never made a custom dialog form before so I thought maybe I’d just try to get the beginning date box to work before going any further. I created the form and then enter the following into the criteria of the query:

[Forms]![frmBeginningDate]![ShCondate1]

No way Jose’. All that did was bring up the same Access dialog with the same text as the criteria input and, of course, no input mask. I’m not sure where I lost my way here, but I know I’ve broken a cardinal rule of logic – I’m becoming emotional. I’ve been through the help files and my A97 book, but can’t seem to locate anything that clears up the issue. Great learning opportunity here, but I can’t see the forest for the trees. Any help greatly appreciated!

Reply | Quote

Replies

In order to make this kind of thing work, you want to change from the Criteria you’ve used to:

Criteria: Between [Forms]![frmEnterDates]![StartDate] And [Forms]![frmEnterDates]![EndDate]

The form frmEnterDates should be an unbound form (or at least unbound controls) the require a date to be entered, and can have an input mask. Once both fields have been filled in (you may need some VBA logic to check that, or you could use validation rules), then the query behind your continuous form should work fine, and the user will have the “/” characters provided by the mask. If this doesn’t make sense, post back and I’ll do my best to explain.

Reply | Quote

No cigar.

I created a new unbound form (frmEnterDates) with two text boxes (StartDate and EndDate) with the input masks for short date. I went back to the qry and entered the following in the criteria:

Between [Forms]![frmEnterDates]![StartDate] And [Forms]![frmEnterDates]![EndDate]

I’m still getting the default Access parameter window with the text “[Forms]![frmEnterDates]![StartDate]” displayed. If you make an entry here (still no input mask) it takes you to the next Access default parameter window with the text “[Forms]![frmEnterDates]![EndDate]” displayed. It is not opening the custom form.

Reply | Quote

I presume you put dates in each of the fields before you tried to open the query. In that case there is likely to be a spelling error, though sometimes you do have to declare parameters in the query design with Query/Parameters – beats me why you occasionally will have to do that to make it work, but most of the time you don’t need to. Now in order to open the custom form, you need something to say “open the custom form” either as a macro or in code – I would suggest you use the Command Button Wizard to create a simple VBA routine to open the form. Hope this helps.

Reply | Quote

OK. I can’t even see the trees now.

You don’t mean to put a Command Button on the query do you? How do you do that?
The frmLoadBoardDateParameter is based on the qfltLoadBoardDateParameter and doesn’t even open until the query parameters are entered.

I tried to look at this a little differently by placing a command button on the form and using the on click event to call mcrqfltLoadBoardDateParameter, but still get the same default Access parameter window. Still no custom form.

Reply | Quote

This becomes a bit like the “Chicken ot the Egg” problem, which came first?
What I would do is along the lines of what Wendell has suggested, but introduce a new form (frmEnterDates as you suggest) for the input of dates only (and don’t use the short date as the input mask, but use 99/99/00;0 instead). After both dates have been entered then open the continuous form frmLoadBoardDateParameter from this form.
Don’t forget to change the criteria in the query qfltLoadBoardDateParameter to [Forms]![frmEnterDates]![StartDate] And [Forms]![frmEnterDates]![EndDate] .

HTH
Pat

Reply | Quote

Thanks Pat, I think our posts just passed each other.

I think I came up with an idea that follows your logic, but without utilizing the custom dialog.

Thanks!

Reply | Quote

An aside to this is being able to reference the parameter fields on the form to include the criteria in the report header or report page header. Simply create an unbound text box on the report and put something like:

“Tickets Cleared ” & [Forms]![frmMENU]![Begin] & ” Through ” & [Forms]![frmMENU]![End]

to print “Tickets Cleared 12/2/2002 Through 12/6/2002” at the top of the report or report pages.

….sorry to interrupt this thread!

Howard

Reply | Quote

Thanks Howard, but there are no “reports” run from this form.

Reply | Quote

Finally, an answer. Not the one they asked for, but one they like even better.

I abandoned the thought of using a custom dialog form for the input. One, because I wasn’t getting anywhere; and two, because in the middle of all this they wanted the parameters to be displayed on the open form. (apparently people loose track of what dates they have entered).

I now have two text boxes in the form header for start and end dates, with a Cmd button to apply the dates. The on click event runs a macro (apply filter) which runs the query with the parameters designated on the form. Works great and everyone is happy – the date parameters are even shown in the header.

I suppose (guess) that the criteria option in the select query only permits input from the source of the query. In using unbound text boxes on the form, and then the command button to run the query, a frm filter is created with the date parameter. This only works after the form has been opened, so I had to change the form source to the table the qry is based on rather the the qry itself.

Thanks for your input!!

Reply | Quote

You said:
<>
What you could do is:
1. Put a default for the dates to today, or
2. you could set Data Entry to yes to limit the form initially to not show existing records.
So there is no need to use the table rather than the query.

What did you mean by:
<>

HTH
Pat

Reply | Quote

I did manage to put the default as =Date() so the form would display at least something.

<>
I didn’t put that very well. What I meant to say was that it appears that you can’t simply refer to [Forms]![frmLoadBoardDateParameter]![StartDate] without having provided some way for the form to know that it needs to run the filter. Even when the date parameter is entered on the form, it doesn’t have “permission” to run the filter until the CmdApply is clicked. What I had been trying to do with a custom dialog form wasn’t working because Access was treating the criteria entry as parameter text. That’s why I kept getting the criteria in the default dialog. I guess.

Reply | Quote

Actually what we often do in cases like this is to change the DataSource of the form on the fly based on unbound text boxes (in the header as you suggest works fine), and then build the SQL string that pulls in the value that way. That avoids the issue with parameter queries, and works fairly elegantly. In those cases we may hide the detail section of the form until the Data Source has been set, and then unhide it. That way the user doesn’t see data until they have made the entries. Just another way of “skinning the cat.”

Reply | Quote