This is viagra australia price done in fluid medium in the laboratory. You can visit this generic levitra easily take this medicine; it won’t disturb the taste of your mouth.Kamagra oral jelly has been presented by its maker Ajanta Pharma so as to bond the achievement brought by their non specific item Kamagra pills. Ever since that discovery cialis buy on line was used to treat ED. They feel unloved, frustrated and stressed due to cialis sale uk some or the other problem.
How to Use Recordset
A recordset is a data structure that consists of a group of database records, and can either come from a table, query or SQL.
Syntax
expression .OpenRecordset(Name, Type(Optional), Options(Optional), LockEdit(Optional))
expression A variable that represents a Database object.
#1 Set your references
versions of Access that are earlier than Access 2000.
Dim rs As DAO.Recordset
new version of Access doesn’t need DAO reference
Dim rs As Recordset
#2 Type of Recordset
Dynaset:- A Dynaset is temporary set of data from one or more tables. A Dynaset may be a query. Like a table, a Dynaset is updatable if file is not locked or open for read only. Data in Dynaset is live that is updatable.
Set rs = db.OpenRecordset(“TableName”, dbOpenDynaset)
Snapshot:- Snapshot Record Set, like a dynaset, may be taken from one or more tables or query. The difference is that snapshot is like a screenshot that not updatable.
Set rs = db.OpenRecordset(“TableName”, dbOpenSnapShot)
#3 Set Up Recordset Object
To set a recordset object, we use the OpenRecordset method of a database object. There are a few ways to do this:
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset(“DataSource”)
Or
Dim rs As DAO.Recordset
Dim db As Database
Set db = CurrentDb
Set rs = db.OpenRecordset(“DataSource”)
Note: Data Sources with a Recordset
The “DataSource” can be one of three things, passed as a literal string or a variable:
- a Table Name
- a Query Name
- an SQL string that returns records
#4 Moving the Cursor on Recordset
To move the cursor, use one of the following moving methods of the Recordset object:
rs.MoveFirst rs.MoveLast rs.MoveNext rs.MovePrevious rs.Move
#5 BOF (Beginning of File) and EOF (End of File)
Using any of the Move methods above (MoveFirst, MoveLast, MoveNext, or MovePrevious) will cause an error if the recordset has no records.
To avoid the error we need to test if there is a record before using any of the Move methods by using these methods:
If Not (rs.BOF And rs.EOF) Then
'There are no records if Beginning-Of-File and End-Of-File are both true.
End if
If rs.RecordCount <> 0 Then
‘there are some records
End if
If (rs.EOF = False) OR (rs.BOF = False) Then
'there are recordset
End If
#6 RecordCount with MoveLast
The function below will get a number of records from a data source of Recordset. The cursor will move to the last record and count the record.
Function FindRecordCount(strSQL As String) As Long Dim db As Database Dim rstRecords As Recordset Set db = CurrentDb Set rstRecords = db.OpenRecordset(strSQL) If rstRecords.EOF Then FindRecordCount = 0 Else RstRecords.MoveLast FindRecordCount = rstRecords.RecordCount End If rstRecords.Close db.Close Set rstRecords = Nothing Set db = Nothing End Function
Note: Don’t MoveLast unless you really need to: this will be slow with a large recordset or a recordset drawn across a network. RecordCount will always be at least 1 if records exist.
#7 MoveNext Using EOF and Loop
A MoveNext may take you to the end of the recordset (EOF) or a MovePrevious to the beginning of the recordset (BOF). Failure to test for these conditions means your code works for most cases, but generates an error one day when the last/first record is accessed.
Code:
Use this construct for looping through Access recordsets:
Do while Not rst.EOF If rst![MyField] <> Something Then Exit Do 'The real loop exit condition. End If ' Rest of your code here. rst.MoveNext Loop
#8 FindFirst/Seek Using NoMatch
Always test for NoMatch after using Seek or a Find method(FindFirst, FindLast, FindNext, FindPrevious). If you do not test for NoMatch, your code will appear to work until you strike a case where the find fails.
Code:
rs.FindFirst "City = ""New York"""
If Not rs.NoMatch Then
'It's okay to keep processing.
End If
#9 Referencing a Field on Recordset
The fields available to us in the recordset is based on the table or query that is used as a datasource, and are ordered in the recordset the same as they are ordered in the table or query.
We can reference the field to read or write a value to it by calling the Fields collection of the Recordset object. There are a number of ways to do this:
rs.Fields("FieldName")
rs.Fields(VariableName)
rs.Fields(#)
rs(#)
#10 AddNew without moving to LastModified
When inserting a new record into a recordset, the new record does not automatically become the current record. To access the new record, move to the bookmark LastModified.
Code:
rst.AddNew
.Fields("FieldName1") = Value1
.Fields("FieldName2") = Value2
rst.Update
rst.Bookmark = rst.LastModified
' Work with the new record here.
Example of Adding New Record with Recordset here.
#11 Edit a Record
To edit the existing record.
Set rs = CurrentDb.OpenRecordset(“DataSource”)
With rs
.Edit
.Fields("Field1") = Value1
.Fields("Field2") = Value2
.Update
End With
Or
Set rs = CurrentDb.OpenRecordset("DataSource")
rs .Edit
rs!Fields(“Fields1”) = Value1
rs!Fields("Field2") = Value2
rs .Update
Example of updating record with Recordset here.
# 12 Close a recordset
Always close recordsets and set objects to Nothing in the error recovery of your procedure. It is poor programming to open anything without explicitly closing it. This problem is particularly serious in Access 97. Short of pressing Ctrl+Alt+Del, you may find that Access will not quit if recordsets or other objects are not closed and dereferenced.
Code:
Sub SomeName()
On Error Goto Err_Name
Dim db as Database
Dim rst As Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("MyTable")
'Useful code here.
rst.Close 'Close what you opened
Exit_Name:
Set rst = Nothing 'De-assign all objects
Set db = Nothing
Exit Sub
Err_MyProc:
'Error handler here.
Resume Exit_Name
End Sub
#13 Complete Recordset
Sub SomeName()
On Error Goto Err_Name
Dim db as Database
Dim rst As Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("MyTable")
Do while Not rst.EOF
If rst![MyField] <> Something Then
Exit Do 'The real loop exit condition
End If
rs .Edit
rs!Fields(“Field1”) = Value1
rs!Fields("Field2") = Value2
rs .Update
rst.MoveNext
Loop
rst.Close 'Close what you opened
Exit_Name:
Set rst = Nothing 'De-assign all objects
Set db = Nothing
Exit Sub
Err_Name:
'Error handler here.
Resume Exit_Name
End Sub
