Code not working

[magster06]

Hello all,

I have a table "LightDuty" with 2 fields: [L/D Start Date] and [1yr/+]

What I am trying to do is when the form loads, I want the [L/D Start Date] field date to be checked to see if it is >= to one year.

If it is >= one year then I would like to have an asterick placed in the [1yr/+] field.

This is what I have so far:

Dim db as DAO.Database
Dim rs as DAO.Recordset
 
With rs
If IsNull([1yr/+]) = True Then
   If [L/D Start Date] = DateDiff('yyyy',[L/D Start Date], Now()) >=1 Then
       Do Until .EOF
          .Edit
          .Fields([1yr/+]) = "*"
          .Update
          .Movenext
       Loop
  End if
Else
  Exit Sub
End if
End With
 
rs.Close
Set db = Nothing
Set rs = Nothing

Unfortunately nothing is happening.

Oh, I have this code in my Form_Load Event

 

[pr2-eugin]

Hi magster06, before actually answering your question, there is a few things that needs to be picked up.

* Your naming conventions are a bit off. It is not advisable to use special characters in your field names that includes special characters / or + they will in some point kick back.

* Why are you planning to have this in your table? For me it feels like an unwanted process of updating with a *, could you not use a query instead? But it is your system, so I am unsure of this requirement, having said that.. Are you sure you wish to have this process of checking in Form Load? Form Load, Form Open is triggered only once in the Form's life time (i.e. from the point it is opened to closed). Form Current is triggered every time to move to the previous/next/first/last records within the same form.

* If that question is answered then you might want to rethink the use of recordset.

* You have not SET any record set for it to MoveNext or to Update..

* If I am right, the rs.Fields() method needs its parameter to be included inside double quotes like.. rs.Fields("[fieldName]")

 

[magster06]

pr2-eugin,

Thanks so much for the reply.

* Your naming conventions are a bit off. It is not advisable to use special characters in your field names that includes special characters / or + they will in some point kick back.

Yes, I was lazy and tired and could not think of anything else to name the field at the time. I was planning on changing the name (lol, I knew someone was going to get me on that, sorry).

* Why are you planning to have this in your table? For me it feels like an unwanted process of updating with a *, could you not use a query instead? But it is your system, so I am unsure of this requirement, having said that.. Are you sure you wish to have this process of checking in Form Load? Form Load, Form Open is triggered only once in the Form's life time (i.e. from the point it is opened to closed). Form Current is triggered every time to move to the previous/next/first/last records within the same form.

The user wanted a way to see, at a glance, if a person had been on light duty for a year or more without doing a query. Yes, I had a query already setup for the user.

I decided to place the code in the form load due to the user opening and closing this database so often, but I will change this.

* You have not SET any record set for it to MoveNext or to Update..

oops! I hand typed this code and left it out of the question. This is what I have:

Set db = CurrentDb
Set rs = db.OpenRecordset("LightDuty")

 

[pr2-eugin]

The user wanted a way to see, at a glance, if a person had been on light duty for a year or more without doing a query. Yes, I had a query already setup for the user.

So fill me in here.. (a) Does it mean the user has absolute control over tables? or (b) does it have a Subform/Form that is in datasheet view??

If your answer to (a) is Yes, then you should rethink, if they have access to tables directly, then there is no point of having forms to edit/update data..
If your answer to (b) is yes, then why not have its record source to the query you have built??

oops! I hand typed this code and left it out of the question. This is what I have:

Set db = CurrentDb
Set rs = db.OpenRecordset("LightDuty")

If you change the source of the Form then there will no need to give them the table as the record source.

 

[magster06]

So fill me in here.. (a) Does it mean the user has absolute control over tables?

I am not sure on this question. The user can add, edit, and delete records from a form, but he cannot delete fields or change datatype; so I guess that would be no, lol (sorry I am a beginner Access person).

does it have a Subform/Form that is in datasheet view??

No.

