Object Required Error in Recordset

[drunkenneo]

Hi Have Written this Code which is Showing Object Required Error in the line which have Asterisk:

Option Compare Database
Option Explicit
Private Sub btGeneration_Click()
'********************REDEMPTION FILE IMPORT***********
On Error GoTo Err_btGeneration_Err:

Dim rs, rsord, rspurchaseord As DAO.Recordset
Dim n, totamt As Integer
Dim strqry, strqry1, strqry2, strqry3, ponum, filepath As String

filepath = "E:\Intel\Redemptions\APAC\Order Processing\MDB\Database Files\Purchase Order\"


'Query for PO to be Generated Partners

strqry = "SELECT partnerDetails.name FROM tempPurchaseOrder INNER JOIN partnerDetails ON tempPurchaseOrder.partnerDetailsId = partnerDetails.partnerDetailsId " & _
"GROUP BY partnerDetails.name, tempPurchaseOrder.purchaseOrderCheck HAVING (((tempPurchaseOrder.purchaseOrderCheck)=True))"


Set rs = CurrentDb.OpenRecordset(strqry, dbOpenDynaset)
rs.MoveLast


If (rs.RecordCount = 0) Then
MsgBox "No PO for Generation..."
Exit Sub
End If

'Query For Purchase Order

rs.MoveFirst
strqry1 = "SELECT purchaseOrder.poId, purchaseOrder.poYear, " & _
"purchaseOrder.poDate, purchaseOrder.userId, purchaseOrder.currencyId, purchaseOrder.poSendDate " & _
"FROM purchaseOrder"

'Query for PO particular Partner


Do Until rs.EOF
n = DMax("poId", "purchaseOrder")

Set rspurchaseord = CurrentDb.OpenRecordset(strqry1)
rspurchaseord.AddNew
rspurchaseord![poId] = n + 1
rspurchaseord![poYear] = Format(Date, "yyyy")
rspurchaseord![poDate] = Date
rspurchaseord![userId] = globalUserId
rspurchaseord![currencyId] = 1
rspurchaseord![poSendDate] = Date
rspurchaseord.Update

ponum = CStr(Format(Date, "yyyy") & "-" & Format(n + 1, "00000"))

strqry2 = "SELECT " & Chr(34) & "M" & Chr(34) & " & Format(orderDetails.internalNo," & Chr(34) & "00000" & Chr(34) & ") as InternalNo , partnerDetails.Name, partnerBiWeekly.Description, " & _
"orderDetails.poNo, orderDetails.totalPOAmt, orderDetails.totalCost, orderDetails.adminCost, orderDetails.shippingCost,  " & _
"orderDetails.vatTax, (Cint(Nz(orderDetails.totalPOAmt," & Chr(34) & "0" & Chr(34) & "))+cint(Nz(orderDetails.totalCost," & Chr(34) & "0" & Chr(34) & "))+Cint(Nz(orderDetails.adminCost," & Chr(34) & "0" & Chr(34) & "))+cint(Nz(orderDetails.shippingCost," & Chr(34) & "0" & Chr(34) & "))+cint(Nz(orderDetails.vatTax," & Chr(34) & "0" & Chr(34) & "))) AS Total, " & _
"orderDetails.deliveredQty, orderDetails.deliveredDate,  " & _
"orderDetails.finalStatus " & _
"FROM ((partnerBiWeekly INNER JOIN ((orderDetails INNER JOIN product ON orderDetails.productId = product.productId) INNER JOIN partnerDetails ON  " & _
"orderDetails.partnerDetailsId = partnerDetails.partnerDetailsId) ON partnerBiWeekly.Id = partnerDetails.Id) INNER JOIN distributor ON orderDetails.distributorId = distributor.distributorId)  " & _
"INNER JOIN tempPurchaseOrder ON partnerDetails.partnerDetailsId = tempPurchaseOrder.partnerDetailsId " & _
"WHERE (((partnerDetails.Name)=" & Chr(34) & rs![name] & Chr(34) & ") AND ((partnerBiWeekly.Description)=" & Chr(34) & "daily" & Chr(34) & ") AND ((orderDetails.poNo) Is Null) AND ((orderDetails.deliveredDate) Is Not Null  " & _
"And (orderDetails.deliveredDate)<[tempPurchaseOrder]![purchaseOrderBillDate]) AND ((InStr([product].[variationSku]," & Chr(34) & "voucher" & Chr(34) & "))=0) AND  " & _
"((InStr([product].[rewardTitle]," & Chr(34) & "ichoose" & Chr(34) & "))=0) AND ((InStr([product].[rewardTitle]," & Chr(34) & "voucher" & Chr(34) & "))=0) AND ((InStr([product].[variationSKU]," & Chr(34) & "ichoose" & Chr(34) & "))=0) AND  " & _
"((InStr([product].[variationTitle]," & Chr(34) & "ichoose" & Chr(34) & "))=0) AND ((InStr([product].[variationTitle]," & Chr(34) & "voucher" & Chr(34) & "))=0) AND ((tempPurchaseOrder.purchaseOrderCheck)=True) AND  " & _
"((orderDetails.purchaseOrderId) Is Null)) OR (((orderDetails.finalStatus)<>" & Chr(34) & "Cancelled" & Chr(34) & ")) OR (((orderDetails.poNo)=" & Chr(34) & "" & Chr(34) & ") AND ((orderDetails.finalStatus)<>" & Chr(34) & "Canceled" & Chr(34) & "))"

Debug.Print strqry2
Set rsord = CurrentDb.OpenRecordset(strqry2, dbOpenDynaset)
totamt = 0
rsord.MoveFirst
Do Until rsord.EOF
totamt = rsord![Total] + totamt
rsord.MoveNext
Loop

rsord.MoveFirst
Do Until rsord.EOF
********rsord.Edit
rsord![orderDetails.poNo] = ponum
rsord![orderDetails.totalPOAmt] = CStr(totamt)
rsord.Update
rsord.MoveNext
Loop





rs.MoveNext
rsord.Close
rspurchaseord.Close
Loop

rs.Close
MsgBox "Details Updated"












'Creating File Saving Path
'Call directory_Making(filepath)


Err_btGeneration_Exit:
    Exit Sub

Err_btGeneration_Err:
MsgBox Err.Number & ": " & Error.Description
Resume Err_btGeneration_Exit:

End Sub

Required Help to solve this Problem.

[ItsMe]

When you make declarations they will be a Variant unless explicitly declared otherwise.

Dim rs, rsord As DAO.Recordset, rspurchaseord As DAO.Recordset

The previous example declares rs as Variant and the other two as DAO.Recordset

[drunkenneo]

When you make declarations they will be a Variant unless explicitly declared otherwise.

Dim rs, rsord As DAO.Recordset, rspurchaseord As DAO.Recordset

The previous example declares rs as Variant and the other two as DAO.Recordset

Does it will b a change in behaviour of recordset as variant

[ItsMe]

The following would require rs to be declared as a recordset.
Set rs = CurrentDb.OpenRecordset(strqry, dbOpenDynaset)
I believe you will want a DAO.Recordset.

[drunkenneo]

I did the Changes but still the message is same, i have a join query in recordset, how to make it editable?

[ItsMe]

I don't understand what a join has to do with an "Object Required" exception.

When you make declarations they will be a Variant unless explicitly declared otherwise.

Did you go through all of your declarations? Are you still getting the same error? What line is it stopping on? Comment out the error trap to debug.
'On Error GoTo Err_btGeneration_Err: