multiple queries in form

[geraldk]

Hello,



Please forgive if someone else ask this question. I am programming a new database for my work. I am having a problem to get two queries in a form. I have query to bound some text boxes into my form. I need to add another query for some calculation and display the results in a text box. Right now I have tried to put Control Source into a macro and when the form is ran it comes with #Name? error.

If someone has some suggestions to me it would be appreciated.

Thank you in advance.

Season Greetins!

Using Access 2010 32bit.

[June7]

We need to know more about data structure and the data manipulation you want to do. It is possible one query can accomplish what you want or maybe a form/subform arrangement is called for.

[Rainlover]

One Form can only have one Query as its Record Source.

Although that one Query could comprise multiple Queries/Tables.

What does this Calculation Query do? What is its Record Source. Is it the same as the Main Query.

[geraldk]

I beleive I have this database set up as ADO format.

This is the query that I have setup for control for the form:

SELECT tblEmpVac.EMPID, tblEmpVac.VACID, tblEmpVac.EMPVACDate, tblEmpVac.EMPVACID
FROM tblEmpVac;

This is the unbound sql that I want to display at another text box on the form.

SELECT Count(vacid) AS VAC
FROM tblempvac
WHERE (((tblempvac.vacid)=1) And ((tblempvac.empid)=forms!frmEmployee!EmpID));

Basicly this database is employee database for HR department to keep track of some vacations days that they taken.

[Rainlover]

Have a look at Access Help or Google the Function "DSum"

This will give you what you need instead of using the second Query.

If you really wanted to do this with a Query you would have to do it in Code and create a Recordset Clone.

Finally, could you not add the where clause to the first query or do you want the Form to show all records which include those outside of your where conditions.

[June7]

Is purpose of this form to enter vacation records? You want to count the number of vacation days for each employee for each vacation ID? Yes, could use DSUM function to show the count in a textbox. Domain aggregate functions can slow performance but if not too many records might not be so noticeable. Ideally, create a report to output this aggregate data.

The form RecordSource includes all records. The Count you show is limited to a specific VACID. This will put the Count out of sync with the form. Need to either filter the form RecordSource to specific VACID or also use dynamic parameter as you have done with the EMPID.