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

Carpetwarehouse · Jun 11, 2012

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 · Jun 11, 2012

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

Carpetwarehouse · Jun 11, 2012

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 · Jun 11, 2012

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

Carpetwarehouse · Jun 12, 2012

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

Row Type: Table/Query

liamfitz · Jun 12, 2012

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 · Jun 12, 2012

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 · Jun 12, 2012

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 · Jun 12, 2012

Mr. B said:
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 · Jun 12, 2012

Here is what I use to do this:

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

Alan

Carpetwarehouse · Jun 12, 2012

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 · Jun 12, 2012

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:

Code:

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 · Jun 12, 2012

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 · Jun 12, 2012

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 · Jun 12, 2012

Mr. B said:
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 · Jun 13, 2012

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 · Jun 13, 2012

bob fitz said:
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 · Jun 13, 2012

Carpetwarehouse said:
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.

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

Carpetwarehouse

Registered User.

bob fitz

AWF VIP

Carpetwarehouse

Registered User.

bob fitz

AWF VIP

Carpetwarehouse

Registered User.

liamfitz

Registered User.

Mr. B

"Doctor Access"

Carpetwarehouse

Registered User.

Carpetwarehouse

Registered User.

Alansidman

AWF VIP

Carpetwarehouse

Registered User.

Mr. B

"Doctor Access"

bob fitz

AWF VIP

Mr. B

"Doctor Access"

bob fitz

AWF VIP

Carpetwarehouse

Registered User.

Carpetwarehouse

Registered User.

bob fitz

AWF VIP

Similar threads

Users who are viewing this thread