Results 1 to 8 of 8
  1. #1
    DTO. is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    48

    How can I create pop up messages in MS Access FE instead of SQL Server messages?

    Dear friends,

    I have developed an application (MS Access FE - SQL Server BE)

    What I am trying to do is to bring my own customized messageboxes instead of SQL Server's own database messages, when a not null fields are left empty by users. I want only my own customized messages to come the users.

    Is it possible to do?



    Thanks in advance

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    do you have error trapping?

    Code:
    Private Sub Command2_Click()
    On Error GoTo Err_Command2_Click
    
    'your main code here 
    
    
    
    Exit_Command2_Click:return spot set rst = nothing
    exit sub 'leave the main code
    'error trap here
    Err_Command2_Click: MsgBox Err.Description,,err Resume Exit_Command2_Click 'resume if needed. End Sub


  3. #3
    DTO. is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    48
    Dear Ranman256, thanks for reply.

    I have following message from ODBC connection of SQL Server saying that, those fields can not be null. INSERT Fails. (#515)..... The statement has been terminated (#3621).

    And second thing is that you wrote the column inside the clicking event. But I am just going forward to the next record (next row) for new record. And then SQL Server message comes.

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    that was an example ,
    yet you never answered my question if you have error traps.

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Validate your entry in the BeforeUpdate event of the form. Check that all required fields are populated and cancel the update if any fails:
    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    If IsNull(Me.txtRequiredField1) then
        Msgbox "Please enter a value for RequiredField1!",vbCritical,"Error-Missing required data"
        Me.txtRequiredField1.SetFocus
        Cancel=True
        Exit Sub
    End if
    'next field
    If IsNull(Me.txtRequiredField2) then
        Msgbox "Please enter a value for RequiredField2!",vbCritical,"Error-Missing required data"
        Me.txtRequiredField2.SetFocus
        Cancel=True
        Exit Sub
    End if
    '...............
    End Sub
    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  6. #6
    DTO. is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    48
    Hello ranman256,

    I apologize, I am still very amateur regarding coding.
    As I understand you are asking error handling. I applied it some other codes and issues but I knew error number in those situations. But SQL Server gives different messages as I sent you. I did not have it here.

    By the way I obtained desired result with codes of Gicu.

    Thanks anyway.

  7. #7
    DTO. is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    48
    Quote Originally Posted by Gicu View Post
    Validate your entry in the BeforeUpdate event of the form. Check that all required fields are populated and cancel the update if any fails:
    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    If IsNull(Me.txtRequiredField1) then
        Msgbox "Please enter a value for RequiredField1!",vbCritical,"Error-Missing required data"
        Me.txtRequiredField1.SetFocus
        Cancel=True
        Exit Sub
    End if
    'next field
    If IsNull(Me.txtRequiredField2) then
        Msgbox "Please enter a value for RequiredField2!",vbCritical,"Error-Missing required data"
        Me.txtRequiredField2.SetFocus
        Cancel=True
        Exit Sub
    End if
    '...............
    End Sub
    Cheers,
    Vlad

    Hello Sir Vlad, many thanks. it gave me desired output. I integrated it to my forms.

  8. #8
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    if you have many fields that are required you can also use a public function for validation using the tag property of the controls.
    This is similar to Vlad's code but a lot less typing if there are many controls to validate. Additionally it will concatenate a string of the controls labels
    and fire a messagebox. It will also color the border of the control red.

    In a standard module put the following code:
    Code:
    Public Function ValidateForm(frm As Form, TagCharacter As String) As Boolean
    'validated controls must have a label. Ok to use a hidden label if needed.
    
    
        Dim ctl As control
        Dim flg As Boolean
        Dim strOut As String
    
    
        'flg = True
    
    
        For Each ctl In frm.Controls
    
    
            If InStr(1, ctl.Tag, TagCharacter) Then  'using Instr() allows for multiple tags
                If Nz(ctl.Value, "") = "" Then
                    flg = True
                    ctl.BorderColor = vbRed
                    strOut = strOut & Space(10) & "* " & ctl.Controls.Item(0).Caption & vbNewLine
                Else
                    ctl.BorderColor = vbBlack
                End If
    
    
            End If
        Next
    
    
        If flg = True Then
            MsgBox "The following field(s) must be completed:" & vbNewLine & strOut
        End If
    
    
        ValidateForm = flg
    
    
    End Function
    On your forms:
    In the tag property of the control you enter a value such as VD8 then in the before update of the form enter
    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    
    
        Cancel = ValidateForm(Me, "VD8")
    
    
    End Sub
    Personally I tend to use it in the close form command button on my forms

    Code:
    Private Sub cmdCloseForm_Click()
    
    
        If ValidateForm(Me, "VD8") = True Then
            Exit Sub
        End If
    
    
        DoCmd.Close acForm, Me.Name
    
    
    End Sub
    HTH
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 09-09-2017, 01:56 AM
  2. MS Access 2010 System Messages
    By MarkA70 in forum Access
    Replies: 2
    Last Post: 02-04-2016, 01:09 PM
  3. Problem with access messages on queries
    By gstylianou in forum Access
    Replies: 9
    Last Post: 06-04-2014, 12:15 AM
  4. Suppressing Access messages
    By Reaper in forum Forms
    Replies: 1
    Last Post: 11-06-2011, 04:02 PM
  5. Custom messages to Access' default error messages.
    By evander in forum Programming
    Replies: 1
    Last Post: 06-26-2010, 02:06 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums