Ok This one is Tuff

[Bob Vance]

If by Chance one of my Horses does not have an Owner when It comes time to
distribute my Invoices I have a major Hang, with a runtime error '3704'
Operation is not allowed when the object is closed. so im looking to add to
my code if no owner just skip or dont distribute that Invoice, there is a
yellow line error code noted below

'Code TO Distribute Charges Into Owners
Private Sub subSetInvoiceValues()
Dim recInvoice As ADODB.Recordset
Set recInvoice = New ADODB.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 = DMax("InvoiceID", "tblInvoice") + 1
lngInvoiceNo = DMax("InvoiceNo", "tblInvoice") + 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 '<***************************Yellow
Line******************************************
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(tblOwnerInfwnerFirstName),'',tblOwnerInfwnerFirstName & '
')" _
& " &
IIf(isnull(tblOwnerInfwnerLastName),'',tblOwnerInfwnerLastName) 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")

'Change the OwnerName field.
.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;"
End Sub

 

[boblarson]

Not so tough -
The error message you are getting is because you have this code in there
before it gets to the MoveFirst command:

recHorseOwners.Close
Set recHorseOwners = Nothing

it can't move to the first record if the object recHorseOwners has been
closed and destroyed.

--
Bob Larson

Free Tutorials and Samples at http://www.btabdevelopment.com

__________________________________

 

[Bob Vance]

Bob, if I take the message box out of the code is it possible that I can
alter it so as if the HorseID has No OwnerID it will just not distribute
that Invoice and not error my db.....Thanks Bob

 

[Bob Vance]

Don't worry Bob I made a label No Visible unless there is a No Client in my
query then they can not click the distribute button because of the label
over top of it!..regards Bob
Me.ListNoClient.Visible = IIf(Me.ListNoClient.ListCount = 0, False, True)