By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,473 Members | 3,234 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,473 IT Pros & Developers. It's quick & easy.

2 fields, REQUIRED and NON-REQUIRED dates

P: 69
Hi there, am wondering if there is a way to have this code capture 2 dates. You see, I have several fields and some are REQUIRED fields and some are NON-REQUIRED fields. I am attempting to capture one date after the REQUIRED fields have been populated and totally different date for the NON-REQUIRED fields after they have been populated. It seems that I am getting one date being populated in both REQUIRED and NON-REQUIRED fields.

You see, sometimes it takes people several days to capture all the REQUIRED fields or all the NON-REQUIRED fields. Because of this, I need to be sure that I can capture the correct date for both fields.

Have any ideas on what I can do to correct this??? It would be greatly appreciated if you or someone can show me exactly what I need to do.

Thank you VERY much for your assistance.



Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2.  
  3.     Dim FLAG_2 As Integer
  4.     FLAG_2 = 0
  5.  
  6.  
  7.     If Not IsNull(Me.Communicated_Date) Then
  8.     FLAG_2 = FLAG_2 + 1
  9.     End If
  10.  
  11.     If Not IsNull(Me.DescribeInjury) Then
  12.     FLAG_2 = FLAG_2 + 1
  13.     End If
  14.  
  15.     If Not IsNull(Me.BODY_PART_ID) Then
  16.     FLAG_2 = FLAG_2 + 1
  17.     End If
  18.  
  19.     If Not IsNull(Me.F_MEASURES!DATE_COMPL) Then
  20.     FLAG_2 = FLAG_2 + 1
  21.     End If
  22.  
  23.     If Not IsNull(Me.F_SME_INVEST_TEAM!SME_NAME) Then
  24.     FLAG_2 = FLAG_2 + 1
  25.     End If
  26.  
  27.     If Not IsNull(Me.F_SME_INVEST_TEAM!SME_TITLE) Then
  28.     FLAG_2 = FLAG_2 + 1
  29.     End If
  30.  
  31.     If Not IsNull(Me.INV_TEAM_REVIEWED) Then
  32.     FLAG_2 = FLAG_2 + 1
  33.     End If
  34.  
  35.     If FLAG_2 = 7 Then
  36.     Me.DATE_NOT_REQ = Now()
  37.     End If
  38.  
  39.  
  40.  
  41.  
  42.     Dim FLAG_3 As Integer
  43.     FLAG_3 = 0
  44.  
  45.     If Not IsNull(Me.SRI) Then
  46.     FLAG_3 = FLAG_3 + 1
  47.     End If
  48.  
  49.     If Not IsNull(Me.LOC_CODE) Then
  50.     FLAG_3 = FLAG_3 + 1
  51.     End If
  52.  
  53.     If Not IsNull(Me.COST_CTR) Then
  54.     FLAG_3 = FLAG_3 + 1
  55.     End If
  56.  
  57.     If Not IsNull(Me.INCIDENT_DATE) Then
  58.     FLAG_3 = FLAG_3 + 1
  59.     End If
  60.  
  61.     If Not IsNull(Me.INCIDENT_TIME) Then
  62.     FLAG_3 = FLAG_3 + 1
  63.     End If
  64.  
  65.     If Not IsNull(Me.WEEKDAY_CODE) Then
  66.     FLAG_3 = FLAG_3 + 1
  67.     End If
  68.  
  69.     If Not IsNull(Me.SITE_LOC_CODE) Then
  70.     FLAG_3 = FLAG_3 + 1
  71.     End If
  72.  
  73.     If Not IsNull(Me.SITE_AREA_CODE) Then
  74.     FLAG_3 = FLAG_3 + 1
  75.     End If
  76.  
  77.     If Not IsNull(Me.DateReported) Then
  78.     FLAG_3 = FLAG_3 + 1
  79.     End If
  80.  
  81.     If Not IsNull(Me.TimeReported) Then
  82.     FLAG_3 = FLAG_3 + 1
  83.     End If
  84.  
  85.     If Not IsNull(Me.INCIDENT_TYPE_CODE) Then
  86.     FLAG_3 = FLAG_3 + 1
  87.     End If
  88.  
  89.     If Not IsNull(Me.IncidentDescription) Then
  90.     FLAG_3 = FLAG_3 + 1
  91.     End If
  92.  
  93.     If Not IsNull(Me.KNOWN_FACTS) Then
  94.     FLAG_3 = FLAG_3 + 1
  95.     End If
  96.  
  97.     If Not IsNull(Me.F_IMMED_FACTOR_B!IMM_FACTOR_ID) Then
  98.     FLAG_3 = FLAG_3 + 1
  99.     End If
  100.  
  101.  
  102.     If Not IsNull(Me.F_KEY_FACTORS!ID) Then
  103.     FLAG_3 = FLAG_3 + 1
  104.     End If
  105.  
  106.     If Not IsNull(Me.PatientHandlingFactor) Then
  107.     FLAG_3 = FLAG_3 + 1
  108.     End If
  109.  
  110.     If Not IsNull(Me.F_MEASURES!ACTION_ID) Then
  111.     FLAG_3 = FLAG_3 + 1
  112.     End If
  113.  
  114.     If Not IsNull(Me.F_MEASURES!DESCRIPTION) Then
  115.     FLAG_3 = FLAG_3 + 1
  116.     End If
  117.  
  118.     If Not IsNull(Me.F_MEASURES!RESPONSIBLE_PARTY) Then
  119.     FLAG_3 = FLAG_3 + 1
  120.     End If
  121.  
  122.     If Not IsNull(Me.F_MEASURES!TARGET_COMP_DATE) Then
  123.     FLAG_3 = FLAG_3 + 1
  124.     End If
  125.  
  126.     If Not IsNull(Me.F_MEASURES!RULE_PROC) Then
  127.     FLAG_3 = FLAG_3 + 1
  128.     End If
  129.  
  130.     If Not IsNull(Me.F_AUTHOR!AUTHOR_NAME) Then
  131.     FLAG_3 = FLAG_3 + 1
  132.     End If
  133.  
  134.     If Not IsNull(Me.F_AUTHOR!AUTHOR_TITLE) Then
  135.     FLAG_3 = FLAG_3 + 1
  136.     End If
  137.  
  138.     If Not IsNull(Me.EMP_INVEST_TEAM) Then
  139.     FLAG_3 = FLAG_3 + 1
  140.     End If
  141.  
  142.  
  143.     If IsNull(Me.DATE_REQUIRED) Then
  144.         If FLAG_3 = 24 Then
  145.         Me.DATE_REQUIRED = Now()
  146.         End If
  147.     End If
  148.  
  149.   End Sub
