Accounting system query

[Guest]

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, to do this, 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.
Any assistance would be greatly appreciated.

Thanks.

 

[Steve Schapel]

FJ,

If I understand you correctly, you mean "do not include invoices where
one of the related payments received has been marked as 'final'". Right?

Ok, I suggest a 3 step process (caution: untested!)...

1. Make a query to identify the finalised invoices. The SQL view of
such a query will look something like this...
SELECT InvoiceID
FROM [Receipts Allocation]
WHERE [Final]=-1

2. Make a query, based on your Receipts Allocation table, plus the
query in step 1, to work out the payment totals for open invoices. The
SQL view of such a query will look something like this...
SELECT InvoiceID, Sum([AmountPaid]) As ReceiptsToDate
FROM [Receipts Allocation] LEFT JOIN [Step1Query]
ON [Receipts Allocation].[InvoiceID] = [Step1Query].[InvoiceID]
WHERE [Step1Query].[InvoiceID] Is Null
GROUP BY [Receipts Allocation].[InvoiceID]

3. Make a query, based on your Invoices table, plus the query in step
2, to work out the credit exposure for open invoices. The SQL view of
such a query will look something like this...
SELECT CustomerID, Sum([InvoiceAmount]-[ReceiptsToDate])
FROM Invoices INNER JOIN Step2Query
ON Invoices.[InvoiceID] = Step2Query.[InvoiceID]
GROUP BY [Invoices].[CustomerID]

 

[Guest]

Worked like a charm. Thanks once again.

FJ,

If I understand you correctly, you mean "do not include invoices where
one of the related payments received has been marked as 'final'". Right?

Ok, I suggest a 3 step process (caution: untested!)...

1. Make a query to identify the finalised invoices. The SQL view of
such a query will look something like this...
SELECT InvoiceID
FROM [Receipts Allocation]
WHERE [Final]=-1

2. Make a query, based on your Receipts Allocation table, plus the
query in step 1, to work out the payment totals for open invoices. The
SQL view of such a query will look something like this...
SELECT InvoiceID, Sum([AmountPaid]) As ReceiptsToDate
FROM [Receipts Allocation] LEFT JOIN [Step1Query]
ON [Receipts Allocation].[InvoiceID] = [Step1Query].[InvoiceID]
WHERE [Step1Query].[InvoiceID] Is Null
GROUP BY [Receipts Allocation].[InvoiceID]

3. Make a query, based on your Invoices table, plus the query in step
2, to work out the credit exposure for open invoices. The SQL view of
such a query will look something like this...
SELECT CustomerID, Sum([InvoiceAmount]-[ReceiptsToDate])
FROM Invoices INNER JOIN Step2Query
ON Invoices.[InvoiceID] = Step2Query.[InvoiceID]
GROUP BY [Invoices].[CustomerID]

--
Steve Schapel, Microsoft Access MVP

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, to do this, 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.
Any assistance would be greatly appreciated.

Thanks.

 

[Guest]

Steve,

As noted earlier the query you recommended worked fine. However now I have
the problem of slipping it into the proper form. Currently the data in the
form is as listed below. All I need to do is add one field called [Credit
Available] located within the Query called [Credit Limit Check - Debtors 4].
The query is linked to the form with the field [DEBTORID]:

SELECT Invoices.INVOICEID, Invoices.ASSIGNMENTREQUESTID, Invoices.STATUSID,
Invoices.EntryDate, Invoices.DEBTORID, Invoices.STOREID,
Invoices.AssignorInvoiceNumber, Invoices.InvoiceDate,
Invoices.InvoiceDueDate, Invoices.InvoiceAmount,
Invoices.AllowableDiscountPct, Invoices.AdvanceRate AS Invoices_AdvanceRate,
Invoices.AdvanceAmount, Invoices.Closed, Invoices.ClosedHow,
Invoices.DateClosed, Invoices.Rebated, Invoices.REBATEID, Invoices.DiscFee,
Invoices.DateUsedforDiscFee, Invoices.ReceivedAmount, Invoices.NetRebate,
Invoices.Verify, Invoices.Verified, Invoices.VerificationDate,
Invoices.VerificationNotes, Invoices.CollectionNotes, Invoices.Memo,
Assignors.Advance_Percent, Debtors.AdvanceRate AS Debtors_AdvanceRate,
Debtors.RecourseDays, Debtors.VerificationThreshold FROM Debtors INNER JOIN
Invoices ON Debtors.DEBTORID=Invoices.DEBTORID;


Similarly I have another form called [Assignment Requests]. I want to add
the field called [Credit Available] from the query called [Credit Limit
Cheque - Assignors 4] into the form. It will be linked using the field
[ASSIGNORID]. Currently the form's data is as follows:

