Form problem

[Guest]

I have a text box on the form that i set to run off of a macro. The macro
runs a query that takes the sum of an attribute called meeting_actcost from
the meeting table where the employee_department matches whats put in the
prompt.

I keep getting a error saying " Access cant find the macro ' WHERE Budget'.
I made sure i saved it,so i know it exists. This is the SQL that the query:

SELECT DISTINCTROW Sum(Meeting.meeting_actcost) AS [Sum Of meeting_actcost]
FROM (Budget INNER JOIN Employee ON Budget.employee_department =
Employee.employee_department) INNER JOIN Meeting ON Employee.employee_code =
Meeting.employee_code
WHERE (((Budget.employee_department)=[Enter Branch]));

 

[Tim Ferguson]

I keep getting a error saying " Access cant find the macro ' WHERE
Budget'. I made sure i saved it,so i know it exists. This is the SQL
that the query:

This sounds like a broken SQL creation code: can you post the lines that
create the query and run it?

Tim F

 

[Dirk Goldgar]

I have a text box on the form that i set to run off of a macro. The
macro runs a query that takes the sum of an attribute called
meeting_actcost from the meeting table where the employee_department
matches whats put in the prompt.

I keep getting a error saying " Access cant find the macro ' WHERE
Budget'. I made sure i saved it,so i know it exists. This is the SQL
that the query:

SELECT DISTINCTROW Sum(Meeting.meeting_actcost) AS [Sum Of
meeting_actcost] FROM (Budget INNER JOIN Employee ON
Budget.employee_department = Employee.employee_department) INNER JOIN
Meeting ON Employee.employee_code = Meeting.employee_code
WHERE (((Budget.employee_department)=[Enter Branch]));

I'm not sure what you mean when you say the text box is "run off of a
macro". What is the value of the text box's Control Source property?
Is this macro executed by an event of the form, the text box, or some
other control? If so, what exactly is the event in question, what is in
that event's property line, and what are the actions performed by the
macro?

 

[Guest]

The event is Before update. I just have the macro run the query that i posted
in my question.

I have a text box on the form that i set to run off of a macro. The
macro runs a query that takes the sum of an attribute called
meeting_actcost from the meeting table where the employee_department
matches whats put in the prompt.

I keep getting a error saying " Access cant find the macro ' WHERE
Budget'. I made sure i saved it,so i know it exists. This is the SQL
that the query:

SELECT DISTINCTROW Sum(Meeting.meeting_actcost) AS [Sum Of
meeting_actcost] FROM (Budget INNER JOIN Employee ON
Budget.employee_department = Employee.employee_department) INNER JOIN
Meeting ON Employee.employee_code = Meeting.employee_code
WHERE (((Budget.employee_department)=[Enter Branch]));

I'm not sure what you mean when you say the text box is "run off of a
macro". What is the value of the text box's Control Source property?
Is this macro executed by an event of the form, the text box, or some
other control? If so, what exactly is the event in question, what is in
that event's property line, and what are the actions performed by the
macro?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

The event is Before update. I just have the macro run the query that
i posted in my question.

BeforeUpdate of the form or of the text box, or of some other control?
What *exactly* does the macro say?

 

[Guest]

I placed the macro on the beforeUpdate event of the text box. The action that
the macro performs is RunQuery. The query that it runs is:

SELECT DISTINCTROW Sum(Meeting.meeting_actcost) AS [Sum Of meeting_actcost]
FROM (Budget INNER JOIN Employee ON Budget.employee_department =
Employee.employee_department) INNER JOIN Meeting ON Employee.employee_code =
Meeting.employee_code
WHERE (((Budget.employee_department)=[Enter Branch]));

The query does what i want it to do,but the value that the query spits out
doesn't show up on the form.

 

[Dirk Goldgar]

I placed the macro on the beforeUpdate event of the text box. The
action that the macro performs is RunQuery. The query that it runs is:

SELECT DISTINCTROW Sum(Meeting.meeting_actcost) AS [Sum Of
meeting_actcost] FROM (Budget INNER JOIN Employee ON
Budget.employee_department = Employee.employee_department) INNER JOIN
Meeting ON Employee.employee_code = Meeting.employee_code
WHERE (((Budget.employee_department)=[Enter Branch]));

The query does what i want it to do,but the value that the query
spits out doesn't show up on the form.

I don't think there's any such macro action as "RunQuery". Do you mean
"OpenQuery" or "RunSQL"? RunSQL shouldn't work with a SELECT statement,
as it's only for action queries, and OpenQuery would require that the
SQL be stored as a saved query, and that the name of that query be
passed to the OpenQuery action. Is that what you're actually doing?
Even then, it would have no effect on any control on the form; it would
just open the query as a datasheet.

Could you please step back a bit, and explain in words exactly what it
is you're trying to do with this form? Please understand that I can't
see your form, and I don't know any of the details of your database, so
it's very hard to advise you without more information than you've given
so far.

 

[Guest]

Ok. I have a form with fields called:
employee_division
ibud
cbud
budused

Before you open the form, i made a prompt that will ask you to put in a
department.
The form runs off of a query that contains all of those fields. I made
another query that takes the sum of the values in an attribute in another
table called meeting. I wanted to that query to display the budused value of
a specific department (employee_division)on the form. So, i made a macro that
opens the query and placed it in the beforeUpdate event of the textbox. When
i took the form out of design view, i got the error. When i placed it in the
on open event of the whole form, it pulled up the query,but it didn't place
the info in the text box. I think I solved the problem by just using one
query and modifying it to do the task that I wanted it to do. Here it is:

SELECT DISTINCTROW Sum(Meeting.meeting_actcost) AS budused, Budget.ibud,
Budget.cbud, Budget.employee_department
FROM (Budget INNER JOIN Employee ON Budget.employee_department =
Employee.employee_department) INNER JOIN Meeting ON Employee.employee_code =
Meeting.employee_code
GROUP BY Budget.ibud, Budget.cbud, Budget.employee_department
HAVING (((Budget.employee_department)=[Enter Branch]));

I'll run some more test data to it to see if it runs correctly. Thanks for
all of your help. Sorry for my poor explaination of my problem.

I placed the macro on the beforeUpdate event of the text box. The
action that the macro performs is RunQuery. The query that it runs is:

SELECT DISTINCTROW Sum(Meeting.meeting_actcost) AS [Sum Of
meeting_actcost] FROM (Budget INNER JOIN Employee ON
Budget.employee_department = Employee.employee_department) INNER JOIN
Meeting ON Employee.employee_code = Meeting.employee_code
WHERE (((Budget.employee_department)=[Enter Branch]));

The query does what i want it to do,but the value that the query
spits out doesn't show up on the form.

I don't think there's any such macro action as "RunQuery". Do you mean
"OpenQuery" or "RunSQL"? RunSQL shouldn't work with a SELECT statement,
as it's only for action queries, and OpenQuery would require that the
SQL be stored as a saved query, and that the name of that query be
passed to the OpenQuery action. Is that what you're actually doing?
Even then, it would have no effect on any control on the form; it would
just open the query as a datasheet.

Could you please step back a bit, and explain in words exactly what it
is you're trying to do with this form? Please understand that I can't
see your form, and I don't know any of the details of your database, so
it's very hard to advise you without more information than you've given
so far.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Ok. I have a form with fields called:
employee_division

In your queries, that seems to be employee_department.

ibud
cbud
budused

Before you open the form, i made a prompt that will ask you to put in
a department.
The form runs off of a query that contains all of those fields. I made
another query that takes the sum of the values in an attribute in
another table called meeting. I wanted to that query to display the
budused value of a specific department (employee_division)on the
form. So, i made a macro that opens the query and placed it in the
beforeUpdate event of the textbox. When i took the form out of design
view, i got the error. When i placed it in the on open event of the
whole form, it pulled up the query,but it didn't place the info in
the text box.

