add new item to combo list 1

[kcmorrissey]

I have a combo box which lists products. I want to add a record at the bottom of the list to 'Add new product'

If the user selects 'Add new product' I want to open the products form.

How do I add the 'Add new product' entry into the combo box list.

Thx.
Kev

 

[ZOR]

Have you looked in the FAQ section of the site??

 

[hneal98]

Do you want to do it programatically or do you just want to add it for the user to be able to select? If you just want to add it for the user to select, you can go to the property of the combo box and find the item collection. There is a little button to the right that has three dots like this ... on it. Click that and then just add that line.

Not sure if that is what you are asking.

 

[kcmorrissey]

I'm Sorry I must be thick!

I cant find an item labled 'item collection' in the combbox values. And I can't find a solution in the FAQs that adds a record to the bottom of a list so the user can select it.

I would like to do this with standard features so the user can select it and then use afterupdate event to pull up the products form. However I it has to be coded, then it has to be..

Thx,
Kev.

 

[fkegley]

If you are getting the products list from a table then the simplest way is to add a UNION SELECT to the statement that is fetching from the table.

SELECT ProductID, ProductName FROM Products
UNION
SELECT 999999, "Add New Product" As ProductName
ORDER BY ProductID

The 999999 is intended to be a value that is bigger than any existing ProductID. Change it if it is text.

Frank kegley
fkegley@hotmail.com

 

[kcmorrissey]

Frank,

Thanks for this, I thought I had found the solution at last, and I probably have, But I can't make it work yet and I suspect it's my SQL (lack of) skills that are letting me down.

My afterupdate code looks like this:

Private Sub comboCatagory_ID_AfterUpdate()
Dim sProd_description As String
    sProd_description = "SELECT products_table.product_id, products_table.prod_description " & _
        "FROM products_table " & _
            "WHERE products_table.prod_catagoryID = " & "'" & Me.comboCatagory_ID.Column(0) & "'" & _
            " UNION SELECT 999999, 'Add New Product' As prod_description" & _
            " ORDER BY product_id"
    Me.comboProd_description.RowSource = sProd_description
    Me.comboProd_description.Requery
End Sub
[end code]

when called to execute access gives the following message:
'Query input must contain at least one table or query.'

If I include a msgbox to report the string value of sProd_description access displays this:
SELECT products_table.product_id, products_table.prod_description FROM products_table 
            WHERE products_table.prod_catagoryID =  'ASCAS 27001' 
	     UNION SELECT 999999, 'Add New Product' As prod_description
              ORDER BY product_id

I am not sure how SQL works, but my understanding so far is that I have created a temparary table in memory with two columns (product_id & Prod_description) and it looks like I am trying to add or union a single column.  Could this be my problem?

Thx
   Kev

 

[PHV]

And this ?
sProd_description = "SELECT product_id,prod_description " & _
"FROM products_table " & _
"WHERE prod_catagoryID='" & Me.comboCatagory_ID.Column(0) & "'" & _
" UNION SELECT 999999,'Add New Product' FROM products_table" & _
" ORDER BY 1"


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[kcmorrissey]

Thanks PHV that worked for me.

Kev