Dialog in MsgBox

[Dan @BCBS]

The following code is used to delete a record and store the data in a table.
I'd like to add a dialog box to my Msgbox, so the person deleting the
record, can make a comment.
I have added a Field to the table to collect the comments, but cannot seem
to plug it into the Msgbox correctly:



Private Sub cmdDelete_Click()
Dim lCriteria As String
Dim lICCNNO As String
Dim lRacfid As String

lICCNNO = Me!ICNNO

If MsgBox("Are you sure you want to delete ICN No. " & lICCNNO,
vbQuestion & vbYesNo, gappname) = vbYes Then
DoCmd.SetWarnings False
Dim lID As Long
lID = GetNewID("tblTrackingDataDeleted")
lCriteria = "INSERT INTO tblTrackingDataDeleted ( ID, ICNNO,
MEMBERNO, TR_PRODUCT, "
lCriteria = lCriteria & "TR_INQUIRYTYPE, TR_DATE_TIMERCVD_HOI,
TR_DATE_TIMERCVD, "
lCriteria = lCriteria & "TR_GRIEVANCECOORDINATOR, TR_CLOSEDATE,
TR_9000CAUSECODE, "
lCriteria = lCriteria & "TR_PROBLEMCODE, TR_ACKNOWLTR, "
lCriteria = lCriteria & "TR_MEDICALRELEASERETURNDT, TR_DECISION,
TR_EXPEDITED, TR_SOURCE, TR_COMMENTS, "
lCriteria = lCriteria & "TR_CASESUMMARY, TR_STATUS, "
lCriteria = lCriteria & "TR_Who, TR_When ) "

lCriteria = lCriteria & "SELECT " & lID & " AS tID,
tblTrackingData.ICNNO, tblTrackingData.MEMBERNO, "
lCriteria = lCriteria & "tblTrackingData.TR_PRODUCT, "
lCriteria = lCriteria & "tblTrackingData.TR_INQUIRYTYPE, "
lCriteria = lCriteria & "tblTrackingData.TR_DATE_TIMERCVD_HOI,
tblTrackingData.TR_DATE_TIMERCVD, "
lCriteria = lCriteria & "TR_GRIEVANCECOORDINATOR,
tblTrackingData.TR_CLOSEDATE, "
lCriteria = lCriteria & "tblTrackingData.TR_9000CAUSECODE, "
lCriteria = lCriteria & "tblTrackingData.TR_PROBLEMCODE, "
lCriteria = lCriteria & "tblTrackingData.TR_ACKNOWLTR, "
lCriteria = lCriteria &
"tblTrackingData.TR_MEDICALRELEASERETURNDT, tblTrackingData.TR_DECISION, "
lCriteria = lCriteria & "tblTrackingData.TR_EXPEDITED,
tblTrackingData.TR_SOURCE, "
lCriteria = lCriteria & "tblTrackingData.TR_COMMENTS,
tblTrackingData.TR_CASESUMMARY, "
lCriteria = lCriteria & "tblTrackingData.TR_STATUS, " & """" &
gcurrentuser & """" & " AS tUser, "
lCriteria = lCriteria & "#" & Format$(Now, "mm\/dd\/yyyy") & "#
AS tDate "

'lCriteria = lCriteria & "#" & Format$(Now, "mm\/dd\/yyyy
hh\:nn\:ss") & "# AS tDate "
'This documents Date & time it was deleted
lCriteria = lCriteria & "FROM tblTrackingData "
lCriteria = lCriteria & "WHERE (((tblTrackingData.ICNNO)=" &
"""" & lICCNNO & """" & "));"
DoCmd.RunSQL lCriteria

lCriteria = "DELETE DISTINCTROW tblTrackingData.ICNNO FROM
tblTrackingData "
lCriteria = lCriteria & "WHERE (((tblTrackingData.ICNNO)=" &
"""" & lICCNNO & """" & "));"
DoCmd.RunSQL lCriteria

DoCmd.SetWarnings True

DoCmd.GoToRecord , , acNewRec
End If
Forms!frmTrackingData.Refresh
Forms!frmTrackingData.Visible = False

Forms!frmMain.Visible = True


Exit_cmdDelete_Click:
Exit Sub
End Sub

 

[Ken Snell \(MVP\)]

A MsgBox cannot accept typed entries from a user. You need to create your
own form to use as the message box, with a textbox on the form into which
the user types the comment. Open your form in Dialog mode, and let the user
click a button on the form to "close" it; in reality, this button should
make the form invisible, and then your code needs to read the value from
that form and write it to the table's field. Your code then must close the
form explicitly.

 

[Dan @BCBS]

That would explain why I'm have a problem doing it!!
Thanks I'll give that a try..

 

[Dan @BCBS]

Ok, I've done what you said, this code opens my form so the person can make a
commnet. But this form must link with the current one they have opened.
Suggestions??


Private Sub WhyDelete_Click()
Dim lICCNNO As String
Dim Question As String

lICCNNO = Me!ICNNO

Question = MsgBox("Enter Delete Reason for ICN " & lICCNNO, vbQuestion +
vbYesNo, "Be Careful")
If vbYes Then
DoCmd.OpenForm "f_WhyDeleteCase"

End If
End Sub

 

[Dan @BCBS]

NEVERMIND - I figured it out - THANKS

Ok, I've done what you said, this code opens my form so the person can make a
commnet. But this form must link with the current one they have opened.
Suggestions??


Private Sub WhyDelete_Click()
Dim lICCNNO As String
Dim Question As String

lICCNNO = Me!ICNNO

Question = MsgBox("Enter Delete Reason for ICN " & lICCNNO, vbQuestion +
vbYesNo, "Be Careful")
If vbYes Then
DoCmd.OpenForm "f_WhyDeleteCase"

End If
End Sub