Adding a value to the rowsource of a combo box

[RIP]

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

 

[Klatuu]

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.

 

[John Spencer (MVP)]

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;

 

[Tim Ferguson]

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

 

[RIP]

John,

Eliminating the extra commas did the trick...

Thank U
Walter

 

[RIP]

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

 

[RIP]

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

 

[Tim Ferguson]

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