SELECT [Assignment Requests].ASSIGNMENTREQUESTID, [Assignment
Requests].ASSIGNORID, [Assignment Requests].AdvanceDate, [Assignment
Requests].CURRENCYID, [Assignment Requests].ExchangeRate, [Assignment
Requests].ChequeNumber, [Assignment Requests].AdvanceNotes, [Assignment
Requests].Posted, [Assignment Requests].QBExported, [Assignment
Requests].QBExportDate, [Assignment Requests].BatchID, [Assignment
Requests].StatusID, [Assignment Requests].AdvanceReconciled, [Assignment
Requests].BankStatementDate, Assignors.CompanyName,
Assignors.Advance_Percent, Currencies.Currency FROM Currencies INNER JOIN
(Assignors INNER JOIN [Assignment Requests] ON
Assignors.ASSIGNORID=[Assignment Requests].ASSIGNORID) ON
Currencies.CURRENCYID=[Assignment Requests].CURRENCYID;

Could you let me know how to insert the two fields from the two queries into
the two forms. Otherwise I'll have to start the forms fropm scratch using
the trusty (but clumsy) wizard!

Thanks again for the bail out!

 

[Steve Schapel]

FJ,

A form has a Record Source property, which determines which data the
from relates to. This is normally either a table or a query. The
"trusty" from wizard assigns this property to the form in the process of
setting it up. But you can just as easily do it yourself, by going into
the Properties of the form, and putting the appropriate entry in the
Record Source property.

If I understand you correctly, you want fields from the Credit Limit
Check - Debtors 4 query to be included in the Record Source of the form,
instead of the way the form was first set up. So you will need to
create another query, which gives you the data you need on your form.
This will presumably mean a query that includes the original query, plus
the Credit Limit Check - Debtors 4 query, suitably joined.

I didn't study your queries in depth, but I did notice an oddity... the
first SQL string you mentioned includes Assignors.Advance_Percent but
the Assignors table does not appear in the FROM clause. Strange?

When you start basing forms on complex queries, you must also consider
whether you want the form to be editable. Queries are updateable or
not, depending on a number of factors which I won't go into here. But
if a form is based on a non-updateable query, you won't be able to
add/edit records there. Credit Limit Check - Debtors 4 I am pretty
certain will not be updateable, and thus any query that includes the
Credit Limit Check - Debtors 4 query will not be updateable.

