Need One Form to Capture All Query Criterias

G

Guest

My database uses switchboard buttons, which are driven by queries, macros or
codes.
My CURRENT POSITION: Example, right now to generate a report, I click on the
EXPENSE REPORTS button, it prompts me for START DATE, I input and press OK.
Then prompts me for END DATE, I enter and press OK, and a report is generated.

I have another reports that ask for start dates, end dates, start cost
center and end cost center. Each time the user has to make an input and click
OK.

QUESTION: How do you create one form that will capture ALL criteria of a
query.
i.e. one form that has, say, 2 fields called START DATE and END DATE. The
user can now enter the info and press OK just once.

I hope I have been able to create a clear picture, and you can help. I
appreciate the help that this community offers. Thanks,

Godwin
 
J

Joan Wild

Create a form and put all the necessary textboxes, command buttons, etc.
that you need.

In your queries change the criteria to reference the controls on this form.
i.e. Instead of Start Date as a criteria put
Forms!frmCriteria!txtStart
Substitute the name of you form for frmCriteriaand the name the textbox for
txtStart
 
G

Guest

Thanks Joan for your quick reposnse. I have created the form and added all
reqd controls. For each criteria (Date, Cost Center and Company - I have a
FROM and TO input box). On the query, I changed the criteria to:

BETWEEN Forms!frmCostCtrExpense!txtFromDate AND
Forms!frmCostCtrExpense!txtToDate

However, I get an error msg:
You did not enter the keyword AND in the Between...And Operator. The correct
syntax is...

Please help. Thanks,
 
G

Guest

Joan, IT WORKS!!!. Thanks


chessMaster said:
Thanks Joan for your quick reposnse. I have created the form and added all
reqd controls. For each criteria (Date, Cost Center and Company - I have a
FROM and TO input box). On the query, I changed the criteria to:

BETWEEN Forms!frmCostCtrExpense!txtFromDate AND
Forms!frmCostCtrExpense!txtToDate

However, I get an error msg:
You did not enter the keyword AND in the Between...And Operator. The correct
syntax is...

Please help. Thanks,
Click to expand...
 
G

Guest

I have done as suggested. I have set the Switchboard item to open the new
form. However, when I input the dates, cost ctr, etc and press OK it still
opens a new dialog for each of the item separately. I have tried setting the
propery of the OK button to Open Report, Run Query, etc.

In other words, it's running the query again. How do I resolve this so that
my entry into the form is auto transfered to the query criteria.

Once again, thanks for your time.
 
J

Joan Wild

Check the properties on the query that the old parameter isn't still sitting
somewhere (in the filter property or in View, Parameters. Check the report
properties as well, and also the Sorting and Grouping dialog and any code.
 
G

Guest

I must be doing something wrong! As a test, in design view I pressed RUN.
Ideally, the new form should open so that I can enter the reqd info. Instead,
it's opening different dialog boxes for each criteria.

Right now, the criterias are:

COST CENTER:
BETWEEN [Forms]![frmCostCtrExpense]![txtFromCostCtr] AND
[Forms]![frmCostCtrExpense]![txtToCostCtr]

DATE:
BETWEEN [Forms]![frmCostCtrExpense]![txtFromDate] AND
[Forms]![frmCostCtrExpense]![txtToDate]

Thanks!!
 
J

Joan Wild

I'm sorry I wasn't clear. You must have the form open and the textboxes
filled in before you run the query/report.

You can add a command button to your form to open the report.

If the form isn't open first, then the query will prompt you for the Form
reference, since it can't 'see' it.


--
Joan Wild
Microsoft Access MVP
I must be doing something wrong! As a test, in design view I pressed
RUN. Ideally, the new form should open so that I can enter the reqd
info. Instead, it's opening different dialog boxes for each criteria.

Right now, the criterias are:

COST CENTER:
BETWEEN [Forms]![frmCostCtrExpense]![txtFromCostCtr] AND
[Forms]![frmCostCtrExpense]![txtToCostCtr]

DATE:
BETWEEN [Forms]![frmCostCtrExpense]![txtFromDate] AND
[Forms]![frmCostCtrExpense]![txtToDate]

Thanks!!

Joan Wild said:
Check the properties on the query that the old parameter isn't still
sitting somewhere (in the filter property or in View, Parameters.
Check the report properties as well, and also the Sorting and
Grouping dialog and any code.
Click to expand...
Click to expand...
 
G

Guest

Joan, thanks so much. It works perfectly now.

Joan Wild said:
I'm sorry I wasn't clear. You must have the form open and the textboxes
filled in before you run the query/report.

You can add a command button to your form to open the report.

If the form isn't open first, then the query will prompt you for the Form
reference, since it can't 'see' it.


--
Joan Wild
Microsoft Access MVP
I must be doing something wrong! As a test, in design view I pressed
RUN. Ideally, the new form should open so that I can enter the reqd
info. Instead, it's opening different dialog boxes for each criteria.

Right now, the criterias are:

COST CENTER:
BETWEEN [Forms]![frmCostCtrExpense]![txtFromCostCtr] AND
[Forms]![frmCostCtrExpense]![txtToCostCtr]

DATE:
BETWEEN [Forms]![frmCostCtrExpense]![txtFromDate] AND
[Forms]![frmCostCtrExpense]![txtToDate]

Thanks!!

Joan Wild said:
Check the properties on the query that the old parameter isn't still
sitting somewhere (in the filter property or in View, Parameters.
Check the report properties as well, and also the Sorting and
Grouping dialog and any code.

--
Joan Wild
Microsoft Access MVP

chessMaster wrote:
I have done as suggested. I have set the Switchboard item to open
the new form. However, when I input the dates, cost ctr, etc and
press OK it still opens a new dialog for each of the item
separately. I have tried setting the propery of the OK button to
Open Report, Run Query, etc.

In other words, it's running the query again. How do I resolve this
so that my entry into the form is auto transfered to the query
criteria.

Once again, thanks for your time.


:

Create a form and put all the necessary textboxes, command buttons,
etc. that you need.

In your queries change the criteria to reference the controls on
this form. i.e. Instead of Start Date as a criteria put
Forms!frmCriteria!txtStart
Substitute the name of you form for frmCriteriaand the name the
textbox for txtStart


--
Joan Wild
Microsoft Access MVP

chessMaster wrote:
My database uses switchboard buttons, which are driven by queries,
macros or codes.
My CURRENT POSITION: Example, right now to generate a report, I
click on the EXPENSE REPORTS button, it prompts me for START DATE,
I input and press OK. Then prompts me for END DATE, I enter and
press OK, and a report is generated.

I have another reports that ask for start dates, end dates, start
cost center and end cost center. Each time the user has to make an
input and click OK.

QUESTION: How do you create one form that will capture ALL
criteria of a query.
i.e. one form that has, say, 2 fields called START DATE and END
DATE. The user can now enter the info and press OK just once.

I hope I have been able to create a clear picture, and you can
help. I appreciate the help that this community offers. Thanks,

Godwin
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Capture Dates from Form, Put at Heading of Report 2
Report / Query sums 2
Need Help Linking? 1
Access Form values to Report to Query 1
Changed query, Form went Blank 2
Duplicate prompts in crosstab query and subsequent report 1
How do I show the [Start date] and [End Date] on form? 4
order of prompt from queries for Form 1

Top