Problems using functions in forms - data won't populate to table

[teresamichele]

I'm having a bit of an issue with an inherited database and I'm hoping someone can help.

I have a table called "Project Entry" and a form called "FormEntry" that were both created by the database creator. At the request of the people the database is for, I changed the "Priority" field from a user-entered field to one that, with use of the IIf function, calculates the code based on the difference between the current date and the due date. I did this by creating a hidden field that uses the DateDiff function, and then the Priority field uses the IIf function based on the result from the hidden field.

That all works fine.

The problem I am having is that the Priority code no longer populates to the "Project Entry" table. From what I can tell, this is because the "Control Source" is now the IIf function and not "Priority" which is what it was before.

I've tried moving the function to various places on the "Event" tab but none of them work.



What am I missing to make this work? I'm sure it's user error but I can't figure it out! That table is used for several reports, so I need to figure out how to get the priority code back on there!

Teresa

[pbaldy]

Calculated fields should generally not be saved, but:

http://allenbrowne.com/casu-14.html

Sounds like your event might be the after update event of the due date textbox.

[teresamichele]

Hi again! You're who tried to help me with the Priority field!

I'm trying the "after update" thing but it keeps opening up the debugger and I can't figure out what's wrong. It's probably something simple.

Here's what I have:

Private Sub Date_Due_AfterUpdate()
FormEntry.Priority = IIf([DateDiff] < 30, "A", IIf([DateDiff] < 60, "B", IIf([DateDiff] < 90, "C", "D")))
End Sub

It doesn't say WHY it won't run (mouse-over in VBA just shows that the DateDiff is 32), it just won't run.

I hope I just need to tweak something simple. I've been infatuated with Access for the past few weeks but now I'm getting good and annoyed!

Teresa

[pbaldy]

Does this work?

Me.Priority = IIf([DateDiff] < 30, "A", IIf([DateDiff] < 60, "B", IIf([DateDiff] < 90, "C", "D")))

[teresamichele]

Does this work?

Me.Priority = IIf([DateDiff] < 30, "A", IIf([DateDiff] < 60, "B", IIf([DateDiff] < 90, "C", "D")))

I have had a horrible day and you have just made it 100 times better.

It works! It's in the table and on the reports!

Thank you so much!

Teresa

[pbaldy]

Happy to help Teresa. The problem with storing that data is that today's "A" is tomorrow's "B". This is something you should calculate as needed rather than store. Otherwise you have to recalculate every record every day.

[teresamichele]

I'm not sure what you mean.

If I run a report based on Priority Code, it should update, right? What I mean is, if I run it today, and it's a code of C today, when I run it two weeks from now, it should recalculate the difference between the current date and the date due and assign the correct code, right - or is that just wishful thinking?

Teresa

[pbaldy]

That was wishful thinking.

Your code will recalculate it in the after update event of that form control. At no other point will it recalculate without you explicitly doing so (it's not like Excel). This is among the reasons you should generally not store a value that can be calculated from other stored fields. I would simply have the calculation on the report; that will always return the correct value at that moment.

[teresamichele]

Dang it.

Well, how would one do THAT? Right now they press a button and the report comes up. I've edited the report in design view (more design tweaks than anything), but what event would you even assign the code to, since the report is just "run"?

Thank you!

[pbaldy]

You probably don't need code. Given your earlier post, a textbox with a control source of:

= IIf([DateDiff] < 30, "A", IIf([DateDiff] < 60, "B", IIf([DateDiff] < 90, "C", "D")))

Should display the appropriate value. I assume DateDiff is a value in your data. If so, the name isn't a good idea, since Access may confuse it with the function of the same name. If that's also a calculated field, I'd say all the same things about it. Your textbox could then be:

= IIf(DateDiff("d", DueDateField, Date()) < 30, "A",...

[teresamichele]

I changed the name to "DatePriority" and tried the code - but when I tried to run the report, it wanted me to specify the DatePriority. I thought that perhaps it didn't know where to look for the number, so I changed the code to this:

=IIf([Forms]![FormEntry]![DatePriority]<30,"A",IIf([Forms]![FormEntry]![DatePriority]<60,"B",IIf([Forms]![FormEntry]![DatePriority]<90,"C","D")))

Now it doesn't ask for anything, but the box says "#Error" which is rather unhelpful.

I'm sure you know what I'm doing wrong, but if you could let ME know what I'm doing wrong, that would be great!

[pbaldy]

Can you post the db, or a representative sample of it?

[teresamichele]

I really can't because it's for work and it's basically a bunch of confidential information.

I have since tried to add it as an event to the report itself, but no luck.

The VBA code I tried to use is:

Private Sub Report_Load()
Me.Priority = IIf([Me.FormEntry.DatePriority] < 30, "A", IIf([Me.FormEntry.DatePriority] < 60, "B", IIf([Me.FormEntry.DatePriority] < 90, "C", "D")))
End Sub

And that gives me "Run-time error '2465': Microsoft Office Access can't find the field '|' refererred to in your expression. - but I have no idea what it's talking about. Not that I think that should surprise you at this point!

[pbaldy]

That appears to be a mix of referring to the report and a form. Is there a field in the report's data the calculation can be based on? The due date field? Did you try this one:

= IIf(DateDiff("d", DueDateField, Date()) < 30, "A",...

expanding to add the other parts.

[pbaldy]

I should have added that you'd want that in the detail format event anyway. In the load event it will only run once. In any case, code is not the answer here, I don't think.