How to build a custom MsgBoxs in (Audit Trail)

[Falcon88]

all dears hiii i have find a very good example (Audit Trail). in link http://www.access-programmers.co.uk/forums/showthread.php?p=650844 but when delete any record , i have another msgbox for every frm. and subfrm. , depending on the data in that frm. how to use this (Audit Trail that is in the link ), with my custom msgboxs ?

 

[Falcon88]

in this function code:

Option Compare Database
Option Explicit

Public gstrReason      As String

Public Function Delete_Rec(MyForm As Form, UniqID_Field As String, UniqID As String)
On Error GoTo Err_Delete_Rec

    MyForm.AllowEdits = True
    MyForm.AllowDeletions = True

    If MsgBox("Are you sure you want to delete this record", vbYesNo, "Delete this record?") = vbYes Then
        Dim ctl As Control
        Dim sUser As String
        Dim delvals As String
        
        Dim strSQL As String
        Const cQUOTE = """" 'That is 2 quotes in sequence
            
        Dim action As String
        action = "*** Record Deleted ***"
               
        sUser = Environ("UserName") 'get the users login name
                                              
        'Check each data entry control for change and record old value of the control.
        For Each ctl In MyForm.Controls
            'Only check data entry type controls.
            Select Case ctl.ControlType
                Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox
                If ctl.Name Like "*" & "txt" & "*" Then GoTo TryNextControl 'Skip AuditTrail field.
                If Len(ctl.Value) > 0 Then delvals = delvals & "| " & ctl.Name & " = " & ctl.Value & " "
            End Select
            
TryNextControl:
Next ctl
                       
        'Broken down into 4 separate variables for ease of view and troubleshooting
        strSQL = "INSERT INTO tblAudit ( [User], [DateTime], UniqID_Field, UniqID, Form, [Action], Reason, DelValues)"
        strSQL = strSQL & " SELECT " & cQUOTE & sUser & cQUOTE & ", " & cQUOTE & Now & cQUOTE & " , "
        strSQL = strSQL & cQUOTE & UniqID_Field & cQUOTE & ", " & cQUOTE & UniqID & cQUOTE & ", "
        strSQL = strSQL & cQUOTE & MyForm.Name & cQUOTE & ", " & cQUOTE & action & cQUOTE & ", " & "[Reason for change] AS Expr1" & ", "
        strSQL = strSQL & cQUOTE & delvals & cQUOTE & ";"
                
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
        
        DoCmd.SetWarnings False
        DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
        DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
        DoCmd.SetWarnings True
        
    End If
    
    
    MyForm.AllowEdits = False
    MyForm.AllowDeletions = False
    
Exit_Delete_Rec:
    Exit Function
    
Err_Delete_Rec:
        Beep
        MsgBox Err.Number & " - " & Err.Description
        'Resume Exit_Delete_Rec
        Exit Function
End Function

instead of msgbox:

  If MsgBox("Are you sure you want to delete this record", vbYesNo, "Delete this record?")

i have a custom MsgBox for every Form and Subfrom.

i want to change this code to work with my msgbox.

who that ?

 

[jdraw]

You are going to have to show readers something about your custom Message Box.
We can not guess what you are saying- show us code, screen capture, sample usage or something.

 

[Fuse3k]

Replace the MsgBox line in your code with this line:

DoCmd.OpenForm FormName:="FORM_NAME_HERE", WindowMode:=acDialog

Opening a form as a Dialog pauses code execution, just like a message box does. Be sure to replace "FORM_NAME_HERE" with the name of your custom message box form.

In your custom message box, you need a way to return the variable to your code. This is what I do: Assuming your custom message box has a command button, add this in there:

TempVars.Add("MsgBoxRetVal", Text1.Value)

Replace "Text1" with the name of the control that you want the message box to return to the calling function.

Now, go back to your function and add this line after the DoCmd.OpenForm line:

Msgbox "You selected " & tempvars("MsgBoxRetVal")

Please Thank if this is helpful!

 

[Falcon88]

Please see to Delete Button in every form.
i have a massage for every record delete in every Form

 

[jdraw]

There is more info (maybe more than you want) at this site by Doug Steele.