Using the not in list event

[John F]

I have asked this question before but for some reason I can no longer find
the previous answer.

I am using a table to store the values of a look up field. I would like to
add new values to the look up list using the not in list event. Also there
were issues adding to the list when the value contained certain special
characters and if I remember correctly the specific characters were the
single quote and/or the double quote.

 

[Douglas J. Steele]

Depends on how you're actually adding the values.

If you're using a SQL Insert Into query, take a look at my May, 2004 "Access
Answers" column in Pinnacle Publication's "Smart Access". You can download
the column (and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

If you're not using SQL, how are you trying to add the values?

 

[John F]

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

Dim str As String
Dim cmd As New ADODB.Command

Set cmd.ActiveConnection = CurrentProject.Connection
str = "Insert Into lutblDescript (dscDescription) Values ('" _
& Replace(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

 

[Douglas J. Steele]

That should work. Do you have an example of what value doesn't work? Do you
get an error?

 

[John F]

AH HA, I finally found my issue I forgot to activate the Microsoft ActiveX
Data Objects 2.8 Library. Finally found my comments in one of my code snipet
files.

Thanks

 

[John F]

I do not really notice any difference. Any idea about how much is saved? I
guess it could be noticeable if the code was longer. I'll keep trying to
write faster code. Don't know how successfully but try.

Thanks

This doesn't have to do with your question, but if code will only be executed
under certain condtions you shouldn't run it unless those conditions are met.
If you really want to get efficient, do this:

Dim str As String
Dim cmd As ADODB.Command 'remove the New keyword here

If MsgBox("Value is not in list. Add it?", vbOKCancel) = vbOK Then
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection
str = "Insert Into lutblDescript (dscDescription) Values ('" _
& Replace(NewData, "'", "''") & "');"
cmd.CommandText = str
cmd.Execute
Set cmd = Nothing
Response = acDataErrAdded
Else
Response = acDataErrContinue
Me.itmDescription.Undo
End If

Not a big deal, you won't notice a difference watching it execute, the
savings aren't huge, just good programming practice. In cases where you're
repeating things many times in a loop, things like that can make a big
difference.

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

Dim str As String
Dim cmd As New ADODB.Command

Set cmd.ActiveConnection = CurrentProject.Connection
str = "Insert Into lutblDescript (dscDescription) Values ('" _
& Replace(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

Depends on how you're actually adding the values.

[quoted text clipped - 13 lines]

characters and if I remember correctly the specific characters were the
single quote and/or the double quote.