How to apply Random Access to an Access Database

1
How to apply Random Access to an Access Database

• Lecture 11

2
Lecture Outline

• Similarities of Access to VB
• Queries
• Criteria (of your search)
• Binding form to a table or query in Access

3
Similarities

• Access is a Microsoft product (same as VB)
• Uses Forms, Command buttons, etc.
• Has an Interactive Development Environment (IDE).

4
Access Databases

• Allows a user to rapidly design a database
• Has ability to make maintenance of database
  simple
• As stated in the last slide, lot of the power of
  VB like objects such command buttons textboxes,
  etc.

5
Random access

• Need to have ability to go directly to a given
  record in the database.
• Can be done in a form bound to a Query

6
Binding and Queries

• A form is said to be bound if it is attached to a
  table (or query) such that data entered onto the
  form is automatically entered into the bound
  table (or query).
•  Queries are used for Searching, sorting,
  collating and manipulating data
• If we bind a query to a form we can use the query
  to search a table

7
First lets create a database

• For our in class example we will have 5 fields
• Member number
• Member name
• Amount owed
• Date of last measurements
• Date of joining the center

8
Creating a table in design view
9
Add the Fields and Select Primary Key
10
On the Date Fields Changing the Format

• Select Date Field and change format to Short date

11
Add Data to your Table

• First Save your table as tblmember and open the
  table to add test data.

12
Create A Form by First Selecting the Forms Tab
13
Creating the Form

• Select the Create form in design view
• This form is not going to be bound, it is the
  search form

14
Creating the form (cont.)
15
Add Objects to form

• On left side you need a text box in which you
  will call txtSearch
• On right side you need to create a command button
  and close the command button wizard
• Call the command button btnSearch and the
  caption search
• Save the form as frmSearch

16
Click the Queries Tab
17
Create Query In Design View
18
Add Table Members
19
Creating query (cont.)

• Select the star () attribute in the field list
• Select the field in which you want to search (for
  this project we will select Member Number)
• Uncheck the show box under Member Number
• Save the query for now as qryMember

20
By now your query should look like this
21
Criteria

• Criteria is often used for searching a table
• It uses words such as Like, And ,Or etc.

22
Adding criteria to our query

• In the criteria box under Member Number, type in
  like forms!frmSearch!txtSearch
• This means find data like the one in txtSearch
  within frmSearch within forms
• Save the query

23
Creating a bound form

• In the Forms tab, this time select Create forms
  by using wizard
• In the combo box, select qryMember and move all
  fields across

24
Selecting Fields (before)
25
Selecting Fields (After)
26
Creating Bound form (cont.)

• Click next and select the format of the form as
  Columnar
• Click next and select what style of form you want
• Click next and change title of the form as
  frmSearchMember
• Click Finish

27
Coding frmSearch

• Reopen frmSearch in design view
• In button properties select the Event and
  choose which event starts the command
• For our example we will use click

28
Coding frmSearch
29
Coding frmSearch

• Select Code Builder and click ok
• This should bring up another window

30
Should look like this
31
Adding the Code
32
Close the VB window

• Save frmSearch and test the example
• You should now be able to randomly access records
  from the table.