Add "Add New" option to top of combo

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

If a combo box pulls from a table, is there any way to add an additional
option ("Add New", for instance) to the top of the combo box? Also a
separator line between "Add New" and the rest of the choices would be nice.
 
Hi Brent,

While there are ways to piece meal this together, there is another
alternative that you might want to consider first.

The combo box control has a "NotInList" event. What you could do is catch
this event, run a query against the table that is populating the combo box,
and if it isn't a duplicate (due to casing or whatever), you could prompt the
user with a message box indicating that they have entered an option that is
not currently listed and ask them if they want to include it. If they say
Yes, then add their option to the database. If not, ignore it and don't
allow them to continue with an action that would cause an inappropriate
action to be executed against the database (for example, updating a record
with their typed in combo box selection that isn't a valid option).

Hope that helps. If not, let me know and maybe we can think of something
else.

Lance
 
Hi Brent,

You're going to need to use a UNION query to get what you want.

Try this

select [fld1], [fld2], ... from [tbl1]
union select -1,"Add New", "",... from [tbl1]
union select 0, "---------","",.... from [tbl1]

Have a look on help if you want more info on UNION queries.

Cheers,

Jason
 
Using your suggestion, I can get this to work:

SELECT tblproducts.productid, tblproducts.productname from tblproducts
UNION SELECT null, "Add New" from tblproducts
ORDER BY tblproducts.productname;

But not this:

SELECT tblproducts.productid, tblproducts.productname from tblproducts
UNION SELECT -1, null, "Add New" from tblproducts
UNION SELECT 0, null, "-----------" from tblproducts
ORDER BY tblproducts.productname;

Access tells me that the columns are not equal. I assumed that the -1 and 0
were to indicate sort placement in the result set. Is there a syntax
problem? Any ideas?

-Brent

--
Thanks,
Brent


Jason Houchen said:
Hi Brent,

You're going to need to use a UNION query to get what you want.

Try this

select [fld1], [fld2], ... from [tbl1]
union select -1,"Add New", "",... from [tbl1]
union select 0, "---------","",.... from [tbl1]

Have a look on help if you want more info on UNION queries.

Cheers,

Jason

Brent Walker said:
If a combo box pulls from a table, is there any way to add an additional
option ("Add New", for instance) to the top of the combo box? Also a
separator line between "Add New" and the rest of the choices would be nice.
Click to expand...
Click to expand...
 
Back
Top