NotInList help

[EAB1977]

Hi All,

I have a form to where a user enters information into a combo box. If
the data in the combo box does not match what is in the combo box, my
NotInList event is triggered. WIthin this code, I have a option to
take the user to the item, if they want to. If they click Yes, the
NotInList gets triggered again. I do not want this to happen.

How do I prevent this from happening?

Option Compare Database
Dim cnn As ADODB.Connection

Private Sub cboPlateNumber_NotInList(NewData As String, Response As
Integer)
Dim strSQL As String, rsInfo As New ADODB.Recordset, msg As Integer
Dim db As DAO.Database, rsStandard As New ADODB.Recordset

' -- Check to see if the plate number is already entered into the
database.
' -- EB - 7/2/2010
Stop
Response = acDataErrContinue

Set cnn = CurrentProject.Connection
strSQL = "SELECT StandardType FROM tblStandard WHERE PlateNumber =
'" & NewData & "'"
rsStandard.Open strSQL, cnn, adOpenKeyset, adLockOptimistic

If Not rsStandard.BOF And Not rsStandard.EOF Then
strSQL = "SELECT LLBPRD, LLDSCP FROM
dbo_vwProductPlateAttribute WHERE " _
& "LLUPRD = '" & NewData & "'"
rsInfo.Open strSQL, cnn, adOpenKeyset, adLockOptimistic
msg = MsgBox("The print standard " & rsInfo!LLBPRD & " - " &
rsInfo!LLDSCP & " is already entered into" _
& " the database." & Chr(13) & Chr(13) & "Would you like
to go to the print standard?", _
vbYesNo)
Select Case msg
Case 6 'Yes
Select Case rsStandard!StandardType
Case 1
intFormStatus = 2 'Edit Mode
blnNewStandard = False
DoCmd.OpenForm "frmFoamCupStandard", acNormal
Case 2
intFormStatus = 2 'Edit Mode
blnNewStandard = False
DoCmd.OpenForm "frmFusionCupStandard",
acNormal
End Select
DoCmd.Close acForm, Form.Name, acSaveNo
Case 7 'No
'Do nothing, the user clicked No
End Select
Else
' -- State to the user why the print is not in the list
strSQL = "SELECT LLBPRD, LLDSCP, LLPSTS FROM
dbo_vwProductPlateAttribute WHERE " _
& "LLUPRD = '" & NewData & "'"
rsInfo.Open strSQL, cnn, adOpenKeyset, adLockOptimistic
Select Case rsInfo!LLPSTS
Case "C"
MsgBox "The print standard " & rsInfo!LLBPRD & " - " &
rsInfo!LLDSCP & " has been" _
& " cancelled accoring to the AS/400.", vbOKOnly,
"Cancelled Print"
Case "I"
MsgBox "The print standard " & rsInfo!LLBPRD & " - " &
rsInfo!LLDSCP & " has been" _
& " inactivated accoring to the AS/400.",
vbOKOnly, "Inactivated Print"
Case "P"
MsgBox "The print standard " & rsInfo!LLBPRD & " - " &
rsInfo!LLDSCP & " is" _
& " pending accoring to the AS/400.", vbOKOnly,
"Pending Print"
Case "E"
MsgBox "The print standard " & rsInfo!LLBPRD & " - " &
rsInfo!LLDSCP & " has been" _
& " classified as experimental accoring to the AS/
400.", vbOKOnly, "Experimental Print"
Case "R"
MsgBox "The print standard " & rsInfo!LLBPRD & " - " &
rsInfo!LLDSCP & " has been" _
& " replaced accoring to the AS/400.", vbOKOnly,
"Replaced"
Case "S", "W"
MsgBox "The print standard " & rsInfo!LLBPRD & " - " &
rsInfo!LLDSCP & "has not been" _
& " approved for use.", vbOKOnly, "Unapproved
Print"
Case Else
MsgBox "The print standard does not exist.", vbOKOnly,
"No Plate Found"
End Select
End If

rsStandard.Close
rsInfo.Close

End Sub

 

[Ken Snell]

Try changing this line:

Response = acDataErrContinue


to this:

Response = acDataErrAdded

Also, I suggest that you put this step at end of code after you're sure the
new item has been added; else, you're telling ACCESS the item has been added
when it might not have been if some error/problem occurred during the code
that is adding the new item.

 

[EAB1977]

Ken,

I don't want to add the item to the combo box. The combo box is bound
to a field in the SQL Server.

Only three senerios can happen:

1. The item is currently active and being tracked which I check for
within the code through a SQL statement.
2. The item is invalid becuase the item doesn't exist.
3. The item is valid but the status is not active.

I only want to filter items that are valid, active and have not been
tracked.

 

[Ken Snell]

Well, the LimitToList property will disallow the entry of any value that is
not in the combo box's source list, so if you don't want to add the item to
that source list and you don't want to reject the item, you'll have to use a
combo box that has LimitToList property set to No. Consider using such a
combo box that runs code in the AfterUpdate event to check the entered value
and inform the user if the item is not in the combo box's source list, and
if the user wants that value, then run the code that you're using in the
NotInList event procedure.