Results 1 to 2 of 2
  1. #1
    mreniff is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Posts
    41

    Help with Validation rule in Access table or form


    I am newby with applying the validation rule expression. I need help with the expression for the validation rule at the table or form level. There is a field that cannot be blank when the record is saved. Using the “required” property interferes with other processes that need to run to complete the transaction

    Steps are:

    1. Select a date from a combo box list
      1. Record is appended to the Appointment table

    2. GroupID attribute that cannot be blank. A value is copied from another list (on a subform) and pasted this value in the GroupID attribute (see ** code below).
    3. The Save Appointment button (* see code below) updates the record in the Appointment

    These expressions failed:

    • Is Not Null
    • >””
    • >0


    *Button code:
    Code:
    Private Sub sv_Click()
    On Error GoTo Err_sv_Click
       
    DoCmd.Close
    DoCmd.Close acForm, "AdvisorAppointments-new Scheduler1:1"
    DoCmd.SetWarnings False
        DoCmd.OpenQuery "Updates Appointment table with Major from Students table" 'updates major in appointment table
        DoCmd.OpenQuery "Update GroupAdvisingSessionID in Students table" 'updates student table DoCmd.SetWarnings True
    Exit_sv_Click:
    **Copy and pasting code
    Code:
    Private Sub GroupAdvisingRecordID_DblClick(Cancel As Integer)
    'updates GroupRecID in the appointment table from the scheduler form when GroupAdvisingRecordID  field is double clicked
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "Update GroupRecID in Appointment table from Scheduler1:1"
    'updates GroupAdvisingSessionID in Students table
    DoCmd.OpenQuery "Update GroupAdvisingSessionID in Students table"
    DoCmd.SetWarnings True
    'DoCmd.RefreshRecord
    
    
    End Sub

    I appreciate any help ....

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    I don't understand the "bigger picture" of what you are trying to do here or why you can't use validation at the table level.
    However, IMHO, form validation should be done in the forms Before Update event. This event is the only always one that always fires. Try something like:
    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)    If Len(Me.NameOfControl) = 0 Then
            MsgBox "Data for '" & Me.NameOfControl & "' is required", , "Incomplete Data"
            Me.NameOfControl.SetFocus
            Cancel = True
        End If
    
    
    End Sub
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

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

Similar Threads

  1. Replies: 2
    Last Post: 03-21-2016, 11:05 AM
  2. Replies: 2
    Last Post: 06-16-2015, 03:52 AM
  3. Replies: 2
    Last Post: 12-27-2013, 07:32 AM
  4. Validation Rule by relating the data with table
    By Falahuddin in forum Access
    Replies: 14
    Last Post: 12-23-2013, 07:15 PM
  5. Validation Rule in Table vs. Form
    By LanieB in forum Forms
    Replies: 2
    Last Post: 03-12-2012, 03:27 PM

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