Creating array from a query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to load a dynamic array upon the opening of a form to create an
array of Holiday dates that I use in several places on the form to calculate
a date in the future using a function that strips weekend days and holidays.
The function calls for an array of dates where you put in array(#3/15/06#,
#3/20/06#) for example and it will skip the dates in question. This method
works but it requires someone (me) to update holidays every year or so in
code, not a desirable solution. I want to create an array on opening the
form that will create an array from a table so that the user can update the
holidays in the table every so often and not have to try and mess with the
code (or bug me to do it). I tried the following and I get only the first
date in the Holiday table when I try it so I am obviously missing somthing.
Any suggestions?

Public Sub Form_Load()
Dim HolKeys() As Date

Dim r As Integer

Set rst = New ADODB.Recordset


rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenStatic
rst.Open "Select HolidayDate from Holidays"

r = 0
With rst
ReDim HolKeys(.RecordCount - 1)

Do Until .EOF
HolKeys(r) = ![Holidaydate]
.MoveNext
r = r + 1
Loop

.Close
End With
Debug.Print HolKeys(Holidaydate)

Set rst = Nothing


End Sub
 
Nothing strikes me as obviously wrong with the code.

Have you tried single-stepping through the code?

On the topic of knowing when holidays are, you might want to take a look at
my June, 2005 "Access Answers" column in Pinnacle Publication's "Smart
Access". You can download the column (and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html
 
I beleive the code is right but I may be using the results wrong. I am
trying to use the array created in a function I got off of the Access Web
site and written by Ken Getz. The function is in a module in my database and
goes as follows;

Public Function dhAddWorkDaysA(lngDays As Long, _
Optional dtmDate As Date = 0, _
Optional adtmDates As Variant) As Date
' Add the specified number of work days to the
' specified date.

' Modified from code in
' "Visual Basic Language Developer's Handbook"
' by Ken Getz and Mike Gilbert
' Copyright 2000; Sybex, Inc. All rights reserved.

' In:
' lngDays:
' Number of work days to add to the start date.
' dtmDate:
' date on which to start looking.
' Use the current date, if none was specified.
' adtmDates (Optional):
' Array containing holiday dates. Can also be a single
' date value, if that's what you want.
' Out:
' Return Value:
' The date of the working day lngDays from the start, taking
' into account weekends and holidays.
' Example:
' dhAddWorkDaysA(10, #2/9/2000#, Array(#2/16/2000#, #2/17/2000#))
' returns #2/25/2000#, which is the date 10 work days
' after 2/9/2000, if you treat 2/16 and 2/17 as holidays
' (just made-up holidays, for example purposes only).

' Did the caller pass in a date? If not, use
' the current date.
Dim lngCount As Long
Dim dtmTemp As Date

If dtmDate = 0 Then
dtmDate = Date
End If

dtmTemp = dtmDate
For lngCount = 1 To lngDays
dtmTemp = dhNextWorkdayA(dtmTemp, adtmDates)
Next lngCount
dhAddWorkDaysA = dtmTemp
End Function

I was using it in code and using as follows;
Mnumber = dhAddWorkDaysA(10, SDate, Array(#5/29/2006#, #7/4/2006#,
#9/4/2006#, #11/10/2006#, #11/23/2006#, #11/24/2006#, #12/25/2006#,
#12/26/2006#, #1/1/2007#, #1/2/2007#, #1/15/2007#))

Sdate is a variable containing the start date. I want to avoid having to
update the code on a regular basis to add and delete dates. I would prefer
using a table that the user can update with dates. I put the code to create
the array with the dates on the load property of the form (I tried using on
the subform too where the fields in question are) but it does not work. Any
ideas?

Douglas J Steele said:
Nothing strikes me as obviously wrong with the code.

Have you tried single-stepping through the code?

On the topic of knowing when holidays are, you might want to take a look at
my June, 2005 "Access Answers" column in Pinnacle Publication's "Smart
Access". You can download the column (and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Al Westerlund said:
I am trying to load a dynamic array upon the opening of a form to create an
array of Holiday dates that I use in several places on the form to calculate
a date in the future using a function that strips weekend days and holidays.
The function calls for an array of dates where you put in array(#3/15/06#,
#3/20/06#) for example and it will skip the dates in question. This method
works but it requires someone (me) to update holidays every year or so in
code, not a desirable solution. I want to create an array on opening the
form that will create an array from a table so that the user can update the
holidays in the table every so often and not have to try and mess with the
code (or bug me to do it). I tried the following and I get only the first
date in the Holiday table when I try it so I am obviously missing somthing.
Any suggestions?

Public Sub Form_Load()
Dim HolKeys() As Date

Dim r As Integer

Set rst = New ADODB.Recordset


rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenStatic
rst.Open "Select HolidayDate from Holidays"

r = 0
With rst
ReDim HolKeys(.RecordCount - 1)

Do Until .EOF
HolKeys(r) = ![Holidaydate]
.MoveNext
r = r + 1
Loop

.Close
End With
Debug.Print HolKeys(Holidaydate)

Set rst = Nothing


End Sub
Click to expand...
Click to expand...
 
Hold on. Are you saying it doesn't work based on what's printed out by

Debug.Print HolKeys(Holidaydate)

?

You haven't declared the variable Holidaydate anywhere, which implies that
you set Access to require variable declaration (you really should!). (Yes,
you're referring to a field in the recordset named Holidaydate, but they're
not related) Because Holidaydate hasn't been defined anywhere, it'll have a
value of 0, so your Print statement is only going to print the first value
in the array.

Now, you've declared the array HolKeys inside the Form_Load sub: that means
HolKeys is only available in that routine. If you want to be able to use it
while the form is open, declare the array at the top of the module, before
the very first routine. That makes it available anywhere in the form's
class.

Once you've done that, you should be able to use

Mnumber = dhAddWorkDaysA(10, SDate, HolKeys)

instead of what you've got hard-coded.

Take a look, though, at the reference I gave earlier. In addition to the
specific article I cited there, the September, 2004 column shows an
alternative to Ken's code.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Al Westerlund said:
I beleive the code is right but I may be using the results wrong. I am
trying to use the array created in a function I got off of the Access Web
site and written by Ken Getz. The function is in a module in my database
and
goes as follows;

Public Function dhAddWorkDaysA(lngDays As Long, _
Optional dtmDate As Date = 0, _
Optional adtmDates As Variant) As Date
' Add the specified number of work days to the
' specified date.

' Modified from code in
' "Visual Basic Language Developer's Handbook"
' by Ken Getz and Mike Gilbert
' Copyright 2000; Sybex, Inc. All rights reserved.

' In:
' lngDays:
' Number of work days to add to the start date.
' dtmDate:
' date on which to start looking.
' Use the current date, if none was specified.
' adtmDates (Optional):
' Array containing holiday dates. Can also be a single
' date value, if that's what you want.
' Out:
' Return Value:
' The date of the working day lngDays from the start, taking
' into account weekends and holidays.
' Example:
' dhAddWorkDaysA(10, #2/9/2000#, Array(#2/16/2000#, #2/17/2000#))
' returns #2/25/2000#, which is the date 10 work days
' after 2/9/2000, if you treat 2/16 and 2/17 as holidays
' (just made-up holidays, for example purposes only).

' Did the caller pass in a date? If not, use
' the current date.
Dim lngCount As Long
Dim dtmTemp As Date

If dtmDate = 0 Then
dtmDate = Date
End If

dtmTemp = dtmDate
For lngCount = 1 To lngDays
dtmTemp = dhNextWorkdayA(dtmTemp, adtmDates)
Next lngCount
dhAddWorkDaysA = dtmTemp
End Function

I was using it in code and using as follows;
Mnumber = dhAddWorkDaysA(10, SDate, Array(#5/29/2006#, #7/4/2006#,
#9/4/2006#, #11/10/2006#, #11/23/2006#, #11/24/2006#, #12/25/2006#,
#12/26/2006#, #1/1/2007#, #1/2/2007#, #1/15/2007#))

Sdate is a variable containing the start date. I want to avoid having to
update the code on a regular basis to add and delete dates. I would prefer
using a table that the user can update with dates. I put the code to
create
the array with the dates on the load property of the form (I tried using
on
the subform too where the fields in question are) but it does not work.
Any
ideas?

Douglas J Steele said:
Nothing strikes me as obviously wrong with the code.

Have you tried single-stepping through the code?

On the topic of knowing when holidays are, you might want to take a look
at
my June, 2005 "Access Answers" column in Pinnacle Publication's "Smart
Access". You can download the column (and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Al Westerlund said:
I am trying to load a dynamic array upon the opening of a form to
create an
array of Holiday dates that I use in several places on the form to calculate
a date in the future using a function that strips weekend days and holidays.
The function calls for an array of dates where you put in
array(#3/15/06#,
#3/20/06#) for example and it will skip the dates in question. This method
works but it requires someone (me) to update holidays every year or so
in
code, not a desirable solution. I want to create an array on opening
the
form that will create an array from a table so that the user can update the
holidays in the table every so often and not have to try and mess with
the
code (or bug me to do it). I tried the following and I get only the
first
date in the Holiday table when I try it so I am obviously missing somthing.
Any suggestions?

Public Sub Form_Load()
Dim HolKeys() As Date

Dim r As Integer

Set rst = New ADODB.Recordset


rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenStatic
rst.Open "Select HolidayDate from Holidays"

r = 0
With rst
ReDim HolKeys(.RecordCount - 1)

Do Until .EOF
HolKeys(r) = ![Holidaydate]
.MoveNext
r = r + 1
Loop

.Close
End With
Debug.Print HolKeys(Holidaydate)

Set rst = Nothing


End Sub
Click to expand...
Click to expand...
Click to expand...
 
I realize now why the whole array did not print. The problem is it still
will not work. I put the array creation on the first part of the code as
follows;

Private Sub Form_Load()
Static HolKeys() As Variant


Dim r As Integer

Set rst = New ADODB.Recordset


rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenStatic
rst.Open "Select HolidayDate from tblHolidays"

r = 0
With rst
ReDim HolKeys(.RecordCount - 1)

Do Until .EOF
HolKeys(r) = ![HolidayDate]
.MoveNext
r = r + 1
Loop

.Close
End With

'Debug.Print HolKeys(HolidayDate)


Set rst = Nothing


End Sub

It still did not work. I the put the code in the same routine as the code
used to do the calculation that uses the dhAddWorkDaysA function. It still
did not work. I suspect because it is looking for an array created by the
function itself (which also uses a function in the module that contains the
above function called SkipHolidaysA and uses the parameter adtmdates. When
entering the arguments for the dhAddWorkDaysA after the second comma it
brings up the help information mentioning a paramarray, so perhaps I am not
using the right type of array. At any rate, I appreciate your help. I have
looked at both issues you mentioned and I may be able to use the earlier one.
The second one is problematic since I work for the State of Michigan and
they just started giving state employees even numbered years election days as
holidays. It is not too onerous to ask the user to update the holiday table
every so often anyway so I will try your code. If nothing else, it will give
me some practice. I am not a beginner at Access coding but I do need some
more experience to be considered semi-fluent at it. Do you have a
recommendation for a top notch book on Access VBL? Again, thanks for your
help.

Douglas J. Steele said:
Hold on. Are you saying it doesn't work based on what's printed out by

Debug.Print HolKeys(Holidaydate)

?

You haven't declared the variable Holidaydate anywhere, which implies that
you set Access to require variable declaration (you really should!). (Yes,
you're referring to a field in the recordset named Holidaydate, but they're
not related) Because Holidaydate hasn't been defined anywhere, it'll have a
value of 0, so your Print statement is only going to print the first value
in the array.

Now, you've declared the array HolKeys inside the Form_Load sub: that means
HolKeys is only available in that routine. If you want to be able to use it
while the form is open, declare the array at the top of the module, before
the very first routine. That makes it available anywhere in the form's
class.

Once you've done that, you should be able to use

Mnumber = dhAddWorkDaysA(10, SDate, HolKeys)

instead of what you've got hard-coded.

Take a look, though, at the reference I gave earlier. In addition to the
specific article I cited there, the September, 2004 column shows an
alternative to Ken's code.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Al Westerlund said:
I beleive the code is right but I may be using the results wrong. I am
trying to use the array created in a function I got off of the Access Web
site and written by Ken Getz. The function is in a module in my database
and
goes as follows;

Public Function dhAddWorkDaysA(lngDays As Long, _
Optional dtmDate As Date = 0, _
Optional adtmDates As Variant) As Date
' Add the specified number of work days to the
' specified date.

' Modified from code in
' "Visual Basic Language Developer's Handbook"
' by Ken Getz and Mike Gilbert
' Copyright 2000; Sybex, Inc. All rights reserved.

' In:
' lngDays:
' Number of work days to add to the start date.
' dtmDate:
' date on which to start looking.
' Use the current date, if none was specified.
' adtmDates (Optional):
' Array containing holiday dates. Can also be a single
' date value, if that's what you want.
' Out:
' Return Value:
' The date of the working day lngDays from the start, taking
' into account weekends and holidays.
' Example:
' dhAddWorkDaysA(10, #2/9/2000#, Array(#2/16/2000#, #2/17/2000#))
' returns #2/25/2000#, which is the date 10 work days
' after 2/9/2000, if you treat 2/16 and 2/17 as holidays
' (just made-up holidays, for example purposes only).

' Did the caller pass in a date? If not, use
' the current date.
Dim lngCount As Long
Dim dtmTemp As Date

If dtmDate = 0 Then
dtmDate = Date
End If

dtmTemp = dtmDate
For lngCount = 1 To lngDays
dtmTemp = dhNextWorkdayA(dtmTemp, adtmDates)
Next lngCount
dhAddWorkDaysA = dtmTemp
End Function

I was using it in code and using as follows;
Mnumber = dhAddWorkDaysA(10, SDate, Array(#5/29/2006#, #7/4/2006#,
#9/4/2006#, #11/10/2006#, #11/23/2006#, #11/24/2006#, #12/25/2006#,
#12/26/2006#, #1/1/2007#, #1/2/2007#, #1/15/2007#))

Sdate is a variable containing the start date. I want to avoid having to
update the code on a regular basis to add and delete dates. I would prefer
using a table that the user can update with dates. I put the code to
create
the array with the dates on the load property of the form (I tried using
on
the subform too where the fields in question are) but it does not work.
Any
ideas?

Douglas J Steele said:
Nothing strikes me as obviously wrong with the code.

Have you tried single-stepping through the code?

On the topic of knowing when holidays are, you might want to take a look
at
my June, 2005 "Access Answers" column in Pinnacle Publication's "Smart
Access". You can download the column (and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I am trying to load a dynamic array upon the opening of a form to
create
an
array of Holiday dates that I use in several places on the form to
calculate
a date in the future using a function that strips weekend days and
holidays.
The function calls for an array of dates where you put in
array(#3/15/06#,
#3/20/06#) for example and it will skip the dates in question. This
method
works but it requires someone (me) to update holidays every year or so
in
code, not a desirable solution. I want to create an array on opening
the
form that will create an array from a table so that the user can update
the
holidays in the table every so often and not have to try and mess with
the
code (or bug me to do it). I tried the following and I get only the
first
date in the Holiday table when I try it so I am obviously missing
somthing.
Any suggestions?

Public Sub Form_Load()
Dim HolKeys() As Date

Dim r As Integer

Set rst = New ADODB.Recordset


rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenStatic
rst.Open "Select HolidayDate from Holidays"

r = 0
With rst
ReDim HolKeys(.RecordCount - 1)

Do Until .EOF
HolKeys(r) = ![Holidaydate]
.MoveNext
r = r + 1
Loop

.Close
End With
Debug.Print HolKeys(Holidaydate)

Set rst = Nothing


End Sub
Click to expand...
Click to expand...
Click to expand...
 
Reread my previous note. You're still declaring HolKeys() inside the sub. If
you want to be able to use it in other subs associated with the same form,
you have to declare it at the beginning of the module, before any functions
or subs. (If you want to be able to use it from other forms, you need to
declare it as Public in a stand-alone module, as opposed to in the module
associated with a form)

For several lists of good books, see what Jeff Conrad has at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#Books


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Al Westerlund said:
I realize now why the whole array did not print. The problem is it still
will not work. I put the array creation on the first part of the code as
follows;

Private Sub Form_Load()
Static HolKeys() As Variant


Dim r As Integer

Set rst = New ADODB.Recordset


rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenStatic
rst.Open "Select HolidayDate from tblHolidays"

r = 0
With rst
ReDim HolKeys(.RecordCount - 1)

Do Until .EOF
HolKeys(r) = ![HolidayDate]
.MoveNext
r = r + 1
Loop

.Close
End With

'Debug.Print HolKeys(HolidayDate)


Set rst = Nothing


End Sub

It still did not work. I the put the code in the same routine as the code
used to do the calculation that uses the dhAddWorkDaysA function. It still
did not work. I suspect because it is looking for an array created by the
function itself (which also uses a function in the module that contains the
above function called SkipHolidaysA and uses the parameter adtmdates. When
entering the arguments for the dhAddWorkDaysA after the second comma it
brings up the help information mentioning a paramarray, so perhaps I am not
using the right type of array. At any rate, I appreciate your help. I have
looked at both issues you mentioned and I may be able to use the earlier one.
The second one is problematic since I work for the State of Michigan and
they just started giving state employees even numbered years election days as
holidays. It is not too onerous to ask the user to update the holiday table
every so often anyway so I will try your code. If nothing else, it will give
me some practice. I am not a beginner at Access coding but I do need some
more experience to be considered semi-fluent at it. Do you have a
recommendation for a top notch book on Access VBL? Again, thanks for your
help.

Douglas J. Steele said:
Hold on. Are you saying it doesn't work based on what's printed out by

Debug.Print HolKeys(Holidaydate)

?

You haven't declared the variable Holidaydate anywhere, which implies that
you set Access to require variable declaration (you really should!). (Yes,
you're referring to a field in the recordset named Holidaydate, but they're
not related) Because Holidaydate hasn't been defined anywhere, it'll have a
value of 0, so your Print statement is only going to print the first value
in the array.

Now, you've declared the array HolKeys inside the Form_Load sub: that means
HolKeys is only available in that routine. If you want to be able to use it
while the form is open, declare the array at the top of the module, before
the very first routine. That makes it available anywhere in the form's
class.

Once you've done that, you should be able to use

Mnumber = dhAddWorkDaysA(10, SDate, HolKeys)

instead of what you've got hard-coded.

Take a look, though, at the reference I gave earlier. In addition to the
specific article I cited there, the September, 2004 column shows an
alternative to Ken's code.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I beleive the code is right but I may be using the results wrong. I am
trying to use the array created in a function I got off of the Access Web
site and written by Ken Getz. The function is in a module in my database
and
goes as follows;

Public Function dhAddWorkDaysA(lngDays As Long, _
Optional dtmDate As Date = 0, _
Optional adtmDates As Variant) As Date
' Add the specified number of work days to the
' specified date.

' Modified from code in
' "Visual Basic Language Developer's Handbook"
' by Ken Getz and Mike Gilbert
' Copyright 2000; Sybex, Inc. All rights reserved.

' In:
' lngDays:
' Number of work days to add to the start date.
' dtmDate:
' date on which to start looking.
' Use the current date, if none was specified.
' adtmDates (Optional):
' Array containing holiday dates. Can also be a single
' date value, if that's what you want.
' Out:
' Return Value:
' The date of the working day lngDays from the start, taking
' into account weekends and holidays.
' Example:
' dhAddWorkDaysA(10, #2/9/2000#, Array(#2/16/2000#, #2/17/2000#))
' returns #2/25/2000#, which is the date 10 work days
' after 2/9/2000, if you treat 2/16 and 2/17 as holidays
' (just made-up holidays, for example purposes only).

' Did the caller pass in a date? If not, use
' the current date.
Dim lngCount As Long
Dim dtmTemp As Date

If dtmDate = 0 Then
dtmDate = Date
End If

dtmTemp = dtmDate
For lngCount = 1 To lngDays
dtmTemp = dhNextWorkdayA(dtmTemp, adtmDates)
Next lngCount
dhAddWorkDaysA = dtmTemp
End Function

I was using it in code and using as follows;
Mnumber = dhAddWorkDaysA(10, SDate, Array(#5/29/2006#, #7/4/2006#,
#9/4/2006#, #11/10/2006#, #11/23/2006#, #11/24/2006#, #12/25/2006#,
#12/26/2006#, #1/1/2007#, #1/2/2007#, #1/15/2007#))

Sdate is a variable containing the start date. I want to avoid having to
update the code on a regular basis to add and delete dates. I would prefer
using a table that the user can update with dates. I put the code to
create
the array with the dates on the load property of the form (I tried using
on
the subform too where the fields in question are) but it does not work.
Any
ideas?

:

Nothing strikes me as obviously wrong with the code.

Have you tried single-stepping through the code?

On the topic of knowing when holidays are, you might want to take a look
at
my June, 2005 "Access Answers" column in Pinnacle Publication's "Smart
Access". You can download the column (and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I am trying to load a dynamic array upon the opening of a form to
create
an
array of Holiday dates that I use in several places on the form to
calculate
a date in the future using a function that strips weekend days and
holidays.
The function calls for an array of dates where you put in
array(#3/15/06#,
#3/20/06#) for example and it will skip the dates in question. This
method
works but it requires someone (me) to update holidays every year or so
in
code, not a desirable solution. I want to create an array on opening
the
form that will create an array from a table so that the user can update
the
holidays in the table every so often and not have to try and mess with
the
code (or bug me to do it). I tried the following and I get only the
first
date in the Holiday table when I try it so I am obviously missing
somthing.
Any suggestions?

Public Sub Form_Load()
Dim HolKeys() As Date

Dim r As Integer

Set rst = New ADODB.Recordset


rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenStatic
rst.Open "Select HolidayDate from Holidays"

r = 0
With rst
ReDim HolKeys(.RecordCount - 1)

Do Until .EOF
HolKeys(r) = ![Holidaydate]
.MoveNext
r = r + 1
Loop

.Close
End With
Debug.Print HolKeys(Holidaydate)

Set rst = Nothing


End Sub
Click to expand...
Click to expand...
Click to expand...
 
Doug,

I did put it before everything. I forgot to put that I had done that and it
did not work so I tried putting it right before calling the other function.
Moot point now. I used your earlier code and it works very well. I just
imported your modules (as there are some other things you have I may use).
Adapted your code since I am not asking the user for whether it is a holiday
or not and the days are fixed, not user initiated. It works great and it
will make the users happy and me happy since I will not have to update the
holidays, they can. I appreciate your help and I am still not sure why the
other way did not work but glad your way does. Thanks again and thanks for
the link to the books.

Douglas J Steele said:
Reread my previous note. You're still declaring HolKeys() inside the sub. If
you want to be able to use it in other subs associated with the same form,
you have to declare it at the beginning of the module, before any functions
or subs. (If you want to be able to use it from other forms, you need to
declare it as Public in a stand-alone module, as opposed to in the module
associated with a form)

For several lists of good books, see what Jeff Conrad has at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#Books


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Al Westerlund said:
I realize now why the whole array did not print. The problem is it still
will not work. I put the array creation on the first part of the code as
follows;

Private Sub Form_Load()
Static HolKeys() As Variant


Dim r As Integer

Set rst = New ADODB.Recordset


rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenStatic
rst.Open "Select HolidayDate from tblHolidays"

r = 0
With rst
ReDim HolKeys(.RecordCount - 1)

Do Until .EOF
HolKeys(r) = ![HolidayDate]
.MoveNext
r = r + 1
Loop

.Close
End With

'Debug.Print HolKeys(HolidayDate)


Set rst = Nothing


End Sub

It still did not work. I the put the code in the same routine as the code
used to do the calculation that uses the dhAddWorkDaysA function. It still
did not work. I suspect because it is looking for an array created by the
function itself (which also uses a function in the module that contains the
above function called SkipHolidaysA and uses the parameter adtmdates. When
entering the arguments for the dhAddWorkDaysA after the second comma it
brings up the help information mentioning a paramarray, so perhaps I am not
using the right type of array. At any rate, I appreciate your help. I have
looked at both issues you mentioned and I may be able to use the earlier one.
The second one is problematic since I work for the State of Michigan and
they just started giving state employees even numbered years election days as
holidays. It is not too onerous to ask the user to update the holiday table
every so often anyway so I will try your code. If nothing else, it will give
me some practice. I am not a beginner at Access coding but I do need some
more experience to be considered semi-fluent at it. Do you have a
recommendation for a top notch book on Access VBL? Again, thanks for your
help.

Douglas J. Steele said:
Hold on. Are you saying it doesn't work based on what's printed out by

Debug.Print HolKeys(Holidaydate)

?

You haven't declared the variable Holidaydate anywhere, which implies that
you set Access to require variable declaration (you really should!). (Yes,
you're referring to a field in the recordset named Holidaydate, but they're
not related) Because Holidaydate hasn't been defined anywhere, it'll have a
value of 0, so your Print statement is only going to print the first value
in the array.

Now, you've declared the array HolKeys inside the Form_Load sub: that means
HolKeys is only available in that routine. If you want to be able to use it
while the form is open, declare the array at the top of the module, before
the very first routine. That makes it available anywhere in the form's
class.

Once you've done that, you should be able to use

Mnumber = dhAddWorkDaysA(10, SDate, HolKeys)

instead of what you've got hard-coded.

Take a look, though, at the reference I gave earlier. In addition to the
specific article I cited there, the September, 2004 column shows an
alternative to Ken's code.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I beleive the code is right but I may be using the results wrong. I am
trying to use the array created in a function I got off of the Access Web
site and written by Ken Getz. The function is in a module in my database
and
goes as follows;

Public Function dhAddWorkDaysA(lngDays As Long, _
Optional dtmDate As Date = 0, _
Optional adtmDates As Variant) As Date
' Add the specified number of work days to the
' specified date.

' Modified from code in
' "Visual Basic Language Developer's Handbook"
' by Ken Getz and Mike Gilbert
' Copyright 2000; Sybex, Inc. All rights reserved.

' In:
' lngDays:
' Number of work days to add to the start date.
' dtmDate:
' date on which to start looking.
' Use the current date, if none was specified.
' adtmDates (Optional):
' Array containing holiday dates. Can also be a single
' date value, if that's what you want.
' Out:
' Return Value:
' The date of the working day lngDays from the start, taking
' into account weekends and holidays.
' Example:
' dhAddWorkDaysA(10, #2/9/2000#, Array(#2/16/2000#, #2/17/2000#))
' returns #2/25/2000#, which is the date 10 work days
' after 2/9/2000, if you treat 2/16 and 2/17 as holidays
' (just made-up holidays, for example purposes only).

' Did the caller pass in a date? If not, use
' the current date.
Dim lngCount As Long
Dim dtmTemp As Date

If dtmDate = 0 Then
dtmDate = Date
End If

dtmTemp = dtmDate
For lngCount = 1 To lngDays
dtmTemp = dhNextWorkdayA(dtmTemp, adtmDates)
Next lngCount
dhAddWorkDaysA = dtmTemp
End Function

I was using it in code and using as follows;
Mnumber = dhAddWorkDaysA(10, SDate, Array(#5/29/2006#, #7/4/2006#,
#9/4/2006#, #11/10/2006#, #11/23/2006#, #11/24/2006#, #12/25/2006#,
#12/26/2006#, #1/1/2007#, #1/2/2007#, #1/15/2007#))

Sdate is a variable containing the start date. I want to avoid having to
update the code on a regular basis to add and delete dates. I would prefer
using a table that the user can update with dates. I put the code to
create
the array with the dates on the load property of the form (I tried using
on
the subform too where the fields in question are) but it does not work.
Any
ideas?

:

Nothing strikes me as obviously wrong with the code.

Have you tried single-stepping through the code?

On the topic of knowing when holidays are, you might want to take a look
at
my June, 2005 "Access Answers" column in Pinnacle Publication's "Smart
Access". You can download the column (and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I am trying to load a dynamic array upon the opening of a form to
create
an
array of Holiday dates that I use in several places on the form to
calculate
a date in the future using a function that strips weekend days and
holidays.
The function calls for an array of dates where you put in
array(#3/15/06#,
#3/20/06#) for example and it will skip the dates in question. This
method
works but it requires someone (me) to update holidays every year or so
in
code, not a desirable solution. I want to create an array on opening
the
form that will create an array from a table so that the user can update
the
holidays in the table every so often and not have to try and mess with
the
code (or bug me to do it). I tried the following and I get only the
first
date in the Holiday table when I try it so I am obviously missing
somthing.
Any suggestions?

Public Sub Form_Load()
Dim HolKeys() As Date

Dim r As Integer

Set rst = New ADODB.Recordset


rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenStatic
rst.Open "Select HolidayDate from Holidays"

r = 0
With rst
ReDim HolKeys(.RecordCount - 1)

Do Until .EOF
HolKeys(r) = ![Holidaydate]
.MoveNext
r = r + 1
Loop

.Close
End With
Debug.Print HolKeys(Holidaydate)

Set rst = Nothing


End Sub
Click to expand...
Click to expand...
Click to expand...
 
Back
Top