Results 1 to 12 of 12
  1. #1
    Join Date
    Aug 2009
    Posts
    20

    Question Unanswered: Ok next question about making fields mandatory

    Hi

    Ok so the first puzzle was solved easily by Pootle Flump so cheers for that most appreciated!!!

    Second puzzle - I have a few mandatory fields in my database. There are some that are not but I want the tag to be changed to mandatory if one field is filled in.

    For example I have three fields

    A
    B
    C

    None of these are mandatory but if the user was to fill in field A then I want the mandatory flags on fields B and C to be set to YES so when they click the Add Record button they will be told "Fill in B and C".

    I found a thread on here that explain a way of checking every control that was an acTextBox but I wasn't able to manipulate that code to what I want to use it for.

    Thoughts on a postcard please .... ?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    A few options.
    Without all the details, it strictly sounds like you are storing two different types of entity in one table (one is a subset of another). Some people don't like this. You can create a one-to-one join to another table which contains columns A, B and C and are all mandatory. In that case, if there is a row in this table all three must be completed. If there is no row in this table then A, B and C dont apply to this row.

    Another option is to use a table level check constraint (I don't think Access calls it this). This says (in English) "Either A, B and C are all NULL OR A, B and C are all NOT NULL"
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Aug 2009
    Posts
    20
    Hey

    Ok just to give a few more details I have the following fields as mandatory

    Firstname
    Surname
    Tel Number
    Date Entered (Locked)
    Date Called
    Notes
    Disposition 1
    Disposition 1 Date
    Disposition 1 Time

    All those fields have to be filled regardless

    I then have

    Disposition 2
    Disposition 2 Date
    Disposition 2 Time

    These are not mandatory because the user doesn't necessarily fill this in on a new record. If they recall the record and enter data into Disposition 2 then they need to fill out Disposition 2 Date and Disposition 2 Time.

    I had a thought and a very common sense thought at that was to put something on the Add Record button that says "If Disposition 2 has a value then the other two fields need to be filled in"

    I am no programmer though I tried when I was a lot younger and got bored

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You've got something here that violates the spirit of first normal form - that column values be atomic. this is generally recognised to also mean no repetition of columns.

    In other words, there are many dispositions per person. I would decompose this table in to two - one for people and one for people's dispositions.
    Database Design linky: The Relational Data Model, Normalisation and effective Database Design
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Aug 2009
    Posts
    20
    Thanks for the linky I will take a look!

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You could use the Tag property to add one or several pseudo-properties to the controls.

    In this example, I suppose that each TextBox can have one or more of these pseudo-properties defined in the form: Prop_1;Prop_2;Prop_3 etc., and that a Parent property, if defined, will occupy the first position in the Tag string (i.e., Prop_1).

    The Parent property is the name of another control the contents of which determines if another control can be null or not.

    Code:
    Function Check_Parent_For_Not_Null()
    
        Dim ctl As Control
        Dim Pseudo_Properties As Variant
    
        For Each ctl In Me.Controls
            If ctl.ControlType = 109 Then   ' ControlType 109 = TextBox.
                If Len(ctl.tag) Then        ' User Defined pseudo-properties exist.
                    Pseudo_Properties = Split(ctl.tag)
                    If Not IsNull(Me.Controls(Pseudo_Properties(0)).Value) Then
                        If IsNull(ctl.Value) Then
                            MsgBox ctl.Name & " cannot be null.", vbInformation, "Warning"
                        End If
                    End If
                End If
            End If
        Next ctl
    
    End Function
    If there is only one possible pseudo-property, you can simplify the code and use the .tag property directly and don't need to split it in its possible parts.

    Have a nice day!

  7. #7
    Join Date
    Aug 2009
    Posts
    20
    Ok so trying to learn this as I go...if I create a new table like you said one table for Customers and one table for Dispositions - one assumes the form has to be an unrelated object to any of my tables

  8. #8
    Join Date
    Aug 2009
    Posts
    20
    Thanks Sindho

    Could the code you provided be adjusted to just look at a particular text box and if .Value = True then two further boxes changed to Required ????

    Thats all I want - I am not sure if that breaks the FIRST NORMAL FORM rules. Yes each customer has three call attempts made against them but being a n00b to databases I am in the dark and my DBA has gone off on holiday (the one time I need him)...

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Assuming that Text_1 is the parent (or master) and Text_2 and Text_3 the dependant (or slaves):
    Code:
    Function Check_Parent_For_Not_Null()
    
        If Me.Text_1.Value = True Then
            If IsNull(Me.Text_2.Value) Then
                MsgBox "Text_2 cannot be null.", vbInformation, "Warning"
            End If
            If IsNull(Me.Text_3.Value) Then
                MsgBox "Text_3 cannot be null.", vbInformation, "Warning"
            End If
    
    End Function
    Have a nice day!

  10. #10
    Join Date
    Aug 2009
    Posts
    20
    Thanks Sinndho - complete n00b question - where do I enter this code? On the form or on the AddRecord_Click ???

    EDIT
    Code:
    Function Check_Parent_For_Not_Null()
    
        If Me.AttDisp2.Value = True Then
            If IsNull(Me.Att2Date.Value) Then
                MsgBox "Attempt 2 Date cannot be null.", vbInformation, "Warning"
            End If
            If IsNull(Me.Att2Time.Value) Then
                MsgBox "Attempt 2 Time cannot be null", vbInformation, "Warning"
            End If
    
    End Function
    Thats how I have written it for my form but Im not sure how its meant to work. The AddRecord button has a Macro assigned to it to add the record but it appears I can't have that and VB at the same time...
    Last edited by BackForBreakfast; 08-19-09 at 13:05.

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I would put that code in the BeforUpdate event handler of the form:

    Code:
    Private Sub <Form_X>_BeforeUpdate(Cancel As Integer)
        Check_Parent_For_Not_Null
    End Sub
    Where <Form_X> is the name of your form.

    Have a nice day!

  12. #12
    Join Date
    Aug 2009
    Posts
    20
    Hey Guys

    Just to let you know here is how to do it in a single form single table way - I know its against the rules but I will work on fixing that when I build the beta version!

    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    If AttDisp2.Value <> "" Then
            If IsNull(Att2Date.Value) Then
                MsgBox "Attempt 2 Date cannot be null.", vbInformation, "Warning"
                Cancel = 1
            End If
            If IsNull(Att2Time.Value) Then
                MsgBox "Attempt 2 Time cannot be null", vbInformation, "Warning"
                Cancel = 1
            End If
     
    End If
    End Sub
    Thanks for all your inputs it is very much appreciated!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •