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

 

[JimBurke via AccessMonster.com]

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]

 

[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.

 

[JimBurke via AccessMonster.com]

For that event it won't make any noticable difference. It's more about
getting yourself into good programming practices. Like I said, if you had a
loop that was executed many times and you were executing a few lines of code
every time thru when it was only needed a portion of the time it can make a
difference. On 'principle' you shouldn't execute code unless it's needed.
Does it always make a noticable difference? No. Also, the less code you
execute the less likely you are to get an unexpected error.

Just to give a real-life example, I had to make some modifications to some
existing code recently. I have a loop that reads thru a sizable table. Inside
that I have a loop that is executed 12 times and does quite a bit of
processing. I accidentally had some code inside that inner loop that should
have been outside it. The changes I made caused the function to run for a
couple of minutes. When I moved that code outside the inner loop it ran in
about 10 seconds. That's an extreme example, but it's the kind of thing that
can happen. Broke my own rules in that case!

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.

[quoted text clipped - 49 lines]