That's right. Opening a query will not put a value from that query into
a control on your form, no matter what event you open it in.

I think I solved the problem by just using one query
and modifying it to do the task that I wanted it to do. Here it is:

SELECT DISTINCTROW Sum(Meeting.meeting_actcost) AS budused,
Budget.ibud, Budget.cbud, Budget.employee_department
FROM (Budget INNER JOIN Employee ON Budget.employee_department =
Employee.employee_department) INNER JOIN Meeting ON
Employee.employee_code = Meeting.employee_code
GROUP BY Budget.ibud, Budget.cbud, Budget.employee_department
HAVING (((Budget.employee_department)=[Enter Branch]));

I'll run some more test data to it to see if it runs correctly.
Thanks for all of your help. Sorry for my poor explaination of my
problem.

I think you're probably on the right track with this approach. The only
problem I see with it is that using it as the recordsource for your form
will make the form non-updatable, because totals queries are not
updatable.

Note also that the calculated value [budused] will not be stored in your
Budget table. Normally, that is not a problem, since it's usually a bad
practice to store values that can be calculated whenever you need them.
Sometimes people are confused about this, though, and expect a
calculated value to be stored. That's why I pointed it out.

 

[Guest]

How can i correct the problem associated with making a query the recordsource?

Ok. I have a form with fields called:
employee_division

In your queries, that seems to be employee_department.

ibud
cbud
budused

Before you open the form, i made a prompt that will ask you to put in
a department.
The form runs off of a query that contains all of those fields. I made
another query that takes the sum of the values in an attribute in
another table called meeting. I wanted to that query to display the
budused value of a specific department (employee_division)on the
form. So, i made a macro that opens the query and placed it in the
beforeUpdate event of the textbox. When i took the form out of design
view, i got the error. When i placed it in the on open event of the
whole form, it pulled up the query,but it didn't place the info in
the text box.

That's right. Opening a query will not put a value from that query into
a control on your form, no matter what event you open it in.

I think I solved the problem by just using one query
and modifying it to do the task that I wanted it to do. Here it is:

SELECT DISTINCTROW Sum(Meeting.meeting_actcost) AS budused,
Budget.ibud, Budget.cbud, Budget.employee_department
FROM (Budget INNER JOIN Employee ON Budget.employee_department =
Employee.employee_department) INNER JOIN Meeting ON
Employee.employee_code = Meeting.employee_code
GROUP BY Budget.ibud, Budget.cbud, Budget.employee_department
HAVING (((Budget.employee_department)=[Enter Branch]));

I'll run some more test data to it to see if it runs correctly.
Thanks for all of your help. Sorry for my poor explaination of my
problem.

I think you're probably on the right track with this approach. The only
problem I see with it is that using it as the recordsource for your form
will make the form non-updatable, because totals queries are not
updatable.

Note also that the calculated value [budused] will not be stored in your
Budget table. Normally, that is not a problem, since it's usually a bad
practice to store values that can be calculated whenever you need them.
Sometimes people are confused about this, though, and expect a
calculated value to be stored. That's why I pointed it out.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Rick Brandt]

How can i correct the problem associated with making a query the
recordsource?

You can't. The problem isn't that the RecordSource is a query. It's that
it is a non-updateable query and there is no way to make a totals query
editable.

 

[Dirk Goldgar]

How can i correct the problem associated with making a query the
recordsource?

As Rick said, it's not that you're using a query as a recordsource, it's
that you're using a *totals* query as a recordsource. No totals query
can ever be updatable, because each record it returns repreaents an
aggregation of data from multiple source records.

However, you can probably get around this problem by returning your form
to its original recordsource and using a DSum expression as the
controlsource for your text box. I'm not sure exactly what the DSum
expression should be, though. Could you post the details of the three
tables, "Budget", "Employee", and "Meeting", explain the relationships
between them, and tell what this sum logicallt represents?

 

