NotInList Event

[John F]

I am using lutblDescript as a lookup table for a combobox for filling in a
Description field in another table. I would like to use the NotInList Event
to add what was typed into the combox as a new record in lutblDescript

Private Sub itmDescription_NotInList(NewData As String, Response As Integer)
?
?
End Sub

NewData gives me the information that I want added to the lookup table.
The lookup table has 2 fields an [dscID], primary key and [dscDescription]

So far I have drawn a blank.

 

[code_monkey_number_9]

Private Sub itmDescription_NotInList(NewData As String, Responsse As Integer)

Dim str as String
Dim cmd as New ADODB.Command

Set cmd.ActiveConnection = CurrentProject.Connection
str = "Insert Into lutblDescript (dscDescription) Values ('" & NewData & '");"
cmd.CommandText = str

If MsgBox("Value is not in list. Add it?", vbOKCancel) = vbOK Then
Response = acDataErrAdded
cmd.Execute
Else
Response = acDataErrContinue
Me.itmDescription.Undo
End If

Set cmd = Nothing

End Sub

 

[John F]

Does it make a difference if I am using Access 2007 as I get an error message
using this line. "Compile error: User-defined type not defined"

Dim cmd as New ADODB.Command

Private Sub itmDescription_NotInList(NewData As String, Responsse As Integer)

Dim str as String
Dim cmd as New ADODB.Command

Set cmd.ActiveConnection = CurrentProject.Connection
str = "Insert Into lutblDescript (dscDescription) Values ('" & NewData & '");"
cmd.CommandText = str

If MsgBox("Value is not in list. Add it?", vbOKCancel) = vbOK Then
Response = acDataErrAdded
cmd.Execute
Else
Response = acDataErrContinue
Me.itmDescription.Undo
End If

Set cmd = Nothing

End Sub

I am using lutblDescript as a lookup table for a combobox for filling in a
Description field in another table. I would like to use the NotInList Event
to add what was typed into the combox as a new record in lutblDescript

Private Sub itmDescription_NotInList(NewData As String, Response As Integer)
?
?
End Sub

NewData gives me the information that I want added to the lookup table.
The lookup table has 2 fields an [dscID], primary key and [dscDescription]

So far I have drawn a blank.

 

[code_monkey_number_9]

It doesn't. It just means you don't have a reference set for ADO. In the VB
editor, click Tools, then References. In the references dialog, scroll down
the Available References list until you find "Microsoft ActiveX Data Objects
2.8 Library. Select this, then click OK and try again.

It isn't necessary that you use ADO to update the lookup table if you prefer
DAO or whatever. ADO is just what I use.

Does it make a difference if I am using Access 2007 as I get an error message
using this line. "Compile error: User-defined type not defined"

Dim cmd as New ADODB.Command

Private Sub itmDescription_NotInList(NewData As String, Responsse As Integer)

Dim str as String
Dim cmd as New ADODB.Command

Set cmd.ActiveConnection = CurrentProject.Connection
str = "Insert Into lutblDescript (dscDescription) Values ('" & NewData & '");"
cmd.CommandText = str

If MsgBox("Value is not in list. Add it?", vbOKCancel) = vbOK Then
Response = acDataErrAdded
cmd.Execute
Else
Response = acDataErrContinue
Me.itmDescription.Undo
End If

Set cmd = Nothing

End Sub

I am using lutblDescript as a lookup table for a combobox for filling in a
Description field in another table. I would like to use the NotInList Event
to add what was typed into the combox as a new record in lutblDescript

Private Sub itmDescription_NotInList(NewData As String, Response As Integer)
?
?
End Sub

NewData gives me the information that I want added to the lookup table.
The lookup table has 2 fields an [dscID], primary key and [dscDescription]

So far I have drawn a blank.

 

[John F]

Thank-you the code works now.

It doesn't. It just means you don't have a reference set for ADO. In the VB
editor, click Tools, then References. In the references dialog, scroll down
the Available References list until you find "Microsoft ActiveX Data Objects
2.8 Library. Select this, then click OK and try again.

It isn't necessary that you use ADO to update the lookup table if you prefer
DAO or whatever. ADO is just what I use.

Does it make a difference if I am using Access 2007 as I get an error message
using this line. "Compile error: User-defined type not defined"

Dim cmd as New ADODB.Command

Private Sub itmDescription_NotInList(NewData As String, Responsse As Integer)

Dim str as String
Dim cmd as New ADODB.Command

Set cmd.ActiveConnection = CurrentProject.Connection
str = "Insert Into lutblDescript (dscDescription) Values ('" & NewData & '");"
cmd.CommandText = str

If MsgBox("Value is not in list. Add it?", vbOKCancel) = vbOK Then
Response = acDataErrAdded
cmd.Execute
Else
Response = acDataErrContinue
Me.itmDescription.Undo
End If

Set cmd = Nothing

End Sub

:

I am using lutblDescript as a lookup table for a combobox for filling in a
Description field in another table. I would like to use the NotInList Event
to add what was typed into the combox as a new record in lutblDescript

Private Sub itmDescription_NotInList(NewData As String, Response As Integer)
?
?
End Sub

NewData gives me the information that I want added to the lookup table.
The lookup table has 2 fields an [dscID], primary key and [dscDescription]

So far I have drawn a blank.