VB in Access

Document Sample
VB in Access Powered By Docstoc
					Visual Basic in Access
Ref: MS Access 97 Visual Basic Step By Step (1997)
Practice & camcorder files are available

Part 1 Automation

1. Wizards

To             Do this                              Button
Create DB      File-> New: templates: database

Creating a Database
File-> New: templates: database    (to choose a wizard)
Eg. Contacts / 連絡人管理

Import Data
File-> Get external data-> Import
You can copy or link textfiles, spreadsheets, or database tables.

Eg. Client.txt (連絡人.txt)
1. Go to adVanced to change PageCode or skip any field you don’t want
2. Choose data format (Delimited / fixed Width)
3. Check 1st row is field name
4. Store in existing table: Contacts

Eg. 計算機概論.XLS:
1. Check 1st row is field name
2. Store in existing table: Contacts
Note: You can also export the datasheet from Excel.

Adding Objects to Application
Available objects are: Tables, Forms, Reports, Data Pages, Macros, Modules
Click to focus on DataBase Window
1. Click to choose Object to be created
2. Click New

Adding Command Button to Form
[Tools-> Macro-> Change Marcos in form to Visual Basics]
Eg. Contacts Form

     With Wizard
1. Design View (View->Design) or (R-Click Title Bar->Design View)
2. ToolBox: Wizard+Command Button
3. Click on place in form where Button is to be added
4. Follow the wizard instructions.

     Eg. Move To New Record
In the Command Button Wizard, choose
   Categories: Record Operations
   Actions: Add New Record
Next: Click Text
Next: Add Record (This is the VB module name)
To examine code: Properties: Event: Click on …
VB: Procedure View button is on lower right corner
          Add Record code

Private Sub Add_Record_Click()
On Error GoTo Err_Add_Record_Click

     DoCmd.GoToRecord , , acNewRec

     Exit Sub

    MsgBox Err.Description
    Resume Exit_Add_Record_Click

End Sub

Docmd = Object
  GoToRecord = Method
    , , acNewRec = Arguments
 ( , means “use default value” )

          Accelerator Key
& before letter in Caption
alt-letter to activate

          Changing VB Code
Add the line
to Sub Add_Record_Click()
           Read-Only Form
Form: Properties: Data: Allow Edits = No

2. Customize Application
Eg. 02Contac.mdb

Adding Command Button Without Wizard
Eg. Edit Record
     1. Design View
     2. ToolBox: Command Button
     3.    Click on place in form where Button is to be added
     4.    R-Click Button: Properties (or Toolbar::Properties)
     5.      Caption = &Edit Record
     6.      Name = EditRecord
     7.    Build Events
          (or Properties:Events:onClick)

     Code Building Wizard:

Private Sub EditRecord_Click()
     Me.AllowEdits = True
End Sub

     Change Color of ComboBox

     Macro Building Wizard:
1.   Enter Macro Name
2. Pick Macro from list
Eg. OpenForm

     Operator Building Wizard:

Form Events
1.   Form Properties: Event: OnCurrent: … (Build)
2.   Code-Builder Wizard
3.   Code,
       Private Sub Form_Current()
           Me.AllowEdits = False      'Returns to ReadOnly
       End Sub
4.   Do same for Event: AfterUpdate

1.   Design View
2.   S-Click to select objects
3.   fOrmat-> Align / Size / Hor Spacings / Ver Spacing

VB: Event: AfterUpdate > MsgBox "Record Saved"

3. Find & Filter
Eg. Contact03.mdb

Find Record using Combo Box
Eg. Enter/View form

              1.    Design view
              2.    Toolbox: Wizard+Combo Box
               3.   Click on form to place Combo Box
               4.   Choose “Find A Record On My Form Based On The Value I
                    Selected In My Combo Box”
               5.   D-Click on Fields (eg. First & Last Name)
               6.   Adjust Column Width
               7.   Enter Box Name (eg. &Find Record)

To synchronize data
1. Form Design View:
2. Properties: Event: OnCurrent: …
3. Code:
    Private Sub Form_Current()
      Combo47 = ContactID
     End Sub

Filtering Data

      Filtering Commands

                                                 1. Filter By Selection

                                                 2. Filter By Form

                                                 3. Advanced Filter /


      Filtering Events
1.    Filter: Triggered by Filter By Form or Advanced Filter/Sort command
2.   ApplyFilter: Triggered by Filter By Selection or Apply Filter command

     Responding to Filter Events

     Display Message OnFilter
1.   Form Properties: Event: OnFilter: …

Private Sub Form_Filter(Cancel As Integer, FilterType As Integer)
     If FilterType = acFilterByForm Then
  MsgBox "Select Filed & Click ApplyFilter button"
End If
End Sub

  Option Group

 1. Responds to Data Entry

Part 2 Visual Basic

 2. Functions

 3. Debug

 4. Error Handling

Part 3 Custom Application

 5. Dialog Box

 6. Navigation

 7. Menus & Toolbars

 8. Final Touches

Part 4 Data & Objects
9. Objects & Collections

10. Reports

11. Data Sharing

12. Internet

Shared By: