Few questions on Forms

[ShadowNYC]

Hello everyone -

I have created a form on Access 2003 that has 4 fields:

Date Completed Info Received
Date Projection sent to HR
Total Business Days (which is a calculation of the difference between the last 2 fields using "=calcWorkDays([Date Completed Info Received],[Date Projection sent to HR])"
SLA met? (which looks at the previous text box using "=IIf([Text168]<=4,"Yes",IIf([Text168]>=5,"No"))"

=calcWorkDays is in a module
Public Function calcWorkDays(dteStart As Date, dteEnd As Date) As Long
Dim i As Long 'day counter
Dim dteCurDay As Date
'set i = 1 if you want the first date to count as a full day
'or i = 0 if you do not want the first day to count as a full day

i = 1
dteCurDay = dteStart
Do Until dteCurDay >= dteEnd
'check date against holiday table
If 0 = DCount("[HolidayDate]", "tblHolidays", "[HolidayDate] = #" & dteCurDay & "#") Then
'continue checking for weekdays i.e. i increases only if week day value is not 1(Sunday) and not 7(Saturday)
If Weekday(dteCurDay) <> 1 And _
Weekday(dteCurDay) <> 7 Then

i = i + 1
End If
End If
dteCurDay = DateAdd("d", 1, dteCurDay)
Loop

calcWorkDays = i
'based on Calculating Networkdays

End Function

OK now the problem. When I insert dates into Date Request received from HR & Date Projection sent to HR it works perfectly fine, but when I open a new record I get a "#Error" in fields "Total Business Days" & "SLA met?". Is there any way I can hide or prevent this from showing? (keep in mind this is previous to entering the dates)
Thanks in advance ,
Jon

(I have another question but will wait until this is answered)

Edit: clarification!

 

[missinglinq]

The problem is that you're assigning these using the Controls' Control Sources and on a New Record the Dates aren't there yet.

Instead of using the Control Sources for Total Business Days and SLA met? you need to use the AfterUpdate events of Date Request received from HR and Date Projection sent to HR to assign the values to Total Business Days and SLA met?

Linq ;0)>

 

[ShadowNYC]

Instead of using the Control Sources for Total Business Days and SLA met? you need to use the AfterUpdate events of Date Request received from HR and Date Projection sent to HR to assign the values to Total Business Days and SLA met?

I am not at work until Monday but that sounds exactly what I need.

PLEASE SEE BELOW

I really appreciate you help!

 

[ShadowNYC]

I think I spoke too soon because I misread your email thinking you said to use AfterUpdate events for Total Business Days and SLA met?

How would I instruct the AfterUpdate to link with Total Business Days?

Seeing as Date Projection sent to HR is the second date needed to fill in to make theTotal Business Days work, I guess that's where I would use the AfterUpdate? I also assume I need to change:=calcWorkDays([Date Completed Info Received],[Date Projection sent to HR]).
I tried AfterUpdate: [Text166]=calcWorkDays([Date Completed Info Received],[Date Projection sent to HR]) with no luck.

Thoughts?

Thanks again,
Jon

 

[ShadowNYC]

I hate to ask again since I have never been a fan of "bumping" but I am still hoping for an answer. This is the last problem I have to figure out on this project.

Thanks in advance

 

[vbaInet]

Ok, you would use the AfterUpdate and Current events of the Form. And don't forget to remove what's in the Control Source of the textbox that would get assigned this value.

You've got the code right!

 

[ShadowNYC]

Ok, you would use the AfterUpdate and Current events of the Form. And don't forget to remove what's in the Control Source of the textbox that would get assigned this value.

You've got the code right!

Thanks for the quick response.

So you are saying to not use the AfterUpdate on Date Projection sent to HR but use the Form's AfterUpdate? (also I am unfamiliar with Current!) How does access know what update I am referring to?

When I go into the Form's properties:

On Current.......
Before Insert....
After Insert.......
Before Update...
After Update..... [Text166]=calcWorkDays([Date Completed Info Received],[Date Projection sent to HR]) ????
On Dirty...........

Guess I am still a bit lost. Thanks for your patience

 

[vbaInet]

It depends on how soon you want the update to happen. missinglinq's method will give you an instant response but you will need to perform some further validation to ensure that both controls have been filled in. So if you're going to go with missinglinq's advice, in the After Update event of both controls:

If IsDate(Me!Date Completed Info Received]) And IsDate(Me![Date Projection sent to HR])) Then
    Me.Text166=calcWorkDays([Date Completed Info Received],[Date Projection sent to HR])
End If

To get to where to place this code, click on the elipsis button on the After Update of each control, select Code Builder and click OK. Paste your code where the blinking cursor is.

 

[ShadowNYC]

If IsDate(Me!Date Completed Info Received]) And IsDate(Me![Date Projection sent to HR])) Then
    Me.Text166=calcWorkDays([Date Completed Info Received],[Date Projection sent to HR])
End If

Ahh this is working! Had to add an additional "[" after Me! and remove a ")" from "HR]))" but it looks good for that record. One thing I noticed though is when I open a new record it carries the info in Text166 onto that record. It disappears after I enter the 2 dates with the correct info. Any thoughts?

 

[vbaInet]

When the record is saved, does it reflect in your table?

 

[vbaInet]

Ignore my last post. I misread your reply.

Did you put the code in the On Current event of the form as well?

 

[ShadowNYC]

Ignore my last post. I misread your reply.

Did you put the code in the On Current event of the form as well?

I did not - only AfterUpdate.

 

[vbaInet]

Put it in the Current event too and include an Else statement to set the value to vbNullString.

i.e.

If ...

Else

End If