Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old February 22nd, 2008, 04:33 PM
Authorized User
Join Date: Jan 2006
Posts: 48
Thanks: 0
Thanked 0 Times in 0 Posts
Default how to avoid duplicate contacts? combo box?

i have a work order form i'm creating and i made a combo box to select from existing contact names by either typing them in or scrolling throught them. But i have a couple of problems.
1.) if the name doesn't exist it and i try to leave the combo box blank so i can open up another form to enter in the new name i get an error "You tried to assign a null value to a variable that is not a variant data type"
2.) if i try to enter a new name into the combo box that doesn't exist i get this error "the text you entered isnt an item in the list"

I have over 1000 client names and i dont want to reenter there names when we write up new jobs and end up with duplicate client names in my table. what would be the best way to solve this issue?

I thought by using a combo box i would be able to type in a name and if it was existing i could pull it in to fill in my client fields on the form. and if it wasn't in the list i could simply open another quick form to enter in the new client and go back to the combo box to retrieve it. this seems like a very simple concept but yet its driving me nuts with its complexities.

does anyone have any ideas as how to solve this issue?
i will provide you with more info on how i set my form and query up if that will be of any help....

any suggestions would be greatly appreciated!!!! :)

Old February 22nd, 2008, 04:43 PM
Authorized User
Join Date: May 2007
Posts: 85
Thanks: 0
Thanked 0 Times in 0 Posts

if your local library has a copy of microsoft press' "building access applications" this gives a step by step guide to solving your problem.

The solution would involve soundex and the client address which i think you probably gather elsewhere.

Old February 25th, 2008, 08:39 AM
Friend of Wrox
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts

Use the combo box solution that you currently have, set the "Limit to List" property to Yes, and then add code to the "On Not In List" event for the combo box. You will probably have to open a new form for the user to add the new name, and then reopen the form they came from with the new user record already open.

Did this help?


Look it up at: http://wrox.books24x7.com
Old February 25th, 2008, 04:25 PM
Authorized User
Join Date: Jan 2006
Posts: 48
Thanks: 0
Thanked 0 Times in 0 Posts

i went ahead and wrote a code for the "notinlist" and created a another form to add the customer name in but when i close out of that "add" form my combo box is not updating or refreshing when i return to the main form. how can i get it to update? i've tried several things none of which is working. any suggestions??

Old February 26th, 2008, 06:08 AM
Authorized User
Join Date: Mar 2007
Posts: 53
Thanks: 0
Thanked 0 Times in 0 Posts

Just a quick one, when you open your form from the Not In List event ensure you open the form as dialog, eg
DoCmd.OpenForm "Form Name", acNormal, , , acFormAdd, acDialog
This way your code in the originating form will wait till you close your main form, for the requery try
DoCmd.Requery "Combo Box Name"

Hope this helps??

If not post back and i will post my full code i use in this same situation



Old February 26th, 2008, 05:55 PM
Authorized User
Join Date: Jan 2006
Posts: 48
Thanks: 0
Thanked 0 Times in 0 Posts

well i came up with a work around and it seems to be working the way i would like it too. but i'm not sure if this would be the best way to write the code. but i'm posting it and maybe someone can tell me if there are problems with it that is obvious that i cant see.

Private Sub cmbClientID_NotInList(NewData As String, Response As Integer)
Dim strName As String, strWhere As String

    ' User typed in a contact type that's not in the list
    strName = NewClient
    ' Build the verification search string
    strWhere = "[Client] = '" & strName & "'"

    ' Verify that they want to add the new Client
    If vbYes = MsgBox("The Customer " & NewClient & " is not in the system. " & _
        "Do you want to add this Customer?", vbYesNo + vbQuestion + vbDefaultButton1, gstrAppTitle) Then
        ' Open the add a Client form and pass it the new value
        ' NOTE: For this simple lookup value we could do an SQL Insert,
        ' but this method gives the user one extra chance to verify the value.
        DoCmd.OpenForm "F_Client", DataMode:=acFormAdd, WindowMode:=acDialog, _
        ' Customer Added - Tell Access so that combo gets requeried
        Response = acDataErrAdded
        ' Don't want to add what they typed - show standard error message
        Response = acDataErrDisplay
    End If

End Sub

let me know if you see any problems with using this. like i said its working exactly the way i need it to, but i like things to be perfect as much as possible and dont want to end up with some major headaches down the road. :)

Similar Threads
Thread Thread Starter Forum Replies Last Post
how to avoid duplicate values from the xml file us balasundarrao XSLT 2 June 4th, 2014 11:56 PM
How Do I avoid duplicate values xsltier XSLT 3 June 4th, 2008 01:36 PM
Combo box choice creating filtered combo box stevensj5 Access 11 September 13th, 2007 11:33 AM
avoid duplicate entry of records mikersantiago Classic ASP Basics 4 February 10th, 2005 09:14 PM
Avoid duplicate node values nambati XSLT 1 September 8th, 2004 02:41 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.