How to "POST" accounting data into underlying tables

[FJquestioner]

I have a form to record customer payment receipts into a Receipts table
indicating customer, amount, date etc... Within that form is a
subform based on a query that selects all open invoices to that customer
from the Invoices table. I want to be able to allocate the receipt among
those invoices that are being paid (in full or in part) into a Receipts
Allocation table.

Receipts table is joined to Receipts Allocation table by ReceiptsID.

Receipts Allocation table is then joined to the Invoices table by InvoicesID.

So the Receipts Allocation table sits in the middle of the other two and is
joined to both by their respective Primary keys.


So within the Form for entering Receipts I've got a subform in datasheet
view that shows the open invoices to the customer making the payment.
Ideally, beside each of these invoice records I'd like to have fields for
Previous Payments and Outstanding Balance (derived from a query on the
Receipts Allocation table) and Amount Being Allocated and a Checkbox to
indicate whether that payment closes the invoice or not (both fields which
will ultimately be fed into the Receipts Allocation Table).

However, the first time a receipt is received for a given invoice, there is
no previous record of the invoice in the Receipts Allocation table. So when I
run the query for previous payments it returns a null set. And similarly the
only way I can indicate the Amount Being Allocated into the Receipts
Allocation table is if I manually type in the associated InvoiceID first
which is repetitive and prone to error.

It seems to me that in other systems, a temporary holding table is used to
input the receipt allocation info which is then "posted" into the underlying
real table. I'm not sure if this intermediary step is necesary but in any
event I'd still need to somehow "post" the Primary key (InvoiceID) from the
subform of open invoices into a new record in the Receipts Allocation table
so I can then fill in the other fields of that record. [This "posting" issue
will occur several times in my accounting database.]

And the tough part is I know nothing about coding. I'm trying to accomplish
this using macros.

Any assistance or suggestions would be greatly appreciated !

 

[Steve Schapel]

FJ,

I would recommend the following changes to your model:

1. Remove the Amount field from the Receipts table. This is confusing
and redundant. The amounts are being allocated against the invoices,
and are to be stored in the Receipts Allocation table. The total amount
for the receipt is always easily derivable when required.

2. Forget the idea of a InvoiceClosed checkbox. This is unnecessarily
complicated. This information is easilt derivable when required, based
on the difference between the payments received and the invoice amount.

3. Forget the idea of a temporary holding table. This is unnecessarily
complicated.

4. It doesn't work to have the subform show the invoices - you can't
really enter the required data with that structure. Remove this subform.

On your Receipts form, you need a subform based on the Receipts
Allocation table. All you need on this form is two controls, being for
the Invoice and the Amount. The ReceiptsID is automatically taken care
of via the Link Master Fields and Link Child Fields settings for the
subform. There will also be the I recommend a continuous view form.
The Invoice field can be represented as a Combobox, which will list all
the outstanding invoices for that customer, and show the outstanding
amount in one of the combobox columns. This is fairly easy to work out.
When you select an invoice in the combobox, the outstanding amount can
be written (using a SetValue macro) as the default amount to the Amount
field, but you can edit it of course if necessary. The total amount can
be shown in a control in the footer or header of the subform, for you to
cross-check against the total payment received.

--
Steve Schapel, Microsoft Access MVP

I have a form to record customer payment receipts into a Receipts table
indicating customer, amount, date etc... Within that form is a
subform based on a query that selects all open invoices to that customer
from the Invoices table. I want to be able to allocate the receipt among
those invoices that are being paid (in full or in part) into a Receipts
Allocation table.

Receipts table is joined to Receipts Allocation table by ReceiptsID.

Receipts Allocation table is then joined to the Invoices table by InvoicesID.

So the Receipts Allocation table sits in the middle of the other two and is
joined to both by their respective Primary keys.


So within the Form for entering Receipts I've got a subform in datasheet
view that shows the open invoices to the customer making the payment.
Ideally, beside each of these invoice records I'd like to have fields for
Previous Payments and Outstanding Balance (derived from a query on the
Receipts Allocation table) and Amount Being Allocated and a Checkbox to
indicate whether that payment closes the invoice or not (both fields which
will ultimately be fed into the Receipts Allocation Table).

