Combo Box needed to accept not in list data...

[Carpetwarehouse]

Hello hoping someone can help me ive been searching the internet all afternoon.

I have a combo box on a form which looks up products from a products table.

The products are commonly sold products but i would like to be able to have the choice of selecting one from the list or manually typing on in.

I have found code examples on how to add new data to the list but i dont want to change the list. I just want access to accept a choice from the drop down or what ever has been typed into the field.

Thanks

 

[bob fitz]

Try changing the "Limit To List" property of the combo box from "Yes" to "No".

 

[Carpetwarehouse]

No that still displays and error, im assuming i have to leave this yes and write some code under the not in list event for the combo box but its what to write? im no good with code....

 

[bob fitz]

What is the setting of "Row Source" property and the "Row Source Type" property of the combo box.

 

[Carpetwarehouse]

Row Source: SELECT [StockItemsTbl].[StockItemID], [StockItemsTbl].[Item Description], [StockItemsTbl].[Unit Price] FROM StockItemsTbl ORDER BY [Item Description];

Row Type: Table/Query

 

[liamfitz]

If you do a 'Not In List' general search ( try Microsoft's VBA or Access help on internet ) say google. It should show you the generic format for this Method ( it certainly can be done once you know the syntax ) As the event triggrers on your combo box, I think it's Click, but check, it compares the string you have just typed to what is already loaded by your 'SELECT' statement. What you effectively do programatically, is tell the Combo Box, to accept a new item, as a further option even though it is 'Not In List' Hope this helps.

 

[Mr. B]

The event you want to use is "On Not In List".

Here is a link that should get you going.
http://access.mvps.org/access/forms/frm0015.htm

If you have problems implementing this, just post back.

 

[Carpetwarehouse]

I found this... msdn.microsoft.com/en-us/library/bb214329(v=office.12).aspx

But i honistly dont know where to go from here i dont understand it. Im assuming NewData is what has been entered and i need to tell it when the not in list error is triggered to pass on the newdata to the record.

I really need someone to give me the code to paste in

 

[Carpetwarehouse]

The event you want to use is "On Not In List".

Here is a link that should get you going.

If you have problems implementing this, just post back.

Thanks for this i'll give it a try if i can get it working the next step would be taking out the message boxes as i dont need the verification as i am using this database myself.

 

[Alansidman]

Here is what I use to do this:

http://allenbrowne.com/ser-27.html

Alan

 

[Carpetwarehouse]

Private Sub Item_Description_NotInList(NewData As String, Response As Integer)

Dim db As DAO.Database
Dim rs As DAO.Recordset


Set db = CurrentDb
Set rs = db.OpenRecordset("OrderDetailsTbl", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!AEName = NewData
rs.Update



rs.Close
Set rs = Nothing
Set db = Nothing

End Sub


Ive tried this but now wont let me enter anything or select anything from drop down...

 

[Mr. B]

Try pasting the following code into the On Not In List event and change the "YourFieldNameNere" to the actual name of the field you need to add then new value to:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String

    strMsg = "'" & NewData & "' is not a currently available Value " & vbCrLf & vbCrLf
    strMsg = strMsg & "Do you want to add the new Value to the current List?"
    strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link or No to re-type it."
    
    If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new value?") = vbNo Then
        Response = acDataErrContinue
    Else
        Set db = CurrentDb
        Set rs = db.OpenRecordset("OrderDetailsTbl", dbOpenDynaset)
        On Error Resume Next
        rs.AddNew
            rs.Fields("YourFieldNameNere").Value = NewData
        rs.Update
        
        If Err Then
            MsgBox "An error occurred. Please try again."
            Response = acDataErrContinue
        Else
            Response = acDataErrAdded
        End If
        
    End If

rs.Close
Set rs = Nothing
Set db = Nothing

Test adding a new value to your combo box.

 

[bob fitz]

Post #1

I have found code examples on how to add new data to the list but i dont want to change the list. I just want access to accept a choice from the drop down or what ever has been typed into the field.

Maybe I'm missing something here, but won't the proposed solutions add the "NewData" to the list?

 

[Mr. B]

Bob,
I think you are quite correct. I think I and others may have just gone down the wrong path.

Most likely just changing the "Limit To List" property of the combo box from "Yes" to "No" as suggested in Post #2 would be the best solution.

Thanks for point this out.

 

[bob fitz]

Most likely just changing the "Limit To List" property of the combo box from "Yes" to "No" as suggested in Post #2 would be the best solution.

Well that's what I thought, but in post #3 the OP says that this gives an error.

I think this may be because the combo box is bound to a numeric field.

 

[Carpetwarehouse]

Ok guys maybe im getting confused here, when you say allow new data to add to list doesnt that mean it will add the entered item to the sub menu list? I do not want that to happen, i need it to bypass the list altogether. Look at the combo box in my situation as a list of commonly purchased items, with 100s of other items which need manually typed in. To end up with a great long combo box of all items entered would not suit my situation. I appreciated your help.

 

[Carpetwarehouse]

Well that's what I thought, but in post #3 the OP says that this gives an error.

I think this may be because the combo box is bound to a numeric field.

If i look at the table in design view the data type is set to Number. I set this up using the look up wizard and it automatically set the data type for me??

 

[bob fitz]

To end up with a great long combo box of all items entered would not suit my situation

The usual way to deal with this is to have more than one combo box. The selection in the first combo filters the items that are shown in the second. If you sold Gents clothing, the first combo may have "Categories" like: Socks, Trousers, Shorts, Underpants, Vests, Shirts, Tshirts etc.