Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2014
    Posts
    2

    Unanswered: Need help writing onbeforeupdate code to address required fields

    Good morning all.

    I am new to this forum. I have worked many many years with Access but never really got into VB. I have recently been tasked to do some more intensive DB management and form design and am a little stuck with this one. I am using Access 2010.

    These are the names of the form objects I wish to make required:
    cmbModel
    txtSerialNo
    txtExpDate
    txtPONo
    cmbOfficeLoc

    I know I need to build some if then statements in there and incorporate some onfocus cmds I just dont have the experience. Ideally what I am looking for is code that will prompt a user if they left any of the fields above empty. If they left one or more of the fields above empty, prompt the user to enter a value and focus back on the first field left empty. Note...the variables listed above are listed in the order they appear on the form.

    I currently have the code below on my beforeupdate and wish to add the required fields code prior to the code shown below.

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim strMsg As String
    Dim iResponse As Integer

    ' Specify the message to display.
    strMsg = "Do you wish to save the changes?" & Chr(10)
    strMsg = strMsg & "Click Yes to Save or No to Discard changes."

    ' Display the message box.
    iResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Save Record?")

    ' Check the user's response.
    If iResponse = vbNo Then

    ' Undo the change.
    DoCmd.RunCommand acCmdUndo

    ' Cancel the update.
    Cancel = True
    End If
    End Sub

    How do I incorporate the required fields code to the code above so they work together?

    Note I am also using a "save" button on the form that performs a basic Access save button function.

    Your help is greatly appreciated.

    Carlos

  2. #2
    Join Date
    Apr 2014
    Posts
    2

    Sorry everyone. I had a difficult time with posting so it showed up 3 times.

    Sorry everyone. I had a difficult time with posting so it showed up 3 times. My bad.

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    I fixed that for you.

    There are a number of ways to approach this, the simplest being something like this, added in the Form_BeforeUpdate event, prior to your current code:
    Code:
    If Nz(Me.Control1,"") = "" Then
       MsgBox "Control1 Must Not Be Left Blank!"
       Cancel = True
       Control1.SetFocus
       Exit Sub
     End If
     
    If Nz(Me.Control2, "") = "" Then
       MsgBox "Control2 Must Not Be Left Blank!"
       Cancel = True
       Control2.SetFocus
       Exit Sub
     End If

    You should only present the user with one Control at a time, even when there are multiple Controls that are 'empty,' to keep from overwhelming them.

    Welcome to the forum!

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

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
  •