I know this is getting old, but please bare with me...

This is the SQL Statement that is currently on my form's model combo box row
source:

SELECT tblModel.model, tblModel.bstyle, tblModel.engine FROM tblMake RIGHT
JOIN tblModel ON tblMake.make=tblModel.make WHERE
(((tblMake.make)=forms!frmInventory!Make)) ORDER BY tblModel.model;

It works perfect, but I would like to add a "<Add New>" value to the list
generated by this query. I tried adding the "<Add New>" directly to the
table, but it keeps getting filtered out by the criteria of the query.

Eventhough I am not familiar with SQL syntax, I tried using a UNION query.
I used the following statement:

SELECT tblModel.model, tblModel.bstyle, tblModel.engine FROM tblMake RIGHT
JOIN tblModel ON tblMake.make=tblModel.make WHERE
(((tblMake.make)=forms!frmInventory!Make)) ORDER BY tblModel.model UNION
SELECT "<Add New>", NULL, NULL, FROM tblModel;

Needless to say, I am getting syntax errors out the wazoo...

Thanks in advance for all the help you guyz have given me...
Walter

I have seen questions on this before, but I don't know if there is a way to
do that. (It would be nice). I think the easiest way to do this is to use
the NotInList event of the Combo box so that if a user types in a value not
in the query, it will present a message box asking if they want to add a new
record. If they do, go to a rew record and carry on from there.

"RIP" wrote:

> I know this is getting old, but please bare with me...
>
> This is the SQL Statement that is currently on my form's model combo box row
> source:
>
> SELECT tblModel.model, tblModel.bstyle, tblModel.engine FROM tblMake RIGHT
> JOIN tblModel ON tblMake.make=tblModel.make WHERE
> (((tblMake.make)=forms!frmInventory!Make)) ORDER BY tblModel.model;
>
> It works perfect, but I would like to add a "<Add New>" value to the list
> generated by this query. I tried adding the "<Add New>" directly to the
> table, but it keeps getting filtered out by the criteria of the query.
>
> Eventhough I am not familiar with SQL syntax, I tried using a UNION query.
> I used the following statement:
>
> SELECT tblModel.model, tblModel.bstyle, tblModel.engine FROM tblMake RIGHT
> JOIN tblModel ON tblMake.make=tblModel.make WHERE
> (((tblMake.make)=forms!frmInventory!Make)) ORDER BY tblModel.model UNION
> SELECT "<Add New>", NULL, NULL, FROM tblModel;
>
> Needless to say, I am getting syntax errors out the wazoo...
>
> Thanks in advance for all the help you guyz have given me...
> Walter
>
>
>

Too many commas in the second SELECT clause.
ORDER BY applies at the end of the last query only

Try

SELECT tblModel.model, tblModel.bstyle, tblModel.engine FROM tblMake RIGHT
JOIN tblModel ON tblMake.make=tblModel.make WHERE
(((tblMake.make)=forms!frmInventory!Make))
UNION
SELECT "<Add New>", NULL, NULL FROM tblModel
ORDER BY model;

RIP wrote:
>
> I know this is getting old, but please bare with me...
>
> This is the SQL Statement that is currently on my form's model combo box row
> source:
>
> SELECT tblModel.model, tblModel.bstyle, tblModel.engine FROM tblMake RIGHT
> JOIN tblModel ON tblMake.make=tblModel.make WHERE
> (((tblMake.make)=forms!frmInventory!Make)) ORDER BY tblModel.model;
>
> It works perfect, but I would like to add a "<Add New>" value to the list
> generated by this query. I tried adding the "<Add New>" directly to the
> table, but it keeps getting filtered out by the criteria of the query.
>
> Eventhough I am not familiar with SQL syntax, I tried using a UNION query.
> I used the following statement:
>
> SELECT tblModel.model, tblModel.bstyle, tblModel.engine FROM tblMake RIGHT
> JOIN tblModel ON tblMake.make=tblModel.make WHERE
> (((tblMake.make)=forms!frmInventory!Make)) ORDER BY tblModel.model UNION
> SELECT "<Add New>", NULL, NULL, FROM tblModel;
>
> Needless to say, I am getting syntax errors out the wazoo...
>
> Thanks in advance for all the help you guyz have given me...
> Walter

=?Utf-8?B?UklQ?= <(E-Mail Removed)> wrote in
news:0EBC1DE9-04F8-4D2F-9D76-(E-Mail Removed):

> It works perfect, but I would like to add a "<Add New>" value to the
> list generated by this query. I tried adding the "<Add New>" directly
> to the table, but it keeps getting filtered out by the criteria of the
> query.

Easy way: use a UNION to add an extra row to the result set:

SELECT tblModel.model,
tblModel.bstyle,
tblModel.engine
FROM tblMake
RIGHT JOIN tblModel
ON tblMake.make=tblModel.make
WHERE tblMake.make=forms!frmInventory!Make
ORDER BY tblModel.model

UNION

SELECT "<Add new>", NULL, NULL, NULL

I think that in older versions of Jet you have to have a FROM clause so
you'll need a line at the bottom like FROM tblModel

The A-level way is to use a List Box Function, which is incredibly
satisfying when it goes right (tee hee) -- fully documented in the Help
files.

All the best


Tim F

John,

Eliminating the extra commas did the trick...

Thank U
Walter

"John Spencer (MVP)" wrote:

> Too many commas in the second SELECT clause.
> ORDER BY applies at the end of the last query only
>
> Try
>
> SELECT tblModel.model, tblModel.bstyle, tblModel.engine FROM tblMake RIGHT
> JOIN tblModel ON tblMake.make=tblModel.make WHERE
> (((tblMake.make)=forms!frmInventory!Make))
> UNION
> SELECT "<Add New>", NULL, NULL FROM tblModel
> ORDER BY model;
>
> RIP wrote:
> >
> > I know this is getting old, but please bare with me...
> >
> > This is the SQL Statement that is currently on my form's model combo box row
> > source:
> >
> > SELECT tblModel.model, tblModel.bstyle, tblModel.engine FROM tblMake RIGHT
> > JOIN tblModel ON tblMake.make=tblModel.make WHERE
> > (((tblMake.make)=forms!frmInventory!Make)) ORDER BY tblModel.model;
> >
> > It works perfect, but I would like to add a "<Add New>" value to the list
> > generated by this query. I tried adding the "<Add New>" directly to the
> > table, but it keeps getting filtered out by the criteria of the query.
> >
> > Eventhough I am not familiar with SQL syntax, I tried using a UNION query.
> > I used the following statement:
> >
> > SELECT tblModel.model, tblModel.bstyle, tblModel.engine FROM tblMake RIGHT
> > JOIN tblModel ON tblMake.make=tblModel.make WHERE
> > (((tblMake.make)=forms!frmInventory!Make)) ORDER BY tblModel.model UNION
> > SELECT "<Add New>", NULL, NULL, FROM tblModel;
> >
> > Needless to say, I am getting syntax errors out the wazoo...
> >
> > Thanks in advance for all the help you guyz have given me...
> > Walter
>

Tim,

Thank you also for your input... The way you broke down the statement
shined alot of light on my understanding of the syntax...

Thank U
Walter

"Tim Ferguson" wrote:

> =?Utf-8?B?UklQ?= <(E-Mail Removed)> wrote in
> news:0EBC1DE9-04F8-4D2F-9D76-(E-Mail Removed):
>
> > It works perfect, but I would like to add a "<Add New>" value to the
> > list generated by this query. I tried adding the "<Add New>" directly
> > to the table, but it keeps getting filtered out by the criteria of the
> > query.
>
> Easy way: use a UNION to add an extra row to the result set:
>
> SELECT tblModel.model,
> tblModel.bstyle,
> tblModel.engine
> FROM tblMake
> RIGHT JOIN tblModel
> ON tblMake.make=tblModel.make
> WHERE tblMake.make=forms!frmInventory!Make
> ORDER BY tblModel.model
>
> UNION
>
> SELECT "<Add new>", NULL, NULL, NULL
>
> I think that in older versions of Jet you have to have a FROM clause so
> you'll need a line at the bottom like FROM tblModel
>
> The A-level way is to use a List Box Function, which is incredibly
> satisfying when it goes right (tee hee) -- fully documented in the Help
> files.
>
> All the best
>
>
> Tim F
>
>
>

Klatuu,

I am familiar with utilizing the On Not In List event, I wanted to learn
how the SQL Statement works, which is a little more complicated... Thats why
I wanted to add the <Add New> to the value list of the control.

Thank U also for ur input...

Walter

"Klatuu" wrote:

> I have seen questions on this before, but I don't know if there is a way to
> do that. (It would be nice). I think the easiest way to do this is to use
> the NotInList event of the Combo box so that if a user types in a value not
> in the query, it will present a message box asking if they want to add a new
> record. If they do, go to a rew record and carry on from there.
>
> "RIP" wrote:
>
> > I know this is getting old, but please bare with me...
> >
> > This is the SQL Statement that is currently on my form's model combo box row
> > source:
> >
> > SELECT tblModel.model, tblModel.bstyle, tblModel.engine FROM tblMake RIGHT
> > JOIN tblModel ON tblMake.make=tblModel.make WHERE
> > (((tblMake.make)=forms!frmInventory!Make)) ORDER BY tblModel.model;
> >
> > It works perfect, but I would like to add a "<Add New>" value to the list
> > generated by this query. I tried adding the "<Add New>" directly to the
> > table, but it keeps getting filtered out by the criteria of the query.
> >
> > Eventhough I am not familiar with SQL syntax, I tried using a UNION query.
> > I used the following statement:
> >
> > SELECT tblModel.model, tblModel.bstyle, tblModel.engine FROM tblMake RIGHT
> > JOIN tblModel ON tblMake.make=tblModel.make WHERE
> > (((tblMake.make)=forms!frmInventory!Make)) ORDER BY tblModel.model UNION
> > SELECT "<Add New>", NULL, NULL, FROM tblModel;
> >
> > Needless to say, I am getting syntax errors out the wazoo...
> >
> > Thanks in advance for all the help you guyz have given me...
> > Walter
> >
> >
> >

=?Utf-8?B?UklQ?= <(E-Mail Removed)> wrote in news:BDF99838-
4354-4899-BA4E-(E-Mail Removed):

> The way you broke down the statement
> shined alot of light on my understanding of the syntax...

The one thing that I HATE about Access's SQL designer is the way it refuses
to format the command; and often it seems trash a laid-out command
deliberately. SQL does not have to be a human-unreadable language, rather
the reverse, in fact.

All the best


Tim F