New data entry from combo box

[DarkProdigy]

Hi guys,

I'm looking for a little bit of help here. I have a form and on this form I have a combo box. Now this combo box is populated by a table that I have called "Options". So this is all fine and dandy to select items already existing in the table, but ideally I would like to be able to input a new "Option" through this combo box if it doesn't exist in the table already.

I'm thinking I'd have to do something with the "On Not in List" event but I don't know VBA at all.

Also, I will eventually have a Price box next to this combo running off a query driven by the entry in the combo box. Again, I'd like to be able to input a new cost to the existing item or a cost for the newly entered item

Thanks in advance for the help

Nathan

 

[DarkProdigy]

/bump

sorry guys, I need an answer for this

 

[RuralGuy]

Maybe this link will give you an assist in the right direction. Mastering the Combo Box NotInList Event

 

[DarkProdigy]

Thanks for the link RuralGuy

I think I understood what was going on in the article, but I tried using it and changing the names and stuch, but it doesn't seem to do anything for me. Here's the code, maybe I missed something?

Private Sub cboOptions_NotInList(NewData As String, Response As Integer)
    On Error GoTo cboOptions_NotInList_Err
    Dim intAnswer As Integer
    Dim strSQL As String
    intAnswer = MsgBox("The Option " & Chr(34) & NewData & _
        Chr(34) & " is not currently listed." & vbCrLf & _
        "Would you like to add it to the list now?" _
        , vbQuestion + vbYesNo, "ZCL Composites")
    If intAnswer = vbYes Then
        strSQL = "INSERT INTO tblOptions([Options]) " & _
                 "VALUES ('" & NewData & "');"
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
        MsgBox "The new option has been added to the list." _
            , vbInformation, "ZCL Composites"
        Response = acDataErrAdded
    Else
        MsgBox "Please choose an option from the list." _
            , vbInformation, "ZCL Composites"
        Response = acDataErrContinue
    End If
cboOptions_NotInList_Exit:
    Exit Sub
cboOptions_NotInList_Err:
    MsgBox Err.Description, vbCritical, "Error"
    Resume cboOptions_NotInList_Exit

End Sub

 

[RuralGuy]

Replace then next 3 lines:
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

With:
CurrentDB.Execute MySQL, dbFailOnError

It may show us what is going on.

 

[DarkProdigy]

Thanks for your reply RG

I did as you said, and nothing happens. I type in something that's not in the list and hit enter, and it just goes to the next item on the form

 

[RuralGuy]

Has it added the NewData to your list of Options?

 

[DarkProdigy]

no, nothing was added

 

[RuralGuy]

This is probably a dumb question but the RowSource of the ComboBox *is* the tblOptions table or a query of it, correct? RowSourceType is set to Table/Query?

 

[DarkProdigy]

yes to both

and I've checked that I spelled all the references correctly

 

[RuralGuy]

Could you post the SQL for the RowSource? How about a stripped down version of your db zipped up that demonstrates the problem and has any sensitive data removed?

 

[DarkProdigy]

Replace then next 3 lines:
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

With:
CurrentDB.Execute MySQL, dbFailOnError

It may show us what is going on.

on this, should "MySQL" be "strSQL"?

 

[DarkProdigy]

here's the row source SQL

SELECT tblOptions.ID, tblOptions.Options FROM tblOptions;

I'm off work in 10 minutes, but I'll try and get a stripped down copy tomorrow morning

thanks again for all your help RG

 

[RuralGuy]

on this, should "MySQL" be "strSQL"?

Definately *YES*. Sorry, I was a little careless and I suspect you do *not* have Option Explicit at the top of your module. It would have caught the spelling issue!

 

[RuralGuy]

I'll look foreward to the db but my last post might just show/fix the problem.

 

[DarkProdigy]

Here is the sample of what I'm kinda going for

your further help is appreciated in this RG

 

[RuralGuy]

Here's the db back with LimitToList set to yes and the table name changed. If you don't limit to list then the NotInList event never fires.

 

[DarkProdigy]

Thank you for all your help RG

one more question if you've got the time/patience to help me out: I would like to be able to include "Cost" text box for these options so that when an existing option is picked, the cost of that one will be shown and when a new option is inputted, you can type in a price for that option

is there a way that this can be done?

 

[RuralGuy]

Something like this?

 

[DarkProdigy]

Yes, that is exactly what Im looking for

I can't thank you enough Rural Guy. This addition was kinda just thrown at me last minute and I really didn't have a clue how to do it. Now I think my boss will be quite impressed with this db

Thank you again 1000 times over