Results 1 to 5 of 5
  1. #1
    sunnyday is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2014
    Posts
    45

    Smile Conditional required fields


    Hi I have a split database. The original fe file resides on my pc. Now I would like certain fields as required when an option is selected from a drop down box.
    Eg if a Status = "Closed" then certain fields on the form become mandatory for the user to populate in before they can log out or move to another record that is those fields cannot be empty . Else if Status =" Open" field population not required

    Thanks in advance

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2013
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    IMHO The best place for validation code would be in the forms BeforeUpdate event. If the validation fails then the update of the data in the form can be cancelled and the user advised with a message box.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    sunnyday is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2014
    Posts
    45
    So this is the code that actually works on the form. Its going well for Status with "Closed". But it is also asking the required field for if the Status is "Open". That is it is asking the value for the 2nd condition with the msg "Please...close action"
    How do I solve this? Also is there a way if users can cancel the req field and log out or close the db in case?

    Also for a status being closed multiple fields are required before saving. Can you give me a syntax of the procedure or an example, such that if status is closed ,Field a reqd,Field B reqd,Field C reqd.
    If Open then req fields are not reqd.
    Can you please point out where is the mistake. I am not a VBA expert
    Private Sub Form_BeforeUpdate(Cancel As Integer)

    If Me.Status = "Closed" _
    And Len(Me.Field A & vbNullString) = 0 Then
    MsgBox "You need to fill in Field A"
    Cancel = True
    Me.Field A.SetFocus
    Exit Sub

    ElseIf Len(Me.Field B & vbNullString) = 0 Then
    MsgBox " Please enter link to close our action"
    Cancel = True
    Me.Field B.SetFocus
    Exit Sub
    Else
    Cancel = False
    End If
    End Sub

    Thanks in advance.

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Me.Status = "Closed" _
    And Len(Me.FieldA & vbNullString) = 0 Then
    MsgBox "You need to fill in Field A"
    Cancel = True
    Me.FieldA.SetFocus
    Exit Sub
    ElseIf Len(Me.Field B & vbNullString) = 0 Then
    MsgBox " Please enter link to close our action"
    Cancel = True
    Me.Field B.SetFocus
    Exit Sub
    Else
    Cancel = False
    End If

  4. #4
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    Before we can go into detail we need to know a couple of things.

    Is it FieldA or Field A (with the space)?

    If Field A (with the space) I assume it's also Field B and Field C, with spaces. Is that correct?

    Field names shouldn't have spaces in them, but if they do, you have to let the Access Gnomes know that the space is connecting two parts of the name, and in VBA code, you do that by enclosing the name in Square Brackets, such as:

    Me.[Field A].SetFocus

    And just to be clear...if Status = "Closed", Fields A, B and C are required, but not if Status = "Open"...is that correct?

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    sunnyday is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2014
    Posts
    45
    Umm FieldA, FieldB..are examples from my side. The actual fields have their own individual names..and they do not have spaces. My apologies, it should be FieldA,FieldB,FieldC
    Yes if the Status has been chosen as "Open" the fields are not required for user to input before exiting the record or the DB.

    Thanks.

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

Similar Threads

  1. Required fields in subform
    By Ray67 in forum Forms
    Replies: 3
    Last Post: 12-13-2012, 06:35 AM
  2. Conditional required field
    By jaffar2000 in forum Forms
    Replies: 10
    Last Post: 06-05-2012, 06:28 AM
  3. Conditionally Required Fields
    By HawkGuru in forum Programming
    Replies: 6
    Last Post: 10-03-2011, 05:47 AM
  4. Required Fields
    By scubagal in forum Forms
    Replies: 9
    Last Post: 09-29-2011, 07:26 AM
  5. Required Fields in a Form
    By Alaska1 in forum Access
    Replies: 3
    Last Post: 12-23-2010, 01:41 PM

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