473,287 Members | 2,682 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

2 fields, REQUIRED and NON-REQUIRED dates

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
1 1530
missinglinq
3,532 Expert 2GB
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

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

Similar topics

3
by: jason | last post by:
If one has a series of price reduction fields (3) and corresponding date fields (3) in each record.....how would one check the row to see which of these fields contain non-null or non-empty...
2
by: | last post by:
does anyone know what is the maximum number of fields that can be updated at once in access 2000 i am updating approx. 150 at once and keep getting this error: Microsoft JET Database Engine error...
2
by: shahla.saeed | last post by:
wat i want is to insert the calculated values(simple addition or subtraction) in fields of my table by using expression builders in my forms..i have made that feild in the table but still it...
6
by: nahald | last post by:
hello, I have a problem to a database (Access) and I reference its fields before in Vb 6, I am do this with: Dim RS As Recordset Nombre_BD = "SERVICIOS.MDB" Set ConexionBD =...
6
by: Ian Davies | last post by:
Hi Can anyone tell me why the following code will NOT work The error message should be displayed when there is something in both fields if ((isset($_POST) && $_POST !=='') && (isset($_POST)...
1
by: jpatchak | last post by:
Hi Guys, Sorry if this is a really stupid question. I am trying to upsize my Access database to SQL server. When I used the Access upsizing wizard, some of my queries didn't get upsized so I am...
5
by: phpCodeHead | last post by:
I am needing to determine how to go about validating that a field in my form contains only a positive integer. I know that this is fairly simple if the form contains only one element to be...
6
by: bmoore | last post by:
Greetings, This seems like it should be a simple problem, but I'm new to Access and my digging hasn't yielded a solution. I would like to create a table in a non-protected database that contains...
2
by: DiAvOl | last post by:
Hello everyone, I read here and there that bit-field usage should be avoided because it's not portable. My question is why it's not portable? For example let's say I have a struct in my code...
1
by: jej1216 | last post by:
Background: I have an PHP form that inserts data into a MySQL DB, and it works. Severity of Incident: <select name="severity" size="1"> <option value="">Select a Severity Option</option>...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.