Solved

Problem NotinList in Combo box

Posted on 2009-03-30
12
347 Views
Last Modified: 2013-12-05
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

0
Comment
Question by:Ved Prakash Agrawal
  • 9
  • 3
12 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
change this

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

to

    Result = DLookup("[CompanyName]", "Customers", _
             "[CompanyName]=" & chr(34) & NewData & chr(34))
0
 
LVL 11

Author Comment

by:Ved Prakash Agrawal
Comment Utility
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
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
ved17nov,

compare what you have to what i posted..
0
 
LVL 11

Author Comment

by:Ved Prakash Agrawal
Comment Utility
capricorn,
I attached the code where i am testing.

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

Thanks
Sample.zip
0
 
LVL 11

Author Comment

by:Ved Prakash Agrawal
Comment Utility
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
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 11

Author Comment

by:Ved Prakash Agrawal
Comment Utility
I am using {"}
0
 
LVL 11

Author Comment

by:Ved Prakash Agrawal
Comment Utility
I am not using double quotation.

shift+'
0
 
LVL 11

Author Comment

by:Ved Prakash Agrawal
Comment Utility
I removed the extension from file. Please add the extension as below.

NorthwindCS.SQL
NorthwindCS_Backup.adp


Sample-adp.zip
0
 
LVL 11

Author Comment

by:Ved Prakash Agrawal
Comment Utility
Sorry, I am using double quotation.
0
 
LVL 11

Author Comment

by:Ved Prakash Agrawal
Comment Utility
Hello capricorn1,
did you get tme to look the problem?


Thanks
0
 
LVL 11

Accepted Solution

by:
Ved Prakash Agrawal earned 0 total points
Comment Utility
I solved myself this problem.

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

0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now