Feb 8 '08 #1
Share this Question
Share on Google+
1 Reply


missinglinq
Expert 2.5K+
P: 3,532
All of this code is run everytime the record is updated, i.e. data is entered/edited. With Line #1 of this code

Expand|Select|Wrap|Line Numbers
  1. If IsNull(Me.DATE_REQUIRED) Then
  2. If FLAG_3 = 24 Then
  3. Me.DATE_REQUIRED = Now()
  4. End If
  5. End If
  6.  
you ensure that DATE_REQUIRED is only populated if it hasn't been populated before.

The problem is that this code
Expand|Select|Wrap|Line Numbers
  1. If FLAG_2 = 7 Then
  2. Me.DATE_NOT_REQ = Now()
  3. End If
  4.  
doesn't do the same thing! If all the Not Required fields are filled in before all the Required Fields, the DATE_NOT_REQ will be re-populated each time a Required Field is populated.

Emend this piece of code to read

Expand|Select|Wrap|Line Numbers
  1. If IsNull(Me.DATE_NOT_REQ) Then
  2.   If FLAG_2 = 7 Then
  3.   Me.DATE_NOT_REQ = Now()
  4. End If
  5. End If
  6.  
and this date will only be populated once.


Linq ;0)>

P.S. When you use code tags such as [Code = VB] you have to make sure that there are no spaces between the square braces. It has to be Code=VB.
Feb 8 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.