Read DB Table RECORDSET Loop

Private Sub Command5_Click()

On Error GoTo Command5_Click_Err

DoCmd.SetWarnings False

DoCmd.RunCommand acCmdSaveRecord

If IsNull(UserName_RET) Then

MsgBox "The User Name is empty. Please input a User Name"

Close #1 ' Close file.

Forms("UserNameRetrieve")![UserName].SetFocus

Exit Sub

End If

'Reading from Database cell ***********

Dim db As DAO.Database

Dim rs As DAO.Recordset

Set db = CurrentDb

'Set rs = New ADODB.Recordset

Set rs = db.OpenRecordset("select * from qry_UserNameRetrieve")

'DoCmd.GoToRecord , "data", acNewRec

rs.OpenRecordset

UserName_RET = rs.Fields("[UserName]")

Email_RET = rs.Fields("[Email]")

rs.Close

'Reading from Database cell ***********

'Dim db As DAO.Database

'Dim rs As DAO.Recordset

Set db = CurrentDb

'Set rs = New ADODB.Recordset

Set rs = db.OpenRecordset("select * from qry_SECURITY")

'DoCmd.GoToRecord , "data", acNewRec

rs.OpenRecordset

With rs

While (Not .EOF)

ID_SECR = rs.Fields("[ID]")

FirstName_SECR = rs.Fields("[FirstName]")

LastName_SECR = rs.Fields("[LastName]")

UserName_SECR = rs.Fields("[UserName]")

Password_SECR = rs.Fields("[Password]")

Email_SECR = rs.Fields("[Email]")

TimeStamp_SECR = rs.Fields("[TimeStamp]")

If UserName_SECR = UserName_RET Then

Set MyApp = CreateObject("Outlook.Application")

Set MyItem = MyApp.CreateItem(0)

With MyItem

.to = (Email_SECR)

.Subject = "FBI User Information Retrieval"

.ReadReceiptRequested = False

.HTMLBody = "User Name: " & (UserName_SECR) & " " & "Password: " & (Password_SECR) & " " & " " & " Thank You"

End With

MyItem.Send

End If

.MoveNext

Wend

rs.Close

If UserName_SECR <> UserName_RET Then

Set MyApp = CreateObject("Outlook.Application")

Set MyItem = MyApp.CreateItem(0)

With MyItem

.to = (Email_RET)

.Subject = "FBI User Information Retrieval"

.ReadReceiptRequested = False

.HTMLBody = "User Name: " & (UserName_RET) & " could not be found. You might not be registered. Please Register to continue with the process." & " " & " " & "Thank You"

End With

MyItem.Send

End If

Forms("UserNameRetrieve")![UserName] = ""

Forms("UserNameRetrieve")![Email] = ""

DoCmd.RunCommand acCmdSaveRecord

DoCmd.OpenQuery "qry_DEL_UserNameRetrieve", acViewNormal, acEdit

DoCmd.Close acForm, "UserNameRetrieve"

End With

Command5_Click_Exit:

Exit Sub

Command5_Click_Err:

MsgBox Error$

Resume Command5_Click_Exit

End Sub

***************************************************************************************************************************************************************************************************************

Article

Looping Through a Recordset

Robert Austin | October 3, 2013

In this blog post we will be demonstrating how to loop through a recordset.

Recordsets act like a cursor or a ruler underneath a row of data. They only operate on one row at a time so to access the data returned by the database we must Move the cursor Next or Previous, First or Last.

Please download Looping Through A Recordset

Looping Through A Recordset

The EOF and BOF properties

Recordsets have two important properties when looping through data, EOF (End-Of-File) and BOF (Beginning-Of-File). Recordsets are like tables and when you loop through one, you are literally moving from record to record in sequence. As you move through the records the EOF property is set to false but after you try and go past the last record, the EOF property becomes true. This works the same in reverse for the BOF property.

These properties let us know when we have reached the limits of a recordset.

The Code

Sub DAOLooping() On Error GoTo ErrorHandler Dim strSQL As String Dim rs As DAO.Recordset strSQL = "tblTeachers" 'For the purposes of this post, we are simply going to make 'strSQL equal to tblTeachers. 'You could use a full SELECT statement such as: 'SELECT * FROM tblTeachers (this would produce the same result in fact). 'You could also add a Where clause to filter which records are returned: 'SELECT * FROM tblTeachers Where ZIPPostal = '98052' ' (this would return 5 records) Set rs = CurrentDb.OpenRecordset(strSQL) 'This line of code instantiates the recordset object!!! 'In English, this means that we have opened up a recordset 'and can access its values using the rs variable. With rs If Not .BOF And Not .EOF Then 'We don’t know if the recordset has any records, 'so we use this line of code to check. If there are no records 'we won’t execute any code in the if..end if statement. .MoveLast .MoveFirst 'It is not necessary to move to the last record and then back 'to the first one but it is good practice to do so. While (Not .EOF) 'With this code, we are using a while loop to loop 'through the records. If we reach the end of the recordset, .EOF 'will return true and we will exit the while loop. Debug.Print rs.Fields("teacherID") & " " & rs.Fields("FirstName") 'prints info from fields to the immediate window .MoveNext 'We need to ensure that we use .MoveNext, 'otherwise we will be stuck in a loop forever… '(or at least until you press CTRL+Break) Wend End If .close 'Make sure you close the recordset... End With ExitSub: Set rs = Nothing '..and set it to nothing Exit Sub ErrorHandler: Resume ExitSub End Sub

Running the code above produces this result in the immediate window:

DAO Records

1 Anna

2 Antonio

3 Thomas

4 Christina

5 Martin

6 Francisco

7 Ming-Yang

8 Elizabeth

9 Sven

ADODB Recordsets

Here is the code from above set to work with an ADODB recordset. Make sure you set your references to the Microsoft Active X library…

Sub ADOLooping() On Error GoTo ErrorHandler Dim strSQL As String Dim rs As New ADODB.Recordset 'we will be opening tblTeachers strSQL = "tblTeachers" rs.Open strSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic With rs 'Ensure recordset is populated If Not .BOF And Not .EOF Then 'not necessary but good practice .MoveLast .MoveFirst While (Not .EOF) 'print info from fields to the immediate window Debug.Print rs.Fields("teacherID") & " " & rs.Fields("FirstName") .MoveNext Wend End If .close End With ExitSub: Set rs = Nothing Exit Sub ErrorHandler: Resume ExitSub End Sub