Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington, Cheshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The original version of a staff DB was conceived as a means of producing formal contracts of employment. As all fields were needed to produce this contract, the 'Required' property was set in the contract field definitions. This ensured that, once the user started to input data for an employee, they could not get off the form without supplying data for all the fields.

    The user now wishes to extend this DB to include agency staff. These do not have a formal contract with the user, their terms and conditions being set by the agency. Thus, the contract fields cannot now have the Required property set as they are not relevant for agency staff.
    The in-house staff must still have all the contract fields completed but the agency staff must not. The in-house/agency staff can be distinguished via a new field in the DB.

    Q1 Is it possible to set the required property using VB?
    Q2 If not, is there a smart way of checking whether all the fields have had data input for in-house staff/been left blank for agency staff or does it require code to check each field individually?
    Thanks
    Silverback
    Silverback

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Although it is possible to set the Required property of a field using VBA (through DAO), you can't do this dynamically, because the structure of the table that the form is bound to will be locked while the form is open.

    If you want to check ALL fields, you can use a loop in the Before Update event of the form

    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
      Dim ctl As Control
      ' Only test for in-house staff
      If Me.StaffType = 1 Then ' in-house
    	' Loop through all controls
    	For Each ctl In Me.Controls
    	  ' Check control type
    	  Select Case ctl.ControlType
    		' Look at text boxes and combo boxes
    		Case acTextBox, acComboBox
    		  ' Has the control been left blank
    		  If IsNull(ctl) Then
    			' Select the control
    			ctl.SetFocus
    			' Display a message
    			MsgBox "Please enter a value.", vbExclamation
    			' Cancel updating the record
    			Cancel = True
    			' Get out
    			Exit Sub
    		  End If
    		Case Else
    		  ' Do nothing
    	  End Select
    	Next ctl
      End If
    End Sub
    If you want to check only specific controls, you could enumerate the names:

    Code:
    ...
    	  ' Check control name
    	  Select Case ctl.Name
    		' List controls to check
    		Case "txtContract", "txtDuration", "txtPayScale"
    ...
    Substitute the relevant names and expand or contract the list as needed.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington, Cheshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Many thanks for this, Hans.
    My wife and I are due to meet the user for a design meeting this coming week, so won't be progressing development until afterwards.
    Will post back when this solution implemented (or when we need further help!)

    Silverback
    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
  •