However, the first time a receipt is received for a given invoice, there is
no previous record of the invoice in the Receipts Allocation table. So when I
run the query for previous payments it returns a null set. And similarly the
only way I can indicate the Amount Being Allocated into the Receipts
Allocation table is if I manually type in the associated InvoiceID first
which is repetitive and prone to error.

It seems to me that in other systems, a temporary holding table is used to
input the receipt allocation info which is then "posted" into the underlying
real table. I'm not sure if this intermediary step is necesary but in any
event I'd still need to somehow "post" the Primary key (InvoiceID) from the
subform of open invoices into a new record in the Receipts Allocation table
so I can then fill in the other fields of that record. [This "posting" issue
will occur several times in my accounting database.]

And the tough part is I know nothing about coding. I'm trying to accomplish
this using macros.

Any assistance or suggestions would be greatly appreciated !

 

[FJquestioner]

Steve,

Thanks for the suggestion. I like the simplicity. Just a couple of issues:

1) I need the checkbox to indicate a closed invoice because they are
frequently closed prior to being received in full.

2) The main problem I can't surmount is that when I make the query to feed
into the InvoiceID combobox I can get the selection to work perfectly IF
there have been previous payments (ie. if the InvoiceID already exists in
the Receipt Allocation table. However, if its a first time payment on that
invoice then when the query attempts to Sum any previous payments to the open
invoices from the Receipt Allocation table, it comes up completely blank
because the InvoiceID number doesn't exist within the Receipt Allocation
table. So rather than getting a selection of open invoices with a $0 for
prior payments, the whole query comes up Null.

I suspect there's some simple way around this but I'm stumped.

I'd really appreciate it if you could solve that one for me!

FJ,

I would recommend the following changes to your model:

1. Remove the Amount field from the Receipts table. This is confusing
and redundant. The amounts are being allocated against the invoices,
and are to be stored in the Receipts Allocation table. The total amount
for the receipt is always easily derivable when required.

2. Forget the idea of a InvoiceClosed checkbox. This is unnecessarily
complicated. This information is easilt derivable when required, based
on the difference between the payments received and the invoice amount.

3. Forget the idea of a temporary holding table. This is unnecessarily
complicated.

4. It doesn't work to have the subform show the invoices - you can't
really enter the required data with that structure. Remove this subform.

On your Receipts form, you need a subform based on the Receipts
Allocation table. All you need on this form is two controls, being for
the Invoice and the Amount. The ReceiptsID is automatically taken care
of via the Link Master Fields and Link Child Fields settings for the
subform. There will also be the I recommend a continuous view form.
The Invoice field can be represented as a Combobox, which will list all
the outstanding invoices for that customer, and show the outstanding
amount in one of the combobox columns. This is fairly easy to work out.
When you select an invoice in the combobox, the outstanding amount can
be written (using a SetValue macro) as the default amount to the Amount
field, but you can edit it of course if necessary. The total amount can
be shown in a control in the footer or header of the subform, for you to
cross-check against the total payment received.

--
Steve Schapel, Microsoft Access MVP

I have a form to record customer payment receipts into a Receipts table
indicating customer, amount, date etc... Within that form is a
subform based on a query that selects all open invoices to that customer
from the Invoices table. I want to be able to allocate the receipt among
those invoices that are being paid (in full or in part) into a Receipts
Allocation table.

Receipts table is joined to Receipts Allocation table by ReceiptsID.

Receipts Allocation table is then joined to the Invoices table by InvoicesID.

So the Receipts Allocation table sits in the middle of the other two and is
joined to both by their respective Primary keys.


So within the Form for entering Receipts I've got a subform in datasheet
view that shows the open invoices to the customer making the payment.
Ideally, beside each of these invoice records I'd like to have fields for
Previous Payments and Outstanding Balance (derived from a query on the
Receipts Allocation table) and Amount Being Allocated and a Checkbox to
indicate whether that payment closes the invoice or not (both fields which
will ultimately be fed into the Receipts Allocation Table).

However, the first time a receipt is received for a given invoice, there is
no previous record of the invoice in the Receipts Allocation table. So when I
run the query for previous payments it returns a null set. And similarly the
only way I can indicate the Amount Being Allocated into the Receipts
Allocation table is if I manually type in the associated InvoiceID first
which is repetitive and prone to error.

It seems to me that in other systems, a temporary holding table is used to
input the receipt allocation info which is then "posted" into the underlying
real table. I'm not sure if this intermediary step is necesary but in any
event I'd still need to somehow "post" the Primary key (InvoiceID) from the
subform of open invoices into a new record in the Receipts Allocation table
so I can then fill in the other fields of that record. [This "posting" issue
will occur several times in my accounting database.]

And the tough part is I know nothing about coding. I'm trying to accomplish
this using macros.

Any assistance or suggestions would be greatly appreciated !

 

[Steve Schapel]

FJ,

1) I need the checkbox to indicate a closed invoice because they are
frequently closed prior to being received in full.

