2 DATE fields

[KMEscherich]

Using ACCESS '97

Hi there. Am wondering what would be the best way for Access to recognize that each of the following fields have changed and drop in a date in the DATE_NOT_REQ control. It seems that after making an entry in each of the fields, the DATE_NOT_REQ control does not capture today's date. Have any suggestions for me?

Thank you VERY much for your assistance.



Private Sub Form_AfterUpdate()
Dim FLAG_2 As Integer

If Not IsNull(Me.Communicated_Date) Then
FLAG_2 = 1
End If

If Not IsNull(Me.DescribeInjury) Then
FLAG_2 = 1
End If

If Not IsNull(Me.BODY_PART_ID) Then
FLAG_2 = 1
End If

If Not IsNull(Me.F_MEASURES!DATE_COMPL) Then
FLAG_2 = 1
End If

If Not IsNull(Me.F_MEASURES!FINISHED) Then
FLAG_2 = 1
End If

If Not IsNull(Me.F_SME_INVEST_TEAM) Then
FLAG_2 = 1
End If

If Not IsNull(Me.F_SME_INVEST_TEAM) Then
FLAG_2 = 1
End If

If Not IsNull(Me.INV_TEAM_REVIEWED) Then
FLAG_2 = 1
End If

If FLAG_2 = 8 Then
Me.DATE_NOT_REQ = Now()
Me.DATE_NOT_REQ.Locked = True
End If
End Sub

[missinglinq]

Okay, first off, you're not asking Access to recognize if the fields have changed, but rather if they have values. That's OK! You just weren't expalining your problem very well.

Next, you're trying to use a "counter" that you call FLAG_2. What you apparently want to do is to add the value of 1 to FLAG_2 each time a textbox is found to contain data. But that's not what doing! Each time a control is found to hold data, you're resetting the value of FLAG_2 to 1!

FLAG_2 = 1

So what you need to do is assign the value of zero to FLAG_2 at the beginning of the sub.

Dim FLAG_2 As Integer
FLAG_2 = 0

Next, instead of assigning the value of 1 to FLAG_2 each time you find a field contains data, you need to add 1 to the value of FLAG_2!

So, instead of

If Not IsNull(Me.Communicated_Date) Then
FLAG_2 = 1
End If

you need

If Not IsNull(Me.Communicated_Date) Then
FLAG_2 = FLAG_2 + 1
End If

And so on for each of the eight fields!

You also need to move the all this code to the Form_BeforeUpdate sub instead of the AfterUpdate event.

The concept is known as an "incrementing counter" and it's a great hack to keep on file.

Good Luck!

Linq ;0)>

[KMEscherich]

Okay, first off, you're not asking Access to recognize if the fields have changed, but rather if they have values. That's OK! You just weren't expalining your problem very well.

Next, you're trying to use a "counter" that you call FLAG_2. What you apparently want to do is to add the value of 1 to FLAG_2 each time a textbox is found to contain data. But that's not what doing! Each time a control is found to hold data, you're resetting the value of FLAG_2 to 1!

FLAG_2 = 1

So what you need to do is assign the value of zero to FLAG_2 at the beginning of the sub.

Dim FLAG_2 As Integer
FLAG_2 = 0

Next, instead of assigning the value of 1 to FLAG_2 each time you find a field contains data, you need to add 1 to the value of FLAG_2!

So, instead of

If Not IsNull(Me.Communicated_Date) Then
FLAG_2 = 1
End If

you need

If Not IsNull(Me.Communicated_Date) Then
FLAG_2 = FLAG_2 + 1
End If

And so on for each of the eight fields!

You also need to move the all this code to the Form_BeforeUpdate sub instead of the AfterUpdate event.

The concept is known as an "incrementing counter" and it's a great hack to keep on file.

Good Luck!

Linq ;0)>

Will try that out. Thank you VERY MUCH for your assistance.

[missinglinq]

That's why we're all here!

Linq ;0)>

[KMEscherich]

That's why we're all here!

Linq ;0)>

Hi there, I did what you suggested and it works GREAT. I have tried this using 2 DATE fields:
DATE_NOT_REQ
DATE_REQUIRED

