Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington, Cheshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Mandatory Fields (Access2000)

    I have created a form with subform. The main form displays employee details and the subform displays salary information in datasheet mode. Because I want the salary information to be in date order, I have created a simple query which retrieves the fields from the salary table but sorted in ascending date order; the subform is based on this query. There can be more than one salary per employee - the DB is keeping a track of salary history.
    There are two underlying tables - EmployeeDetails (which are displayed on the main form) and Salary (the subform displays all salaries for each employee and the date each particular salary became effective)
    The form also allows new employees to be entered.
    All fields in the two tables have the Required = Yes attribute set.
    The Problem :
    When entering new employee details, if any field on the main form is missed out, an error is generated saying the field is required and a value must be supplied. This is the behaviour I want. However, if salary information is omitted, the record is written successfully ie there is no error caused by lack of salary information being provided.

    Why isn't Access reporting that this field is mandatory and how can I force it to generate this error, please. (Salary table has Default salary set to 0; I tried it with no default but the form behaviour is the same.)
    TIA
    SIlverback
    Silverback

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Mandatory Fields (Access2000)

    Check the table to see if you have a default value of zero set for that field. If so, remove the default value. Also make sure that the Allow Zero Length property is set to No.
    Charlotte

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington, Cheshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mandatory Fields (Access2000)

    Charlotte
    Thanks for the reply, but no luck I'm afraid.
    1. I removed the default value of 0 from the salary field, but it made no difference to the behaviour of the form/subform/
    2. There is no Allow Zero length field for either of the two salary fields. This attribute is only available on Text, Memo or Hyperlink fields and salary is a Currency field, and date is a Date/Time field.
    Silverback
    Silverback

  4. #4
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mandatory Fields (Access2000)

    If I understand your setup correctly, Access will create the EmployeeDetails record quite happily because all of its fields have been completed. The Salary record would be in a separate table, thus is not involved at the stage of creating a new Employee. If you set Referential Integrity, then Access will not allow you to create a 'sub record' (Salary in this case) without a corresponding 'main record' EmployeeDetail - so that does not solve your problem.

    I suspect that you will need to create a code solution to eliminate any Employee who has been entered without a Salary or alternatively code to force the creation of a 'sub record' as soon as the Employee is created.
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington, Cheshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mandatory Fields (Access2000)

    Thank you for your helpful posting.
    A couple of questions, please.
    1. I am trying to write code to deal with the situation as you suggested. I propose to use the After Update event at form level (logic : in case they enter more than one employee at a time, and I think it actions before the On Close event which will occur if they click the x at the top right hand corner). Is this a correct assumption?

    2. The form names are EmployeeDetail and Salary Subform - note the space in the second name. I've referenced the subform, using the following style :
    [Forms]![Salary Subform]![Salary} when I check if the field is empty but I get an error - Access can't find the form 'Salary Subform' referred to in a macro expression or Visual Basic code; what's wrong with that reference, please?
    Thanks
    Silverback
    Silverback

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Mandatory Fields (Access2000)

    2. Subforms are not part of the Forms collection, only main forms. You must refer to a subform through its main form:

    [Forms]![MainformName]![SubformName]

    where SubformName is the name the subform has as a control on the main form. This is not necessarily the same as the name of the subform in the database window. To find the name of the subform as a control, open the main form in design view, and click once on the subform (not twice, for that will select something in the subform). The name will be displayed in the caption of the Properties window, and in the Name property in the Other tab.

    See Forms: Refer to Form and Subform properties and controls on the Access web for more details.

  7. #7
    3 Star Lounger
    Join Date
    Jun 2002
    Location
    Nottingham, Nottinghamshire, United Kingdom
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mandatory Fields (Access2000)

    Your problem would be solved by preventing users from adding a new employee through the form you are using. Instead, set up an unbound form with all the fields you need to set up both a new employee and the first salary record. As the fields on this form are unbound you can manipulate them in ways that are not so easy on a bound form.

    When the user clicks a 'Save' button on the AddEmployee form, your code can validate the data, ensuring that all mandatory fields are completed, and then write both new records.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Mandatory Fields (Access2000)

    The AfterUpdate event of the form won't work. The moment the user enters the subform to enter a salary, the main form record is saved, so you'd end up in a vicious circle.

    I would do it the way <!profile=DollyP>DollyP<!/profile> described, using an unbound form to enter new employees together with the initial salary.

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington, Cheshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mandatory Fields (Access2000)

    I would like to thank you and all the other loungers who have helped with this posting.
    I have learnt a lot about forms, sub forms, saving records etc., but I recognise that the proposed solution is beyond my Access and Visual Basic capabilities at the moment.

    I have adopted a simpler/cruder solution :
    - The query used to provide the data for the Mail Merge which produces employee contracts has been amended to return all employee records, regardless of whether or not there are associated salary records.
    - The Mail Merge document has been amended to include an error clause if current salary is zero
    - I
    Silverback

Posting Permissions

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