recordset In VBA |

Recordset In VBA – PT4

This video will show you how to create the SQL recordset in VBA. SQL is used to perform updates quickly to a data source. The 4 main SQL statements cover the “action” queries in MS Access: 1. SELECT 2. UPDATE 3. DELETE 4. INSERT 1. The syntax for the SELECT is: SELECT [field] FROM [table] […]

Recordset In VBA – PT3

In this video we are talking about how to use MS Access form and bind in to a recordset In VBA. We talk about how to navigate the recordset. Here is an image of the way the process of the recordset’s methods work: Here’s the video:

Option Compare Database

Dim m_rst As Recordset


Private Sub btnMoveFirst_Click()
    m_rst.MoveFirst
    
    Me.txtCustomerID = m_rst.Fields("CustomerID")
    Me.txtCustomerName = m_rst.Fields("CompanyName")
            
End Sub

Private Sub btnMoveLast_Click()
    m_rst.MoveLast
    
    Me.txtCustomerID = m_rst.Fields("CustomerID")
    Me.txtCustomerName = m_rst.Fields("CompanyName")
                
End Sub

Private Sub btnNext_Click()
        
    Debug.Print "Move Next: " & m_rst.AbsolutePosition
    
    If Not m_rst.EOF Then
        m_rst.MoveNext
        
        'check the eof again....
        If Not m_rst.EOF Then
            Me.txtCustomerID = m_rst.Fields("CustomerID")
            Me.txtCustomerName = m_rst.Fields("CompanyName")
        End If
    Else
        MsgBox "You have reached the end of the file."
        
        Exit Sub
    End If

End Sub

Private Sub btnPrevious_Click()
    'Dim rst As Recordset
    
    Debug.Print "Move Previous: " & m_rst.AbsolutePosition
    

    If Not m_rst.BOF Then
        m_rst.MovePrevious
        
        'check the eof again....
        If Not m_rst.BOF Then
            Me.txtCustomerID = m_rst.Fields("CustomerID")
            Me.txtCustomerName = m_rst.Fields("CompanyName")
        End If
    Else
        MsgBox "You have reached the beginning of the file."
        Exit Sub
    End If

End Sub

Private Sub Form_Load()
    'Customize this when you want to have more control over the form's workings.
        
    'Set the recordsource to the "Customer" table
    Me.RecordSource = "Customers"
    Set m_rst = Me.RecordsetClone
    
End Sub

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

Option Compare Database

Dim m_rst As Recordset

Private Sub btnMoveFirst_Click()

m_rst.MoveFirst

Me.txtCustomerID = m_rst.Fields("CustomerID")

Me.txtCustomerName = m_rst.Fields("CompanyName")

End Sub

Private Sub btnMoveLast_Click()

m_rst.MoveLast

Me.txtCustomerID = m_rst.Fields("CustomerID")

Me.txtCustomerName = m_rst.Fields("CompanyName")

End Sub

Private Sub btnNext_Click()

Debug.Print "Move Next: " & m_rst.AbsolutePosition

If Not m_rst.EOF Then

m_rst.MoveNext

'check the eof again....

If Not m_rst.EOF Then

Me.txtCustomerID = m_rst.Fields("CustomerID")

Me.txtCustomerName = m_rst.Fields("CompanyName")

End If

Else

MsgBox "You have reached the end of the file."

Exit Sub

End If

End Sub

Private Sub btnPrevious_Click()

'Dim rst As Recordset

Debug.Print "Move Previous: " & m_rst.AbsolutePosition

If Not m_rst.BOF Then

m_rst.MovePrevious

'check the eof again....

If Not m_rst.BOF Then

Me.txtCustomerID = m_rst.Fields("CustomerID")

Me.txtCustomerName = m_rst.Fields("CompanyName")

End If

Else

MsgBox "You have reached the beginning of the file."

Exit Sub

End If

End Sub

Private Sub Form_Load()

'Customize this when you want to have more control over the form's workings.

'Set the recordsource to the "Customer" table

Me.RecordSource = "Customers"

Set m_rst = Me.RecordsetClone

End Sub

<< Recordset In VBA – PT2 | Recordset […]

Recordset In VBA – PT2

There are times when you will need to access your data programatically and you will have to create the recordset in VBA rather than from the built in data connection Access provides. ADO is a superset of DAO in functionality point of view. The primary benefit of using the ADO recordset object is: -Ease of […]

Recordset In VBA – PT1

In these next 2 videos, we are going to discuss generating the recordset in VBA techiques: -We will access data in tables by using the ADO recordset object. -We will retrieve data from a table using SQL and ADO recordsets. -We will alter the data in the table using SQL and ADO recordsets. In this […]