Open Form is cancelled

[Claudette Hennessy]

This should be simple but...
I have a form that is opened by a form by specifying the year = xxxx (I
also tried year = xxxx or year is null, and various combinations of XOR)

The table data is:

Dealer Year
Dealer1 2008
Dealer2
Dealer3 2009
Dealer4 2009
Dealer5
Dealer6 2008
....
The desired output for 2008 would be
Dealer1 2008
Dealer2
Dealer3
Dealer4
Dealer5
Dealer6 2008

The desired output for 2009 would be
Dealer1
Dealer2
Dealer3 2009
Dealer4 2009
Dealer5
Dealer6

The query for the called form is
SELECT tblDealer.ShopID, tblDealer.ShopName, tblContractTemp.Spring,
tblContractTemp.Summer, tblContractTemp.Fall, tblContractTemp.Year
FROM tblContractTemp INNER JOIN tblDealer ON tblContractTemp.ShopID =
tblDealer.ShopID
ORDER BY tblDealer.ShopName
UNION SELECT tblDealer.ShopID, tblDealer.ShopName," " AS Spring, " " AS
Summer, " " AS Fall, " " AS Year
FROM tblContractTemp INNER JOIN tblDealer ON tblContractTemp.ShopID =
tblDealer.ShopID
ORDER BY tblDealer.ShopName;

The query runs fine on its own, but when the form is called from the dialog
form I get an 'Open Form is cancelled' message. The query returns

Dealer1
Dealer2
Dealer3
Dealer3 2009
Dealer4
Dealer4 2009
Dealer5
Dealer6

Which is not exactly what I want, but is close.

Thanks in advance,
Claudette

 

[tina]

why are you making a UNION query of the same records twice? of course you're
getting duplicate records in the output - except one pulls the year and one
doesn't - because you're calling duplicate records using the exact same
tables/joins.

try an ordinary SELECT query rather than the UNION query, as

SELECT tblDealer.ShopID, tblDealer.ShopName, tblContractTemp.Spring,
tblContractTemp.Summer, tblContractTemp.Fall, tblContractTemp.Year As
ShopYear
FROM tblContractTemp INNER JOIN tblDealer ON tblContractTemp.ShopID =
tblDealer.ShopID
ORDER BY tblDealer.ShopName

notice that i aliased the "Year" field as "ShopYear". Year is a Reserved
word in Access, and as such should not be used by itself to name anything in
the database that *you* name. the optimum solution is to rename the field in
the table, but if you're too far along in development to make that
practical, aliasing the fieldname everywhere you use it is a workaround. for
more information, see http://home.att.net/~california.db/tips.html#aTip5.

you don't say how the "form called from a form" has its' records filtered.
but whether you're setting criteria in a query that's used as the called
form's RecordSource, or by applying a WHERE clause in the OpenForm action,
or by applying a filter to the called form after it opens, the *basic*
syntax is the same, as

"ShopYear = " & Me!CallingFormControl _
& " Or ShopYear Is Null"

hth

 

[Claudette Hennessy]

I'm unioning the two queries because I need to have every dealer listed in
the form whether or not they have an entry in the Year field and whether or
not they are listed for years other than the year selected. If they have an
entry for 2008, they do not show up in a query that selects for 2009. I need
a dynaset like Ex 1 or 2. You are right about the Year issue, I need to
change it.
Claudette

 

[tina]

the single-query SQL i posted, from your posted SQL statement, should give
you all records in the table, regardless of the presence of a value in the
year field - because there is no criteria in that base query to filter for
year values, or lack of same.

the criteria i posted can then be applied at the form level by one of the
methods i described, to show only the records with a specified year or no
year value, as your example showed. did you try the solution i suggested? if
so, what problems occurred? maybe i or someone else can help you solve them.

hth

 

[Claudette Hennessy]

Tina, thank you for your response, but...let me restate the problem.
Running your query does not provide the desired result, which is a list of
all dealers, including those with the criteria. The desired output for for
2009 or is null would be

Dealer1
Dealer2
Dealer3 2009
Dealer4 2009
Dealer5
Dealer6

Dealers1 and 6 have values of 2008, if I apply criteria to your query, I
get

Dealer2
Dealer3 2009
Dealer4 2009
Dealer5

I need to have Dealer 1 and Dealer 6 in the list, so an entry for 2009 can
be made.
Thanks again, I keep thinking there is a simple solution to this...
Claudette

 

[tina]

okay, i understand what you're saying, more or less. my next question would
be, are you wanting to *update* the year for the 2008 dealers to 2009? or
are you wanting to add additional records for those dealers, so they have a
2008 record and a 2009 record?

after you answer that question, i'd like to know what your tables structure
is, i think.

hth

 

[Claudette Hennessy]

Tina, yes, in the example below, if the user selects Dealer1 and Dealer2 to
enter 2009, new records for Dealer1 and Dealer2 are added to the table. The
code for that insert works fine. So my issues are two, how to write a select
statement that gets me the desired output listed below, and why I get an
"Open Form Action is cancelled" when I open a form sourced by the query
posted below.
Claudette

 

[tina]

well, as for why you're getting the Open Form Action is cancelled message,
i'm afraid i can't guess. that's the sort of thing i figure out via hands-on
trial-and-error troubleshooting. as for the query, i'm afraid i'm still
trying to understand your goal. if you want to add dealer records for a
specific year, why run an Append query, or recordset Add, or however you're
inserting records in the table? how about a query that pulls the dealers who
already have a record for the given year (if you need to see those), and
simply add records directly for the dealers not already in the recordset?
i've no idea what your tables/relationships structure is, but i'm guessing
that you could use a combobox control to list all the dealers; and a unique
index on the dealer/year fields in the table would prevent duplicate
dealer/year records from being added.

what it comes down to is, when you have a particular vision for your user
interface but can't get it to work, you can always find another way to get
what you're after.

hth

 

[Claudette Hennessy]

Actually I got the solution from another source, it is

Select Dealer,Max(DealerYear) As FilteredYear FROM
(select Dealer, iif([Year]=[Enter Year],[Year],null) as DealerYear
from table) as q
group by Dealer

Also There is a good discussion on Open Form Action is cancelled at
mydatabasesupport.com/forums/ms-access/

thank you for your response.

Claudette

 

[tina]

you're welcome, glad you found a solution that works for you.

Actually I got the solution from another source, it is

Select Dealer,Max(DealerYear) As FilteredYear FROM
(select Dealer, iif([Year]=[Enter Year],[Year],null) as DealerYear
from table) as q
group by Dealer

Also There is a good discussion on Open Form Action is cancelled at
mydatabasesupport.com/forums/ms-access/

thank you for your response.

Claudette

well, as for why you're getting the Open Form Action is cancelled message,
i'm afraid i can't guess. that's the sort of thing i figure out via
hands-on
trial-and-error troubleshooting. as for the query, i'm afraid i'm still
trying to understand your goal. if you want to add dealer records for a
specific year, why run an Append query, or recordset Add, or however
you're
inserting records in the table? how about a query that pulls the dealers
who
already have a record for the given year (if you need to see those), and
simply add records directly for the dealers not already in the recordset?
i've no idea what your tables/relationships structure is, but i'm guessing
that you could use a combobox control to list all the dealers; and a
unique
index on the dealer/year fields in the table would prevent duplicate
dealer/year records from being added.

what it comes down to is, when you have a particular vision for your user
interface but can't get it to work, you can always find another way to get
what you're after.

hth


Dealer2
to 2009?
or "
AS