date calculation

[awake2424]

Currently I use a module to calculate DueDate, excluding weekends and holidays. I store all the holiday in a tblHoliday and reference this table in the module. A formula then calculated the DueDate. This works great in an access form, but I now realize that I need that value stored so it appears on the table as well, but do not know how to accomplish this. Thanks.

 

[CJ_London]

Why do you need to store it in a table when you can calculate on the fly? if someone changes the start date or number of days (I presume these are the parameters) then you can use it in a query. If you've updated a table you will have a lot more work to do.

If you need to store it in a table then you can still call the function (I presume it is a function, not a module) in your insert or update query or if in a form you need a bit of vba in the form before update event to call the function and assign it your your field.

 

[awake2424]

The db is linked to a sharepoint list, that unless the Due Date value is stored, will not display on the sharepoint list. Thank you.

 

[CJ_London]

OK - then do what I suggest in my second paragraph. If you need more help, please provide more information

 

[awake2424]

I have attached the db so you can see it.

I use a module that references a table of all the holidays in it. Then on a form I call a function (GetBusinessDay) that calculates the Due Date. It uses the value in the Date Received field as the start and then applies the function. I hope this helps. Thanks.

 

[CJ_London]

in your form change the name of the unbound control named 'due date' to something else because it conflicts with the field named 'due date'

As per my second paragraph

if in a form you need a bit of vba in the form before update event to call the function and assign it your your field

put this in your form before update
event

 Me.[due date] = GetBusinessDay([Date_Received], IIf([Test] = "LR", 4, IIf([Test] = "HR", 5, IIf([Test] = "STR", 2, IIf([Test] = "MOLDX", 10[COLOR=red], 0[/COLOR])))))

Note the bit in red which is missing from your form code but is required in VBA

EDIT: or you can simply have

Me.[due date] = Me.DueDate

if you have renamed your form control to DueDate

 

[awake2424]

The Due Date calculates in the form but when I go to add a new record:

The schema for this table has changed. You must refresh the table before adding new records.

Thanks.

 

[CJ_London]

not sure if you are asking a question but regret I don't use sharepoint so probably can't answer that one.

The code I provided worked for me

 

[awake2424]

Thank you.

 

[awake2424]

I'm sorry, but I am getting an invalid use of Null error, with the line in bold highlighted. Thank you.

 Private Sub Form_BeforeUpdate(Cancel As Integer)
[B] Me.[Due_Date] = GetBusinessDay([Date_Received], IIf([Test] = "LR", 4, IIf([Test] = "HR", 5, IIf([Test] = "STR", 2, IIf([Test] = "MOLDX", 10, 0)))))[/B]
End Sub