User defined type not defined error

  • Thread starter Christina Wise via AccessMonster.com
  • Start date
C

Christina Wise via AccessMonster.com

I am trying to create a module that calculates business days in the future.
In other words, given a date, calculate the business date say five days in
the future, excluding holidays and weekends.

Per the instructions at Arvin Meyer’s site
http://www.datastrat.com/Code/GetBusinessDay.txt , I created a table
“Holidays” that contains a column “HolidayDate” listing the holidays.

I then copied the code for the function into a module. But when I try to run
the debugger it gets caught up on the line
Dim rst As DAO.Recordset
And tells me “Compile error: User defined type not defined”. I’m not sure
what I need to rename or change to get this to work.

I am using Access2000. I tried going into “References” on the “Tools” menu
but was not sure which DAO library to select or if this was even what was
causing the problemThe options were:
Microsoft DAO 2.5/3.5 Compatibility Library
Microsoft DAO 3.51 Object Library
Microsoft DAO 3.6 Object Library

I realize there’s probably something very straightforward that I’m missing,
but I’m not very efficient with VB. Any help would be greatly appreciated.
The module/function code I’m trying to run is as follows:

Function GetBusinessDay(datStart As Date, intDayAdd As Integer)
On Error GoTo Error_Handler
'Adds/Subtracts the proper Business day skipping holidays and weekends
'Requires a table (tblHolidays) with a date field (HolidayDate)
'Arvin Meyer 05/26/98 revised 3/12/2002
'© Arvin Meyer 1998 - 2002 You may use this code in your application provided
author
' is given credit. This code may not be distributed as part of a collection
' without prior written permission. This header must remain intact.


Dim rst As DAO.Recordset
Dim DB As DAO.Database
'Dim strSQL As String

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays",
dbOpenSnapshot)

If intDayAdd > 0 Then
Do While intDayAdd > 0
datStart = datStart + 1
rst.FindFirst "[HolidayDate] = #" & datStart & "#"
If Weekday(datStart) <> vbSunday And Weekday(datStart) <> vbSaturday
Then
If rst.NoMatch Then intDayAdd = intDayAdd - 1
End If
Loop

ElseIf intDayAdd < 0 Then

Do While intDayAdd < 0
datStart = datStart - 1
rst.FindFirst "[HolidayDate] = #" & datStart & "#"
If Weekday(datStart) <> vbSunday And Weekday(datStart) <> vbSaturday
Then
If rst.NoMatch Then intDayAdd = intDayAdd + 1
End If
Loop

End If

GetBusinessDay = datStart

Exit_Here:
rst.Close
Set rst = Nothing
Set DB = Nothing
Exit Function

Error_Handler:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Here
End Function
 
R

RuralGuy

Christina Wise wrote:

[snip]
I am using Access2000. I tried going into “References” on the “Tools”
menu but was not sure which DAO library to select or if this was even
what was causing the problemThe options were:
Microsoft DAO 2.5/3.5 Compatibility Library
Microsoft DAO 3.51 Object Library
Microsoft DAO 3.6 Object Library

[snip]

Even though my guess is any of those libraries would clear the error,
if you havent already done so, put a check next to the latest (3.6).

HTH
 
C

Christina Wise via AccessMonster.com

Thanks. I tried that and it seemed to satisfy the debugger. The problem is
when I went back in to try to write a query using my new function, it gives
me the following Microsoft error:

3078 The Microsoft Jet database engine cannot find the input table or query
'tblHolidays'. Make sure it exists and that its name is spelled correctly.

That is followed by this error:
91:Object variable or withblock variable not set

I checked, the table "Holidays" is spelled correctly. The query I'm writing
is pretty straightforward:

Date1Wk: GetBusinessDay([DateField],5)

I'm pretty much at a loss.
 
D

Dirk Goldgar

Christina Wise via AccessMonster.com said:
Thanks. I tried that and it seemed to satisfy the debugger. The
problem is when I went back in to try to write a query using my new
function, it gives me the following Microsoft error:

3078 The Microsoft Jet database engine cannot find the input table or
query 'tblHolidays'. Make sure it exists and that its name is spelled
correctly.

That is followed by this error:
91:Object variable or withblock variable not set

I checked, the table "Holidays" is spelled correctly. The query I'm
writing is pretty straightforward:

Date1Wk: GetBusinessDay([DateField],5)

I'm pretty much at a loss.

You say you created a table named "Holidays", but the code you posted is
looking for a table named "tblHolidays".
 
R

RuralGuy

Christina Wise via AccessMonster.com said:
Thanks. I tried that and it seemed to satisfy the debugger. The
problem is when I went back in to try to write a query using my new
function, it gives me the following Microsoft error:

3078 The Microsoft Jet database engine cannot find the input table or
query 'tblHolidays'. Make sure it exists and that its name is spelled
correctly.

That is followed by this error:
91:Object variable or withblock variable not set

I checked, the table "Holidays" is spelled correctly. The query I'm
writing is pretty straightforward:

Date1Wk: GetBusinessDay([DateField],5)

I'm pretty much at a loss.

Not a problem Christina. The code you posted in the original post
is looking for a table named "tblHolidays" not one named "Holidays".

I believe if you simply rename your "Holidays" table to "tblHolidays"
everything should begin to work!

HTH
 
C

Christina Wise via AccessMonster.com

That did the trick. I've been looking at this thing for way to long today and
somehow missed the naming aspect. Thank you both SO much. You're lifesavers.
I really appreciate the quick help.

Christina.
 
R

RuralGuy

Christina Wise via AccessMonster.com said:
That did the trick. I've been looking at this thing for way to long
today and somehow missed the naming aspect. Thank you both SO much.
You're lifesavers. I really appreciate the quick help.

Christina.

You're very welcome from both of us. We appreciate you posting back
with your success.
 
G

Guest

The content here would be great but----

I have Access 2k2 and cannot seem to connect to the dao library (it is where
it should be). How do I connect tro the libraries mentioned?

RuralGuy said:
Christina Wise wrote:

[snip]
I am using Access2000. I tried going into “References†on the “Toolsâ€
menu but was not sure which DAO library to select or if this was even
what was causing the problemThe options were:
Microsoft DAO 2.5/3.5 Compatibility Library
Microsoft DAO 3.51 Object Library
Microsoft DAO 3.6 Object Library

[snip]

Even though my guess is any of those libraries would clear the error,
if you havent already done so, put a check next to the latest (3.6).

HTH

--
RuralGuy

Please reply to the newsgroup so all may benefit.
n
 
D

Douglas J Steele

Go into Tools | References (when you're in the VB Editor), scroll through
the list and select "Microsoft DAO 3.6 Object Library"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


MarkB said:
The content here would be great but----

I have Access 2k2 and cannot seem to connect to the dao library (it is where
it should be). How do I connect tro the libraries mentioned?

RuralGuy said:
Christina Wise wrote:

[snip]
I am using Access2000. I tried going into "References" on the "Tools"
menu but was not sure which DAO library to select or if this was even
what was causing the problemThe options were:
Microsoft DAO 2.5/3.5 Compatibility Library
Microsoft DAO 3.51 Object Library
Microsoft DAO 3.6 Object Library

[snip]

Even though my guess is any of those libraries would clear the error,
if you havent already done so, put a check next to the latest (3.6).

HTH

--
RuralGuy

Please reply to the newsgroup so all may benefit.
n
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top