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!!!! :)

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.

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

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

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

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. :)