Ok. You know your business better than me. In my experience, this
would normally involve the outstanding aomout being "written off", in
which case a write-off transaction needs to be entered in order to
balance the books .

2) The main problem I can't surmount is that when I make the query to feed
into the InvoiceID combobox I can get the selection to work perfectly IF
there have been previous payments (ie. if the InvoiceID already exists in
the Receipt Allocation table. However, if its a first time payment on that
invoice then when the query attempts to Sum any previous payments to the open
invoices from the Receipt Allocation table, it comes up completely blank
because the InvoiceID number doesn't exist within the Receipt Allocation
table. So rather than getting a selection of open invoices with a $0 for
prior payments, the whole query comes up Null.

You will need a Left Join in the query that you use as the combobox's
Row Source. And you will probably need an Nz() function to handle the
invoices with no previous transactions. I imagine something like this,
as the SQL view of such a query...
SELECT [InvoiceID], [Amount]-Nz([SumOfAmountReceived],0) AS Outstanding
FROM Invoices LEFT JOIN PaymentsSummary
ON Invoices.InvoiceID = PaymentsSummary.InvoiceID
WHERE CustomerID = [Forms]![Receipts]![CustomerID]
AND [Amount]-Nz([SumOfAmountReceived],0)>0

PaymentsSummary in this example is a totals query that gives the
SumOfAmountReceived for each InvoiceID.
.... something like that, anyway .

 

[FJquestioner]

Steve, I tried as you suggested (I think, see query below) but it still comes
up blank.


SELECT Invoices.CUSTOMERID, [Receipt Allocations].INVOICEID, [Receipt
Allocations].ReceiptAllocation, [Invoices]![InvoiceAmount]-Nz([Invoices
Payments Received To Date]![SumOfAmount Paid],0) AS Balance
FROM (Invoices LEFT JOIN [Invoices Payments Received To Date] ON
Invoices.INVOICEID = [Invoices Payments Received To Date].INVOICEID) INNER
JOIN [Receipt Allocations] ON Invoices.INVOICEID = [Receipt
Allocations].INVOICEID
WHERE (((Invoices.CUSTOMERID)=[Forms]![Receipts]![CUSTOMERID]));

The [Invoice payments Received To Date] is a query that sums past receipts
per INVOICEID.

Can you see a flaw in this ?? Seems to me that the query is still trying to
find the INVOICEID field in the Receipts Allocation table. Although we've
converted the Null to 0 within the formula for calculating the Balance
outstanding, the query doesn't get that far. It shuts down as soon as it
tries to match the INVOICEID within the Invoice table to an INVOICEID within
the Receipts Allocation table, despite the Left Join.

Thanks again !

FJ,

1) I need the checkbox to indicate a closed invoice because they are
frequently closed prior to being received in full.

Ok. You know your business better than me. In my experience, this
would normally involve the outstanding aomout being "written off", in
which case a write-off transaction needs to be entered in order to
balance the books .

2) The main problem I can't surmount is that when I make the query to feed
into the InvoiceID combobox I can get the selection to work perfectly IF
there have been previous payments (ie. if the InvoiceID already exists in
the Receipt Allocation table. However, if its a first time payment on that
invoice then when the query attempts to Sum any previous payments to the open
invoices from the Receipt Allocation table, it comes up completely blank
because the InvoiceID number doesn't exist within the Receipt Allocation
table. So rather than getting a selection of open invoices with a $0 for
prior payments, the whole query comes up Null.

You will need a Left Join in the query that you use as the combobox's
Row Source. And you will probably need an Nz() function to handle the
invoices with no previous transactions. I imagine something like this,
as the SQL view of such a query...
SELECT [InvoiceID], [Amount]-Nz([SumOfAmountReceived],0) AS Outstanding
FROM Invoices LEFT JOIN PaymentsSummary
ON Invoices.InvoiceID = PaymentsSummary.InvoiceID
WHERE CustomerID = [Forms]![Receipts]![CustomerID]
AND [Amount]-Nz([SumOfAmountReceived],0)>0

PaymentsSummary in this example is a totals query that gives the
SumOfAmountReceived for each InvoiceID.
.... something like that, anyway .

 

[FJquestioner]

Steve,
I've stripped the query down to the fundamental problem elements for analysis:

SELECT Invoices.CUSTOMERID, [Invoices]![InvoiceAmount]-Nz([Invoices Payments
Received To Date]![SumOfAmount Paid],0) AS Balance
FROM (Invoices LEFT JOIN [Invoices Payments Received To Date] ON
Invoices.INVOICEID = [Invoices Payments Received To Date].INVOICEID) INNER
JOIN [Receipt Allocations] ON Invoices.INVOICEID = [Receipt
Allocations].INVOICEID
WHERE (((Invoices.CUSTOMERID)=[Forms]![Receipts]![CUSTOMERID]));


The above returns the same results whether there is an inner join or a left
join.

The query [Invoices Payments Received To Date] works fine on its own. It
simply returns all invoices with any past payments. However, as soon as the
search is narrowed to invoices from a customer without past payments it
returns a null set again.

Steve, I tried as you suggested (I think, see query below) but it still comes
up blank.


SELECT Invoices.CUSTOMERID, [Receipt Allocations].INVOICEID, [Receipt
Allocations].ReceiptAllocation, [Invoices]![InvoiceAmount]-Nz([Invoices
Payments Received To Date]![SumOfAmount Paid],0) AS Balance
FROM (Invoices LEFT JOIN [Invoices Payments Received To Date] ON
Invoices.INVOICEID = [Invoices Payments Received To Date].INVOICEID) INNER
JOIN [Receipt Allocations] ON Invoices.INVOICEID = [Receipt
Allocations].INVOICEID
WHERE (((Invoices.CUSTOMERID)=[Forms]![Receipts]![CUSTOMERID]));

The [Invoice payments Received To Date] is a query that sums past receipts
per INVOICEID.

Can you see a flaw in this ?? Seems to me that the query is still trying to
find the INVOICEID field in the Receipts Allocation table. Although we've
converted the Null to 0 within the formula for calculating the Balance
outstanding, the query doesn't get that far. It shuts down as soon as it
tries to match the INVOICEID within the Invoice table to an INVOICEID within
the Receipts Allocation table, despite the Left Join.

Thanks again !

FJ,

1) I need the checkbox to indicate a closed invoice because they are
frequently closed prior to being received in full.

Ok. You know your business better than me. In my experience, this
would normally involve the outstanding aomout being "written off", in
which case a write-off transaction needs to be entered in order to
balance the books .

