UNION Query Statement Bug/Problem

[Guest]

For my Microsoft Access Database I have have a Customer form which allows me
to add and edit Customer details. The Customer Table contains a 'Customer ID'
field (The Primary Key), First Name, Last Name and Business Name (There are
others but these are the only ones used for the search combo box). Previously
for the customer form, I set the Search Combo box (which looks up a record
and display it) Row Source to allow the User to type in the First Name and
then Last Name with a space between. This worked great but didn't allows me
to search by Surname if I didn't know the first name or allow me to search by
business name.

Recently I just Purchased Microsoft Access 2007 (My database was built
originally in Microsoft Access 2000 Format, Upgraded to Microsoft Access
2002/2003 format 6 months ago) and I upgraded my database to the new
Microsoft Access 2007 format. As far as I could see the database was working
100% fine and so I decided to do a lot of upgrading to forms and try out some
new things. So when I found out how to use UNION Query Statements I thought I
should use them in my Customer Form. When I opened up my database to edit the
Combo Box I held down the shift key to bring up the navigation bar (I have
setup my database not to show the navigation bar when opening up the database
normally) and I edited the row source.

After some trial and error I was able to get the combo box to work exactly
the way I wanted it. If I selected a customer by First Name and then Last
Name the record would show and the same occured with last name,first name and
business name. I tested this quite a few times and it indeed worked. the next
day I opened up the database (This time without the shift key as I needed
only to edit customer details) and attempted to lookup the customer. When I
selected the customer from the combo box the form would not go to the record
and would remain blank.

Immediately I assumed I must have made a mistake editing the night before so
I closed down the database and started it up by holding the shift key. After
briefly looking at the SQL Query Statement for the Combo Boxes row source
(and not editing the code) I opened the form to test it and it worked. I then
closed the database down and opened it up (without holding the shift key) and
it didn't work. I then closed it down holding the shift key this time and it
did work. Since then I have tested this numerous times and can conclude that
the Customer Form will only lookup customers if I open the database holding
the shift key. I then assumed this may have something to do with the
navigation bar, so I set the navigation bar to always be on in the Access
Options. This still didn't help.

This to me would appear to be more of a Microsoft Access 2007 bug than a
mistake I have made since opening forms up to test in the editing mode (by
holding down the shift key when the Microsoft Access Database is opened)
should work the same if I opened it up not in editing mode. The code that I
use for the Search Combo Box is as follows:

SELECT Customers.ID, [Customers].[First Name] & " " & [Customers].[Last
Name] AS Expr1 FROM Customers; UNION ALL SELECT Customers.ID,
[Customers].[Last Name] & " " & [Customers].[First Name] FROM Customers;
UNION ALL SELECT Customers.ID, [Customers].[Business Name] FROM Customers;

 

[Sylvain Lafontaine]

Wild guess: try removing the ; in: « ... Customers; UNION ... » (two
times).

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

For my Microsoft Access Database I have have a Customer form which allows
me
to add and edit Customer details. The Customer Table contains a 'Customer
ID'
field (The Primary Key), First Name, Last Name and Business Name (There
are
others but these are the only ones used for the search combo box).
Previously
for the customer form, I set the Search Combo box (which looks up a record
and display it) Row Source to allow the User to type in the First Name and
then Last Name with a space between. This worked great but didn't allows
me
to search by Surname if I didn't know the first name or allow me to search
by
business name.

Recently I just Purchased Microsoft Access 2007 (My database was built
originally in Microsoft Access 2000 Format, Upgraded to Microsoft Access
2002/2003 format 6 months ago) and I upgraded my database to the new
Microsoft Access 2007 format. As far as I could see the database was
working
100% fine and so I decided to do a lot of upgrading to forms and try out
some
new things. So when I found out how to use UNION Query Statements I
thought I
should use them in my Customer Form. When I opened up my database to edit
the
Combo Box I held down the shift key to bring up the navigation bar (I have
setup my database not to show the navigation bar when opening up the
database
normally) and I edited the row source.

After some trial and error I was able to get the combo box to work exactly
the way I wanted it. If I selected a customer by First Name and then Last
Name the record would show and the same occured with last name,first name
and
business name. I tested this quite a few times and it indeed worked. the
next
day I opened up the database (This time without the shift key as I needed
only to edit customer details) and attempted to lookup the customer. When
I
selected the customer from the combo box the form would not go to the
record
and would remain blank.

Immediately I assumed I must have made a mistake editing the night before
so
I closed down the database and started it up by holding the shift key.
After
briefly looking at the SQL Query Statement for the Combo Boxes row source
(and not editing the code) I opened the form to test it and it worked. I
then
closed the database down and opened it up (without holding the shift key)
and
it didn't work. I then closed it down holding the shift key this time and
it
did work. Since then I have tested this numerous times and can conclude
that
the Customer Form will only lookup customers if I open the database
holding
the shift key. I then assumed this may have something to do with the
navigation bar, so I set the navigation bar to always be on in the Access
Options. This still didn't help.

This to me would appear to be more of a Microsoft Access 2007 bug than a
mistake I have made since opening forms up to test in the editing mode (by
holding down the shift key when the Microsoft Access Database is opened)
should work the same if I opened it up not in editing mode. The code that
I
use for the Search Combo Box is as follows:

SELECT Customers.ID, [Customers].[First Name] & " " & [Customers].[Last
Name] AS Expr1 FROM Customers; UNION ALL SELECT Customers.ID,
[Customers].[Last Name] & " " & [Customers].[First Name] FROM Customers;
UNION ALL SELECT Customers.ID, [Customers].[Business Name] FROM
Customers;

 

[Guest]

I removed the ";" from between the "FROM Customers; UNION" part of my SQL
Query Statement twice and still got the same result. I am only able to lookup
the record if I hold down shift when I open the database. After I tried this
I removed the last ";" from the statement as well (right at the end) and
tested this, same result. I believe these were initially added by
auto-correct to make my SQL Statements use the proper syntax.

As I said before, this appears to be a bug and not a syntax error I have
made but I am willing to hear someone prove me otherwise.
--
Dave

Wild guess: try removing the ; in: « ... Customers; UNION ... » (two
times).

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

For my Microsoft Access Database I have have a Customer form which allows
me
to add and edit Customer details. The Customer Table contains a 'Customer
ID'
field (The Primary Key), First Name, Last Name and Business Name (There
are
others but these are the only ones used for the search combo box).
Previously
for the customer form, I set the Search Combo box (which looks up a record
and display it) Row Source to allow the User to type in the First Name and
then Last Name with a space between. This worked great but didn't allows
me
to search by Surname if I didn't know the first name or allow me to search
by
business name.

Recently I just Purchased Microsoft Access 2007 (My database was built
originally in Microsoft Access 2000 Format, Upgraded to Microsoft Access
2002/2003 format 6 months ago) and I upgraded my database to the new
Microsoft Access 2007 format. As far as I could see the database was
working
100% fine and so I decided to do a lot of upgrading to forms and try out
some
new things. So when I found out how to use UNION Query Statements I
thought I
should use them in my Customer Form. When I opened up my database to edit
the
Combo Box I held down the shift key to bring up the navigation bar (I have
setup my database not to show the navigation bar when opening up the
database
normally) and I edited the row source.

After some trial and error I was able to get the combo box to work exactly
the way I wanted it. If I selected a customer by First Name and then Last
Name the record would show and the same occured with last name,first name
and
business name. I tested this quite a few times and it indeed worked. the
next
day I opened up the database (This time without the shift key as I needed
only to edit customer details) and attempted to lookup the customer. When
I
selected the customer from the combo box the form would not go to the
record
and would remain blank.

Immediately I assumed I must have made a mistake editing the night before
so
I closed down the database and started it up by holding the shift key.
After
briefly looking at the SQL Query Statement for the Combo Boxes row source
(and not editing the code) I opened the form to test it and it worked. I
then
closed the database down and opened it up (without holding the shift key)
and
it didn't work. I then closed it down holding the shift key this time and
it
did work. Since then I have tested this numerous times and can conclude
that
the Customer Form will only lookup customers if I open the database
holding
the shift key. I then assumed this may have something to do with the
navigation bar, so I set the navigation bar to always be on in the Access
Options. This still didn't help.

This to me would appear to be more of a Microsoft Access 2007 bug than a
mistake I have made since opening forms up to test in the editing mode (by
holding down the shift key when the Microsoft Access Database is opened)
should work the same if I opened it up not in editing mode. The code that
I
use for the Search Combo Box is as follows:

SELECT Customers.ID, [Customers].[First Name] & " " & [Customers].[Last
Name] AS Expr1 FROM Customers; UNION ALL SELECT Customers.ID,
[Customers].[Last Name] & " " & [Customers].[First Name] FROM Customers;
UNION ALL SELECT Customers.ID, [Customers].[Business Name] FROM
Customers;

 

[Sylvain Lafontaine]

If this problem occurs only with the new ACCDB format or if it's the same
with the older MDB format? Did you try to create an empty database file and
import everything into it? Are you sure that all the references (in the
VBA window) are OK?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

I removed the ";" from between the "FROM Customers; UNION" part of my SQL
Query Statement twice and still got the same result. I am only able to
lookup
the record if I hold down shift when I open the database. After I tried
this
I removed the last ";" from the statement as well (right at the end) and
tested this, same result. I believe these were initially added by
auto-correct to make my SQL Statements use the proper syntax.

As I said before, this appears to be a bug and not a syntax error I have
made but I am willing to hear someone prove me otherwise.
--
Dave

Wild guess: try removing the ; in: « ... Customers; UNION ... » (two
times).

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

For my Microsoft Access Database I have have a Customer form which
allows
me
to add and edit Customer details. The Customer Table contains a
'Customer
ID'
field (The Primary Key), First Name, Last Name and Business Name (There
are
others but these are the only ones used for the search combo box).
Previously
for the customer form, I set the Search Combo box (which looks up a
record
and display it) Row Source to allow the User to type in the First Name
and
then Last Name with a space between. This worked great but didn't
allows
me
to search by Surname if I didn't know the first name or allow me to
search
by
business name.

Recently I just Purchased Microsoft Access 2007 (My database was built
originally in Microsoft Access 2000 Format, Upgraded to Microsoft
Access
2002/2003 format 6 months ago) and I upgraded my database to the new
Microsoft Access 2007 format. As far as I could see the database was
working
100% fine and so I decided to do a lot of upgrading to forms and try
out
some
new things. So when I found out how to use UNION Query Statements I
thought I
should use them in my Customer Form. When I opened up my database to
edit
the
Combo Box I held down the shift key to bring up the navigation bar (I
have
setup my database not to show the navigation bar when opening up the
database
normally) and I edited the row source.

After some trial and error I was able to get the combo box to work
exactly
the way I wanted it. If I selected a customer by First Name and then
Last
Name the record would show and the same occured with last name,first
name
and
business name. I tested this quite a few times and it indeed worked.
the
next
day I opened up the database (This time without the shift key as I
needed
only to edit customer details) and attempted to lookup the customer.
When
I
selected the customer from the combo box the form would not go to the
record
and would remain blank.

Immediately I assumed I must have made a mistake editing the night
before
so
I closed down the database and started it up by holding the shift key.
After
briefly looking at the SQL Query Statement for the Combo Boxes row
source
(and not editing the code) I opened the form to test it and it worked.
I
then
closed the database down and opened it up (without holding the shift
key)
and
it didn't work. I then closed it down holding the shift key this time
and
it
did work. Since then I have tested this numerous times and can conclude
that
the Customer Form will only lookup customers if I open the database
holding
the shift key. I then assumed this may have something to do with the
navigation bar, so I set the navigation bar to always be on in the
Access
Options. This still didn't help.

This to me would appear to be more of a Microsoft Access 2007 bug than
a
mistake I have made since opening forms up to test in the editing mode
(by
holding down the shift key when the Microsoft Access Database is
opened)
should work the same if I opened it up not in editing mode. The code
that
I
use for the Search Combo Box is as follows:

SELECT Customers.ID, [Customers].[First Name] & " " & [Customers].[Last
Name] AS Expr1 FROM Customers; UNION ALL SELECT Customers.ID,
[Customers].[Last Name] & " " & [Customers].[First Name] FROM
Customers;
UNION ALL SELECT Customers.ID, [Customers].[Business Name] FROM
Customers;

 

[Guest]

I was just about to send you an empty database via e-mail including just the
Customer Form and the Customer Table. To do this I exported onto an empty
database the Customer Form and the Customer Table and then tested this. For
some reason the problem disappeared. My guess is that this problem have
occurred when I upgraded to the new Microsoft Access 2007 ACCDB Format. It is
strange that this problem didn't go away when I ran an automated repair in
Microsoft Access. I'll post back in a couple of hours to tell you if
completely exporting the database forms, tables, queries, reports and
importing them onto a blank ACCDB Microsoft Access 2007 file format
completely fixes the problem.
--
Dave

If this problem occurs only with the new ACCDB format or if it's the same
with the older MDB format? Did you try to create an empty database file and
import everything into it? Are you sure that all the references (in the
VBA window) are OK?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

I removed the ";" from between the "FROM Customers; UNION" part of my SQL
Query Statement twice and still got the same result. I am only able to
lookup
the record if I hold down shift when I open the database. After I tried
this
I removed the last ";" from the statement as well (right at the end) and
tested this, same result. I believe these were initially added by
auto-correct to make my SQL Statements use the proper syntax.

As I said before, this appears to be a bug and not a syntax error I have
made but I am willing to hear someone prove me otherwise.
--
Dave

Wild guess: try removing the ; in: « ... Customers; UNION ... » (two
times).

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


For my Microsoft Access Database I have have a Customer form which
allows
me
to add and edit Customer details. The Customer Table contains a
'Customer
ID'
field (The Primary Key), First Name, Last Name and Business Name (There
are
others but these are the only ones used for the search combo box).
Previously
for the customer form, I set the Search Combo box (which looks up a
record
and display it) Row Source to allow the User to type in the First Name
and
then Last Name with a space between. This worked great but didn't
allows
me
to search by Surname if I didn't know the first name or allow me to
search
by
business name.

Recently I just Purchased Microsoft Access 2007 (My database was built
originally in Microsoft Access 2000 Format, Upgraded to Microsoft
Access
2002/2003 format 6 months ago) and I upgraded my database to the new
Microsoft Access 2007 format. As far as I could see the database was
working
100% fine and so I decided to do a lot of upgrading to forms and try
out
some
new things. So when I found out how to use UNION Query Statements I
thought I
should use them in my Customer Form. When I opened up my database to
edit
the
Combo Box I held down the shift key to bring up the navigation bar (I
have
setup my database not to show the navigation bar when opening up the
database
normally) and I edited the row source.

After some trial and error I was able to get the combo box to work
exactly
the way I wanted it. If I selected a customer by First Name and then
Last
Name the record would show and the same occured with last name,first
name
and
business name. I tested this quite a few times and it indeed worked.
the
next
day I opened up the database (This time without the shift key as I
needed
only to edit customer details) and attempted to lookup the customer.
When
I
selected the customer from the combo box the form would not go to the
record
and would remain blank.

Immediately I assumed I must have made a mistake editing the night
before
so
I closed down the database and started it up by holding the shift key.
After
briefly looking at the SQL Query Statement for the Combo Boxes row
source
(and not editing the code) I opened the form to test it and it worked.
I
then
closed the database down and opened it up (without holding the shift
key)
and
it didn't work. I then closed it down holding the shift key this time
and
it
did work. Since then I have tested this numerous times and can conclude
that
the Customer Form will only lookup customers if I open the database
holding
the shift key. I then assumed this may have something to do with the
navigation bar, so I set the navigation bar to always be on in the
Access
Options. This still didn't help.

This to me would appear to be more of a Microsoft Access 2007 bug than
a
mistake I have made since opening forms up to test in the editing mode
(by
holding down the shift key when the Microsoft Access Database is
opened)
should work the same if I opened it up not in editing mode. The code
that
I
use for the Search Combo Box is as follows:

SELECT Customers.ID, [Customers].[First Name] & " " & [Customers].[Last
Name] AS Expr1 FROM Customers; UNION ALL SELECT Customers.ID,
[Customers].[Last Name] & " " & [Customers].[First Name] FROM
Customers;
UNION ALL SELECT Customers.ID, [Customers].[Business Name] FROM
Customers;

 

[Guest]

Found out what the problem was. The problem does not occur depending on
whether I open the database holding the shift key or not but depends on
whether the Startup Menu which I have setup to start when I open the
Microsoft Access Database file is open or not. I then found out the problem
was a small error I made in the button which opens up the Customer Form. I
changed this and everything works okay now.
--
Dave

If this problem occurs only with the new ACCDB format or if it's the same
with the older MDB format? Did you try to create an empty database file and
import everything into it? Are you sure that all the references (in the
VBA window) are OK?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

I removed the ";" from between the "FROM Customers; UNION" part of my SQL
Query Statement twice and still got the same result. I am only able to
lookup
the record if I hold down shift when I open the database. After I tried
this
I removed the last ";" from the statement as well (right at the end) and
tested this, same result. I believe these were initially added by
auto-correct to make my SQL Statements use the proper syntax.

As I said before, this appears to be a bug and not a syntax error I have
made but I am willing to hear someone prove me otherwise.
--
Dave

Wild guess: try removing the ; in: « ... Customers; UNION ... » (two
times).

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


For my Microsoft Access Database I have have a Customer form which
allows
me
to add and edit Customer details. The Customer Table contains a
'Customer
ID'
field (The Primary Key), First Name, Last Name and Business Name (There
are
others but these are the only ones used for the search combo box).
Previously
for the customer form, I set the Search Combo box (which looks up a
record
and display it) Row Source to allow the User to type in the First Name
and
then Last Name with a space between. This worked great but didn't
allows
me
to search by Surname if I didn't know the first name or allow me to
search
by
business name.

Recently I just Purchased Microsoft Access 2007 (My database was built
originally in Microsoft Access 2000 Format, Upgraded to Microsoft
Access
2002/2003 format 6 months ago) and I upgraded my database to the new
Microsoft Access 2007 format. As far as I could see the database was
working
100% fine and so I decided to do a lot of upgrading to forms and try
out
some
new things. So when I found out how to use UNION Query Statements I
thought I
should use them in my Customer Form. When I opened up my database to
edit
the
Combo Box I held down the shift key to bring up the navigation bar (I
have
setup my database not to show the navigation bar when opening up the
database
normally) and I edited the row source.

After some trial and error I was able to get the combo box to work
exactly
the way I wanted it. If I selected a customer by First Name and then
Last
Name the record would show and the same occured with last name,first
name
and
business name. I tested this quite a few times and it indeed worked.
the
next
day I opened up the database (This time without the shift key as I
needed
only to edit customer details) and attempted to lookup the customer.
When
I
selected the customer from the combo box the form would not go to the
record
and would remain blank.

Immediately I assumed I must have made a mistake editing the night
before
so
I closed down the database and started it up by holding the shift key.
After
briefly looking at the SQL Query Statement for the Combo Boxes row
source
(and not editing the code) I opened the form to test it and it worked.
I
then
closed the database down and opened it up (without holding the shift
key)
and
it didn't work. I then closed it down holding the shift key this time
and
it
did work. Since then I have tested this numerous times and can conclude
that
the Customer Form will only lookup customers if I open the database
holding
the shift key. I then assumed this may have something to do with the
navigation bar, so I set the navigation bar to always be on in the
Access
Options. This still didn't help.

This to me would appear to be more of a Microsoft Access 2007 bug than
a
mistake I have made since opening forms up to test in the editing mode
(by
holding down the shift key when the Microsoft Access Database is
opened)
should work the same if I opened it up not in editing mode. The code
that
I
use for the Search Combo Box is as follows:

SELECT Customers.ID, [Customers].[First Name] & " " & [Customers].[Last
Name] AS Expr1 FROM Customers; UNION ALL SELECT Customers.ID,
[Customers].[Last Name] & " " & [Customers].[First Name] FROM
Customers;
UNION ALL SELECT Customers.ID, [Customers].[Business Name] FROM
Customers;