Problem NotinList in Combo box

Hi All,
I am trying to implement NotInList to add new item in Combo box. This is working for add new item without quotation sign. I need to add item with quotation sign for new Item but its giving problem that value are in not in list item.

The quotation sign i am using for Inch. I can't used any substitute for quotation sign.

I am using the code from microsoft site
http://support.microsoft.com/kb/197526

I am using SQL Server 2005 and Access 2003 Access Data Project.

e.g, Item 1 1x1"


Private Sub CustomerID_NotInList (NewData As String, Response As _
                                        Integer)
Dim Result
Dim Msg As String
Dim CR As String
 
    CR = Chr$(13)
 
    ' Exit this subroutine if the combo box was cleared.
    If NewData = "" Then Exit Sub
 
    ' Ask the user if he or she wishes to add the new customer.
    Msg = "'" & NewData & "' is not in the list." & CR & CR
    Msg = Msg & "Do you want to add it?"
    If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
       ' If the user chose Yes, start the Customers form in data entry
       ' mode as a dialog form, passing the new company name in
       ' NewData to the OpenForm method's OpenArgs argument. The
       ' OpenArgs argument is used in Customer form's Form_Load event
       ' procedure.
       DoCmd.OpenForm "Customers", , , , acAdd, acDialog, NewData
    End If
 
    ' Look for the customer the user created in the Customers form.
    Result = DLookup("[CompanyName]", "Customers", _
             "[CompanyName]='" & NewData & "'")
    If IsNull(Result) Then
       ' If the customer was not created, set the Response argument
       ' to suppress an error message and undo changes.
       Response = acDataErrContinue
       ' Display a customized message.
       MsgBox "Please try again!"
    Else
       ' If the customer was created, set the Response argument to
       ' indicate that new data is being added.
       Response = acDataErrAdded
    End If
End Sub

Open in new window

LVL 11
Ved Prakash AgrawalDatabase Consultant/Performance ArchitectAsked:
Who is Participating?
 
Ved Prakash AgrawalConnect With a Mentor Database Consultant/Performance ArchitectAuthor Commented:
I solved myself this problem.

I think the problem is only in adp project and code was working fine with mdb.

0
 
Rey Obrero (Capricorn1)Commented:
change this

    Result = DLookup("[CompanyName]", "Customers", _
             "[CompanyName]='" & NewData & "'")

to

    Result = DLookup("[CompanyName]", "Customers", _
             "[CompanyName]=" & chr(34) & NewData & chr(34))
0
 
Ved Prakash AgrawalDatabase Consultant/Performance ArchitectAuthor Commented:
capricorn1,
Its not working.
I modified the code
  Result = DLookup("[CompanyName]", "Customers", _
             "[CompanyName]='" & NewData & chr(34) & "'")

AND
  Result = DLookup("[CompanyName]", "Customers", _
             "[CompanyName]='" & replace(NewData,"""","") & chr(34) & "'")


But the code still not working.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Rey Obrero (Capricorn1)Commented:
ved17nov,

compare what you have to what i posted..
0
 
Ved Prakash AgrawalDatabase Consultant/Performance ArchitectAuthor Commented:
capricorn,
I attached the code where i am testing.

in main form -->Products--> change supplier combo box text to  Testing1"

Thanks
Sample.zip
0
 
Ved Prakash AgrawalDatabase Consultant/Performance ArchitectAuthor Commented:
When i tried to use the same code as it is which you suggest. I am getting following error.

Error Message:
Closing delimiter not found for the string beginning at position 79 in the command. The string begins with: "Testing7"")} AS.
0
 
Rey Obrero (Capricorn1)Commented:
your file can't be opened..

are using { ' } and { " } in your entries?

this should do it

  Result = DLookup("[CompanyName]", "Customers", _
             "[CompanyName]='" & Replace(NewData,"'","''") & "'")



exploded view

                      "[CompanyName]=' " & Replace(NewData," ' "," ' ' ") & " ' ")





0
 
Ved Prakash AgrawalDatabase Consultant/Performance ArchitectAuthor Commented:
I am using {"}
0
 
Ved Prakash AgrawalDatabase Consultant/Performance ArchitectAuthor Commented:
I am not using double quotation.

shift+'
0
 
Ved Prakash AgrawalDatabase Consultant/Performance ArchitectAuthor Commented:
I removed the extension from file. Please add the extension as below.

NorthwindCS.SQL
NorthwindCS_Backup.adp


Sample-adp.zip
0
 
Ved Prakash AgrawalDatabase Consultant/Performance ArchitectAuthor Commented:
Sorry, I am using double quotation.
0
 
Ved Prakash AgrawalDatabase Consultant/Performance ArchitectAuthor Commented:
Hello capricorn1,
did you get tme to look the problem?


Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.