Date Error with Windows 7

[Bob Vance]

All my Dates in my friends access db are erroring now that my friend has
windows 7 Installed, Has anyone heard about this???

 

[John W. Vinson]

All my Dates in my friends access db are erroring now that my friend has
windows 7 Installed, Has anyone heard about this???

This appears to be the very common References bug. Open any
module in design view, or open the VBA editor by typing
Ctrl-G. Select Tools... References from the menu. One of the
..DLL files required by Access will probably be marked
MISSING. Uncheck it, recheck it, close and open Access.

If none are MISSING, check any reference; close and open
Access; then uncheck it again. This will force Access to
relink the libraries.

It may be that the .DLL files have different names or a different location in
Windows 7.

 

[Jerry Whittle]

Anytime I see a problem with the Date() function, I have a knee-jerk reaction
about references. Go to the following web page for information on checking
references:

http://www.mvps.org/access/bugs/bugs0001.htm

 

[Bob Vance]

Thanks John, My db on my machine is fine with XP, My db on my friends
machine is Runtime Windows 7 will that make a difference??
Regadrs Bob

 

[Bob Vance]

Going by my friends screen shoots of my db, It is erroring
Function is not available in Expressions in query expression
Format([HorseDate],"dd-mmm-yy".
12 errors and they all finish with a dot and not a bracket is this normal???
Regards Bob

 

[John W. Vinson]

Going by my friends screen shoots of my db, It is erroring
Function is not available in Expressions in query expression
Format([HorseDate],"dd-mmm-yy".
12 errors and they all finish with a dot and not a bracket is this normal???
Regards Bob

I have no idea; I don't have Win7 and have no immediate intention to install
it.

 

[Bob Vance]

Thanks John but what happens when you buy a new machine that has win7 pre
loaded
Regards Bob

Going by my friends screen shoots of my db, It is erroring
Function is not available in Expressions in query expression
Format([HorseDate],"dd-mmm-yy".
12 errors and they all finish with a dot and not a bracket is this
normal???
Regards Bob

I have no idea; I don't have Win7 and have no immediate intention to
install
it.

 

[John W. Vinson]

Thanks John but what happens when you buy a new machine that has win7 pre
loaded

I'll cross that bridge when I get to it.

Sorry if my response sounded like criticism of you - that wasn't the intent at
all! I'm simply saying that "I Don't Know" and can't give you any advice,
because I don't have Win7 and cannot do any testing. Apologies if it came
across as anything other than that!

 

[David W. Fenton]

Going by my friends screen shoots of my db, It is erroring
Function is not available in Expressions in query expression
Format([HorseDate],"dd-mmm-yy".
12 errors and they all finish with a dot and not a bracket is this
normal??? Regards Bob

I have no idea; I don't have Win7 and have no immediate intention
to install it.

Windows 7 does not cause any more reference problems than any other
version of Windows.

If everyone would avoid adding any references other than the base
three (Access, VBA and DAO), they'd not run into these problems.
Late binding allows the use of all sorts of external libraries
without exposing the app to the broken reference problem.

I can't see why anyone would ever add references to anything other
than those basic three, except perhaps naivete, or laziness.

 

[David W. Fenton]

I'll cross that bridge when I get to it.

Sorry if my response sounded like criticism of you - that wasn't
the intent at all! I'm simply saying that "I Don't Know" and can't
give you any advice, because I don't have Win7 and cannot do any
testing. Apologies if it came across as anything other than that!

If you have an MSDN subscription, I highly recommend that you
download and install Win7. It's the best version of Windows since
Windows 2000, by a longshot, and I'd say it's better than Win2K, as
well. I really love it and am finding my clients do, too.

 

[Bob Vance]

Going by my friends screen shoots of my db, It is erroring
Function is not available in Expressions in query expression
Format([HorseDate],"dd-mmm-yy".
12 errors and they all finish with a dot and not a bracket is this
normal??? Regards Bob

I have no idea; I don't have Win7 and have no immediate intention
to install it.

Windows 7 does not cause any more reference problems than any other
version of Windows.

If everyone would avoid adding any references other than the base
three (Access, VBA and DAO), they'd not run into these problems.
Late binding allows the use of all sorts of external libraries
without exposing the app to the broken reference problem.

I can't see why anyone would ever add references to anything other
than those basic three, except perhaps naivete, or laziness.

Ok my Coder not me as I dont have enough experience with References Changed

Visual Basic For Application
Microsoft Access 12.0 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.1 Library
Microsoft DAO 3.6 Object Library

For these:

Visual Basic For Application
Microsoft Access 12.0 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.1 Library
Microsoft Visual Basic for Application Extensilbilty 5.3
Microsoft Outlook 12 Object Library
Microsoft Office 12.0 Access database engine Object Library

I have added a email code to outlook and I suspect one of these 3 new
references is not liked by Win7 !!
I am currently testing with my friend with Win7 which one it is
Regards Bob

 

[Douglas J. Steele]

Ok my Coder not me as I dont have enough experience with References
Changed

Visual Basic For Application
Microsoft Access 12.0 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.1 Library
Microsoft DAO 3.6 Object Library

For these:

Visual Basic For Application
Microsoft Access 12.0 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.1 Library
Microsoft Visual Basic for Application Extensilbilty 5.3
Microsoft Outlook 12 Object Library
Microsoft Office 12.0 Access database engine Object Library

I have added a email code to outlook and I suspect one of these 3 new
references is not liked by Win7 !!
I am currently testing with my friend with Win7 which one it is

As David suggests, try using just

- Visual Basic For Application
- Microsoft Access 12.0 Object Library
- Microsoft Office 12.0 Access database engine Object Library

Use Late Binding rather than setting a reference to Microsoft Outlook 12
Object Library.

 

[Bob Vance]

As David suggests, try using just

- Visual Basic For Application
- Microsoft Access 12.0 Object Library
- Microsoft Office 12.0 Access database engine Object Library

Use Late Binding rather than setting a reference to Microsoft Outlook 12
Object Library.

I tried that thanks Douglas but now erroring on: Dim recHorseID As New
ADODB.Recordset

Function funGetHorse(Optional lngInvoiceID As Long = 0, Optional lngHorseID
As Long = 0, Optional bHorse As Boolean = False) As Variant

Dim recHorseID As New ADODB.Recordset, strAge As String, strName As String
Dim recHorseName As New ADODB.Recordset

If lngHorseID = 0 And lngInvoiceID = 0 Then
funGetHorse = ""
Exit Function
End If

If lngHorseID = 0 Then
recHorseID.Open "SELECT HorseID FROM tblInvoice WHERE InvoiceID=" _
& lngInvoiceID, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic

If recHorseID.EOF = True And recHorseID.BOF = True Then
Set recHorseID = Nothing
funGetHorse = ""
Exit Function
End If
lngHorseID = recHorseID.Fields("HorseID")
End If

recHorseName.Open "SELECT * FROM tblHorseInfo WHERE HorseID=" _
& lngHorseID, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

If recHorseName.EOF = True And recHorseName.BOF = True Then
Set recHorseName = Nothing
funGetHorse = ""
Exit Function
End If

If IsNull(recHorseName.Fields("HorseName")) Or
recHorseName.Fields("HorseName") = "" Then

'If flg is true and Horse Name is null then Horse Name is set as
blank.
If bHorse = False Then
If IsNull(recHorseName.Fields("DateOfBirth")) Or
recHorseName.Fields("DateOfBirth") = "" Then
strAge = "0yo"
Else
strAge = funCalcAge(Format(CDate("01-Aug-" &
recHorseName.Fields("DateOfBirth")), "dd-mmm-yyyy"), Format(Now(),
"dd-mmm-yyyy"), 1)
End If
strName = Nz(recHorseName.Fields("FatherName"), "") & " -- " &
Nz(recHorseName.Fields("MotherName"), "") _
& " " & strAge & " " & Nz(recHorseName.Fields("Sex"), "")

Else
strName = ""
End If
Else
strName = recHorseName.Fields("HorseName")
End If

Set recHorseID = Nothing
Set recHorseName = Nothing
funGetHorse = strName
End Function

 

[Douglas J. Steele]

I tried that thanks Douglas but now erroring on: Dim recHorseID As New
ADODB.Recordset

Is there a specific reason you're using ADO? (DAO is better when dealing
with Jet databases)

If you feel you must use ADO, add a reference to Microsoft ActiveX Data
Objects 2.1 Library. If you're not using DAO, you can remove the reference
to Microsoft Office 12.0 Access database engine Object Library

 

[David W. Fenton]

I tried that thanks Douglas but now erroring on: Dim recHorseID
As New ADODB.Recordset

You are using ADO for that function, but aren't doing anything with
ADo that's not trivially converted to DAO.

DAO is preferred and you should use ADO only for things that DAO
*cannot* do. And in that case, I'd use late binding, and use the
Connection object of the Application object so I didn't need to even
use CreateObject.

 

[David W. Fenton]

Visual Basic For Application
Microsoft Access 12.0 Object Library
OLE Automation

Have you looked at this library's content in the object viewer? It
offers virtually nothing useful, and I don't understand why MS has
made it one of the default references starting at least in A2003 (I
always remove it immediately).

Microsoft ActiveX Data Objects 2.1 Library

I've discussed this in another reply, but I would say to avoid it
because it duplicateds 99% of the functionality of ADO. Also, 2.1 is
an old version.

Microsoft DAO 3.6 Object Library

For these:

Visual Basic For Application
Microsoft Access 12.0 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.1 Library
Microsoft Visual Basic for Application Extensilbilty 5.3

Why do you think you need this one?

Microsoft Outlook 12 Object Library

Use late binding with Outlook to avoid needing a reference. If you
do that, all your non-Outlook code will run on machines without
Outlook installed.

Microsoft Office 12.0 Access database engine Object Library

This last is just DAO for A2007.

 

[Bob Vance]

Why do you think you need this one?


Use late binding with Outlook to avoid needing a reference. If you
do that, all your non-Outlook code will run on machines without
Outlook installed.


This last is just DAO for A2007.

Thanks David , I removed "Microsoft Visual Basic for Application
Extensilbilty 5.3" and my email output worked fine!
My Output seems to be CreateItem, can I change this code for Late Binding
maybe CreateObject??
Regards Bob
--------------------------------------------------------
Private Sub SendMailButton_Click()

On Error GoTo ErrorHandler
If Me.Dirty = True Then
Me.Dirty = False
Dim myfile1 As String, myfile2 As String
End If
Dim mydir As String
mydir = Left(CurrentDb.Name, Len(CurrentDb.Name) -
Len(Dir(CurrentDb.Name)))
Dim lngID As Long, strMail As String, strBodyMsg As String, _
blEditMail As Boolean, sndReport As String, strCompany As String
Dim msgPmt As String, msgBtns As Integer, msgTitle As String, msgResp As
Integer, tbAmount As String
Dim strFormat As String
Dim mytot As Long
mytot = DCount("[InvoiceID]", "qrySelInvoices", "")

Select Case Me.tbEmailOption.value

Case "ADOBE"
strFormat = acFormatPDF
myfile1 = mydir & "Statement.pdf"
myfile2 = mydir & "Invoice.pdf"
Case "WORD"
strFormat = acFormatRTF
myfile1 = mydir & "Statement.rtf"
myfile2 = mydir & "Invoice.rtf"

Case "SNAPSHOT"
strFormat = acFormatSNP
myfile1 = mydir & "Statement.SNP"
myfile2 = mydir & "Invoice.SNP"

Case "TEXT"
strFormat = acFormatTXT
myfile1 = mydir & "Statement.txt"
myfile2 = mydir & "Invoice.txt"

Case "HTML"
strFormat = acFormatHTML
myfile1 = mydir & "Statement.htm"
myfile2 = mydir & "Invoice.htm"

Case Else ' catch all others
strFormat = acFormatHTML
myfile1 = mydir & "Statement.htm"
myfile2 = mydir & "Invoice.htm"

End Select

Select Case Me.OpenArgs

Case "OwnerStatement"

sndReport = "rptOwnerPaymentMethod"


lngID = Nz(Me.cbOwnerName.Column(0), 0)
strMail = OwnerEmailAddress(lngID)
tbAmount = Nz(Me.cbOwnerName.Column(5), 0)

strBodyMsg = "To: "
strBodyMsg = strBodyMsg & Nz(DLookup("[ClientTitle]",
"tblOwnerInfo", _
"[OwnerID]=" & lngID), " ") & " "
strBodyMsg = strBodyMsg & Nz(DLookup("[OwnerLastName]",
"tblOwnerInfo", _
"[OwnerID]=" & lngID), " Owner")
strBodyMsg = strBodyMsg & "," & Chr(10) & Chr(10) & Chr(13) _
& "Please find attached your Statement, Dated:" & " " &
Format(Date, "d-mmm-yyyy") & Chr(10) & "Your Statement Total: " &
Format(tbAmount, "$ #,##.00") & Chr(10) & Chr(10) &
Nz(DLookup("[EmailMessage]", "tblCompanyInfo"), "") & eMailSignature("Best
Regards", True) & Chr(10) & Chr(10) & DownloadMessage("PDF") _


DoCmd.OutputTo acOutputReport, sndReport, strFormat,
myfile1, False
If mytot > 0 Then
DoCmd.OutputTo acOutputReport, "rptInvoiceModify",
strFormat, myfile2, False
End If

CurrentDb.Execute "UPDATE tblOwnerInfo " & _
"SET EmailDateState = Now() " & _
"WHERE OwnerID = " & lngID, dbFailOnError
Dim myitem As Outlook.MailItem
Dim myout As Outlook.Application
Set myout = New Outlook.Application
Set myitem = myout.CreateItem(olMailItem)
With myitem
.To = strMail
.CC = Nz(DLookup("EmailCC", "tblOwnerInfo", "OwnerID = " & lngID), "")
.Bcc = Nz(DLookup("EmailBCC", "tblOwnerInfo", "OwnerID = " & lngID), "")
.Subject = "Your Statement" & " / " & Nz(DLookup("[CompanyName]",
"tblCompanyInfo"))
.Body = strBodyMsg 'EditMessage:=blEditMail
.Attachments.Add myfile1
If mytot > 0 Then
.Attachments.Add myfile2
End If
On Error Resume Next
.Send
On Error GoTo ErrorHandler
End With
Set myitem = Nothing
Set myout = Nothing
cbOwnerName.SetFocus

Case Else
Exit Sub

End Select
ExitProc:
Exit Sub
ErrorHandler:

msgTitle = "Untrapped Error"
msgBtns = vbExclamation

Select Case Err.Number
'User cancelled message (2293 & 2296 are raised
'by Outlook, not Outlook Express).
Case 2501, 2293, 2296
Case Else
MsgBox "Error Number: " & Err.Number & Chr(13) _
& "Description: " & Err.Description & Chr(13) & Chr(13) _
& "(frmBillStatement SendMailButton_Click)", msgBtns, msgTitle
End Select

Resume ExitProc

End Sub

 

[David W. Fenton]

Dim myitem As Outlook.MailItem
Dim myout As Outlook.Application
Set myout = New Outlook.Application
Set myitem = myout.CreateItem(olMailItem)

Change these to this:

Dim myitem As Object
Dim myout As Object
Set myout = CreateObject(Outlook.Application, "localhost")
Set myitem = myout.CreateItem(olMailItem)

....and hyou should be set.

The key is that you don't use any of the Outlook data types, and use
object variables instead.

 

[Douglas J. Steele]

Change these to this:

Dim myitem As Object
Dim myout As Object
Set myout = CreateObject(Outlook.Application, "localhost")
Set myitem = myout.CreateItem(olMailItem)

...and hyou should be set.

The key is that you don't use any of the Outlook data types, and use
object variables instead.

He'll also need to fix the line of code

Set myitem = myout.CreateItem(olMailItem)

Either change it to

Set myitem = myout.CreateItem(0)

or else add a declaration of the olMailItem constant

Const olMailItem As Long = 0

 

[Bob Vance]

He'll also need to fix the line of code

Set myitem = myout.CreateItem(olMailItem)

Either change it to

Set myitem = myout.CreateItem(0)

or else add a declaration of the olMailItem constant

Const olMailItem As Long = 0

Thanks Guys, I did what you said and got this error
Error Number 463
Description: Class not registered on local machine
Regards Bob