Combo box - Add New

[Robert Neville]

I have a combo box that allows me to add data or select from the list.
If I select the record marker, [Add New], then the code adds a new
record. Now, I am developing a complicating routine that requires the
[Add New] marker to remain first in the list. Thus have a listindex
property must equal to zero. Generally, it does not create a problem,
yet on the rarely occasion inconsistent data may sort before the [Add
New] Marker, because preceding spaces sort before the bracket; and
null value & empty strings add complexity to this scenario.

Several approaches exist, yet I would appreciate any ideas from thread
readers before implementing one approach or the other. I am hoping to
do it through the combo boxes SQL/Query source. Here's what its look
like now.

SELECT tblAddr.AddrID, tblAddr.Addr1, tblAddr.AddrName, tblAddr.City,
tblAddr.StateID, tblAddr.PostalCode, tblAddr.Country
FROM tblAddr
ORDER BY tblAddr.Addr1;UNION SELECT AddNewMarker As Addr1 FROM
zstblDefault;

 

[Graham Mandeno]

Hi Robert

Add another dummy field to both parts of your union query, to indicate the
[Add New] row and sort on that first, before Addr1:

Select AddrID, Addr1, AddrName, City,
StateID, PostalCode, Country,
False as Header
from tblAddrSelect
Union Select 0, "[Add New]", "", "", "", "", "",
True from tblAddrSelect
Order by Header, Addr1;

Note that both parts of the union query must have the same number of columns
(8 in this case). I can't quite see how the query you quoted below would
work.

Also, note that your "[Add New]" doesn't have to come from a table, but you
can instead select a whole bunch of constants from any table and it will
return you a single row.

Assuming your bound column is set to 1 (returning AddrID as the control
value), selection of [Add New] would return a value of 0.

Note also that the "Header" column doesn't need to be displayed in the
combo, so here you could set ColumnCount to 7.

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.

 

[Robert Neville]

I have a combo box that allows me to add data or select from the list.
If I select the record marker, [Add New], then the code adds a new
record. Now, I am developing a complicating routine that requires the
[Add New] marker to remain first in the list. Thus have a listindex
property must equal to zero. Generally, it does not create a problem,
yet on the rarely occasion inconsistent data may sort before the [Add
New] Marker, because preceding spaces sort before the bracket; and
null value & empty strings add complexity to this scenario.

Several approaches exist, yet I would appreciate any ideas from thread
readers before implementing one approach or the other. I am hoping to
do it through the combo boxes SQL/Query source. Here's what its look
like now.

SELECT tblAddr.AddrID, tblAddr.Addr1, tblAddr.AddrName, tblAddr.City,
tblAddr.StateID, tblAddr.PostalCode, tblAddr.Country
FROM tblAddr
ORDER BY tblAddr.Addr1;UNION SELECT AddNewMarker As Addr1 FROM
zstblDefault;

Ok, here's one idea, yet it does not seem to fully work. When
activating datasheet view from the query window, it ask me to "Enter
Parameter Value." After clicking OK, it displays the records, yet
Addr1 is not sorted. The [Add New] marker appears at the top.

SELECT tblAddr.AddrID, tblAddr.Addr1, tblAddr.AddrName, tblAddr.City,
tblAddr.StateID, tblAddr.PostalCode, tblAddr.Country, 1 AS SortParm
FROM tblAddr
ORDER BY SortParm, tblAddr.Addr1;
UNION SELECT "", AddNewMarker As Addr1,"", "", "", "", "", 0 As
SortParm FROM zstblDefault;

 

[Graham Mandeno]

Robert

If you had used the query I posted it would have worked! As it is, you
should lose the "As Addr1", and the semicolon before UNION, and move the
ORDER BY clause to the end.

Any particular reason why you insist on opening a different table for the
[Add New] when you could just use another constant?

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.

I have a combo box that allows me to add data or select from the list.
If I select the record marker, [Add New], then the code adds a new
record. Now, I am developing a complicating routine that requires the
[Add New] marker to remain first in the list. Thus have a listindex
property must equal to zero. Generally, it does not create a problem,
yet on the rarely occasion inconsistent data may sort before the [Add
New] Marker, because preceding spaces sort before the bracket; and
null value & empty strings add complexity to this scenario.

Several approaches exist, yet I would appreciate any ideas from thread
readers before implementing one approach or the other. I am hoping to
do it through the combo boxes SQL/Query source. Here's what its look
like now.

SELECT tblAddr.AddrID, tblAddr.Addr1, tblAddr.AddrName, tblAddr.City,
tblAddr.StateID, tblAddr.PostalCode, tblAddr.Country
FROM tblAddr
ORDER BY tblAddr.Addr1;UNION SELECT AddNewMarker As Addr1 FROM
zstblDefault;

Ok, here's one idea, yet it does not seem to fully work. When
activating datasheet view from the query window, it ask me to "Enter
Parameter Value." After clicking OK, it displays the records, yet
Addr1 is not sorted. The [Add New] marker appears at the top.

SELECT tblAddr.AddrID, tblAddr.Addr1, tblAddr.AddrName, tblAddr.City,
tblAddr.StateID, tblAddr.PostalCode, tblAddr.Country, 1 AS SortParm
FROM tblAddr
ORDER BY SortParm, tblAddr.Addr1;
UNION SELECT "", AddNewMarker As Addr1,"", "", "", "", "", 0 As
SortParm FROM zstblDefault;

 

[Robert Neville]

Robert

If you had used the query I posted it would have worked! As it is, you
should lose the "As Addr1", and the semicolon before UNION, and move the
ORDER BY clause to the end.

Any particular reason why you insist on opening a different table for the
[Add New] when you could just use another constant?

Thanks Graham,

Your post helped me resolve the issue. We replied at about the same
time, so my previous reply did not account for your comments.
Apparently, I only had to move the ORDER BY clause to the end.

I used a separate table for the [Add New] to have a global marker. I
may or not keep this approach depending on my finally implementation.

Thanks for your comments.

-Robert