Adding to a Combo Box

[mare]

I'm almost finished with my database in Access 97, but for one little problem. I have a combo list box that will require additions to it. Instead of going back to the table that controls that combo box, I want to be able to type the new information into the field and have it automatically added to the appropriate table.

I have checked through the forum, and noticed I'm not the only one who has encountered this. I've tried to use the solutions suggested, but nothing seems to work.

My knowledge of VBA at this point is very limited. I will be going on a course, but not soon enough!

Here are the details:

Form that is used: Stock Number NEW
Field Name: Program (also known as: Combo18)
(Combo List - Limit to List: Yes)
Data that is input is strictly text - no numbers
Table for this
Combo List: Programs

What I would like to do is:

If the program (i.e., Training) is not in the list, to add it (by typing it in the program field.

When I have finished typing in "Training" and TAB to the next field, to get the message that "Training is not in the list - do you wish to add it?"

A "Yes" button that once it is pressed, "Training" is saved to the Programs Table. Also, a "No" button (do not add to the list) - just in case.

Any suggestions will be greatly appreciated.

Also, can anyone suggest a good VBA book for beginners?

Thank you.

 

[Mile-O]

Here's the code for your OnNotinList event of the combobox...

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

    Response = MsgBox("The bla bla bla you have entered is not in this list." & vbCrLf & vbCrLf _
        & "Would you like to add it?", vbQuestion + vbYesNo, "Title message")
    If Response = vbYes Then
        Dim db As Database, rs As Recordset
        Set db = CurrentDb
        Set rs = db.OpenRecordset("yourTable")
        With rs
            .AddNew
            .Fields("yourField") = NewData
            .Update
        End With
        Response = acDataErrAdded
    Else
        Me.Undo
        Response = acDataErrContinue
    End If
    
End Sub

 

[chewy]

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

Dim dbs As Database, rst As Recordset
Set dbs = CurrentDb

Response = acDataErrContinue
DoCmd.Beep
If MsgBox("This is not a current Branch Location!" & vbCrLf & "Add it?", vbOKCancel, "Not in List Error") = vbOK Then
' Set Response argument to indicate that data is being added.
Response = acDataErrAdded
' Add string in NewData argument to a table tblDiscipline.

With dbs
Set rst = .OpenRecordset("table")
rst.AddNew
rst!field = NewData
rst.Update
rst.Close
.Close
End With

Else
' If user chooses Cancel, suppress error message and undo changes.
Response = acDataErrContinue
End If

End Sub

 

[sgiuffrida]

this is confusing to me.

I am tryin to apply this code but am not seeing the places i need to change to reflect my configuration, can you color code the strings i need to change for this to work? thanks steve

 

[mare]

Combo Box listing still not working

Chewy:
I am confused. I've tried your solution, but nothing is working.

Here is what I have done, whereas:

combo18 = the field
Stocknumberlisting = the database that contains all the data
Programs = the table that has the information for combo18

Is there something that I missed? By "table tblDiscipline' what do you mean, as I'm learning VBA as I go along?

Any help will be greatly appreciated!


Private Sub combo18 NotInList(NewData As String, Response As Integer)

Dim dbs As Database, rst As Recordset
Set dbs = Stocknumberlisting

Response = acDataErrContinue
DoCmd.Beep
If MsgBox("This program does not exist in the list!" & vbCrLf & "Add it?", vbOKCancel, "Not in List Error") = vbOK Then
' Set Response argument to indicate that data is being added.
Response = acDataErrAdded
' Add string in NewData argument to a table tblDiscipline.

With dbs
Set rst = .OpenRecordset("Programs")
rst.AddNew
rst!field = NewData
rst.Update
rst.Close
.Close
End With

Else
' If user chooses Cancel, suppress error message and undo changes.
Response = acDataErrContinue
End If

End Sub

 

[sgiuffrida]

With dbs
Set rst = .OpenRecordset("Programs")
rst.AddNew
rst!field = NewData
rst.Update
rst.Close
.Close
End With

rst!field = NewData

I am getting an error here in the rst!field-NewData, please show me how i enter my field and what field it is. Is it the field in the table? or the field of the combo Box? how is it supposed to look?

rst!field ("meds")= NewData

rst!("meds")= NewData

rst!field "meds" = NewData

All i need it this little peice of the puzzle.. thanks