ACCESS FEED: [MS_AccessPros] Reposition Cursor to exact record

Rabu, 25 Mei 2011

[MS_AccessPros] Reposition Cursor to exact record

I am using the following code to update my form base on existing form record

Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim rs1 As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim strSql As String
    Dim lngOrderID As Long


    If IsNull(Me.CustomerCode) Or IsNull(Me.QuotationNo) Or IsNull(Me.QuotationDate) Or IsNull(Me.EmpID) Or Not SalesQuoteContainLines() Then
        MsgBox "Please Complete Quotation before Applied to Sales Order.", vbInformation, "Sales Order Not Completed"
    Exit Sub

    ElseIf MsgBox("Are you sure you want to Apply this Quotation to Sales Order? " & vbCrLf & _
                  "Kindly note that, Once you apply this Quote to Sales Order," & _
                  "it can not be edited, delete or undo.", vbYesNo + vbInformation, "Applied to Sales Order") = vbYes Then

    Me!StatusID = Applied_To_Sales_Order

    Set db = CurrentDb()
    Set rs = db.OpenRecordset("tblSalesOrders", dbOpenDynaset)
    Set rs1 = db.OpenRecordset("tblSalesOrderDetails", dbOpenDynaset)
    Set rs2 = Me.SalesQuotesSubform.Form.Recordset

    rs.AddNew
    rs!CustomerCode = Me.CustomerCode
    rs!EmpID = Me.EmpID
    rs!SalesOrderNo = "QUO - " & Me.QuotationNo
    rs!SalesOrderDate = Me.QuotationDate
    rs!Freight = Me.Freight
    rs!Tax = Me.Tax
    lngOrderID = rs!SalesOrderID
    rs.Update

    rs2.MoveFirst
    Do While Not rs2.EOF
    rs1.AddNew
    rs1!SalesOrderID = lngOrderID
    rs1!SoLineNumber = rs2!sqOrderLineNumber
    rs1!InventoryCode = rs2!InventoryCode
    rs1!Quantity = rs2!Quantity
    rs1!Discount = rs2!Discount
    rs1!Rate = rs2!Rate
    rs1.Update
    rs2.MoveNext

    Loop

    rs.Close
    rs1.Close
    db.Close

    Set rs = Nothing
    Set rs1 = Nothing
    Set db = Nothing

    MsgBox "Sales Order Number Assigned: " & "QUO - " & Me.QuotationNo, vbInformation, "Applying To Sales Order"

    Else
        Me.Undo
    End If

    Me.Requery

how do I reposition to the exact record which is being update, because Requery gets me to the first record I have tried after requery something like that but no luck
Set rs = Me.RecordsetClone
    rs.FindFirst "SalesQuoteID =" & Me.SalesQuoteID
    If Not rs.EOF Then
        Me.Bookmark = rs.Bookmark
    End If
    rs.Close
    Set rs = Nothing

Thanks & Regards,
--
Abdul Shakeel

[Non-text portions of this message have been removed]