Hi this error occurs, not straight away, but when you start to work or scroll through the database.
The error is related to coding "Set dbsThisDatabase = CurrentDb() 'define the database to be used" in the coding below.
This happened after a recent Microsoft update - my IT guy could not figure it out - said it was a trust issue with where the file is located.
Does anyone know of a fix for this please.
This is the VBA Coding:
Option Compare Database
Option Explicit
'-----------------------------------------------------
'define variables to be used to access the BankHolDate table
Dim dbsThisDatabase As Database
Dim rstDatesTable As DAO.Recordset
Dim strSQL As String
'-------------------------------------------------------------
Public Function myCalcHolDays(prmStartDate, prmEndDate) As Integer
'function to calculate the number of weekdays (Mon-Fri) between two dates, inclusive
'also checks a separate table of bank holiday dates and excludes these dates from the hol total
Dim myDate As Date 'used for the working date
Dim myTotal As Integer 'used to add up the count of days
If IsNull(prmStartDate) Then
'do nothing - this occurs when the user moves the cursor to a new record -
' the calculation appears to be invoked before any data is added as well as afterwards.
Else
myDate = prmStartDate 'set first date as working date
myTotal = 0 'initialise count to zero
'loop to process the dates one by one, from start date to end date
While myDate <= prmEndDate 'keep processing until past the end date
If Weekday(myDate) <> vbSunday And Weekday(myDate) <> vbSaturday Then
'it's not Sat or Sun - so check to see if it's a bank hol
'set the SQL statement to select the current date from the BankHolDate table
strSQL = "SELECT * FROM BankHolDate WHERE BankHolDate.HolDate = DateValue(' " & myDate & " ');"
Set dbsThisDatabase = CurrentDb() 'define the database to be used
Set rstDatesTable = dbsThisDatabase.OpenRecordset(strSQL) 'run the SQL and put result in the Recordset
If rstDatesTable.RecordCount = 0 Then '0 = no records found, 1 = otherwise
myTotal = myTotal + 1 'not a weekend or bank hol, so add to total
End If
End If
myDate = DateAdd("d", 1, myDate) 'next working date (add 1 day)
Wend
'set value to return
myCalcHolDays = myTotal
End If
End Function
The error is related to coding "Set dbsThisDatabase = CurrentDb() 'define the database to be used" in the coding below.
This happened after a recent Microsoft update - my IT guy could not figure it out - said it was a trust issue with where the file is located.
Does anyone know of a fix for this please.
This is the VBA Coding:
Option Compare Database
Option Explicit
'-----------------------------------------------------
'define variables to be used to access the BankHolDate table
Dim dbsThisDatabase As Database
Dim rstDatesTable As DAO.Recordset
Dim strSQL As String
'-------------------------------------------------------------
Public Function myCalcHolDays(prmStartDate, prmEndDate) As Integer
'function to calculate the number of weekdays (Mon-Fri) between two dates, inclusive
'also checks a separate table of bank holiday dates and excludes these dates from the hol total
Dim myDate As Date 'used for the working date
Dim myTotal As Integer 'used to add up the count of days
If IsNull(prmStartDate) Then
'do nothing - this occurs when the user moves the cursor to a new record -
' the calculation appears to be invoked before any data is added as well as afterwards.
Else
myDate = prmStartDate 'set first date as working date
myTotal = 0 'initialise count to zero
'loop to process the dates one by one, from start date to end date
While myDate <= prmEndDate 'keep processing until past the end date
If Weekday(myDate) <> vbSunday And Weekday(myDate) <> vbSaturday Then
'it's not Sat or Sun - so check to see if it's a bank hol
'set the SQL statement to select the current date from the BankHolDate table
strSQL = "SELECT * FROM BankHolDate WHERE BankHolDate.HolDate = DateValue(' " & myDate & " ');"
Set dbsThisDatabase = CurrentDb() 'define the database to be used
Set rstDatesTable = dbsThisDatabase.OpenRecordset(strSQL) 'run the SQL and put result in the Recordset
If rstDatesTable.RecordCount = 0 Then '0 = no records found, 1 = otherwise
myTotal = myTotal + 1 'not a weekend or bank hol, so add to total
End If
End If
myDate = DateAdd("d", 1, myDate) 'next working date (add 1 day)
Wend
'set value to return
myCalcHolDays = myTotal
End If
End Function
