On Not in List

[cornishteeth]

I have a combo box that has a list of employees names. I want to be able to update it without actually having to go to the table and edit from there. I was told to go to the On Not in List event under properties, but I am stuck there. I searched the net for something pertaining to this, but nothing was produced. Can someone help me out with some code perhaps or a macro that might work here.

Thanks,

Frank

 

[Norie]

Try something like this, replacing names/fields etc as appropriate:

Private Sub Combo0_NotInList(NewData As String, Response 
As Integer)
' if account not in Accounts dropdown
' ask user if they want to add it
' if so do so

Dim AddNewDataQ
Dim db As Database
Dim rst As Recordset

AddNewDataQ = MsgBox("Add new data?", vbYesNo)

If AddNewDataQ = vbYes Then

    Response = acDataErrAdded

    Set db = CurrentDb
    Set rst = db.OpenRecordset("Accounts")
    
    With rst
        .AddNew
        !Account = NewData
        .Update
    End With

    Set rst = Nothing
    Set db = Nothing

Else

    Response = acDataErrContinue

End If

End Sub

 

[cornishteeth]

I hate to sound stupid, but I am sort of new to VB and not really up to par with it. I can understand what is happening, but where to I make replacements with my field/names?

 

[Norie]

This is where you make a reference to your table, so you would want to put tblEmployee instead of Accounts.

Set rst = db.OpenRecordset("Accounts")

This section is where you actually add the new record.

With rst
.AddNew
!Account = NewData
.Update
End With

The !Account is a field name, and NewData is the value that the user has enterd into the combobox

e.g. the possible new employee so what you will need to do is

Syntax here is

.AddNew ' add new record to table

!Field1 ' enter data in Field1
!Field2 ' enter data in Field2
...
!FieldN ' enter data in FieldN
.Update ' add the new data

So replace field1 etc to whatever you need for your table.

The data you put in must comply with the fields type, length etc.

You could just add the NewData which will create a new record with only that field.

 

[cornishteeth]

Thank you very much!!!!!

 

[cornishteeth]

I keep getting an error at

Dim dB as Database

 

[cornishteeth]

Private Sub EMPLOYEE_NAME_NotInList(NewData As String, Response As Integer)
' if account not in Accounts dropdown
' ask user if they want to add it
' if so do so

Dim AddNewDataQ
Dim db As Database
Dim rst As Recordset

AddNewDataQ = MsgBox("Add new data?", vbYesNo)

If AddNewDataQ = vbYes Then

Response = acDataErrAdded

Set db = CurrentDb
Set rst = db.OpenRecordset("tbl_employees")

With rst
.AddNew
!EMPLOYEE_NAME = NewData
.Update
End With

Set rst = Nothing
Set db = Nothing

Else

Response = acDataErrContinue

End If
End Sub

If I understand you right, the field name in tbl_employees is Employee_Name which is designated as text. So I entered that for the Field. Am I missing something else?

 

[cornishteeth]

I figured it out. Thanks for all your help Norie. Your posts help me each and every day on my Access adventures!