Accounting system query

Thanks Steve. It worked perfectly.

"Steve Schapel" wrote:

FJ,

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

--
Steve Schapel, Microsoft Access MVP

FJquestioner wrote:
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,

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.

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?

--
Steve Schapel, Microsoft Access MVP

FJquestioner wrote:
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.

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" wrote:

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?

--
Steve Schapel, Microsoft Access MVP

FJquestioner wrote:
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.

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

FJquestioner wrote:
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,

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" wrote:

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

FJquestioner wrote:
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?

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

FJquestioner wrote:
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?

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.

??????





"Steve Schapel" wrote:

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

FJquestioner wrote:
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,

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, Microsoft Access MVP

FJquestioner wrote:
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.

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.

--
Steve Schapel, Microsoft Access MVP

Steve Schapel wrote:
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 :-)