NotInList

[Guest]

Hi there,
I am having problems with a NotInList event

Below is what I have:
Private Sub ContainerID_NotInList(NewData As String, Response As Integer)
Dim strName As String, strWhere As String

strName = NewData
strWhere = "[ContainerID] = '" & strName & "'"

If vbYes = MsgBox("Container Number " & NewData & " is not defined. " & _
"Do you want to add this Number?", vbYesNo + vbQuestion +
vbDefaultButton2, gstrAppTitle) Then

DoCmd.OpenForm "ContainerNofrm", DataMode:=acFormAdd,
WindowMode:=acDialog, _
OpenArgs:=strName

If IsNull(DLookup("ContainerID", "ContainerNotbl", strWhere)) Then

MsgBox "You failed to add a Container Number that matched what
you entered. Please try again.", vbInformation, gstrAppTitle

Response = acDataErrContinue
Else

Response = acDataErrAdded
End If
Else

Response = acDataErrDisplay
End If
End Sub

Aften new data is eneterd into the "ContainerNofrm" and the form is closed I
get error message "Datatype Mismatch in query expression" when I debug the
line below is highlighted:
If IsNull(DLookup("ContainerID", "ContainerNotbl", strWhere)) Then

help

 

[Dirk Goldgar]

Hi there,
I am having problems with a NotInList event

Below is what I have:
Private Sub ContainerID_NotInList(NewData As String, Response As
Integer) Dim strName As String, strWhere As String

strName = NewData
strWhere = "[ContainerID] = '" & strName & "'"

If vbYes = MsgBox("Container Number " & NewData & " is not
defined. " & _ "Do you want to add this Number?", vbYesNo +
vbQuestion +
vbDefaultButton2, gstrAppTitle) Then

DoCmd.OpenForm "ContainerNofrm", DataMode:=acFormAdd,
WindowMode:=acDialog, _
OpenArgs:=strName

If IsNull(DLookup("ContainerID", "ContainerNotbl", strWhere))
Then

MsgBox "You failed to add a Container Number that matched
what you entered. Please try again.", vbInformation, gstrAppTitle

Response = acDataErrContinue
Else

Response = acDataErrAdded
End If
Else

Response = acDataErrDisplay
End If
End Sub

Aften new data is eneterd into the "ContainerNofrm" and the form is
closed I get error message "Datatype Mismatch in query expression"
when I debug the line below is highlighted:
If IsNull(DLookup("ContainerID", "ContainerNotbl", strWhere)) Then

help

What type of field is ContainerID? If it's a number field, the value
taken from NewData should not be enclosed in quotes. So instead of
this:

strWhere = "[ContainerID] = '" & strName & "'"

.... write this:

strWhere = "[ContainerID] = " & strName

 

[Guest]

Hey Dirk,
Thanksmuch your a genious it works great.

Hi there,
I am having problems with a NotInList event

Below is what I have:
Private Sub ContainerID_NotInList(NewData As String, Response As
Integer) Dim strName As String, strWhere As String

strName = NewData
strWhere = "[ContainerID] = '" & strName & "'"

If vbYes = MsgBox("Container Number " & NewData & " is not
defined. " & _ "Do you want to add this Number?", vbYesNo +
vbQuestion +
vbDefaultButton2, gstrAppTitle) Then

DoCmd.OpenForm "ContainerNofrm", DataMode:=acFormAdd,
WindowMode:=acDialog, _
OpenArgs:=strName

If IsNull(DLookup("ContainerID", "ContainerNotbl", strWhere))
Then

MsgBox "You failed to add a Container Number that matched
what you entered. Please try again.", vbInformation, gstrAppTitle

Response = acDataErrContinue
Else

Response = acDataErrAdded
End If
Else

Response = acDataErrDisplay
End If
End Sub

Aften new data is eneterd into the "ContainerNofrm" and the form is
closed I get error message "Datatype Mismatch in query expression"
when I debug the line below is highlighted:
If IsNull(DLookup("ContainerID", "ContainerNotbl", strWhere)) Then

help

What type of field is ContainerID? If it's a number field, the value
taken from NewData should not be enclosed in quotes. So instead of
this:

strWhere = "[ContainerID] = '" & strName & "'"

.... write this:

strWhere = "[ContainerID] = " & strName

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)