and it seems that when I update the REQUIRED fields, I do indeed get a date in the DATE_REQUIRED field. But for some reason, when I go back into the same record and attempt to update the non-required fields and capture the DATE_NOT_REQ date, it updates both the DATE_NOT_REQ and DATE_REQUIRED fields with the same date and time. Is there a way to prevent this from happening? You see, I need to capture the date when the REQUIRED fields were completely filled in and then later capture another date for the NON-REQUIRED fields.


Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim FLAG_2 As Integer
FLAG_2 = 0

If Not IsNull(Me.Communicated_Date) Then
FLAG_2 = FLAG_2 + 1
End If

If Not IsNull(Me.DescribeInjury) Then
FLAG_2 = FLAG_2 + 1
End If

If Not IsNull(Me.BODY_PART_ID) Then
FLAG_2 = FLAG_2 + 1
End If

If FLAG_2 = 3 Then
Me.DATE_NOT_REQ = Now()
End If


Dim FLAG_3 As Integer
FLAG_3 = 0

If Not IsNull(Me.SRI) Then
FLAG_3 = FLAG_3 + 1
End If

If Not IsNull(Me.LOC_CODE) Then
FLAG_3 = FLAG_3 + 1
End If

If Not IsNull(Me.COST_CTR) Then
FLAG_3 = FLAG_3 + 1
End If

If FLAG_3 = 3 Then
Me.DATE_REQUIRED = Now()
End If

End Sub



Thank you VERY MUCH for your assistance.

[missinglinq]

The Form_BeforeUpdate kicks in anytime you change a value in any field. When you were just checking one set of fields this worked fine, because once you filled in those fields, the appropriate date field was set to Now() and that was that. But now that you've added the checking on a second set of fields you have a problem. When you fill in the second set of fields, the Form_BeforeUpdate event happens again, the second set of fields is checked and the date for them entered, if they're all filled in, but then the check is again run on the first set of fields and its date is also changed!

To get around this, before assigning Now() to your date fields, you need to check to see whether they already hold a date. If they're empty, your code will assign Now() to them; if they already contain a date, your code will skip over it and leave the date already entered in place.

So replace

If FLAG_2 = 3 Then
 Me.DATE_NOT_REQ = Now()
End If 

with this

If IsNull(Me.DATE_NOT_REQ) Then
 If FLAG_2 = 3 Then
  Me.DATE_NOT_REQ = Now()
 End If
End If

And replace

If FLAG_3 = 3 Then
 Me.DATE_REQUIRED = Now()
End If

with this

If IsNull(Me.DATE_REQUIRED) Then
 If FLAG_3 = 3 Then
  Me.DATE_REQUIRED = Now()
 End If
End If

Linq ;0)>

[KMEscherich]

Using Access '97

Hi there. I have two controls that are tied to a table:
DATE_REQUIRED
DATE_NOT_REQ

I am attempting to have the DATE_REQUIRED be populated with a date when all the required controls have been populated and have the DATE_NOT_REQ populated when all the non-required controls have been populated. For some reason, when I fill in all the controls for the REQUIRED fields, I get a date, then I enter all the controls that are associated with the non-required fields and I get another date. For some reason, upon completing the non-required fields, the DATE_REQUIRED control changes to the new date so both the DATE_REQUIRED and the DATE_NOT_REQ appear with the same date. Have any suggestions on how I can avoid this? Because, I DO need to capture different dates.

Thank you for your assistance.

K_Escherich

[Rabbit]

To figure out why it is doing this you have to tell us how you are doing it. Can you post the code and logic you are using to accomplish what you want to do?

[missinglinq]

I've merged these threads together. I explained to you why this was happening in #6 above! Did you modify your code as I instructed you to?

Linq

[KMEscherich]

I've merged these threads together. I explained to you why this was happening in #6 above! Did you modify your code as I instructed you to?

Linq

I want to thank you VERY, VERY MUCH for your assistance.

[missinglinq]

Glad we could help! Had you lost track of the previous thread? If you haven't done so, from the Top Right side of the screen Click on Control Panel then on the Left of the screen choose Edit Options then Default Thread Subscription Notification and set this to Instant Email Notification and you'll get an email anytime someone responds to your posts!

Linq ;0)>