Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old February 22nd, 2008, 04:33 PM
Authorized User
 
Join Date: Jan 2006
Location: , , .
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!!!! :)

Reply With Quote
  #2 (permalink)  
Old February 22nd, 2008, 04:43 PM
Authorized User
 
Join Date: May 2007
Location: , , .
Posts: 85
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

Reply With Quote
  #3 (permalink)  
Old February 25th, 2008, 08:39 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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?



mmcdonal

Look it up at: http://wrox.books24x7.com
Reply With Quote
  #4 (permalink)  
Old February 25th, 2008, 04:25 PM
Authorized User
 
Join Date: Jan 2006
Location: , , .
Posts: 48
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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??

Reply With Quote
  #5 (permalink)  
Old February 26th, 2008, 06:08 AM
Authorized User
 
Join Date: Mar 2007
Location: Sydney, NSW, Australia.
Posts: 53
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Cheers

Darren

Reply With Quote
  #6 (permalink)  
Old February 26th, 2008, 05:55 PM
Authorized User
 
Join Date: Jan 2006
Location: , , .
Posts: 48
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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, _
            OpenArgs:=strName
        ' Customer Added - Tell Access so that combo gets requeried
        Response = acDataErrAdded
    Else
        ' 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. :)

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 05:47 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.