Best place to put this code?

[Tofias1]

Hello to everyone who takes a look at my question and thanks in advance.

I have written this code for a date field. Once the value of one field changes I would like the value of the date field to default to a set date.

So once the field 'File_Description' = "Perm File". This will trigger the field 'ExpectedArchiveDate' to = 12/31/2099. The only place that I have found where it works is in the on click event of ArchiveDate.

'Here is the code

Private Sub ArchiveDate_Click()
Dim Holdvalue As String
Dim FutureDate As Date

Holdvalue = "Perm File"
FutureDate = "12/31/2099"

If Me.File_Description.Value = Holdvalue Then
ExpectedArchiveDate = FutureDate
Else
ExpectedArchiveDate = ""
End If

End Sub

Thanks,
Tofias1

 

[SBendBuckeye]

Put the code in the After Update event for File_Description. That way the code will automatically be updated anytime File_Descripton is changed.

Good Luck!

 

[Tofias1]

SBendBuckeye,

Thanks for the reply. I have tried to do that but it does not seem to work. Here is another question for you as to why this may not be working. I probably should have said this in my 1'st post. The File_Description field is set to a predefined tbl. With a set of desciptions within. Could this be the problem why the After Update is not working?

Any info would be great and thanks again.

Tofias1

 

[SBendBuckeye]

If your date control is really a date type, then it needs to be #12/31/2002#. Pound signs # are used to delimit date constants just like single quotes ' are used to delimit string constants.

As per above...

Private Sub File_Description_AfterUpdate()

If File_Description = "Perm File" Then
your code
End If

End Sub

If I have misunderstood, please post again and accept my apologies.

Good Luck!

 

[Tofias1]

SBendBuckeye,

I have tried it again and to my dismay no luck. The code only works on the on click event. I also tried on the Forms After Update but that did not work either causing a lot of problems. I thought at first it would work on the Form_Current too but again no luck.

Should I go about this a different way?

Thanks for your time and your help is greatly appreciated.

Tofias1

 

[SBendBuckeye]

Can you post the code you are using in the After Update event code? Thanks!

 

[Tofias1]

SBendBuckeye,

Thanks again, Here it is.

Private Sub File_Description_AfterUpdate()

Dim FutureDate As Date

FutureDate = #12/31/2099#

If Me.File_Description.Value = "Perm File" Then
ExpectedArchiveDate = FutureDate
Else
ExpectedArchiveDate = ""
End If
End Sub

Tofias1

 

[SBendBuckeye]

Have you tried this in debug single step mode to ensure the code is being executed. If the field already contains "Perm File" then the After Update event will not fire. You might also try the On Lost Focus or On Exit events. Your code should work, I copied it into Access97 and it ran OK. You might also want to verify that your date field is enabled and unlocked although I would think that would trigger an error if that was the case.

Good LucK!

 

[Tofias1]

SBendBuckeye,

Hey thanks for all the help. I have tried testing the code by changing the field values. That did not work either but I will try the other events to see if they work. So I will keep you posted (no pun intended)

Tofias1

 

[Tofias1]

SBendBuckeye,

Hey, I tried calling the ArchiveDate() from the File_Description Lost Focus and wouldn't you know. It worked. Although I thought there would be an easier way but this works.

Private Sub ArchiveDate_Click()
Dim Holdvalue As String
Dim FutureDate As Date

Holdvalue = "Perm File"
FutureDate = #12/31/2099#

If Me.File_Description.Value = Holdvalue Then
ExpectedArchiveDate = FutureDate
DoCmd.Requery "ArchiveDate"
ElseIf Me.File_Description.Value = "Test" Then
ExpectedArchiveDate = ""
DoCmd.Requery "ArchiveDate"
Else
'do nothing for now
End If

End Sub


Private Sub File_Description_LostFocus()
Dim Holdvalue As String
Dim TestData As String

Holdvalue = "Perm File"
TestData = "Test"

If File_Description = Holdvalue Then

Call ArchiveDate_Click

ElseIf File_Description = TestData Then

Call ArchiveDate_Click

Else
'do nothing for now.

End If

End Sub

Tofias1