I just talked to the user and he said that he does not want to generate two reports (one for all personnel on light duty and one for personnel on light duty for a year or more). The user originally wanted a textbox with an asterick in it, but I told him that the start date was right in front of him and he could easily tell if a person was on light duty for a year or more.

The user wants one report (all personnel on light duty) and an asterick next to the personnel who have been on light duty for a year or more.

I know I am making this more complicated, sorry

 

[magster06]

So, I am thinking that I probably should create a new textbox on the report with the control source being the criteria:

IIF([L_D Start Date]= "DateDiff('yyyy', [L_D Start Date], Now())>=1","*",)

Does this seem to be the way to go?


Ok, I tried this:

=IIF(L_D Start Date] = DateDiff('yyyy',[L_D Start Date, Now())>=1,"Yes")

The code above does not enter the "yes" into report. I tried wrapping the criteria differently, but still nothing. If I place the "else" part of the IIF (with "no") then it will populate the report with the "no" (as it should). This leads me to believe something is wrong with this part:

DateDiff('yyyy',[L_D Start Date],Now())>=1

or how it is wrapped maybe?

Any ideas?

If you change the source of the Form then there will no need to give them the table as the record source.

Not sure what you mean? Are you saying that I should change the source to a sql statement?

 

[magster06]

Hi all,

I am still looking for some help if anyone has the time.

I have since tried the following:

=IIF(L_D Start Date] = DateDiff('yyyy',[L_D Start Date, Now())>=1,"Yes")

Nothing happens, so I tried this:

=IIf(Int([L_D Start Date])=DateDiff('yyyy',[L_D Start Date],Date())>=1,"yes","")

Get error code with this one, arrgh!

 

[pr2-eugin]

Hello magster06, Sorry I was busy with my work yesterday.. Well your question sounded as though you wanted to show the table with a star in one of the fields which I said was a bad suggestion, but I lost track when you said you display that in a form.. Everything is a bit confusing here..

SO let me try again explaining you, what can be done.. you say that you have a Query that will list all the data that is a year old.. So what is the need for this form you are having? Why not just run the query for the user to see the list??

For better clarity, could you try explaining again what you are trying OR even better if you could upload a stripped down version of your DB to be looked into..

 

[magster06]

I am sorry about all of the confusion.

I did created two queries for the user:

Rpt_All Personnel on LightDuty
Rpt_Personnel One Year or More

The user does not want to print out 2 different forms for his supervisors; he just wants one that will have an asterick next the to personnel who have been on light duty for a year or more.

Originally, the user wanted an asterick to show on his form when he would view personnel, but I finally told him that the start date was right in front of him and he could easily tell if the person was at a year or more.

So, the user finally agreed with this, but now he only wants one report, arrrgh.

So, I thought that I could use the one report (Rpt_All Personnel on LightDuty) and create another textbox with the control source being an IIF statement.

My project is too large. The last time I tried to strip it down to where it would fit, it would not function.

 

[pr2-eugin]

Okay now I get the idea.. You have a Report, which is ontaining its results based on a Query something like..

SELECT * FROM lightDuty;

So, what you can do is, create another field in the Query as,

SELECT *, IIf(DateDiff('yyyy',[L_D Start Date], Date())>=1,"YES","NO") AS oneYearPlus FROM lightDuty;

Then recreate your Report having all fields..

For your form, even though you have convinced them to calculate it manually, you can still show them if they have been over a year.. All you need to do is create an UNBOUND TextBox with its Control Source as

= IIf(DateDiff('yyyy',[L_D Start Date], Date())>=1,"YES","NO")

You can also have a * instead of YES and nothing for No.. something like..

= IIf(DateDiff('yyyy',[L_D Start Date], Date())>=1,"*","")

 

[magster06]

Works like a charm pr2-eugin.

Thanks for the help!!

 

[pr2-eugin]

No problem.. Glad it is now sorted.. Good luck..