DSum help

[Bobby.Dannels]

Being new to vba and Access, I think what I am trying to do is out of
my realm of understanding. I have two querries that query the same
table "QtyUsed". One query "QtyUsed6Mo" filters results by the date
field to records within the last 6 months. The other "QtyUsedYr"
filters within the last year. I want to create a DSum that will add
all the records in the query that have the same "NSN" and add the qty
"Used" to another table "DataTable.QtyUsed6Mo and DataTable.QtyUsedYr"

 

[Jeanette Cunningham]

Bobby,
Usually it is not good practice to store these calculations in a table.
You can always use the query, both queries 6Mo and Yr to give the calculated
values for a form's recordsource or for a report.
Using the query instead of a table, gives you the most up-to-date value for
the values. It also avoids the problem that can happen when one of the dates
is edited and this changes the value for QtyUsed. If the calculation was in
a table, you would need to change the value in the table every time one of
the dates was edited - that is too much work - much easier to just use the
query.
Note: there are some databases that do need to store a calculated value in a
table, but this is not very frequent.

Jeanette Cunningham

 

[Bobby.Dannels]

Can I not write a code that will update the table every time the value
in the query changes? If not, how would I write the code that I need
to have the form pull straight from the query?

 

[Bobby.Dannels]

Can I not write a code that will update the table every time the value
in the query changes? If not, how would I write the code that I need
to have the form pull straight from the query?

More importantly how would I get the query to filter the table by the
nsn that is currently being displayed in the form?

 

[Jeanette Cunningham]

Yes you could write code to do this. What happens if someone imports data
into the table? It is not best practice to store the calculation.
To have the form pull straight form the query - you would do a DLookup to
the query and put the result in a label or textbox.

Jeanette Cunningham

 

[Jeanette Cunningham]

In design view of the query, in the criteria row for nsn, type the correct
syntax for the name of the nsn control on the form.
Forms!YourFormName!TheControlName - assuming a form that is not a subform.
As long as the form is open, the query will be able to get the nsn value
from the form.

Jeanette Cunningham

Can I not write a code that will update the table every time the value
in the query changes? If not, how would I write the code that I need
to have the form pull straight from the query?

More importantly how would I get the query to filter the table by the
nsn that is currently being displayed in the form?