[Guest]

the realtionship between the department table and budget tables is a 1:M
relationship. They're connected by the employee_department attribute (PK in
department, FK in budget). Department is connected to the meeting table
through the employee table. The Department and Employee table is a 1:M
connection through the employee_department field. The employee and meeting
tables is a 1:M relationship. They're connected by the employee_code
attribute (PK in employee, FK in Meeting).

 

[Guest]

Is it possible to use the results form a prompt as the criteria for the Dsum
expression? the expression i wrote was:

=DSum("[meeting_actcost]","meeting","[employee_department]=[enter branch]")

The prompt messages is the "enter branch" part of the expression.

 

[Dirk Goldgar]

the realtionship between the department table and budget tables is a
1:M relationship. They're connected by the employee_department
attribute (PK in department, FK in budget). Department is connected
to the meeting table through the employee table. The Department and
Employee table is a 1:M connection through the employee_department
field. The employee and meeting tables is a 1:M relationship.
They're connected by the employee_code attribute (PK in employee, FK
in Meeting).

Thanks, that helps. I'm not sure I've figure out exactly what you're
after, so check if these assumptions are correct:

1. Your form is supposed to be based on the table Budget, or on an
updatable query of that table.

2. The form should show the record/all records for a specific
department, to be specified by a prompt when the user opens the form.

3. You want to show on that form the sum of Meeting.meeting_actcost for
every employee in that department.

If these assumptions are correct, I think I might handle it as follows:

1. Create a new query named "qryDeptMeetingCost" with SQL like this:

SELECT
employee_department As Dept,
Sum(meeting_actcost) As budused
FROM Employee INNER JOIN Meeting
ON Employee.employee_code = Meeting.employee_code
GROUP BY employee_department;

2. Let your form be based on a recordsource query like this:

SELECT
employee_department
ibud,
cbud,
DLookup("budused", "qryDeptMeetingCost",
"Dept='" & [employee_department] & "'")
FROM Budget
WHERE employee_department = [Enter Branch];

Note: that assumes that the department field is a text field, which it
may not be. If it's a number field, then the criterion for the DLookup
expression should be

"Dept=" & [employee_department])

I have hope that something along those lines is what you're looking for.

 

[Guest]

Thanks. It works.

the realtionship between the department table and budget tables is a
1:M relationship. They're connected by the employee_department
attribute (PK in department, FK in budget). Department is connected
to the meeting table through the employee table. The Department and
Employee table is a 1:M connection through the employee_department
field. The employee and meeting tables is a 1:M relationship.
They're connected by the employee_code attribute (PK in employee, FK
in Meeting).

Thanks, that helps. I'm not sure I've figure out exactly what you're
after, so check if these assumptions are correct:

1. Your form is supposed to be based on the table Budget, or on an
updatable query of that table.

2. The form should show the record/all records for a specific
department, to be specified by a prompt when the user opens the form.

3. You want to show on that form the sum of Meeting.meeting_actcost for
every employee in that department.

If these assumptions are correct, I think I might handle it as follows:

1. Create a new query named "qryDeptMeetingCost" with SQL like this:

SELECT
employee_department As Dept,
Sum(meeting_actcost) As budused
FROM Employee INNER JOIN Meeting
ON Employee.employee_code = Meeting.employee_code
GROUP BY employee_department;

2. Let your form be based on a recordsource query like this:

SELECT
employee_department
ibud,
cbud,
DLookup("budused", "qryDeptMeetingCost",
"Dept='" & [employee_department] & "'")
FROM Budget
WHERE employee_department = [Enter Branch];

Note: that assumes that the department field is a text field, which it
may not be. If it's a number field, then the criterion for the DLookup
expression should be

"Dept=" & [employee_department])

I have hope that something along those lines is what you're looking for.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)