Not in list even

[mtagliaferri]

Hi all, I have copied a code to show a not in list on a combo box, what should do is if the value in the combo box is not in list a message appears askink if you would like to add the data and if yes it open a form to add the new data, when closing the form once added the record it gives a error as:
ERROR ON RUN TIME "3464", the code I have is

Private Sub StaffNumber_NotInList(NewData As String, Response As Integer)
Dim strName As String, strWhere As String
    
    ' User typed in a product type that's not in the list
    strName = NewData
    ' Build the verification search string
    strWhere = "[StaffNumber] = '" & strName & "'"
        
    ' Verify that they want to add the new Contact Type
    If vbYes = MsgBox("Crew Member " & NewData & " is not defined. " & _
        "Do you want to add this Crew Member?", vbYesNo + vbQuestion + vbDefaultButton2, gstrAppTitle) Then
        ' Open the add a Product Type 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 "Crew Members", DataMode:=acFormAdd, WindowMode:=acDialog, _
            OpenArgs:=strName
        ' Code will wait until "add" form closes - now verify that it got added!
        [B]If IsNull(DLookup("StaffNumber", "Crew Member", strWhere)) Then[/B]
            ' Ooops
            MsgBox "You failed to add a Crew Member that matched what you entered.  Please try again.", vbInformation, gstrAppTitle
            ' Tell Access we handled the error, but cancel the update
            Response = acDataErrContinue
        Else
            ' Tell Access new data was added
            Response = acDataErrAdded
        End If
    Else
        ' Don't want to add what they typed - show standard error message
        Response = acDataErrDisplay
    End If
End Sub

Highlighting the Dlookup...
I immagine I havent copied completely the informatio, can I get any help?
Thanks
Marco

 

[wazz]

a real quick look:

- i think you don't have to Dim strName = NewData...just keep using NewData.
- i think in your DLookup you need [] around Crew Member because you can't have spaces.
- i think you should be doing a check on your Crew Members form to make sure everything is OK instead of sending the user back with an error.
- suggest that you rename all objects with no spaces, i.e. frmCrewMembers.

- i'm not sure if that will completely solve it...

 

[ansentry]

Have a look at my attachment in this post:

<< Click Here >>

 

[Declano]

Hi John A, thanks for your help, that is what I need, but unfortunatly I need to open the relative form because when I add a new record I have to fill further information and not only the field in the combo box!
Did I get you a idea of what I need!
Declan

 

[ansentry]

Declan,

I am a little confused, how come you replied when I posted an answer for mtagliaferri.

Not that I mind helping you (if I can), please post back and and let me know are you and mtagliaferri one in the same?

If not then please explain your problem and I or someone else will try and help you.

 

[mtagliaferri]

Hi John A
It is always me, I am closing my email as I am getting to much spam, I tought I could not change the email for the allerts, so I have opened a new account.. and know I am even more confused with the 2 accounts, the correct account is mtagliaferri. Sorry if this coused you some disappointment.