Transcript display

Private Sub Close_Click()
On Error GoTo Err_Close_Click
DoCmd.Close
Exit_Close_Click:
Exit Sub
Err_Close_Click:
MsgBox Err.Description
Resume Exit_Close_Click
End Sub
Private Sub Add_Click()
On Error GoTo Err_Add_Click
DoCmd.GoToRecord , , acNewRec
Exit_Add_Click:
Exit Sub
Err_Add_Click:
MsgBox Err.Description
Resume Exit_Add_Click
End Sub
Private Sub Save_Click()
On Error GoTo Err_Save_Click
DoCmd.DoMenuItem acFormBar, acRecordsMenu,
acSaveRecord, , acMenuVer70
Exit_Save_Click:
Exit Sub
Err_Save_Click:
MsgBox Err.Description
Resume Exit_Save_Click
End Sub
Recordsets
• Recordset object
– The set of records in a table or returned by a
query or SQL statement
• Record Source property
– Assigns specific Recordset object to form
• Current Record
– The record pointed to by the record selector
(form may display one or more records depending on view)
• RecordsetClone
– A copy of the recordset object
Bookmarks
• Track records in a recordset
• Are created for each record in a form’s
recordset when we open the form
• Value of form’s bookmark property is the
value of the bookmark of the current record
• Two operations
– Store
– assign value of bookmark to string variable
- Set Value
- assign previously stored value to form’s bookmark
property
Code for Find Members Combo Box
Private Sub Combo28_AfterUpdate()
Subroutine name
' Find the record that matches the control.
Dim rs As Object
Comment
Variable declaration
Set rs = Me.Recordset.Clone
Assign value to variable
rs.FindFirst "[MemberID] = " & str(Me![Combo28])
Me.Bookmark = rs.Bookmark
End Sub
Call Recordset FindFirst method
Set form’s Bookmark property to
RecodrsetClone Bookmark property
Code in action
Actions
Form Recordset
RecordsetClone
Create clone
1
Jenny
1
Jenny
FindFirst
2
Deborah
2
Deborah
Set bookmark
3
Janet
3
Janet
4
Ian
4
Ian
Find Member
Ian
Procedures
• Units of VBA code to perform an operation
or calculate a value
• Two types
– Sub
• No return value
– Function
• Returns value (e.g. result of calculation)
Modules
• Procedures are stored in Modules
• Two types of Module
– Class Module
• Associated with form or report
– Standard Module
• General purpose
• Run from anywhere within database
• Stored in Module object
Message Box Code
Private Sub Form_Open(Cancel As Integer)
MsgBox "Hello, welcome to Access", vbOKOnly,
"Welcome Message"
End Sub
Confirm Function
Public Function Confirm(strMessage As String)
' Display an important message to the user
MsgBox strMessage, vbOKCancel
End Function
Public Function NewRec(strFormName As String)
' This function is used in the Click event of command buttons to open
forms and create a new record
' Using a function is often more efficient than repeating the same code in
multiple event procedures
' Open specified form
DoCmd.OpenForm strFormName, , , , acFormAdd
DoCmd.Maximize
End Function
Private Sub cmdCountMembers_Click()
Dim dbs As Database, rst As Recordset
'Return reference to current database
Set dbs = CurrentDb
' Open table-type Recordset object
Set rst = dbs.OpenRecordset("tblMembers")
MsgBox "No of Records = " & rst.RecordCount
Set dbs = Nothing
End Sub
Public Function Confirm2(strMessage As String)
' Display an important message to the user
Dim choice As Integer
choice = MsgBox(strMessage, vbOKCancel)
If choice = vbOK Then DoCmd.Quit
End Function