Form Update Question

[bacarat]

Hi,
I have a few fileds in my database that update themselves "On Current". It is a list of expired dates. The problem is that this information will not update unless I navigate through the record and refresh it in the form. Is there any way for this information to autmaticaly update itself wthout me having to navigate through all of my records in my form to update the information? Thanks Alot!

Private Sub Form_Current()
   If Me![Continous Entry] = "-1" Then Me![ExpireDate] = DateAdd("d", 365, Me![Approval Date])
   If Me![Daily Entry] = "-1" Then Me![ExpireDate] = DateAdd("yyyy", 0, Me![To])
   If Me![Daily Entry] = "0" And Me![Continous Entry] = "0" Then Me![ExpireDate] = Me![ExpireDate]   ' This doesn't make sense setting Expire date to expire date
    
   If Me.ExpireDate > Now Then
       Me.Status = "Cleared"
    
    Else: Me.Status = "Expired"
    
   End If

End Sub

 

[RuralGuy]

Are the [Continuous Entry] and [Daily Entry] controls on your form bound to YES/NO fields? If so then setting them to a string value will not work. Can you do what you want to accomplish with a query?

 

[bacarat]

They are actualy set to number. I may be able to do this with a query just not sure how to go about it. I do have a data access page that displays my query of "Cleared" entries..

 

[RuralGuy]

Surrounding -1 with quotes ("-1") makes it a string. Access has several kinds of "number" fields. The default is a Long Integer. It is a confusing programming practice to mix single line and milti-line coding. How often do you need to adjust for "cleared" entries?

 

[bacarat]

Need to update once a day and when a new record is entered.

 

[bacarat]

A query would be great for this. Im just not sure how I would do this VB code in a query. Is it possible? Thanks.

 

[bacarat]

Bump. Still have not figured this out.

 

[RuralGuy]

Here's your code done with multi-line If...EndIf statements and some changes. Maybe you could add some additional comments to explain the "why" behind each test. Even *you* have comments that state it does not make sense and I agree.

Private Sub Form_Current()
   If Me![Continous Entry] = True Then
      '-- Add a year to the Approval Date
      Me![ExpireDate] = DateAdd("yyyy", 1, Me![Approval Date])
'      Me![ExpireDate] = DateAdd("d", 365, Me![Approval Date]) '<-- Previous code
      End If
   If Me![Daily Entry] = True Then
      '-- Set [Expiredate] = to the [To] date???
      Me![ExpireDate] = Me![To]
'      Me![ExpireDate] = DateAdd("yyyy", 0, Me![To]) '<-- The previous code!!
   End If
   If Me![Daily Entry] = False And Me![Continous Entry] = False Then
      Me![ExpireDate] = Me![ExpireDate]
      ' This doesn't make sense setting Expire date to expire date
   End If
   If Me.ExpireDate > Now Then
      Me.Status = "Cleared"
   Else
      Me.Status = "Expired"
   End If

End Sub

 

[bacarat]

Thanks Alot!

New code works great! and is much more ogranized. However I still have to browse to the record in order to update the "Expired or "Cleared" status. Your suggestion of a query would work well for me. I think i could actualy do away with the "Expired" and "Cleared" status if I could create a query to show only records that have not expired based on the "Expire Date" rather than the "Expired" or "Cleared" status. The only problem is I cannot get the "Expire Date" to generate without having to refresh the record in the form view, Basically the same problem im having with 'Expired" and "Cleared" status. Any help with this would be great! Here is a bit of an explination on my previous code in comments. Thanks.

Private Sub Form_Current()
   If Me![Continous Entry] = True Then
      '-- Add a year to the Approval Date
      Me![ExpireDate] = DateAdd("yyyy", 1, Me![Approval Date])
'      Me![ExpireDate] = DateAdd("d", 365, Me![Approval Date]) '<-- Did this in case some months had 28 days etc...
      End If
   If Me![Daily Entry] = True Then
      '-- Set [Expiredate] = to the [To] date???
      Me![ExpireDate] = Me![To]
'      Me![ExpireDate] = DateAdd("yyyy", 0, Me![To]) '<-- The "To" date is the date it expires if "daily"=true
   End If
   If Me![Daily Entry] = False And Me![Continous Entry] = False Then
      Me![ExpireDate] = Me![ExpireDate]
      ' This doesn't make sense setting Expire date to expire date - I did this beacuse I had alot of older entries without continuous and daily as a trigger so i could keep the default values.
   End If
   If Me.ExpireDate > Now Then
      Me.Status = "Cleared"
   Else
      Me.Status = "Expired"
   End If

End Sub

The previous code was a bit confusing for me and I think I made some mistakes and was unorganized from the beggining. Hence why things did not make sense etc.

 

[RuralGuy]

What is the value of [ExpireDate] if [Continous Entry] and [Daily Entry] are false without this code you posted? When do [Continous Entry] and [Daily Entry] get set to True (-1)? Couldn't you set the [ExpireDate] when you set these fields true?

 

[bacarat]

[Continious Entry] and [Daily Entry] Will never both be false. One will always be true "-1". What I would like to do is essentialy what you have already said.

If [Continous Entry]="True" Then [Expire Date]="1 year from [Approval Date]
Else If [Daily Entry]="True" Then [Expire Date]="[To]

Above is basically all I need to accomplish in a query, just not sure how to correctly write it out. Thanks.

 

[RuralGuy]

We can create a one time update query that sets the [Expire Date] but I'm trying to figure out why it is not already set when you set either [Continious Entry] or [Daily Entry] true! Couldn't you set the [ExpireDate] when you set these fields true?

 

[RuralGuy]

I have another question. If [Continious Entry] and [Daily Entry] will never both be false then would one field work for the logic? Do you really need both fields?

 

[bacarat]

The reason I have both is beacuse if [Continous Entry] is true then [Expire Date] is 1 year from [Approval Date] But if [Daily Entry] is true then [Expire Date] is Equal to the date entered into the [To] field. One or the other will always be checked. The [Expire Date] will not generate in my form unless I click away from the record and then go back to it in the form view. It does not update as soon as I check either value to true. Thanks for your patience and help with this! I realy apriciate it.

 

[RuralGuy]

As I asked before, could the field be thought of as the Continuous or Daily Entry field and eliminate one of the fields?

 

[bacarat]

I suppose it could yes. Lets give it a try.

 

[RuralGuy]

The [Expire Date] will not generate in my form unless I click away from the record and then go back to it in the form view. It does not update as soon as I check either value to true.

What code are you using to complete the [Expire Date]?

 

[bacarat]

Private Sub Form_Current()
   If Me![Continous Entry] = True Then
      Me![ExpireDate] = DateAdd("yyyy", 1, Me![Approval Date])
      End If
   If Me![Daily Entry] = True Then
      Me![ExpireDate] = Me![To]
   End If

Still using code in VB, Im not sure where to start for coding this in a query.

 

[RuralGuy]

LOL! I thought you had some code that ran when you set [Continous Entry] or [Daily Entry] true. This code will only run if you switch records or enter the form. How do you set [Continous Entry] or [Daily Entry] true?

 

[bacarat]

Sorry about that, The code running only when I enter the form is my whole problem. Guess I was a bit confused, Both are set using a "checkbox". "-1" "1" values. Field is set to number. Thanks Again!