Recordset Error in VBA at line Marked ****

[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.

 

[pr2-eugin]

Try Indenting your CODE, that will help you identify your problem in seconds. The problem I think is because you might have used an NON-UPDATABLE query. Try the Query in a simple Query window. If you can edit data for the returned recordset, then there is something else wrong. If you cannot there is your answer !

 

[namliam]

Holy unformated code Paul

 

[pr2-eugin]

Holy unformated code Paul

I know !

Posters should atleast try to give code that is readable to help them swiftly.

 

[JHB]

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

Be aware of, you need to declare the type for each variable, else they are by default declared as variant type.
So in the above only:

• rspurchaseord is declared as DAO.Recordset
• totamt is declared as Integer
• filepath is declared as String

the rest are variant type.