2) The main problem I can't surmount is that when I make the query to feed
into the InvoiceID combobox I can get the selection to work perfectly IF
there have been previous payments (ie. if the InvoiceID already exists in
the Receipt Allocation table. However, if its a first time payment on that
invoice then when the query attempts to Sum any previous payments to the open
invoices from the Receipt Allocation table, it comes up completely blank
because the InvoiceID number doesn't exist within the Receipt Allocation
table. So rather than getting a selection of open invoices with a $0 for
prior payments, the whole query comes up Null.

You will need a Left Join in the query that you use as the combobox's
Row Source. And you will probably need an Nz() function to handle the
invoices with no previous transactions. I imagine something like this,
as the SQL view of such a query...
SELECT [InvoiceID], [Amount]-Nz([SumOfAmountReceived],0) AS Outstanding
FROM Invoices LEFT JOIN PaymentsSummary
ON Invoices.InvoiceID = PaymentsSummary.InvoiceID
WHERE CustomerID = [Forms]![Receipts]![CustomerID]
AND [Amount]-Nz([SumOfAmountReceived],0)>0

PaymentsSummary in this example is a totals query that gives the
SumOfAmountReceived for each InvoiceID.
.... something like that, anyway .

 

[Steve Schapel]

FJ,

I can't see why you have incuded the Receipt Allocations table in the
query. Just remove it.

 

[FJquestioner]

YES YES ! Fantastic.....

Thank you for steering me in the right direction.

Regarding the combobox, I can see both the invoice number and balance data
when making my choice of invoice to pay. But after I chose one of them, it
only dispalys the invoice number, not the balance data. Is there anyway I can
see both ?

Also, what event macro can I use to refresh the subform query? After I enter
an amount to apply to an invoice, the balance in the combobox doesn't change
unless I close the entire form and re-open it. (If I do a "Requery" macro
using the OnCurrent event, my entire Access database crashes everytime it
runs!!).

Thanks a lot.

FJ,

I can't see why you have incuded the Receipt Allocations table in the
query. Just remove it.

--
Steve Schapel, Microsoft Access MVP

Steve,
I've stripped the query down to the fundamental problem elements for analysis:

SELECT Invoices.CUSTOMERID, [Invoices]![InvoiceAmount]-Nz([Invoices Payments
Received To Date]![SumOfAmount Paid],0) AS Balance
FROM (Invoices LEFT JOIN [Invoices Payments Received To Date] ON
Invoices.INVOICEID = [Invoices Payments Received To Date].INVOICEID) INNER
JOIN [Receipt Allocations] ON Invoices.INVOICEID = [Receipt
Allocations].INVOICEID
WHERE (((Invoices.CUSTOMERID)=[Forms]![Receipts]![CUSTOMERID]));


The above returns the same results whether there is an inner join or a left
join.

The query [Invoices Payments Received To Date] works fine on its own. It
simply returns all invoices with any past payments. However, as soon as the
search is narrowed to invoices from a customer without past payments it
returns a null set again.

 

[Steve Schapel]

FJ,

One option would be to put a SetValue macro on the After Update event of
your invoice number combobox. Set it up like this in your macro...
Item: [Amount]
Expression: [Invoice Number].[Column](1)
This assumes that Invoice Number is the name of the combobox, and Amount
is the name of the payment amount. Adjust as applicable.
In other words, this has the effect of setting the default amount for
payment to the invoice's outstanding balance. If it's not, you can
simply edit the amount.
If you don't like this idea, then you can put an unbound textbox on the
form, with its Control Source property set to
=[Invoice Number].[Column](1)
.... so this will show the outstanding amount, for you to base your
decision about how much to enter into the payment amount. In such a
case, I would make the background colour of this textbox different, or
in some other way make it distinctive, to help make it clear that it is
not actual data.

I don't know what's up with your Requery causing problems. Try putting
the name of your combobox (enclosed in []s) in the Control Name argument
of the Requery macro, and put the macro instead on the Enter event of
the combobox itself.

 

[FJquestioner]

Steve,

Thanks. I used your suggestion to alter the column which the combox displays
upon selection and it worked well. However, is there anyway I can get more
than one of the columns to display once I've made the selection?

Also, in a completely different form, I have a subform in datasheet view in
which charges to customers are listed. I click a checkbox to indicate those
charges to be added to an invoice. How can I create a total at the bottom of
either the subform or form, to indicate the totals of only those charges
which have been checked?

Thanks once again.

FJ,

One option would be to put a SetValue macro on the After Update event of
your invoice number combobox. Set it up like this in your macro...
Item: [Amount]
Expression: [Invoice Number].[Column](1)
This assumes that Invoice Number is the name of the combobox, and Amount
is the name of the payment amount. Adjust as applicable.
In other words, this has the effect of setting the default amount for
payment to the invoice's outstanding balance. If it's not, you can
simply edit the amount.
If you don't like this idea, then you can put an unbound textbox on the
form, with its Control Source property set to
=[Invoice Number].[Column](1)
.... so this will show the outstanding amount, for you to base your
decision about how much to enter into the payment amount. In such a
case, I would make the background colour of this textbox different, or
in some other way make it distinctive, to help make it clear that it is
not actual data.

I don't know what's up with your Requery causing problems. Try putting
the name of your combobox (enclosed in []s) in the Control Name argument
of the Requery macro, and put the macro instead on the Enter event of
the combobox itself.

--
Steve Schapel, Microsoft Access MVP

YES YES ! Fantastic.....

Thank you for steering me in the right direction.

Regarding the combobox, I can see both the invoice number and balance data
when making my choice of invoice to pay. But after I chose one of them, it
only dispalys the invoice number, not the balance data. Is there anyway I can
see both ?

Also, what event macro can I use to refresh the subform query? After I enter
an amount to apply to an invoice, the balance in the combobox doesn't change
unless I close the entire form and re-open it. (If I do a "Requery" macro
using the OnCurrent event, my entire Access database crashes everytime it
runs!!).

 

[Steve Schapel]

FJ,

Thanks. I used your suggestion to alter the column which the combox displays
upon selection and it worked well. However, is there anyway I can get more
than one of the columns to display once I've made the selection?

There are a number of approaches to this type of scenario. The combobox
can only display the data from one column, but the corresponding data
from other columns can be shown in separate controls (textboxes
probably). There is an explanation in this article...
http://accesstips.datamanagementsolutions.biz/lookup.htm

Also, in a completely different form, I have a subform in datasheet view in
which charges to customers are listed. I click a checkbox to indicate those
charges to be added to an invoice. How can I create a total at the bottom of
either the subform or form, to indicate the totals of only those charges
which have been checked?

I would recommend a continuous view form rather than a datasheet.
In the footer, put an unbound textbox, with its Control Source set to
the equivalent of...
=Sum(IIf([YourCheckbox],[YourCharge],0))
alternatively...
=-Sum([YourCharge]*[YourCheckbox])

 

[FJquestioner]

Great. I'll give that a whirl.

In the meantime I've discovered another problem!
In one of my forms I have a control which is the product of two other
controls (done within a macro).

If the numbers are say $500,000.00 * .80 the answer keeps coming up to
400,000.006

I've taken all numbers out to 12 decimal places and neither the 500000 nor
the .8 have any distant decimals. The 500,000 field format is "currency" and
the .8 field is "double" with 2 decimal places.

I can't think of why this isn't calculating properly. Do you have any
suggestions?

Thanks.

FJ,

Thanks. I used your suggestion to alter the column which the combox displays
upon selection and it worked well. However, is there anyway I can get more
than one of the columns to display once I've made the selection?

There are a number of approaches to this type of scenario. The combobox
can only display the data from one column, but the corresponding data
from other columns can be shown in separate controls (textboxes
probably). There is an explanation in this article...
http://accesstips.datamanagementsolutions.biz/lookup.htm

Also, in a completely different form, I have a subform in datasheet view in
which charges to customers are listed. I click a checkbox to indicate those
charges to be added to an invoice. How can I create a total at the bottom of
either the subform or form, to indicate the totals of only those charges
which have been checked?

I would recommend a continuous view form rather than a datasheet.
In the footer, put an unbound textbox, with its Control Source set to
the equivalent of...
=Sum(IIf([YourCheckbox],[YourCharge],0))
alternatively...
=-Sum([YourCharge]*[YourCheckbox])

 

[Steve Schapel]

FJ,

I suggest changing the field with the 0.8 to Currency data type as well
(unless you will ever need this to be more than 4 decimal places). This
will be more accurate. A floating point number format such as Double
will often produce these types of rounding problems.

 

[FJquestioner]

Seems to be working ok now. I switched to "Fixed" format rahter than
currency because the actual umber was a % and the currency might be confusing.

Thanks.

 

[Steve Schapel]

FJ,

There is a confusion, caused by the use of the word "currency" in two
different contexts.

In the design of a field, then one of the things you have to consider is:
Data Type

If you designate a Data Type of Number, then (among other things) you
have these properties to consider:
Field Size
Format

If you designate a Data Type of Currency, then (among other things) you
have this property to consider:
Format

Whether you select Currency or Number Data Type, amongst the Format
options is Currency, as well as Fixed and the others.

So, you can have a Number Data Type field which is formatted as
Currency, and you can have a Currency Data Type field which is not
formatted as Currency. Two different meanings of the word currency.

The key point to remember here is that the Format property is relatively
trivial, in that it only affects the way the data is displayed, whereas
the Data Type setting of the field is more significant, as it affects
the way the data is stored.

So, generally speaking, if you are dealing with numerical values that
will not require more than 4 decimal places, and for which you want to
do precise calculations, the Currency Data Type is the obvious choice.
Then you can format it to display however you want.

Using a Fixed format for a Number Data Type field simply disguises the
inaccuracy. Well Number fields with Filed Size set to Integer or Long
Integer are accurate.

 

[FJquestioner]

Oh, I see. I didn't make that distinction. I'll switch the Data Type to
Currency then. That should make life easier since none of my calculations
require more than 3 decimals. Thanks.

I just posted the following question to the "Query" area but since you are
obviously a true "MVP" so I'll ask you too if you don't mind.

I have a Receipts Allocation table in which the payment received for invoices
are recorded by INVOICEID number. When the final payment for an invoice is
received (which may or may not cover the full amount of the invoice) a
checkbox is ticked "Yes" in the same table.

I'd like a query where I can calculate my credit exposure to a given
customer. This can be a simple formula of Total Open Invoices - Receipts to
date on same. However, I can't simply add up all invoices and subtract all
receipts (for reasons related to the economics of the business). I need to be
able to eliminate those invoices and all associated receipts where any one of
the receipts for that invoice has been checked as "yes". In other words I
need to get rid of any
"closed" invoices first.

I can't figure out how to set-up this query and formula.
Any assistance would be greatly appreciated.

Thanks.

 

[Steve Schapel]

FJ,

I have replied in the .queries newsgroup.

 

[FJquestioner]

Steve,

I've got a form with a few "Null" fields and a checkbox.

Within that form is an invisible subform containing the results of a query.
Each result is formatted as "Currency" both within the query formulas and on
the form itself.

The Null fields in the master form are also formated as Currency both within
the form and in the underlying table (both Data Type and Format).

I've got a macro which is supposed to copy the data in the subform into the
appropriate fields in the master form if the checkbox is clicked.

However, when the macro runs I keep getting the error message:

THE VALUE YOU ENTERED ISN'T VALID FOR THIS FIELD.
For example, you may have entered text in a numeric field or a number that
is larger than the FieldSize setting permits.

I can't figure out what is causing this. Do you by chance have any
suggestions as to possible problems?

Thanks again.

 

[Joseph R. Pottschmidt]

Dear FJquestioner:

If you can post your macro on this forum, I would be able to
troubleshoot what the problem is. Without coding examples of what you've
written, I wouldn't be able to guess what it might be.

Joe P.

-----Original Message-----
From: FJquestioner [mailto:[email protected]]
Posted At: Tuesday, June 06, 2006 2:38 PM
Posted To: microsoft.public.access.macros
Conversation: How to "POST" accounting data into underlying tables
Subject: Re: How to "POST" accounting data into underlying tables

Steve,

I've got a form with a few "Null" fields and a checkbox.

Within that form is an invisible subform containing the results of a
query.
Each result is formatted as "Currency" both within the query formulas
and on
the form itself.

The Null fields in the master form are also formated as Currency both
within
the form and in the underlying table (both Data Type and Format).

I've got a macro which is supposed to copy the data in the subform into
the
appropriate fields in the master form if the checkbox is clicked.

However, when the macro runs I keep getting the error message:

THE VALUE YOU ENTERED ISN'T VALID FOR THIS FIELD.
For example, you may have entered text in a numeric field or a number
that
is larger than the FieldSize setting permits.

I can't figure out what is causing this. Do you by chance have any
suggestions as to possible problems?

Thanks again.

 

[Steve Schapel]

FJ,

The bit that makes me the most suspicious is "formatted as 'Currency'
within the query formulas". What do you mean by this?