How can I open a recordset based on a query that prompts me for parameters?
	What is a BookMark and how can I use it?
	Why do I get the No Current Record error message when I try to move through a Recordset?
	What is the RecordSetClone property and how do I use it?
	What are Transactions and how do I use them with Recordsets?

How can I open a recordset based on a query that prompts me for parameters?
Keywords: RecordSet Query Parameters
Posted Apr 24, 1997

These tend to be the most useful types of recordsets because they are based off a stored and compiled querydef object rather than dynamic sql. You can easily feed the necessary parameters to the query using the Parameters collection and then open the recordset. Below is a simple example:

        Dim qdf As QueryDef, rst As Recordset
	
        Set qdf = DBEngine(0)(0).QueryDefs("qryCurrentInvoice")
        qdf.Parameters("InvoiceNumber") = 5
        Set rst = qdf.OpenRecordset()

In the example above we first created the querydef object, passed it the parameters and then created the Recordset object off of the final querydef object.

What is a BookMark and how can I use it?
Keywords: BookMark
Posted Apr 24, 1997

A BookMark is a string value, which references a particular row in an open recordset. BookMarks are NOT record numbers!

You can store the value of a BookMark into a variable and later use it to set the BookMark property of the Recordset, hence returning to the original record in the recordset. This comes in useful when performing a search on the recordset of the form. If the search fails, then you can still return to the record you were previously on.

In this example we store the BookMark, move to the last row and then reset the current row:

        Dim szBookmark As String, rst As RecordSet
	
	Set rst=Me.RecordSetClone
	szBookmark = Me.BookMark
	
	rst.MoveLast
	rst.BookMark = szBookmark

Why do I get the No Current Record error message when I try to move through a Recordset?
Keywords: No Current Record RecordSet
Posted Apr 24, 1997

If you open a recordset with No records and try to do something like use the Move methods, you will typically get an error.

What you want to do is first test for the presence of records using the BOF and EOF methods. If both evaluate to True, then there are no records in your recordset:

        Dim rst As RecordSet

	Set rst = DBEngine(0)(0).OpenRecordSet("Invoice")
	With rst
		If .BOF = True And .EOF = True Then
			MsgBox "This recordset is empty"
		Else
			.MoveLast	
		End if
	End With

What is the RecordSetClone property and how do I use it?
Keywords: RecordSetClone
Posted Apr 24, 1997

The RecordSetClone property it used to retrieve a reference to a form's recordset. A common use for this is to do searches on the underlying recordset and, if successful set the current record in the form to the one found.

In this example we want to find a customer name and display it in the form:

        Dim rst As Recordset
	Set rst = Me.RecordSetClone

	rst.FindFirst "[LastName] = 'Smith'"
	
	If rst.NoMatch Then
		Msgbox "The person was not found"
	Else
		''' Use the bookmark property to set the form's current record to the one found.
		Me.Bookmark = rst.Bookmark
	End If

What are Transactions and how do I use them with Recordsets?
Keywords: Transactions RecordSet
Posted Apr 24, 1997

Transactions are intended for allowing rollbacks and committals of bulk data changes. Transactions also buffer data reads and writes, so you can use them to speed up update, edit, and delete operations.

Transactions refer to the process of grouping changes to your data into batches that are treated as single atomic units. Either all the changes succeed or they all fail. An example of this would be in an Accounting package where you wouldn't want to credit one account without debiting the old account.

Access supports Transactions through the use of the BeginTrans, CommitTrans, and Rollback methods of the WorkSpace object. BeginsTrans allows you to mark the start of a series of operations that should be considered as a single unit. CommitTrans takes everything since the most recent BeginTrans and writes it to disk. Rollback is the opposite of CommitTrans; it undoes all your changes back to the last CommitTrans.

An example of this follows:

Function CreateNewInvoice() As Boolean
On Error GoTo CreateNewInvoice_Err

    Dim ErrSave As gTypeErr
    Dim szProcName As String
    Dim db As Database, qdf As QueryDef, wk As Workspace
    szProcName = "CreateNewInvoice"
    
    ''' Assume success
    CreateNewInvoice = True
    
        Set wk = DBEngine(0)
        Set db = wk(0)
        
        ''' Wrap everything into a transaction
        wk.BeginTrans
        
        Set qdf = db.QueryDefs("qryAppendOrderToInvoice")
        qdf.Parameters(0) = mlngOrderToProcess
        qdf.Execute dbFailOnError
        
        ''' Now update the orders table to show that the order
        ''' has now been processed into an Invoice
        Set qdf = db.QueryDefs("qryUpdateOrderToInvoice")
        qdf.Parameters(0) = mlngOrderToProcess
        qdf.Execute dbFailOnError
        
        ''' Next, append the order financial info to the Invoice
        ''' financial table
        Set qdf = db.QueryDefs("qryAppendOrderFinanceToInvoice")
        qdf.Parameters(0) = mlngOrderToProcess
        qdf.Execute dbFailOnError
        
        wk.CommitTrans
        
CreateNewInvoice_Exit:
    On Error Resume Next
    Exit Function

CreateNewInvoice_Err:
    CreateNewInvoice = False
    Call ErrorSave(ErrSave)
    Call ErrorMsg(ErrSave, szProcName, varIcon:=vbExclamation)
    On Error Resume Next
    wk.Rollback
    Resume CreateNewInvoice_Exit
End Function

Back to Top

Back to the FAQ Table of Contents

Read my chips: No new upgrades!