One possible way around this is to use an unbound textbox on the form,
with its Control Source property set to a Domain function, such as...
=DLookup("[Credit Available]","Credit Limit Check - Debtors
4","[AssignorID]=" & [AssignorID])
If you have a lot of data, this method will result in the form being
slow to load.

By the way, as an aside, it is not a good idea to use a - as part of the
name of a query (or field, or control, or any other database object for
that matter).

--
Steve Schapel, Microsoft Access MVP

Steve,

As noted earlier the query you recommended worked fine. However now I have
the problem of slipping it into the proper form. Currently the data in the
form is as listed below. All I need to do is add one field called [Credit
Available] located within the Query called [Credit Limit Check - Debtors 4].
The query is linked to the form with the field [DEBTORID]:

SELECT Invoices.INVOICEID, Invoices.ASSIGNMENTREQUESTID, Invoices.STATUSID,
Invoices.EntryDate, Invoices.DEBTORID, Invoices.STOREID,
Invoices.AssignorInvoiceNumber, Invoices.InvoiceDate,
Invoices.InvoiceDueDate, Invoices.InvoiceAmount,
Invoices.AllowableDiscountPct, Invoices.AdvanceRate AS Invoices_AdvanceRate,
Invoices.AdvanceAmount, Invoices.Closed, Invoices.ClosedHow,
Invoices.DateClosed, Invoices.Rebated, Invoices.REBATEID, Invoices.DiscFee,
Invoices.DateUsedforDiscFee, Invoices.ReceivedAmount, Invoices.NetRebate,
Invoices.Verify, Invoices.Verified, Invoices.VerificationDate,
Invoices.VerificationNotes, Invoices.CollectionNotes, Invoices.Memo,
Assignors.Advance_Percent, Debtors.AdvanceRate AS Debtors_AdvanceRate,
Debtors.RecourseDays, Debtors.VerificationThreshold FROM Debtors INNER JOIN
Invoices ON Debtors.DEBTORID=Invoices.DEBTORID;


Similarly I have another form called [Assignment Requests]. I want to add
the field called [Credit Available] from the query called [Credit Limit
Cheque - Assignors 4] into the form. It will be linked using the field
[ASSIGNORID]. Currently the form's data is as follows:

SELECT [Assignment Requests].ASSIGNMENTREQUESTID, [Assignment
Requests].ASSIGNORID, [Assignment Requests].AdvanceDate, [Assignment
Requests].CURRENCYID, [Assignment Requests].ExchangeRate, [Assignment
Requests].ChequeNumber, [Assignment Requests].AdvanceNotes, [Assignment
Requests].Posted, [Assignment Requests].QBExported, [Assignment
Requests].QBExportDate, [Assignment Requests].BatchID, [Assignment
Requests].StatusID, [Assignment Requests].AdvanceReconciled, [Assignment
Requests].BankStatementDate, Assignors.CompanyName,
Assignors.Advance_Percent, Currencies.Currency FROM Currencies INNER JOIN
(Assignors INNER JOIN [Assignment Requests] ON
Assignors.ASSIGNORID=[Assignment Requests].ASSIGNORID) ON
Currencies.CURRENCYID=[Assignment Requests].CURRENCYID;

Could you let me know how to insert the two fields from the two queries into
the two forms. Otherwise I'll have to start the forms fropm scratch using
the trusty (but clumsy) wizard!

Thanks again for the bail out!

 

[Guest]

Steve,

I'm having trouble with my query for calculating credit exposure to Debtors.
I've made a subquery which calculates all payments received to date on open
invoices, grouped and summed by Debtor.

I also have a subquery calculating the total open invoice value by Debtor.

Now I'm simply trying to calculate the credit limit per Debtor less the
total of all open invoices to that debtor plus payments received to date on
those invoices.

However, I only seem to be able to get the results for those debtors where
receipts have been paid to date. Debtors without such payments are left out
of the caclculation.

Here is the SQL behind my query:

SELECT [Credit Limit Check Debtors 3].DEBTORID, Debtors.DebtorName,
Debtors.CreditLimit, [Credit Limit Check Debtors 3].OpenInvoiceFaceValue,
[Credit Limit Check Debtors 2].ReceiptsToDate,
[CreditLimit]-[OpenInvoiceFaceValue]+[ReceiptsToDate] AS CreditBalance
FROM (Debtors INNER JOIN [Credit Limit Check Debtors 3] ON Debtors.DEBTORID
= [Credit Limit Check - Debtors 3].DEBTORID) LEFT JOIN [Credit Limit Check
Debtors 2] ON Debtors.DEBTORID = [Credit Limit Check Debtors 2].DEBTORID;

I'm sure there is a simple solution to this but I've been staring at it so
long I've lost perspective...... Can you point me in the right direction?

Thanks.

FJ,

A form has a Record Source property, which determines which data the
from relates to. This is normally either a table or a query. The
"trusty" from wizard assigns this property to the form in the process of
setting it up. But you can just as easily do it yourself, by going into
the Properties of the form, and putting the appropriate entry in the
Record Source property.

If I understand you correctly, you want fields from the Credit Limit
Check - Debtors 4 query to be included in the Record Source of the form,
instead of the way the form was first set up. So you will need to
create another query, which gives you the data you need on your form.
This will presumably mean a query that includes the original query, plus
the Credit Limit Check - Debtors 4 query, suitably joined.

I didn't study your queries in depth, but I did notice an oddity... the
first SQL string you mentioned includes Assignors.Advance_Percent but
the Assignors table does not appear in the FROM clause. Strange?

When you start basing forms on complex queries, you must also consider
whether you want the form to be editable. Queries are updateable or
not, depending on a number of factors which I won't go into here. But
if a form is based on a non-updateable query, you won't be able to
add/edit records there. Credit Limit Check - Debtors 4 I am pretty
certain will not be updateable, and thus any query that includes the
Credit Limit Check - Debtors 4 query will not be updateable.

One possible way around this is to use an unbound textbox on the form,
with its Control Source property set to a Domain function, such as...
=DLookup("[Credit Available]","Credit Limit Check - Debtors
4","[AssignorID]=" & [AssignorID])
If you have a lot of data, this method will result in the form being
slow to load.

By the way, as an aside, it is not a good idea to use a - as part of the
name of a query (or field, or control, or any other database object for
that matter).

--
Steve Schapel, Microsoft Access MVP

Steve,

As noted earlier the query you recommended worked fine. However now I have
the problem of slipping it into the proper form. Currently the data in the
form is as listed below. All I need to do is add one field called [Credit
Available] located within the Query called [Credit Limit Check - Debtors 4].
The query is linked to the form with the field [DEBTORID]:

SELECT Invoices.INVOICEID, Invoices.ASSIGNMENTREQUESTID, Invoices.STATUSID,
Invoices.EntryDate, Invoices.DEBTORID, Invoices.STOREID,
Invoices.AssignorInvoiceNumber, Invoices.InvoiceDate,
Invoices.InvoiceDueDate, Invoices.InvoiceAmount,
Invoices.AllowableDiscountPct, Invoices.AdvanceRate AS Invoices_AdvanceRate,
Invoices.AdvanceAmount, Invoices.Closed, Invoices.ClosedHow,
Invoices.DateClosed, Invoices.Rebated, Invoices.REBATEID, Invoices.DiscFee,
Invoices.DateUsedforDiscFee, Invoices.ReceivedAmount, Invoices.NetRebate,
Invoices.Verify, Invoices.Verified, Invoices.VerificationDate,
Invoices.VerificationNotes, Invoices.CollectionNotes, Invoices.Memo,
Assignors.Advance_Percent, Debtors.AdvanceRate AS Debtors_AdvanceRate,
Debtors.RecourseDays, Debtors.VerificationThreshold FROM Debtors INNER JOIN
Invoices ON Debtors.DEBTORID=Invoices.DEBTORID;


Similarly I have another form called [Assignment Requests]. I want to add
the field called [Credit Available] from the query called [Credit Limit
Cheque - Assignors 4] into the form. It will be linked using the field
[ASSIGNORID]. Currently the form's data is as follows:

SELECT [Assignment Requests].ASSIGNMENTREQUESTID, [Assignment
Requests].ASSIGNORID, [Assignment Requests].AdvanceDate, [Assignment
Requests].CURRENCYID, [Assignment Requests].ExchangeRate, [Assignment
Requests].ChequeNumber, [Assignment Requests].AdvanceNotes, [Assignment
Requests].Posted, [Assignment Requests].QBExported, [Assignment
Requests].QBExportDate, [Assignment Requests].BatchID, [Assignment
Requests].StatusID, [Assignment Requests].AdvanceReconciled, [Assignment
Requests].BankStatementDate, Assignors.CompanyName,
Assignors.Advance_Percent, Currencies.Currency FROM Currencies INNER JOIN
(Assignors INNER JOIN [Assignment Requests] ON
Assignors.ASSIGNORID=[Assignment Requests].ASSIGNORID) ON
Currencies.CURRENCYID=[Assignment Requests].CURRENCYID;

Could you let me know how to insert the two fields from the two queries into
the two forms. Otherwise I'll have to start the forms fropm scratch using
the trusty (but clumsy) wizard!

Thanks again for the bail out!

 

[Steve Schapel]

FJ,

It is not immediately obvious what [Credit Limit Check Debtors 3]
refers to, but I would guess it should be included in the query with a
Left Join rather than an Inner Join.

Then, I think you will need to cater to these Left Joins by using a Nz()
function in your calculated field, something likle this...
[CreditLimit]-Nz([OpenInvoiceFaceValue],0)+Nz([ReceiptsToDate],0)

 

[Guest]

Good Lord! I knew there was going to be an incredibly simple solution!
I was putting the Nz function in the Criteria line to no avail. It never
occurred to me to put it directly into the expression formula.

Thank you once again for switching on the lightbulb for me !!

FJ,

It is not immediately obvious what [Credit Limit Check Debtors 3]
refers to, but I would guess it should be included in the query with a
Left Join rather than an Inner Join.

Then, I think you will need to cater to these Left Joins by using a Nz()
function in your calculated field, something likle this...
[CreditLimit]-Nz([OpenInvoiceFaceValue],0)+Nz([ReceiptsToDate],0)

--
Steve Schapel, Microsoft Access MVP

Steve,

I'm having trouble with my query for calculating credit exposure to Debtors.
I've made a subquery which calculates all payments received to date on open
invoices, grouped and summed by Debtor.

I also have a subquery calculating the total open invoice value by Debtor.

Now I'm simply trying to calculate the credit limit per Debtor less the
total of all open invoices to that debtor plus payments received to date on
those invoices.

However, I only seem to be able to get the results for those debtors where
receipts have been paid to date. Debtors without such payments are left out
of the caclculation.

Here is the SQL behind my query:

SELECT [Credit Limit Check Debtors 3].DEBTORID, Debtors.DebtorName,
Debtors.CreditLimit, [Credit Limit Check Debtors 3].OpenInvoiceFaceValue,
[Credit Limit Check Debtors 2].ReceiptsToDate,
[CreditLimit]-[OpenInvoiceFaceValue]+[ReceiptsToDate] AS CreditBalance
FROM (Debtors INNER JOIN [Credit Limit Check Debtors 3] ON Debtors.DEBTORID
= [Credit Limit Check - Debtors 3].DEBTORID) LEFT JOIN [Credit Limit Check
Debtors 2] ON Debtors.DEBTORID = [Credit Limit Check Debtors 2].DEBTORID;

I'm sure there is a simple solution to this but I've been staring at it so
long I've lost perspective...... Can you point me in the right direction?

 

[Guest]

Steve,

I have a query to find duplicate invoice numbers. I use the access find
duplicate wizard query as follows:

SELECT Invoices.AssignorInvoiceNumber
FROM Invoices
WHERE (((Invoices.AssignorInvoiceNumber) In (SELECT [AssignorInvoiceNumber]
FROM [Invoices] As Tmp GROUP BY [AssignorInvoiceNumber] HAVING Count(*)>1)))
ORDER BY Invoices.AssignorInvoiceNumber;

It works fine however I have a related macro wherein a form is closed only
if there are no duplicates found in the above query. I set the form to close
if the AssignorInvoiceNumber in the query is Null. However, when there are
no duplicates the query returns blank cells that seem to be neither Null nor
blank fields like " " so the condition in my macro won't work.

In essence, what condition do I write in a macro such that the macro action
occurs when the query above reveals no duplicates?

I'm stumped.

Thanks very much in advance.

 

[Steve Schapel]

FJ,

Put this in your macro Condition..
DCount("*","YourQuery")=0

 

[Guest]

Thanks Steve. It worked perfectly.

FJ,

Put this in your macro Condition..
DCount("*","YourQuery")=0

--
Steve Schapel, Microsoft Access MVP

Steve,

I have a query to find duplicate invoice numbers. I use the access find
duplicate wizard query as follows:

SELECT Invoices.AssignorInvoiceNumber
FROM Invoices
WHERE (((Invoices.AssignorInvoiceNumber) In (SELECT [AssignorInvoiceNumber]
FROM [Invoices] As Tmp GROUP BY [AssignorInvoiceNumber] HAVING Count(*)>1)))
ORDER BY Invoices.AssignorInvoiceNumber;

It works fine however I have a related macro wherein a form is closed only
if there are no duplicates found in the above query. I set the form to close
if the AssignorInvoiceNumber in the query is Null. However, when there are
no duplicates the query returns blank cells that seem to be neither Null nor
blank fields like " " so the condition in my macro won't work.

In essence, what condition do I write in a macro such that the macro action
occurs when the query above reveals no duplicates?

I'm stumped.

Thanks very much in advance.

 

[Guest]

Steve,

I have a form with a subform. Queries within the subform depend on 3 of the
variables in the main form. Each time I open the main form I have it set to
go to a new record. However, if I close the form without entering any data, I
am asked to enter the variables expected by the queries within the subform.
If I press cancel, the entire Access system crashes with an error report sent
to Microsoft.

I've re-installed Access but to no avail.

Q1: Is there a way to avoid getting requests for query inputs when closing
an empty form?

Q2: If the answer to #1 is No, do you have any thoughts on how I can stop
the system from crashing upon closing this empty form ?

Thanks very much in advance.

 

[Steve Schapel]

FJ,

Well, I think we will need to dig a bit deeper here. There is nothing
inherently in the concept of closing a form with a subform, without
entering a main form record, that could cause this behaviour. So, what
do you mean by "variables"? Can you post the SQL view of the query that
the subform is based on? Is there any code or macro on the Close or
Unload event of the main form? If so, any clues there? What is the
relationship between the main form and the subform? What is the
settings of the subform's Link Master Fields and Link Child Fields
properties? I assume you mean you are getting a "Enter Parameter Value"
dialog pop up... what are the parameters it is asking for?

 

[Guest]

OK. Here goes...

The main form is called Receipts. It contains several fields but 3 of them
are combo-boxes in which I select ASSIGNORID, DEBTORID and CURRENCY.
RECEIPTID is the auto-generated number key and is what ties it to the subform
called Receipts Subform. That subform has its own subform called "Receipts
Subform Invoices and Balances subform". The query for this form contains the
3 fields from the main form (ASSIGNORID, DEBTORID and CURRENCYID) and these
are the "Enter Parameter Value" requests that pop up whenever I close the
form. Although only the first one pops up and when I close it the whole
system crashes before the others appear in sequence.

The query for this second subform is as follows:

SELECT Format(Nz([Invoices Payments Received To Date]![SumOfAmount
Paid],0),"Currency") AS [Previous Payments],
Invoices!InvoiceAmount-Nz([Invoices Payments Received To Date]![SumOfAmount
Paid],0) AS Balance, Invoices.INVOICEID, Invoices.AssignorInvoiceNumber
FROM [Assignment Requests] INNER JOIN (Invoices LEFT JOIN [Invoices Payments
Received To Date] ON Invoices.INVOICEID = [Invoices Payments Received To
Date].INVOICEID) ON [Assignment Requests].ASSIGNMENTREQUESTID =
Invoices.ASSIGNMENTREQUESTID
WHERE (((Invoices.DEBTORID)=[Forms]![Receipts]![DEBTORID]) AND (([Assignment
Requests].ASSIGNORID)=[Forms]![Receipts]![ASSIGNORID]) AND (([Assignment
Requests].CURRENCYID)=[Forms]![Receipts]![Currency]) AND
((Invoices.Closed)=No))
ORDER BY Invoices.AssignorInvoiceNumber;

Just in case its of any value, the query for the first subform is:

SELECT [Receipt Allocations].RECEIPTALLOCATIONID, [Receipt
Allocations].RECEIPTID, [Receipt Allocations].INVOICEID, [Receipt
Allocations].ReceiptAllocation, [Receipt Allocations].ClosingReceipt,
Invoices.Closed, Invoices.DateClosed, Invoices.AssignorInvoiceNumber,
Invoices.CLOSEDHOWID, Invoices.GrossDiscFee, Invoices.ConsFee,
Invoices.RebatePayable
FROM Invoices INNER JOIN [Receipt Allocations] ON Invoices.INVOICEID =
[Receipt Allocations].INVOICEID
WHERE (((Invoices.Closed)=No))
ORDER BY Invoices.AssignorInvoiceNumber;



There is no macro associated with the closing of the main form but there is
a macro upon opening which sets the form to a new record and maximizes it.
However, that same macro is used on several other forms and causes no
problems.

Are you able to decipher anything from this?

Thanks.

 

[Steve Schapel]

FJ,

Thanks for the further details. However, this doesn't really make sense
to me so far. So, more questions I'm afraid!

When it "crashes" after prompting you for the AssignorID value, what
makes you think it is "planning" to ask for the others? Or do you mean
you get prompted for the DebtorID and CurrencyID, even after the crash?
(Which would be astounding). Does the Parameter Prompt ask for
'AssignorID', or does it ask for '[Forms]![Receipts]![AssignorID]'? And
this is happening when you close the main form, right? How are you
closing it?... the [x] at top right, or your own close button on the
form, or something else?

--
Steve Schapel, Microsoft Access MVP

OK. Here goes...

The main form is called Receipts. It contains several fields but 3 of them
are combo-boxes in which I select ASSIGNORID, DEBTORID and CURRENCY.
RECEIPTID is the auto-generated number key and is what ties it to the subform
called Receipts Subform. That subform has its own subform called "Receipts
Subform Invoices and Balances subform". The query for this form contains the
3 fields from the main form (ASSIGNORID, DEBTORID and CURRENCYID) and these
are the "Enter Parameter Value" requests that pop up whenever I close the
form. Although only the first one pops up and when I close it the whole
system crashes before the others appear in sequence.

The query for this second subform is as follows:

SELECT Format(Nz([Invoices Payments Received To Date]![SumOfAmount
Paid],0),"Currency") AS [Previous Payments],
Invoices!InvoiceAmount-Nz([Invoices Payments Received To Date]![SumOfAmount
Paid],0) AS Balance, Invoices.INVOICEID, Invoices.AssignorInvoiceNumber
FROM [Assignment Requests] INNER JOIN (Invoices LEFT JOIN [Invoices Payments
Received To Date] ON Invoices.INVOICEID = [Invoices Payments Received To
Date].INVOICEID) ON [Assignment Requests].ASSIGNMENTREQUESTID =
Invoices.ASSIGNMENTREQUESTID
WHERE (((Invoices.DEBTORID)=[Forms]![Receipts]![DEBTORID]) AND (([Assignment
Requests].ASSIGNORID)=[Forms]![Receipts]![ASSIGNORID]) AND (([Assignment
Requests].CURRENCYID)=[Forms]![Receipts]![Currency]) AND
((Invoices.Closed)=No))
ORDER BY Invoices.AssignorInvoiceNumber;

Just in case its of any value, the query for the first subform is:

SELECT [Receipt Allocations].RECEIPTALLOCATIONID, [Receipt
Allocations].RECEIPTID, [Receipt Allocations].INVOICEID, [Receipt
Allocations].ReceiptAllocation, [Receipt Allocations].ClosingReceipt,
Invoices.Closed, Invoices.DateClosed, Invoices.AssignorInvoiceNumber,
Invoices.CLOSEDHOWID, Invoices.GrossDiscFee, Invoices.ConsFee,
Invoices.RebatePayable
FROM Invoices INNER JOIN [Receipt Allocations] ON Invoices.INVOICEID =
[Receipt Allocations].INVOICEID
WHERE (((Invoices.Closed)=No))
ORDER BY Invoices.AssignorInvoiceNumber;



There is no macro associated with the closing of the main form but there is
a macro upon opening which sets the form to a new record and maximizes it.
However, that same macro is used on several other forms and causes no
problems.

Are you able to decipher anything from this?

 

[Guest]

Steve,

Not sure if this will help but this is what appears on the top of the error
report I get when the system crashes.

AppName: msaccess.exe AppVer: 10.0.6771.0 ModName: msaccess.exe
ModVer: 10.0.6771.0 Offset: 000a90ad


To answer your questions...

The problem occurs whether I close the main form using the X or the macro
driven Close button within the form.

I don't know for sure that the system would prompt for all 3 of the
combo-box fields because as you noted, the system crashes after I cancel the
first prompt. However, if I re-arrange those fields within the query, the
prompt is for whichever field is left-most within the query (currently it
prompts for [Forms]![Receipts]![DEBTORID] ).

Also, I recently bought a new computer and loaded Office onto it (including
Access). I was having the problem of the promt appearing upon closing the
form on my old computer but I don't recall it causing a system crash.
However, I've re-installed Office using the "repair" mode at least 3 times
and the crashing problem persists.

I'm using WinXP and I also did a system restore at one point when I was
loading programs onto the new computer. That caused the Outlook program to
malfunction. However, the error notice identified the problem file (one of
the duplicate application files created by the restore function) and once I
deleted that file it worked fine.

So I suppose its possible that the Prompt problem is a programming problem
and the crash problem could be a software bug from my installation. However,
all other features of Access and all other Office programs seem to be working
fine.

But if I can eliminate the prompts then I needn't worry about the crashes.

Does any of this help with yur sleuthing?

FJ,

Thanks for the further details. However, this doesn't really make sense
to me so far. So, more questions I'm afraid!

When it "crashes" after prompting you for the AssignorID value, what
makes you think it is "planning" to ask for the others? Or do you mean
you get prompted for the DebtorID and CurrencyID, even after the crash?
(Which would be astounding). Does the Parameter Prompt ask for
'AssignorID', or does it ask for '[Forms]![Receipts]![AssignorID]'? And
this is happening when you close the main form, right? How are you
closing it?... the [x] at top right, or your own close button on the
form, or something else?

--
Steve Schapel, Microsoft Access MVP

OK. Here goes...

The main form is called Receipts. It contains several fields but 3 of them
are combo-boxes in which I select ASSIGNORID, DEBTORID and CURRENCY.
RECEIPTID is the auto-generated number key and is what ties it to the subform
called Receipts Subform. That subform has its own subform called "Receipts
Subform Invoices and Balances subform". The query for this form contains the
3 fields from the main form (ASSIGNORID, DEBTORID and CURRENCYID) and these
are the "Enter Parameter Value" requests that pop up whenever I close the
form. Although only the first one pops up and when I close it the whole
system crashes before the others appear in sequence.

The query for this second subform is as follows:

SELECT Format(Nz([Invoices Payments Received To Date]![SumOfAmount
Paid],0),"Currency") AS [Previous Payments],
Invoices!InvoiceAmount-Nz([Invoices Payments Received To Date]![SumOfAmount
Paid],0) AS Balance, Invoices.INVOICEID, Invoices.AssignorInvoiceNumber
FROM [Assignment Requests] INNER JOIN (Invoices LEFT JOIN [Invoices Payments
Received To Date] ON Invoices.INVOICEID = [Invoices Payments Received To
Date].INVOICEID) ON [Assignment Requests].ASSIGNMENTREQUESTID =
Invoices.ASSIGNMENTREQUESTID
WHERE (((Invoices.DEBTORID)=[Forms]![Receipts]![DEBTORID]) AND (([Assignment
Requests].ASSIGNORID)=[Forms]![Receipts]![ASSIGNORID]) AND (([Assignment
Requests].CURRENCYID)=[Forms]![Receipts]![Currency]) AND
((Invoices.Closed)=No))
ORDER BY Invoices.AssignorInvoiceNumber;

Just in case its of any value, the query for the first subform is:

SELECT [Receipt Allocations].RECEIPTALLOCATIONID, [Receipt
Allocations].RECEIPTID, [Receipt Allocations].INVOICEID, [Receipt
Allocations].ReceiptAllocation, [Receipt Allocations].ClosingReceipt,
Invoices.Closed, Invoices.DateClosed, Invoices.AssignorInvoiceNumber,
Invoices.CLOSEDHOWID, Invoices.GrossDiscFee, Invoices.ConsFee,
Invoices.RebatePayable
FROM Invoices INNER JOIN [Receipt Allocations] ON Invoices.INVOICEID =
[Receipt Allocations].INVOICEID
WHERE (((Invoices.Closed)=No))
ORDER BY Invoices.AssignorInvoiceNumber;



There is no macro associated with the closing of the main form but there is
a macro upon opening which sets the form to a new record and maximizes it.
However, that same macro is used on several other forms and causes no
problems.

Are you able to decipher anything from this?

 

[Steve Schapel]

Sleuthing's definitely right! I don't think I've encountered anything
like this before. Just as an experiment, can you temporarily alter the
query by removing the three criteria that reference the main form, i.e.
[Forms]![Receipts]![DEBTORID] and the other two, and see what happens?

 

[Guest]

I removed the 3 criteria as suggested and had no problem closing the form. If
I removed them one at a time, the system crashed each time until all 3 were
gone.

Also, if I open the query itself (with the related forms closed) I get the
same prompt for DEBTORID. However, when I click Cancel, the query closes with
no problem.

So the crash problem arises only when any of the 3 criteria are within the
query and does not arise when the query is closed in isolation.

??????

Sleuthing's definitely right! I don't think I've encountered anything
like this before. Just as an experiment, can you temporarily alter the
query by removing the three criteria that reference the main form, i.e.
[Forms]![Receipts]![DEBTORID] and the other two, and see what happens?

--
Steve Schapel, Microsoft Access MVP

Steve,

Not sure if this will help but this is what appears on the top of the error
report I get when the system crashes.

AppName: msaccess.exe AppVer: 10.0.6771.0 ModName: msaccess.exe
ModVer: 10.0.6771.0 Offset: 000a90ad


To answer your questions...

The problem occurs whether I close the main form using the X or the macro
driven Close button within the form.

I don't know for sure that the system would prompt for all 3 of the
combo-box fields because as you noted, the system crashes after I cancel the
first prompt. However, if I re-arrange those fields within the query, the
prompt is for whichever field is left-most within the query (currently it
prompts for [Forms]![Receipts]![DEBTORID] ).

Also, I recently bought a new computer and loaded Office onto it (including
Access). I was having the problem of the promt appearing upon closing the
form on my old computer but I don't recall it causing a system crash.
However, I've re-installed Office using the "repair" mode at least 3 times
and the crashing problem persists.

I'm using WinXP and I also did a system restore at one point when I was
loading programs onto the new computer. That caused the Outlook program to
malfunction. However, the error notice identified the problem file (one of
the duplicate application files created by the restore function) and once I
deleted that file it worked fine.

So I suppose its possible that the Prompt problem is a programming problem
and the crash problem could be a software bug from my installation. However,
all other features of Access and all other Office programs seem to be working
fine.

But if I can eliminate the prompts then I needn't worry about the crashes.

Does any of this help with yur sleuthing?

 

[Steve Schapel]

FJ,

Well, it makes sense that you would get the parameter prompts when you
try to open the query, since the Receipts form is not open at the time,
so the value of [Forms]![Receipts]![DEBTORID] cannot be evaluated. What
I can't figure out is why Access is trying to evaluate the query when
you close the form, and why the app crashes.

Are you able to email the file to me? If so, zipped for preference, and
send to steves at mvps dot org I'll see how it goes on my computer

 

[Steve Schapel]

FJ,

Ok, I have looked at your database. I was not really able to run your
application in its existing form, as there are References set to files I
don't have for example MSPFCTL1, but I think I would have the same
problem happening here.

To be honest, I haven't been able to completely wade my way through your
design at the moment. It is quite involuted. For example, you have a
query [Receipts Subform Invoices & Balances] as the Row Source of a
combobox on the subform, and this same query is the Record Source of the
subsubform. I have used a subsubform in the Footer section of a subform
before, but only by controlling the subsubform's data via the Link
Master Fields and Link Child Fields properties - in other words the
subsubform is directly related many-to-one to the subform. It is not
clear whether this is the case with your subsubform. In fact, in the
data you supplied, I could not find any example where there was any
subform data at all related to any main form record.

In my experience, it is very unusual to have a Record Source for a
subform which is a query which specifically references main form
controls in its criteria.

It is true that when you close the main form, the main form unloads and
closes before the subform, and the subform in turn before the
subsubform. Still, I did not expect that this would cause a crash.

There would be a kludgy way to work around the problem. You could use a
SetValue action in a macro which is applied on the Unload event of the
main form, to set the Record Surce of the subsubform to "" and this
would solve the immediate problem. However, if it was mine, I would be
re-visiting the overall design and concept of the form. Try to find a
way such that the subform records are defined acccording to the value of
the main form's primary key (or other unique index), and the subsubform
records are defined acccording to the value of the subform's primary key
(or other unique index).

By the way, as an aside, I should mention that it is not a good idea to
usew characters such as & or - or # as part of the name of fields or
controls or database objects.