I have a form frmVendors with a subform frmPayments. The main form shows
information on vendors, and the subform is a continuous form which lists the
checks paid out to each vendor. Users are typically entering new payments
into the subform on the main form.

The forms are linked to tables: Linked by VendorID (one to many)
tblVendor: Key=VendorID, Vendor Name, Vendor Address, Vendor State, Vendor
City
tblPayments: Key=CheckID, VendorID, PaymentAmount

There is a field in tblVendor called NumberChecks to show the total amount
of checks paid out to that vendor. There is a text box on the main form to
show NumberChecks. Right now, the user must increment NumberChecks manually
when they add a new payment amount.

I need a way to count the number of checks for each VendorID in tblPayments,
and store it in tblVendors in the NumberChecks field. I also want the
NumberChecks field on frmVendors to update when a new payment is added or if
a payment is deleted.
Any Ideas?

Stop storing the "number of checks"! This value can be derived using a
simple Totals query, or by using the DCount() function.

That way, no one needs to remember to update the field.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"adna" <(E-Mail Removed)> wrote in message
news:F6F614FB-E27C-491A-AB5D-(E-Mail Removed)...
>I have a form frmVendors with a subform frmPayments. The main form shows
> information on vendors, and the subform is a continuous form which lists
> the
> checks paid out to each vendor. Users are typically entering new payments
> into the subform on the main form.
>
> The forms are linked to tables: Linked by VendorID (one to many)
> tblVendor: Key=VendorID, Vendor Name, Vendor Address, Vendor State,
> Vendor
> City
> tblPayments: Key=CheckID, VendorID, PaymentAmount
>
> There is a field in tblVendor called NumberChecks to show the total amount
> of checks paid out to that vendor. There is a text box on the main form
> to
> show NumberChecks. Right now, the user must increment NumberChecks
> manually
> when they add a new payment amount.
>
> I need a way to count the number of checks for each VendorID in
> tblPayments,
> and store it in tblVendors in the NumberChecks field. I also want the
> NumberChecks field on frmVendors to update when a new payment is added or
> if
> a payment is deleted.
> Any Ideas?
>

You shouldn't store calculated values. As fellow MVP John Vinson likes to
say, "Storing derived data such as this in your table accomplishes three
things: it wastes disk space; it wastes time (almost any calculation will be
MUCH faster than a disk fetch); and most importantly, it risks data
corruption. If one of the underlying fields is subsequently edited, you will
have data in your table WHICH IS WRONG, and no automatic way to detect that
fact. "

Use a query to calculate the total. The SQL would be something like:

SELECT VendorID, [Vendor Name], [Vendor Address], Count(*) AS NumberChecks
FROM tblVendor INNER JOIN tblPayments
ON tblVendor.VendorID = tblPayments.VendorID
GROUP BY VendorID, [Vendor Name], [Vendor Address]

Use that query anywhere you would otherwise have used tblVendor.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"adna" <(E-Mail Removed)> wrote in message
news:F6F614FB-E27C-491A-AB5D-(E-Mail Removed)...
>I have a form frmVendors with a subform frmPayments. The main form shows
> information on vendors, and the subform is a continuous form which lists
> the
> checks paid out to each vendor. Users are typically entering new payments
> into the subform on the main form.
>
> The forms are linked to tables: Linked by VendorID (one to many)
> tblVendor: Key=VendorID, Vendor Name, Vendor Address, Vendor State,
> Vendor
> City
> tblPayments: Key=CheckID, VendorID, PaymentAmount
>
> There is a field in tblVendor called NumberChecks to show the total amount
> of checks paid out to that vendor. There is a text box on the main form
> to
> show NumberChecks. Right now, the user must increment NumberChecks
> manually
> when they add a new payment amount.
>
> I need a way to count the number of checks for each VendorID in
> tblPayments,
> and store it in tblVendors in the NumberChecks field. I also want the
> NumberChecks field on frmVendors to update when a new payment is added or
> if
> a payment is deleted.
> Any Ideas?
>

if your subform is filtered to contain only checks for a single vendor you
can use the recordcount property of its recordset object to get the number of
checks for that vendor.

Or you could write a VBA function to open a recordset, run some SQL
statement, and return the record count of the recordset. Pretty much the
same effect, but wouldn't require the subform to be filter.. or even be open.


"adna" wrote:

> I have a form frmVendors with a subform frmPayments. The main form shows
> information on vendors, and the subform is a continuous form which lists the
> checks paid out to each vendor. Users are typically entering new payments
> into the subform on the main form.
>
> The forms are linked to tables: Linked by VendorID (one to many)
> tblVendor: Key=VendorID, Vendor Name, Vendor Address, Vendor State, Vendor
> City
> tblPayments: Key=CheckID, VendorID, PaymentAmount
>
> There is a field in tblVendor called NumberChecks to show the total amount
> of checks paid out to that vendor. There is a text box on the main form to
> show NumberChecks. Right now, the user must increment NumberChecks manually
> when they add a new payment amount.
>
> I need a way to count the number of checks for each VendorID in tblPayments,
> and store it in tblVendors in the NumberChecks field. I also want the
> NumberChecks field on frmVendors to update when a new payment is added or if
> a payment is deleted.
> Any Ideas?
>

Hi all,
Thanks for the advice. No one ever told me not to store calculated fields
(i'm self taught) but it does make sense. I had been working on a solution
where I had a hidden field txtCheckCount on the subform with control source
=Count([PaymentAmount]).
Then on the main form I had an unbound field with control source
=Forms!frmVendor![frmPayment]!txtCheckCount

This was working to show the number of checks but I wasn't able to store it
in the database. Now I will simply take the NumberChecks field out of
tblVendor calculate the value where needed.

Thanks!

"adna" wrote:

> I have a form frmVendors with a subform frmPayments. The main form shows
> information on vendors, and the subform is a continuous form which lists the
> checks paid out to each vendor. Users are typically entering new payments
> into the subform on the main form.
>
> The forms are linked to tables: Linked by VendorID (one to many)
> tblVendor: Key=VendorID, Vendor Name, Vendor Address, Vendor State, Vendor
> City
> tblPayments: Key=CheckID, VendorID, PaymentAmount
>
> There is a field in tblVendor called NumberChecks to show the total amount
> of checks paid out to that vendor. There is a text box on the main form to
> show NumberChecks. Right now, the user must increment NumberChecks manually
> when they add a new payment amount.
>
> I need a way to count the number of checks for each VendorID in tblPayments,
> and store it in tblVendors in the NumberChecks field. I also want the
> NumberChecks field on frmVendors to update when a new payment is added or if
> a payment is deleted.
> Any Ideas?
>