continuous subform with uneditable query

  • Thread starter Thread starter MattE
  • Start date Start date
M

MattE

I'm stuck!

I have a continuous subform where each line shows a different material
used in the given job and the material's quantity and price and
quantity * price. Most materials are listed more than once (because
they're used in multiple areas of the job) so the quantity is SUMmed in
this query. Now the user needs to be able to edit the price of the
material for this job. But the query's not editable I guess because of
the SUM. Is there a way I can tweak the query to make this work or am
I going to have to provide another place to modify the price of the
material for the job?

Any direction on this would be much appreciated! I seem to have a
brain lock about this.
 
MattE said:
I'm stuck!

I have a continuous subform where each line shows a different material
used in the given job and the material's quantity and price and
quantity * price. Most materials are listed more than once (because
they're used in multiple areas of the job) so the quantity is SUMmed
in this query. Now the user needs to be able to edit the price of the
material for this job. But the query's not editable I guess because
of the SUM. Is there a way I can tweak the query to make this work
or am I going to have to provide another place to modify the price of
the material for the job?

Any direction on this would be much appreciated! I seem to have a
brain lock about this.
Click to expand...

You cannot make any query that aggregates (View Totals) editable.
 
So, Matt, one possible way to handle it is to provide an unbound textbox
on the form, for the user to enter the price for the current material,
and then use code to run an Update Query to write this amount to all
applicable records in the underlying table.

Technically, you have a one-to-many relationship between the price of
the material for the job, and the records of where that material is
used. Some people may consider a separate table, to record the price
for each material for each job, but to me that is "over-normalised".
 
Hi ,

How is everything going? Please feel free to let me know if you need any
assistance.

Sincerely,

Wei Lu
Microsoft Online Community Support

==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Back
Top