Not in List Event

The code below has two problems. First the Access message is still displayed
after my custom msgbox is displayed, and second, the field CaseName is not
updated from NewData on the form frmCase when it is opened.

What am I missing or doing wrong?

Thanks for any help!

Private Sub cboCaseName_NotInList(NewData As String, Response As Integer)

On Error GoTo Error_Handler

Dim intAnswer As Integer

intAnswer = MsgBox("""" & NewData & """ is not listed. " & vbCrLf & _
"Do you want to add a new Case?", vbYesNo + vbQuestion,
"Invalid Case")

Select Case intAnswer
Case vbYes
DoCmd.SetWarnings False
DoCmd.OpenForm "frmCase", acNormal, , , acFormAdd
DoCmd.SetWarnings True
Response = acDataErrAdded
Case vbNo
MsgBox "Please select a Case from the list. ", _
vbExclamation + vbOKOnly, "Invaid Case"
Response = acDataErrContinue
End Select

Exit_Procedu
DoCmd.SetWarnings True
Exit Sub
Error_Handler:
MsgBox Err.Number & ", " & Err.Description
Resume Exit_Procedure
Resume

End Sub

You need to stop the code in the first form until the 2nd form has had a
change to save the record. Dialog mode does this. There is also no reason to
turn off Warnings.

Case vbYes
'-- DoCmd.SetWarnings False
DoCmd.OpenForm "frmCase", acNormal, , , acFormAdd, acDialog
'-- DoCmd.SetWarnings True
Response = acDataErrAdded


Marianne wrote:
The code below has two problems. First the Access message is still displayed
after my custom msgbox is displayed, and second, the field CaseName is not
updated from NewData on the form frmCase when it is opened.

What am I missing or doing wrong?

Thanks for any help!

Private Sub cboCaseName_NotInList(NewData As String, Response As Integer)

On Error GoTo Error_Handler

Dim intAnswer As Integer

intAnswer = MsgBox("""" & NewData & """ is not listed. " & vbCrLf & _
"Do you want to add a new Case?", vbYesNo + vbQuestion,
"Invalid Case")

Select Case intAnswer
Case vbYes
DoCmd.SetWarnings False
DoCmd.OpenForm "frmCase", acNormal, , , acFormAdd
DoCmd.SetWarnings True
Response = acDataErrAdded
Case vbNo
MsgBox "Please select a Case from the list. ", _
vbExclamation + vbOKOnly, "Invaid Case"
Response = acDataErrContinue
End Select

Exit_Procedu
DoCmd.SetWarnings True
Exit Sub
Error_Handler:
MsgBox Err.Number & ", " & Err.Description
Resume Exit_Procedure
Resume

End Sub

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200910/1

Thanks, that worked! Your Awesome and Happy Friday!

"ruralguy via AccessMonster.com" wrote:

You need to stop the code in the first form until the 2nd form has had a
change to save the record. Dialog mode does this. There is also no reason to
turn off Warnings.

Case vbYes
'-- DoCmd.SetWarnings False
DoCmd.OpenForm "frmCase", acNormal, , , acFormAdd, acDialog
'-- DoCmd.SetWarnings True
Response = acDataErrAdded


Marianne wrote:
The code below has two problems. First the Access message is still displayed
after my custom msgbox is displayed, and second, the field CaseName is not
updated from NewData on the form frmCase when it is opened.

What am I missing or doing wrong?

Thanks for any help!

Private Sub cboCaseName_NotInList(NewData As String, Response As Integer)

On Error GoTo Error_Handler

Dim intAnswer As Integer

intAnswer = MsgBox("""" & NewData & """ is not listed. " & vbCrLf & _
"Do you want to add a new Case?", vbYesNo + vbQuestion,
"Invalid Case")

Select Case intAnswer
Case vbYes
DoCmd.SetWarnings False
DoCmd.OpenForm "frmCase", acNormal, , , acFormAdd
DoCmd.SetWarnings True
Response = acDataErrAdded
Case vbNo
MsgBox "Please select a Case from the list. ", _
vbExclamation + vbOKOnly, "Invaid Case"
Response = acDataErrContinue
End Select

Exit_Procedu
DoCmd.SetWarnings True
Exit Sub
Error_Handler:
MsgBox Err.Number & ", " & Err.Description
Resume Exit_Procedure
Resume

End Sub

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200910/1

.

You're welcome. Glad I could help.

Marianne wrote:
Thanks, that worked! Your Awesome and Happy Friday!

You need to stop the code in the first form until the 2nd form has had a
change to save the record. Dialog mode does this. There is also no reason to
[quoted text clipped - 45 lines]

End Sub

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200910/1

As regards your second problem you can pass the new value to the frmCase form
via the OpenArgs mechanism:

Select Case intAnswer
Case vbYes
DoCmd.OpenForm "frmCase ", _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData

' ensure frmCase closed
DoCmd.Close acForm, "frmCase"

' ensure case has been added
If Not IsNull(DLookup("CaseName", "Cases", "CaseName = """
& _
NewData & """")) Then
Response = acDataErrAdded
Else
MsgBox NewData & " was not added to Cases table.", _
vbInformation, "Warning"
Response = acDataErrContinue
ctrl.Undo
End If
Case vbNo

This assumes that the value is to be inserted into a column CaseName in a
table Cases.

In frmCases Open event procedure you put:

If Not IsNull(Me.OpenArgs) Then
Me.CaseName.DefaultValue = """" & Me.OpenArgs & """"
End If

This sets the DefaultValue property of the CaseName control in frmCase. Note
that this does not initiate a new record in itself; the user needs to enter
more data in frmCase before that happens. So if they abort the new record
simply by closing frmCase without entering any additional data, or if they do
so and then change their mind by 'undoing' the new record with the Esc key,
the new record is not saved and the code in the calling procedure detects
this and informs the user accordingly.

The reason for ensuring that frmCase is closed in the calling procedure is
that if a form opened in dialogue mode is hidden rather than closed the
execution of the calling code will also resume. Its unlikely that this will
happen, but Murphy's Law means that this possibility should nevertheless be
catered for.

Ken Sheridan
Stafford, England

Marianne wrote:
Thanks, that worked! Your Awesome and Happy Friday!

You need to stop the code in the first form until the 2nd form has had a
change to save the record. Dialog mode does this. There is also no reason to
[quoted text clipped - 45 lines]

End Sub

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200910/1