Date Error with Windows 7

[David W. Fenton]

I just did a search on ADODB.Recordset in my db and have
174 entries, how do I go about changing them all to DAO.Recordset?

Perhaps it would make more sense to remove DAO?

 

[Bob Vance]

Thanks Douglas, Where do I start reading up about changing to DAO?
Seem to have trouble with "New Record" or New
Regards Bob

 

[Bob Vance]

Thanks David, But I do have a DAO Script in my db a Security feature from
KeyedAccess
How big a job would it be to change?
Most of them are repeated codes? like this
Regards Bob
-----------------------
Dim recInvoiceItmdt As New ADODB.Recordset

recInvoiceItmdt.Open "SELECT IntermediateID FROM tblInvoice_Itmdt WHERE
dtDate BETWEEN #" _
& Format(Forms!frmModify!lstModify.Column(0), "mm/dd/yyyy") & "# AND #"
_
& Format(Forms!frmModify!lstModify.Column(1), "mm/dd/yyyy") & "# AND
HorseID=" _
& val(Forms!frmModify!lstModify.Column(2)) & " ORDER BY IntermediateID",
CurrentProject.Connection, adOpenDynamic, adLockOptimistic

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim lngRecCount As Long
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset(pstrTableOrQueryName, dbOpenForwardOnly)
lngRecCount = 0
Do While Not rst.EOF
lngRecCount = lngRecCount + 1
If lngRecCount > lngRecCountToCheck Then
intRtn = True
Exit Do
End If
rst.MoveNext
-------------------------------------------
Private Sub cbDailyCharge4_AfterUpdate()
Dim recRate As New ADODB.Recordset
Set recRate = CurrentProject.Connection.Execute("SELECT Rate FROM
tblServiceInfo WHERE ServiceInfo LIKE '" & cbDailyCharge4.value & "'")
tbDailyChargeRate4.value = recRate.Fields("Rate")
tbDailyChargeRate4_AfterUpdate
End Sub

 

[Douglas J. Steele]

What's the code that's failing, and what's it supposed to do?

 

[Bob Vance]

What's the code that's failing, and what's it supposed to do?

Thanks Douglas..........Bob

'Code TO Distribute Charges Into Owners
Private Sub subSetInvoiceValues()

Dim recInvoice As DAO.Recordset
'***********This line is Failing Below***************
Set recInvoice = New DAO.Recordset
Dim recInvoice_ItMdt As ADODB.Recordset
Set recInvoice_ItMdt = New ADODB.Recordset
Set recInvoice = New ADODB.Recordset
Dim lngInvoiceID As Long
Dim lngInvoiceNo As Long
Dim lngIntermediateID As Long
recInvoice_ItMdt.Open "Select * from tblInvoice_ItMdt;",
CurrentProject.Connection, adOpenDynamic, adLockOptimistic

recInvoice.Open "Select * from tblInvoice;", CurrentProject.Connection,
adOpenDynamic, adLockOptimistic
lngInvoiceID = Nz(DMax("InvoiceID", "tblInvoice"), 1) + 1

lngInvoiceNo = Nz(DMax("InvoiceNo", "tblInvoice"), 1) + 1


If recInvoice.BOF = False And recInvoice.EOF = False Then
recInvoice.MoveLast
End If

recInvoice.AddNew
Dim lngItMdt As Long
If recInvoice_ItMdt.BOF = False And recInvoice_ItMdt.EOF = False Then
lngItMdt = recInvoice_ItMdt.Fields("IntermediateID")
End If
Do While Not recInvoice_ItMdt.EOF = True
lngIntermediateID = recInvoice_ItMdt.Fields("IntermediateID")

With recInvoice


Dim recHorseOwners As New ADODB.Recordset, curOwnerPercentAmount As
Currency
Dim curTotal As Currency, curGSTContentsValue As Currency

recHorseOwners.Open "SELECT OwnerID,OwnerPercent FROM
tblHorseDetails" _
& " WHERE HorseID=" _
& Nz(val(recInvoice_ItMdt.Fields("HorseID")), 0) _
& " AND OwnerID > 0 AND Invoicing = False ORDER BY OwnerID ",
CurrentProject.Connection, adOpenDynamic, adLockOptimistic

If recHorseOwners.EOF = True And recHorseOwners.BOF = True Then
recHorseOwners.Close
Set recHorseOwners = Nothing
MsgBox "This Horse Has No Owner At ALL.", vbApplicationModal +
vbOKOnly + vbInformation

.Fields("InvoiceID") = lngInvoiceID
.Fields("HorseID") = Nz(val(recInvoice_ItMdt.Fields("HorseID")),
0)
.Fields("HorseName") = Nz(recInvoice_ItMdt.Fields("HorseName"),
"")
.Fields("FatherName") =
Nz(recInvoice_ItMdt.Fields("FatherName"), "")
.Fields("MotherName") =
Nz(recInvoice_ItMdt.Fields("MotherName"), "")

.Fields("DateOfBirth") =
Format(CDate(recInvoice_ItMdt.Fields("DateOfBirth")), "mm/dd/yyyy")

.Fields("HorseDetailInfo") =
recInvoice_ItMdt.Fields("FatherName") _
& "--" & recInvoice_ItMdt.Fields("MotherName") & "--" _
& funCalcAge(Format(recInvoice_ItMdt.Fields("DateOfBirth") _
, "dd-mmm-yyyy"), Format("01-Aug-" & Year(Now()),
"dd-mmm-yyyy"), 1) _
& "-" & recInvoice_ItMdt.Fields("Sex")

.Fields("Sex") = recInvoice_ItMdt.Fields("Sex")
.Fields("GSTOptionsText") =
Nz(recInvoice_ItMdt.Fields("GSTOptionsText"), 0)
.Fields("GSTOptionsValue") =
Nz(recInvoice_ItMdt.Fields("GSTOptionsValue"), 0)
.Fields("SubTotal") = Nz(recInvoice_ItMdt.Fields("SubTotal"), 0)
.Fields("TotalAmount") =
Nz(recInvoice_ItMdt.Fields("TotalAmount"), 0)
.Fields("InvoiceDate") = Format(Now(), "dd/mm/yyyy")
End If
recHorseOwners.MoveFirst
Do Until recHorseOwners.EOF = True

If lngIntermediateID > lngItMdt Then
.AddNew
lngInvoiceID = lngInvoiceID + 1
lngInvoiceNo = lngInvoiceNo + 1
lngItMdt = lngIntermediateID
End If
Dim recOwnersInfo As New ADODB.Recordset


recOwnersInfpen "SELECT OwnerID," _
&
"IIf(isnull(tblOwnerInfwnerTitle),'',tblOwnerInfwnerTitle & ' ')" _
& " & IIf(isnull(tblOwnerInfwnerLastName),'
',tblOwnerInfwnerLastName & ', ')" _
& " &
IIf(isnull(tblOwnerInfwnerFirstName),'',tblOwnerInfwnerFirstName) AS
Name " _
& ",OwnerAddress " _
& "FROM tblOwnerInfo WHERE OwnerID=" _
& val(recHorseOwners.Fields("OwnerID")) _
, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
If recOwnersInfo.EOF = True And recOwnersInfo.BOF = True Then
recOwnersInfo.Close
Set recOwnersInfo = Nothing
Else
'
curTotal = DSum("TotalAmount", "tblInvoice_ItMdt",
"HorseID=" _
& Nz(recInvoice_ItMdt.Fields("HorseID"), 0))
curOwnerPercentAmount = IIf(recHorseOwners.Fields _
("OwnerPercent") = "" Or _
IsNull(recHorseOwners.Fields("OwnerPercent")), 0,
Format(curTotal, "#0.00") _
* recHorseOwners.Fields("OwnerPercent"))

.Fields("OwnerID") = recOwnersInfo.Fields("OwnerID")


.Fields("OwnerName") = recOwnersInfo.Fields("Name")


.Fields("OwnerAddress") =
recOwnersInfo.Fields("OwnerAddress")
.Fields("OwnerPercent") = IIf(recHorseOwners.Fields _
("OwnerPercent") = "" Or IsNull(recHorseOwners.Fields _
("OwnerPercent")), 0, recHorseOwners.Fields("OwnerPercent"))

.Fields("OwnerPercentAmount") =
Format(curOwnerPercentAmount, "#0.00")

curGSTContentsValue = (Format(curOwnerPercentAmount,
"#0.00") / 9)
.Fields("GSTContentsValue") = Format(curGSTContentsValue,
"#0.00")

If Format(curGSTContentsValue, "#0.00") > 0 Then
.Fields("GSTContentsText") = "Tax Contents"
ElseIf Format(curGSTContentsValue, "#0.00") < 0 Then
.Fields("GSTContentsText") = "Credit"
Else
.Fields("GSTContentsText") = ""
End If
End If
recOwnersInfo.Close
Set recOwnersInfo = Nothing

.Fields("InvoiceNo") = lngInvoiceNo


.Fields("InvoiceID") = lngInvoiceID
.Fields("HorseID") = recInvoice_ItMdt.Fields("HorseID")
.Fields("HorseName") = recInvoice_ItMdt.Fields("HorseName")
.Fields("FatherName") = recInvoice_ItMdt.Fields("FatherName")
.Fields("MotherName") = recInvoice_ItMdt.Fields("MotherName")
.Fields("DateOfBirth") =
Format(CDate(Nz(recInvoice_ItMdt.Fields("DateOfBirth"), 0)), "mm/dd/yyyy")

.Fields("HorseDetailInfo") =
recInvoice_ItMdt.Fields("FatherName") _
& "--" & recInvoice_ItMdt.Fields("MotherName") & "--" _
& funCalcAge(Format(Nz(recInvoice_ItMdt.Fields("DateOfBirth"),
0) _
, "dd-mmm-yyyy"), Format("01-Aug-" & Year(Now()),
"dd-mmm-yyyy"), 1) _
& "-" & recInvoice_ItMdt.Fields("Sex")

.Fields("Sex") = recInvoice_ItMdt.Fields("Sex")
.Fields("GSTOptionsText") =
Nz(recInvoice_ItMdt.Fields("GSTOptionsText"), 0)
.Fields("GSTOptionsValue") =
Nz(recInvoice_ItMdt.Fields("GSTOptionsValue"), 0)
.Fields("SubTotal") = Nz(recInvoice_ItMdt.Fields("SubTotal"), 0)
.Fields("TotalAmount") =
Nz(recInvoice_ItMdt.Fields("TotalAmount"), 0)
.Fields("InvoiceDate") = Format(Now(), "dd/mm/yyyy")


Application.SysCmd acSysCmdSetStatus, "Invoice No=" &
..Fields("InvoiceNo") _
& " Horse Name=" & .Fields("HorseName") & " Owner Name=" _
& .Fields("OwnerName")

funSetInvoiceDetailValues lngIntermediateID, lngInvoiceID,
lngInvoiceNo
.Fields("CompanyID") = DLookup("CompanyID", "tblCompanyInfo")
recInvoice.Update
.Requery
recHorseOwners.MoveNext
If recHorseOwners.EOF = False Then
.AddNew
lngInvoiceID = lngInvoiceID + 1
lngInvoiceNo = lngInvoiceNo + 1

End If
Loop
.Update
End With
CurrentProject.Connection.Execute "Delete * from tblAddition_ItMdt where
IntermediateID=" _
& lngIntermediateID

CurrentProject.Connection.Execute "Delete * from tblDaily_ItMdt where
IntermediateID=" _
& lngIntermediateID

recHorseOwners.Close
recInvoice_ItMdt.MoveNext
Loop

Set recHorseOwners = Nothing

CurrentProject.Connection.Execute "Delete * from tblInvoice_ItMdt;"
[Forms]![frmMain]![subfrmDisList].Form!lstModify.Requery

End Sub

 

[Bob Vance]

Douglas, The coder that help me develop my db said DAO wont work with Win7
and will get outdataed . Is this correct??
Regards Bob

 

[Douglas J. Steele]

The coder is very misinformed.

Not only does DAO still work in Win 7, but it's part of the Microsoft Office
12.0 (and Office 14.0) Access database engine object library.

On the other hand, development work stopped on ADO quite some time ago.

 

[Douglas J. Steele]

Unlike ADO recordsets, DAO recordset objects cannot exist "stand alone",
which is essentially what you're trying to do.

You can only instantiate a DAO recordset by using the OpenRecordset method.

Revisit the example I posted earlier for the correct syntax.

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

What's the code that's failing, and what's it supposed to do?

Thanks Douglas..........Bob

'Code TO Distribute Charges Into Owners
Private Sub subSetInvoiceValues()

Dim recInvoice As DAO.Recordset
'***********This line is Failing Below***************
Set recInvoice = New DAO.Recordset
Dim recInvoice_ItMdt As ADODB.Recordset
Set recInvoice_ItMdt = New ADODB.Recordset
Set recInvoice = New ADODB.Recordset
Dim lngInvoiceID As Long
Dim lngInvoiceNo As Long
Dim lngIntermediateID As Long
recInvoice_ItMdt.Open "Select * from tblInvoice_ItMdt;",
CurrentProject.Connection, adOpenDynamic, adLockOptimistic

recInvoice.Open "Select * from tblInvoice;", CurrentProject.Connection,
adOpenDynamic, adLockOptimistic
lngInvoiceID = Nz(DMax("InvoiceID", "tblInvoice"), 1) + 1

lngInvoiceNo = Nz(DMax("InvoiceNo", "tblInvoice"), 1) + 1


If recInvoice.BOF = False And recInvoice.EOF = False Then
recInvoice.MoveLast
End If

recInvoice.AddNew
Dim lngItMdt As Long
If recInvoice_ItMdt.BOF = False And recInvoice_ItMdt.EOF = False Then
lngItMdt = recInvoice_ItMdt.Fields("IntermediateID")
End If
Do While Not recInvoice_ItMdt.EOF = True
lngIntermediateID = recInvoice_ItMdt.Fields("IntermediateID")

With recInvoice


Dim recHorseOwners As New ADODB.Recordset, curOwnerPercentAmount As
Currency
Dim curTotal As Currency, curGSTContentsValue As Currency

recHorseOwners.Open "SELECT OwnerID,OwnerPercent FROM
tblHorseDetails" _
& " WHERE HorseID=" _
& Nz(val(recInvoice_ItMdt.Fields("HorseID")), 0) _
& " AND OwnerID > 0 AND Invoicing = False ORDER BY OwnerID ",
CurrentProject.Connection, adOpenDynamic, adLockOptimistic

If recHorseOwners.EOF = True And recHorseOwners.BOF = True Then
recHorseOwners.Close
Set recHorseOwners = Nothing
MsgBox "This Horse Has No Owner At ALL.", vbApplicationModal +
vbOKOnly + vbInformation

.Fields("InvoiceID") = lngInvoiceID
.Fields("HorseID") =
Nz(val(recInvoice_ItMdt.Fields("HorseID")), 0)
.Fields("HorseName") = Nz(recInvoice_ItMdt.Fields("HorseName"),
"")
.Fields("FatherName") =
Nz(recInvoice_ItMdt.Fields("FatherName"), "")
.Fields("MotherName") =
Nz(recInvoice_ItMdt.Fields("MotherName"), "")

.Fields("DateOfBirth") =
Format(CDate(recInvoice_ItMdt.Fields("DateOfBirth")), "mm/dd/yyyy")

.Fields("HorseDetailInfo") =
recInvoice_ItMdt.Fields("FatherName") _
& "--" & recInvoice_ItMdt.Fields("MotherName") & "--" _
& funCalcAge(Format(recInvoice_ItMdt.Fields("DateOfBirth") _
, "dd-mmm-yyyy"), Format("01-Aug-" & Year(Now()),
"dd-mmm-yyyy"), 1) _
& "-" & recInvoice_ItMdt.Fields("Sex")

.Fields("Sex") = recInvoice_ItMdt.Fields("Sex")
.Fields("GSTOptionsText") =
Nz(recInvoice_ItMdt.Fields("GSTOptionsText"), 0)
.Fields("GSTOptionsValue") =
Nz(recInvoice_ItMdt.Fields("GSTOptionsValue"), 0)
.Fields("SubTotal") = Nz(recInvoice_ItMdt.Fields("SubTotal"),
0)
.Fields("TotalAmount") =
Nz(recInvoice_ItMdt.Fields("TotalAmount"), 0)
.Fields("InvoiceDate") = Format(Now(), "dd/mm/yyyy")
End If
recHorseOwners.MoveFirst
Do Until recHorseOwners.EOF = True

If lngIntermediateID > lngItMdt Then
.AddNew
lngInvoiceID = lngInvoiceID + 1
lngInvoiceNo = lngInvoiceNo + 1
lngItMdt = lngIntermediateID
End If
Dim recOwnersInfo As New ADODB.Recordset


recOwnersInfpen "SELECT OwnerID," _
&
"IIf(isnull(tblOwnerInfwnerTitle),'',tblOwnerInfwnerTitle & ' ')" _
& " & IIf(isnull(tblOwnerInfwnerLastName),'
',tblOwnerInfwnerLastName & ', ')" _
& " &
IIf(isnull(tblOwnerInfwnerFirstName),'',tblOwnerInfwnerFirstName) AS
Name " _
& ",OwnerAddress " _
& "FROM tblOwnerInfo WHERE OwnerID=" _
& val(recHorseOwners.Fields("OwnerID")) _
, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
If recOwnersInfo.EOF = True And recOwnersInfo.BOF = True Then
recOwnersInfo.Close
Set recOwnersInfo = Nothing
Else
'
curTotal = DSum("TotalAmount", "tblInvoice_ItMdt",
"HorseID=" _
& Nz(recInvoice_ItMdt.Fields("HorseID"), 0))
curOwnerPercentAmount = IIf(recHorseOwners.Fields _
("OwnerPercent") = "" Or _
IsNull(recHorseOwners.Fields("OwnerPercent")), 0,
Format(curTotal, "#0.00") _
* recHorseOwners.Fields("OwnerPercent"))

.Fields("OwnerID") = recOwnersInfo.Fields("OwnerID")


.Fields("OwnerName") = recOwnersInfo.Fields("Name")


.Fields("OwnerAddress") =
recOwnersInfo.Fields("OwnerAddress")
.Fields("OwnerPercent") = IIf(recHorseOwners.Fields _
("OwnerPercent") = "" Or IsNull(recHorseOwners.Fields _
("OwnerPercent")), 0,
recHorseOwners.Fields("OwnerPercent"))

.Fields("OwnerPercentAmount") =
Format(curOwnerPercentAmount, "#0.00")

curGSTContentsValue = (Format(curOwnerPercentAmount,
"#0.00") / 9)
.Fields("GSTContentsValue") = Format(curGSTContentsValue,
"#0.00")

If Format(curGSTContentsValue, "#0.00") > 0 Then
.Fields("GSTContentsText") = "Tax Contents"
ElseIf Format(curGSTContentsValue, "#0.00") < 0 Then
.Fields("GSTContentsText") = "Credit"
Else
.Fields("GSTContentsText") = ""
End If
End If
recOwnersInfo.Close
Set recOwnersInfo = Nothing

.Fields("InvoiceNo") = lngInvoiceNo


.Fields("InvoiceID") = lngInvoiceID
.Fields("HorseID") = recInvoice_ItMdt.Fields("HorseID")
.Fields("HorseName") = recInvoice_ItMdt.Fields("HorseName")
.Fields("FatherName") = recInvoice_ItMdt.Fields("FatherName")
.Fields("MotherName") = recInvoice_ItMdt.Fields("MotherName")
.Fields("DateOfBirth") =
Format(CDate(Nz(recInvoice_ItMdt.Fields("DateOfBirth"), 0)), "mm/dd/yyyy")

.Fields("HorseDetailInfo") =
recInvoice_ItMdt.Fields("FatherName") _
& "--" & recInvoice_ItMdt.Fields("MotherName") & "--" _
& funCalcAge(Format(Nz(recInvoice_ItMdt.Fields("DateOfBirth"),
0) _
, "dd-mmm-yyyy"), Format("01-Aug-" & Year(Now()),
"dd-mmm-yyyy"), 1) _
& "-" & recInvoice_ItMdt.Fields("Sex")

.Fields("Sex") = recInvoice_ItMdt.Fields("Sex")
.Fields("GSTOptionsText") =
Nz(recInvoice_ItMdt.Fields("GSTOptionsText"), 0)
.Fields("GSTOptionsValue") =
Nz(recInvoice_ItMdt.Fields("GSTOptionsValue"), 0)
.Fields("SubTotal") = Nz(recInvoice_ItMdt.Fields("SubTotal"),
0)
.Fields("TotalAmount") =
Nz(recInvoice_ItMdt.Fields("TotalAmount"), 0)
.Fields("InvoiceDate") = Format(Now(), "dd/mm/yyyy")


Application.SysCmd acSysCmdSetStatus, "Invoice No=" &
.Fields("InvoiceNo") _
& " Horse Name=" & .Fields("HorseName") & " Owner Name=" _
& .Fields("OwnerName")

funSetInvoiceDetailValues lngIntermediateID, lngInvoiceID,
lngInvoiceNo
.Fields("CompanyID") = DLookup("CompanyID", "tblCompanyInfo")
recInvoice.Update
.Requery
recHorseOwners.MoveNext
If recHorseOwners.EOF = False Then
.AddNew
lngInvoiceID = lngInvoiceID + 1
lngInvoiceNo = lngInvoiceNo + 1

End If
Loop
.Update
End With
CurrentProject.Connection.Execute "Delete * from tblAddition_ItMdt
where IntermediateID=" _
& lngIntermediateID

CurrentProject.Connection.Execute "Delete * from tblDaily_ItMdt where
IntermediateID=" _
& lngIntermediateID

recHorseOwners.Close
recInvoice_ItMdt.MoveNext
Loop

Set recHorseOwners = Nothing

CurrentProject.Connection.Execute "Delete * from tblInvoice_ItMdt;"
[Forms]![frmMain]![subfrmDisList].Form!lstModify.Requery

End Sub

 

[David W. Fenton]

How big a job would it be to change?
Most of them are repeated codes?

It depends entirely on the specifics, so I can't actually say. If
most of it is stuff that overlaps between ADO/DAO, then it's no big
deal. On the other hand, if the code is doing things that are done
differently, it's another matter entirely.

 

[David W. Fenton]

But I do have a DAO Script in my db a Security feature from
KeyedAccess

You could switch this one thing to late binding, since you can
always use the DBEngine object as your top-level DAO
starting point. You would then use Object variables instead of types
specific to the DAO library.

If I were handed a database like yours I'd decide whether to commit
to an ADO or DAO reference based on whether or not I was responsible
for just maintaining the app, or if I was being asked to extend it.
If the former, I'd convert the one DAO function to late binding, and
keep only the ADO reference. If the latter, I'd take the time to
convert all the ADO do DAO.

 

[mansore511]

Hmm. I'd think that wouldn't compile unless you still had the
reference. Of course, if the reference is still there, that would
explain why olMailItem doesn't throw a compile error.