Steve,
I understand your comment on the design issue. I am in the factoring
business which means I purchase the accounts receivable of other companies at
a discount from face value and the debtor pays me down the road (hopefully!).
If I am buying a receivable of say $100, I'll pay $85 as an up front
advance. Then later when I receive the $100 from the debtor I will rebate the
company I bought the invoice from, the remaining $15, less a discount fee
(which works like an interest rate). So this database is to keep track of
all the invoices purchased as well as the advances made and the rebates owed
and the discount fees I've earned.
In the section I'm working on now, I have queries which calculate the rebate
owed and the discount fee earned for each invoice. I have a form [Rebates
Payable and Discount Fees] in which I select a client and a currency. Then
the subform [Rebates Payable Subform Invoices Closed 1 or 2 but not Rebated]
displays all closed (ie paid) invoices (from the Invoices table) which match
the client and currency criteria.
My objective is to select from this list, those invoices on which I
currently want to book a rebate payable and book my discount fee. I indicate
this by clicking a checkbox within that Subform (which is the
"FeeAndRebateAdded" field in the underlying Invoices table).
Clicking this checkbox triggers the macro which then takes the rebate and
discount fee calculations from the queries and places them into the
[RebatePayale] and [DiscFee] fields of the subform and hence into the
underlying Invoices table. The reason I do this (and please correct me if
I'm wrong) is that this data (rebtes, discount fees and other data) is going
to be exported into the QuickBooks accounting program and needs to remain
static. In other words the rebate and discount fee calculations, once made
must remain the same even if I later change the advance rate of interest
charges for a given client (which would alter the results of the query).
The only resaon I have an invisible subform [Rebate and Fees upon Payment]
within the subform [Rebates Payable Subform Invoices Closed 1 or 2 but not
Rebated] is that it contains the rebate and discount fee query calculations
and I don't know how to get a marco to run on an object that isn't part of
the currently open form. (Of course I could get the macro to Open the Query
independently but I don't know how to do this without the query physically
showing up on the screen).
I have other forms and queries in the program that do similar functions and
I've never had a problem with the macro. This error message about an invalid
field value has me stumped.
Thanks.
Steve Schapel said:
FJ,
Ok, so am I right in my understanding that this query is the Record
Source of the [Rebate and Fees upon Payment] subform, which is a subform
on the [Rebates Payable Subform Invoices Closed 1 or 2 but not Rebated]
subform, which is a subform on the [Rebates Payable and Discount Fees]
form? And the macro runs on an event on the [Rebates Payable and
Discount Fees] form? And on the [Rebates Payable and Discount Fees]
form, there is a control named NetRebate. Ok, can you also let us know
what is the Record Source of the [Rebates Payable and Discount Fees]
form? If a table, is NetRebate a field in this table? If a query, what
is the SQL of the query?
Clarification of the above may help. However, you are also referring to
the current record on the [Rebate and Fees upon Payment] subform, and
then I think you said this subform was hidden? So it can't have a
current record? So it can't work? ... Just surmising here.
And beside all this, well, to be honest, if you end up wanting to set
the value of one control to the value of another control, it is almost
always an indication of a database design problem. But that's starting
to get complicated. The good news is that when you finally get through
problems like this, you find out that there is a very easy way to do
what you originally thought was difficult.
--
Steve Schapel, Microsoft Access MVP
Steve,
The following is a query which generates the expression value for NetRebate
which is in my subform. It appears the subform perfectly. However, when I
try to use the Set Value macro to copy the NetRebate amount into the main
form I get the error message. Incidentally, the formatting I was talking
about was Format(xxxx,"Currency"). However, that was not the problem. The
error message persists even when that formattting is removed.
SELECT Invoices.RebatePaidApplied, Invoices.Closed, Invoices.INVOICEID,
IIf(Invoices!ClosedHow<3,[Invoices Payments Received To Date]![SumOfAmount
Paid]-(Invoices!AdvanceAmount+[Disc Fee on Payment
Received]!DiscFee),Nz([Invoices Payments Received To Date]![SumOfAmount
Paid],0)-(Invoices!AdvanceAmount+[Disc Fee on Recourse or
Chargeback]!DiscFee)) AS NetRebate
FROM ((Invoices LEFT JOIN [Invoices Payments Received To Date] ON
Invoices.INVOICEID = [Invoices Payments Received To Date].INVOICEID) LEFT
JOIN [Disc Fee on Payment Received] ON Invoices.INVOICEID = [Disc Fee on
Payment Received].INVOICEID) LEFT JOIN [Disc Fee on Recourse or Chargeback]
ON Invoices.INVOICEID = [Disc Fee on Recourse or Chargeback].INVOICEID
WHERE (((Invoices.RebatePaidApplied)=No) AND ((Invoices.Closed)=Yes));
Does this shed any light??
Steve Schapel said:
FJ,
The bit that makes me the most suspicious is "formatted as 'Currency'
within the query formulas". What do you mean by this?
--
Steve Schapel, Microsoft Access MVP
FJquestioner wrote:
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?
