use of all basic controls@

8/8/2019 Use of All Basic Controls@

1/119

COMPUTER PROGRAM

Computer programs are of two categories

(1)Packaged programs

(2)Custom Programs are usually designed for a specific purpose within a

particular organization.

The main difference between packaged program and custom program is

that a custom program can be modified as per the change in requirement but

packaged programs generally cannot be modified by the users.

Visual Basic is a descendant of BASIC.

BASIC (Beginners All purpose Symbolic Instruction Code)

VB is the Visual version of basic.

VB is also referred to as a Rapid Application Development (RAD) tool.

CREATING PROGRAMS USER INTERFACE

A programs user interface means part(s) of the program that the user

sees and communicate with.

When you want to create any program is VB, start with creating a new

project. In VB, each and every system that you create is known as a project.

A project is a set of files that stores information about the components

that make up an appointment program.

On starting visual basic a new project window will appear.

New project dialogbox has following three tabs:

New: Allows to choose one of the several types of project to create.

Existing: Allow to open an exsiting project that is already created and saved.

Recent: Gives the list of projects that have been worked on previously.

This window shows types of projects that can be made in VB.


2/119

After that by clicking OK, VBs design environment will be displayed.


3/119

Initially VB displays word design in the task bar, it indicates that you

are in design made.

When you run your program, VB will be in the Run mode.

VB applications are comprised of one or more components such as

forms, code modules and classes, controls and other components.

VB building Components

(1) Forms

(2) Controls

(3) Properties

Forms and controls are the components that users see and interact with,

so these objects are collecting known as programs visual component.

And the program code that the program creates is known as programs

code component.

(1) FORMS

A form is a container that holds all the other controls like text boxes,

pictures etc that make up part of a programs user interface.

Program can have single or multiple forms. It is possible to create VB

program that contains no forms at all.

The design time form contains a grid of dots that allows to line up

controls easily on the form. Behavior of the design grid can be controlled

through the options dialog box(Tool menu, options). Using options dialog box

size of the grid can be changed or if can be turned off completely. Automatic

alignment of controls to grid can also be turn on / off from options dialog box.

Functions of forms

(1) Forms represent programs user interface.

(2) Forms are the containers for the controls that users works with like text

box, buttons etc.

(3) Program code related to all the objects on a particular form is stored as a

part of form its if.


4/119

CONTROLS

Visual Basic controls are objects designed to perform specific tasks.

Controls have associated properties, events and methods.

Control is just like a mini program that performs certain tasks for you.

Controls are prepackaged objects that perform specific tasks. Ex. If you assign

20 size of the text of text box the it will display character of size 20 only for

that a text box contains inter code that allows to perform the task.

Toolbox for common controls is displays in left pane of the screen.

PROPERTIES

A property is an adjective that describes one specific thing about an

object. By changing value of one or more property, objects appearance can be

customized.

Form appears as a rectangular window on screen. This windows

appearance can be defined by form properties, like position of form on screen,

size of form by width and height properties, content of title bar by caption

property. Using some properties it can also be specified that which control

buttons will appear in title bar.

When any form is saved as a part of a project, VB creates a text file with

an FRM extension. This file stores information about form, its properties

objects of the form, objects properties and program code that is generated for

that form.

Common Properties

- Name

- Index

- Left

- Top

- Height

- Width

- Enabled

- Visible


5/119

CONTROLS

(1) Label Control

A label control is used to display text to the user. This control is

typically used to identify input areas directly to right or below the label.Common properties of label : font, fore color, height

(0) Appearance : O-flat / 1-3D

(1) Alignment : To align text left, right / center within the label.

(2) Auto size : This property will automatically size (True/ false) the

control to the height and width of the text that is displayed in it. (0

None, 1 fixed size)

(3) Border style : displays border around the label

(4) Caption : display text inside the label.

(5) Name : To identify the name of the control.

(6) Visible : Indicates whether the control is visible True/False.

(7) Word Wrap : It set to true, this property or not allows True/False the

caption to wrap to another line.

(2) Text Box Control

A text box control is used to retrieve information from the user whenany new textbox is added in the form its name and text property is set to

taxation.

Common text box properties

(1) Enabled : True : The user may type text into the text box.

False: The user will not be able to type text in text box or more

the cursor into the textbox. The control will be grayed.

(2) Locked : True : User may move cursor into the textbox but will not be able

to change any of the characters. The textbox will not be grayed.

(3) Maxlength:To set the maximum number of characters the user will be

allowed to type in the text box. If it is, set to 0, any number of

characters up to 2,048 can be entered. If multiline property is

true then up to 32kb text may be input.


6/119

(4) Multi line : True : The user may scroll the text box up and down but

not left and right.

(5) Name : To assign name to the control, to be referred to in code.

(6) Scroll bar : To display horizontal or vertical or both scroll bars on the

text box. Typically used with multi line property.

(7) Tab Index : It is set to the number that reflects the order in which you

want to have your users tab through all the controls on the

form.

(8) Tab stop : True : Textbox control gets focus as the user tabs thru

controls on the form.

False : Text box will not focus thru tab but can be focused

using mouse.

(9) Tool Tip Text : Textual value of this property will be displayed as a tool

tip when user keeps cursor over the textbox for a few

seconds.

(10) Visible : Indicates whether the control is visible or not.

(3) Command Button Controls

A command button control is used to perform some action on a form.

User can click command button with the mouse or tab to it and press spacebar.

When command button is clicked, it will generate an event programmer can

write code in that event to perform an appropriate action.

Common properties

(1) Cancel : If it is true, then it generates its click event when ESC key is

pressed only one command button on a form may have this

property set to true.

(2) Caption : The text in this property is displayed on the command button. If

and is placed before any letter in caption, that letter will have

underline and user can press Alt + that letter to have click event

of that button.


7/119

(3) Default : True : When user presses Enter key then click event will be tired.

Only one command button on the form may have this property set

to True.

(4) Enabled : True user is able to click on button false user is not to click on

button is grayed.

(5) Cause Validation : True : This cause validate event on other controls to

occur. It not is numeric (Text 1) Then MsgBox

Please enter another

(6) Name : To assign name, that is used to refer to the command button in

your code.

(7) Style : 0 standard to display text on button

1 Graphical to display a picture on the command button.

(8) Picture : Can be set to a bitamp, GIF / JPEG file if you want to display

picture on the command button instead of the text in the caption.

(4) Option Button Control

An option button / radio button is used to create manually exclusive

options.

Ex : If users wants to design system for online shopping and in that payment

mode field will be there,

- by credit card

- direct transfer

- by debit card

At a time, user should be allowed to select only on options from given options.

It more than one group of option buttons are need to added then group

options buttons by placing each group in separate frames.

Common Option button properties

(1) Alignment : Right justify Option button will appears on the right side

to text left justify option button will appear on the left side

of text.


8/119

(2) Name : Set this property to the name you want to use in code

preface name with option.

(3) Caption : Set this property to the text that you want to display with

the option button.

(4) Style : 0 standard To display text beside the option button.

1 graphical To display a picture beside the option

button.

(5) Picture: This property can be set to a bitmap GIF or JPEG file. The

selected picture will be displayed beside the option button

instead of text.

(6) Value: If it is set to true, the option button is selected by default.

Only one option button in a group can have, true value.

(5) Check Box Control:

A checkbox allows the user to select one or more choices from a list of

values. These choices are not manually exclusive, user can select multiple

values at a time.

Common Checkbox Properties:

(1) Name(2) Caption(3) Alignment(4) Picture(5) Style(6) Value 0 Unchecked

1 Checked

2 Grayed means checkbox is not available.

Frame Control:

The frame control allows to insert a group of controls into a box and add

a caption to describe the purpose of these controls. Its a contain into a frame,

those controls can be identified as part of a group.


9/119

[ A container control is any control that acts as a parent to all the

controls within it].

Common Properties:

(1) Caption : Set this property to the value you want to display on the top-left

side of the frame.

(2) Enabled: If it is set to false so all the controls in the frame are disabled.

(3) Visible : If it is false then all the controls in the frame will become

invisible.

(4) Border style - 0 - None No border

1 Fixed single

If border style is set to none then no border is displayed and the caption

is also not displayed because it is also a part of the border.

If you change the control on the frame that are already on the form then

those controls are not contained by the frame.

Controls cannot be moved in or out of the frame by change & chop

method.

To move the control from other part of the form into the frame, cut that

control and paste it in the frame.

List Box control:

A list box control is used to provide a list of items to user form which user can

make choice. User can make selection only from the available items. New

items can not be added by the user.

When it is added to a form, it will look as it is shown in the figure. A vertical

scrollbar is added to listbox automatically when more items are added to it.


10/119

Properties:

List property is used to add the items to the listbox at design time. Its an

array of items added to the list box control.

Name property is used to assign meaningful name to the listbox control.

Preface the name with lst.

Columns property is used to display list items in more than one columns to

avoid vertical scrollbar. Its default value is zero. Set this property to a value

greater than zero to create more columns.

Sorted property: Set it to true to display list items in a sorted order. If it is

false then items will be displayed in an order in which they are added to the

listbox.

MultiSelect Property :

0 None : User is allowed to select only one of the available items at a time.

1 Simple : User can select multiple items at a time through mouse clicks.

2 Extended : After first selection user has to hold down Ctrl key for more

selection. Shift key can also be used to select all items between the first

selection and last selection

IntergalHeight Property: If it is true, the listbox calculates the number of

rows it can display and keeps the height of listbox so it can display only full

rows of data without cutting a row in half. If it is set to false, the list box can be

set to any size of your choice.


11/119

Style Property :

0- Standard1- Checkbox.

Example:

Combo Box Control:

Combo box is a combination of a text box and a list box. It also gives

list of items to user from which user can do selection. The list of items will be

displayed in a drop down box when user clicks on the combo box. User can

also type the new item that is not available in the list.

Properties:

List property is used to add the items to the combo box at design time. Its an

array of items added to the combo box control.

Name property is used to assign meaningful name to the combo box control.

Preface the name with cbo.

Sorted property: Set it to true to display list items in a sorted order. If it is

false then items will be displayed in an order in which they are added to the

combo box.


12/119

IntergalHeight Property: If it is true, the combo box calculates the number of

rows it can display and adjusts the height of combo box so it can display only

full rows of data without cutting a row in half. If it is set to false, the combo

box can be set to any size of your choice.

Style Property:

0 DropDown Combo:

With this style, user can either type text into the text portion of

the combo box or select a value from the drop down list.

1 Simple Combo:

This style will not give list of items as a drop down list but it

provides it in form of list box only. This will require more space

on the form.

2 DropDown List:

This style allows to select item only from the available list. It

does not allow to type any text in the text portion.

Timer Control :

The timer control works like a stopwatch or an alarm clock. It executes

code written in its timer event, when the specified interval is complete. It

counts down repeatedly as long as the enabled property is set to true.


13/119

Properties:

(1) Enabled: True then timer control enabled of the code in the timer event is

executed at the end of the time specified in interval property.

(2) Interval: Set this property to any value between zero & 65,635. If it is set

to zero then timer control is disabled. Time can be specified in

milliseconds.

Ex:-

Private Sub Timer1_timer( )

Picture. Left = Picture1.left + 100

If picture. left > Form1.width

Then picture. left = 0

End sub.

In VB, two control are available to display picture on the form. Image

control & Picture box control.

Image Control:

This control allows to display picture of the form, that can be placed

anywhere on the form. To display image within image control, picture property

is used.

Another important property of image control stretch.

If stretch-false the image control is automatically resized to fit the

picture within it . If it is set to true, the picture is automatically resized so that

the entire picture fits with in image controls boundaries.

Picture Control:

It can be used as a container for other controls. Controls within a group

can be treated as a group. It will more or resize with from.

The main difference between picture box & image control is that thepicture doesnt allow resizing of the picture. It shoes only as much of a picture

as if can fit in its current bound.

Authorize True Resize the picture box so it can fir the entire picture with

if.


14/119

Align More /Align left / Align Right / Align Top / Align Bottom. To make

picture control align or not align to the sides of from.

Responding To The User With Event Procedure:

Visual basic is an event driver programming means the flow of program

execution is controlled by the events that occur as the program is running.

Most of the events are generated by actions initiated by the users. Some

events are invoked by other objects or by program it self.

Program events can be triggered by actions such as the user clicking a

mouse button, pressing a key, moving the mouse, changing value of

information in a control or switching to another window in the program.

Set the name property of all controls prior to writing source code of

them. Event procedures are bits of code that ran when something happens, to a

form or control. An event procedure must be defined in a very specific format.

Common Events:

(1) Click : This event occurs when users clicks a particular control of form.

(2) Change: This event occurs when data in a text box or a combo box is

changes.

(3) DblClick: When user double clicks a control or a form.

(4) Gotfocus: This event happens as user tab into a control.

When an object receives focus.

(5) LostFocus: When user move form one control to another.

(8) KeyDown: A key is pressed while an object has the focus.

(9) KeyPress: A key is released while an object has the focus.

(10) KeyUp: A key is released while an object has the focus.

(11) MouseDown: A mouse button is pressed while the mouse pointer is overan object.

(12) MouseMove: The mouse cursor is moved over an object.

(13) MouseUp: A mouse button is released while the mouse pointer is over an

object.

Ex: Form1. Caption = Mouse Co-ordinates Are (& x &, & y &)


15/119

Form Events:

(1) From Initialize( )

This is the first event to occur when user presses F5 or Run Button to

start application. This event invokes show method on the form NO controls are

yet loaded. Generally used to initialize memory variables.

(2) Form Load ( )

In this event, the form is loaded into memory. Form & control are

loaded in memory this event is used for setting properties at runtime.

Ex:

Private sub form_load( )

Picture1.widhth=100

Endsub.

(3) From Resize ( )

When form is resized by user at runtime or by succeeded.

(4) Form Activate ( )

Occurs when form gets focus.

(5) Form Deactivate ( )

Occurs when form losses focus.

Ex:

Private sub form_Deactivate ( )

Command.Enabled = False

EndSub.

Private sub form_Activate ( )

Command.Enabled = True.

End Sub.

(6) Form Unload ( )

It occurs before a form is unloaded.

(7) Form Terminate ( )


16/119

It is the last event to five when a form is unloaded. This event is used to

clean up variables that are initialized in form initialize event.

Text Box events:

(1) Textbox-Change( )

When user types something in the textbox.

Ex:

Private sub txtname change ( )

CmdSave.Enbled = true.

End sub.

Command Button events:

(1) Command Button _Click( )

Label Events:

(1)Change ( )(2)Click ( )

Frame Events:

(1)Click ( )Option Button Events:

(1) Option Button _ click ( )

Private sub optpass _ click ( )

Msgbox congrats!!!

Endsub

Private sub optfail _click ( )

Msgbox Dont be sad..

End sub

Checkbox events:

(1) Check Box _click ( ) :

Fired when checkbox is selected.

List box events:

(1) List box _ Click ( ) : If simple list.

(2) List Box _ Item check ( ): If checked box list.


17/119

Combo Box events:

(1) Combobox_change ( ) :

Fired when user types something in combo box text portion. This event

will only fire if the style property is set to 0 or 1.

(2) Combo box _Click ( )

(3) Combo box _DropDown

Occurs when user opens the dropdown portion of the combo box.

If style property is set to 0 or 2.

Picture Control & Image Control events:

(1) Picture _Click ( )

Ex: Private sub pic_click ( )

FrmNew.Show

End Sub.

MENUBAR :

Menu are used to present multiple choices & actions to perform for a

user. Menu are designed for those users who need to or prefer to user the

keyboard.

In V.B. menu system can be easily & quickly created with the menu

editor. The first step in creating a menu is determinig that what program

commands are needed to be on the menu and how these commands should be

organized. Windows system generally have common menus like, file Edit,.

Format, Tools, View, Help etc.

Menubar can be created using menu editor.

Ways to choose menu editor,

(1)Click the menu editor button on standard tool box(2)TollsMenu editor(3)Press Ctrl + E


18/119

Menu Properties (Controls):

(1) Caption : The text that is displayed in the menu item. Access key can be

specified by placing an ampersand before the appropriated letter of the caption.

(2) Name : To identify menu item in code preface name with mnu.

(3) Shortcut Key: To define shortcut key combination that allow users toselect item with keystroke.

(4) Checked: If it is selected then a checkmark appears to the left of the

menu items caption.

(5) Enabled: True/False, to enable or diable menu item.

(6) Visible : True/False, to make menuitem visibl or invisible on form.

(7) : To create submenu items.

(8) : To move back to previous level of menu

(9) and : To change position of a menu item.


19/119

Creating Pop Up Menu :

A pop up menu is a menu that appears anywhere on form in response to

a program event .

To create popup menu, create a top level menu item with visible

property set to false. Menu items add under this menu as submenu will be

displayed on the popup menu.

Popup menu are generally displayed on mouse night button click So, to

set this write code in MouseUp event of form And to make this menu work as a

popup menu, popupmenu method should be invoked.

Write the following code in the MouseUp event of form:

Private Sub Form_MouseUp

If Button = vbRightButton Then

Form1.PopupMenu mnuEdit

End If

End Sub

Here mnuEdit is the name of menu.

Here first check whether the button pressed is the right. Mouse button by

comparing the argument button with vbRightButton. If they are equal, then

popup menu method will be invoked for form1 with mnuEdit menu.


20/119

DIALOGBOXES:

A dialog box is a window used to display / accept information

to / from user. The use must answer the dialog box before

continuing with any other part of the program.

Message Box

A message box is generally used to provide warnings or an

error message that requires immediate attention of the user.

Its a simple form that displays a message and at least one

command button. The button is used to acknowledge message &

close the form.

Syntax

Msg box (prompt [,Buttons] [,title] [,helpfile, [outext])Prompt: Required string expression displayed as the message in

the dialog box.

Maximum length 1024 characters.

Buttons: Optional the type of buttons to display the icon style to

use. Default value is O which causes only an ok button

to be displayed

Title : Optional string displayed in the title bar of the dialog

box. If omitted, the appn name is placed in title bar.

Help file : Optional to set the help file for the & context appn

There can be form groups in button argument.

1. First group : Determines type of button

Vb ok only - displays ok button

Vb ok cancel - Display ok & cancel buttons

Vb Abort Retry Ingnore Display

Vb Yes No

Vb Yes Cancel

Vb Retry Cancel

2. Second group determines type of icon to display


21/119

Vb critical - Vb Question -

Vb Exclamation - Vb information -

3. Third group determines default button

Vb Default Button 1 - First button is default

Vb Default Button 2 - Second button is default

Vb Default Button 3 - Third button is default

Vb Default Button 4 - Forth button is default

Ex :- Massage Box Hello

Massage Box The last name must not be blank Vb Exclamation

Last name

Massage box data base error, Vb critical, Update Table

Project X

Hello

OK

Last Name X

The Last name . . . . . .

OK

Update Table X

Data based error . . . . . .

OK


22/119

Massage (Are you sure? Vb yes no + Vb Question, Quit) + Vb

Default button2

In Exit buttons click event,If MsgBox (Are toy sure? Vb yes not Vb Question Exit) = Vbyes

then

End

End If

In continue buttons click event,

If txtpassword.text = or txtUserName.text = then

Massage box (You must provide password, VbExclamation,

Password)

End If

Making Massage box model :

When a model massage box is shown, it must be closed

before continuing with the program. When massagebox is model to

system the user must respond to massage box doing any further

work on the computer at all.

Quit X

Are you sure

Yes No

User Name

Pass word

Continue Exit


23/119

VbApplicationModal

Vbsystemmodal

Input box :

The inputbox is used to get a single piece of information from

the user, like, name, filename or a number etc.

The input box display prompt, telling user what to cuter, a

textbox where use can eater the requested information and two

command buttons ok & cancel.

Prompt can display up to 1024 characters & user can enter

upto 255 characters of text in the input boxs text box.

Input box returns a string kind of value so, to store it define a

string type of variable. After cutering the data is textbox when, user

clicks ok button, the input box returns text in the text box whether

it is new text or the default text. If user selects cancel button then it

returns null string, regardless of what is stored in the text box.

If you are expecting a numeric value in the input box then use

val function. If the string contains or starts with numbers then val

function returns that numbers than string doesnt start with a

number, the function returns zero.

Str = InputBox (Enter your Name)

If Len (str) = O Then

Massage box No name was chtered

End if

Title X

Prompt OK

Cancel


24/119

USING DEFAULT DIALOG BOXESVB allows user to use standard windows dialog boxes like font

dialog box, open, save as, print etc. . . common dialog control is

used to enter this kind of dialog boxes. It allows user to access

following standard dialog boxes.

1. Open2. Save as3. Font4. Color5. Print

To access common dialog control, first of all add this control

in the toolbar and to your project. This control can be added by

project menu components dialogbox. From components dialog box,

select Microsoft common dialog control 6.0. So one button will be

added in the common tool box. Click this control & draw if on

the form. It will appear as an icon on the form but it will not be

displayed when you will run the project. Using this icon different

different dialog boxes can be added. For that go to its propertypage by right clicking on icon & selecting properties or from

property window go to custom property.

Open Save Color Font Print Help

OK CANCEL APPLY


25/119

Now lets consider one example in which we will add dialog

box to open a file.

CommandOpen

Common dialog control (CdTest)

In click event of command open,

CdTest.ShowopenTo display Save As dialog box,

CdTest.showsave

The file that user selects from open dialog box is store in the

filename property of open dialog box.

To access it,

CdTest.filename

TOOLBARToolbars provide an easy way to access the must commonly

used functions of the project.

Toolbar control allows to create six different types of buttons ;

1. Push buttons2. Check buttons3. Button group4. Place holder buttons

Open file


26/119

5. Drop down buttons6. Separator buttons

To chew a toolbar on the form, first add the tool that allows to

toolbar to the toolbox,

Project components Microsoft windows common controls

6.0

It will add 9 different buttons (tools) to the toolbar select tool

for toolbar & draw it on form. By default, it will be aligned to the top

of the form but alignment can be changed by changing align

property.

1 - VbAlignTop Default

2 VbAlignBottom3 - VbAlignleft

4 - VbAlignRight

0 VbAlignNone Free floating toolbar

In toolbar some images are required to be displayed on

buttons. So, to get this image first of all imagelist should be

prepared for it.

* IMAGELIST :

Imagelist control allows to add bitmaps, that can be used by

other controls of the project. Image list control is also a part of

Microsoft windows common controls 6.0. so, if you have added this

component to you project than image list is also there in toolbox.

(So, First of all prepare a image list with bitmaps that you

want to display on button)

First step in creating toolbar is adding the images that will be

displayed on the toolbar.

To prepare an image list

1. Draw an imagelist control on your form and assign a uniquename to it. (If will appear as a small icon on the form)


27/119

2. Open property pages dialog box of image list by right-clickingthe image list selecting properties.

Select image page in the dialog box

3. To add an image to the image list, click the insert picturebutton. So it will open select picture dialog box, from which

the bitmap can be selected. As the picture is selected, it is

added to the image list & displayed in the images cruel

4.Click ok button after adding all the images.

Now, image list control is ready to supply images to the toolbar.

(appearance)

Now, set the properties of tool bar by making required

changes in properties of toolbar.

Right click on toolbar and select properties to get property

dialog box.

In first page, it will display several properties like.

1. Imagelist set it to the name of imagelist control that iscreated to provide images

2. Button Height : Hight of buttons in toolbar (in twips)3. Button width : Width of toolbar buttons[twip is 1/20

th part of a point]


28/119

4. Allow customize check box determines whether the usersare allowed to add, delite or move button.

5. Showtips checkbox tooltips are shown or not6. Wrappable checkbox toolbar wraps around second row of

buttons or not

7. Enabled check box8. Style tbrstandard 3D toolbar9. Borderstyle = None No border

= 1 fixed single fixed size single border

10. Text alignment 0 button to align text with the1 Right button image

11.Disabled image list : Image list to provide images for buttonswith enabled property set to tales.

Now to add buttons go to the second page of properties dialog

box.

To add button, click insert button. Now, change its key

property to specify the name that is used to identify button in code.

Image Property specifies the index of the picture that you

want to appear on buttom.

Style property to select style (type) of button

STYLE :

0-tbrDefault push button

1-tbrcheck check button

2-tbrbuttongroup button group

3-tbrseparator

4-tbrplaceholder

Caption text to be displayed below the picture giveing button

description.

Tool tip text

Value Returns the current state of the button tbrPressed /

tbrUnpressed


29/119

To add items to chopdown button menu, click the insert

button menu and enter menu item in its text property.

Ex :

Private sub TblTest_Button click (By val button as MSComct/Lib-

Button)

If Button.key = Bold then

Text1.font.bold = trueElse

Text1.font.bold = False

End of

Else If Buttom.key = Italic then

If button.nalul = tbrpressed then

Text1.Font.Italic = True

Else

Text1.font.italic = False

Else If Button. Key = Uline Then

Textt.font.Underline = True

Else

Text1.font,underline=false

End IF

End if

Variables :

Syntax of variable declaration :

(Data type)

Dim variable name As variable type

B I U


30/119

Dim statement is used for declaring (dimensioning) variables.

Restrictions in naming variables :

1. The name must start with a letter, not a number or othercharacter.

2. Variable name can contain letters, number of under corecharacter. Spaces periods or other punctuation characters are

not allowed.

3. It must be unique within variables scope.4. Maximum 255 characters can be there in var-name5. It cannot be any of VBs reserved words.

TYPES OF VARIABLES :

1 Integer 2 bytes -32, 768 to +32,767

2 Bytes 1 byte 0 to 255

3 Single 4 bytes Single precision nos.

4 Double 8 bytes Double precision nos.

5 Currency 8 bytes 15 fixed point nos. digitsbefore decimal point and 4

digits after decimal point.

6 String 1 byte per

char

Maximum 65,400 char for

fixed length sting & upto 2

billion characters for var-

length.

7 Boolean 2 bytes True / False

8 Date 8 bytes Date & time information

9 Object 4 bytes Instance of classes

10 Variant 16 bytes +

1 byte / char

User defined types can be created using type statement


31/119

Sytax

Private Type Type Name

Variable list

End type

Ex. Private Type Rectangle

Length As integer

Width As integer

End type

Dim My Rect as rectangle

My Rect. Length = 10

My Rect. Width = 5

Variable Declarations :

If a variable is not delared, VB create the variable using data

type, which can contain any type of data.

Draw backs wastage of memory space

If might be invalid with fun that requires specific

data type.

There are two ways to declare a variable in to,

1. Explicit declaration :

Just defending variable names with their data types, without

assigning value to the variables.

Dim, private, static, public keywords can be used for explicit

declaration.

Dim varname as type, varnamez as type vanames as type.


32/119

2. Implicit declaration

In implicit declaration, a special characters is used at the end

of variable name where the variable is first assigned a value.

Variable type character

Integer %

Long &

Single !

Double #

Currency @

String $

Num % = 10Names $ = xyz

If declaration character is missing than that variable will be of

variant type.

Fixed length strings :

Strings declared thru implicit or explicit declarations are

variables length strings, that can store upto 2 billion characters.

Fixed length string can also be defined by specifying length

within the declaration statement. but it can have maximum 65,400

characters.

Syntax :

Dim varname As string * str length

Ex. : Dim name as string * 20

Now if name variable is assigned less than 20 characters than

reaming length will be filled with blank spaces & If expression is

longer than the variable, then remaining characters are truncated

(discarded)

If can be used with database.

Scope of variables :


33/119


34/119

Constant declaration

Constant is ta variable whose value cannot be changed once it

is defined.

Contents cannot be modified, after you define them. if you try to

modify. VB generates runtime errors.

Syntax :

Const varuname = value

Ex. Const pie = 3.142

OPTION EXPLICIT STAEMENT

By setting environment option, VB can give error message ifvariable is being used without declaration.

Tools options require variable declaration

Selecting this option requires declaration of each variable

before use. Selecting this option causes the option explicit

statement to be placed in general declaration section of all new

modules & forms that are added to project.

If a form or model contains option explicit statement and you

fail to declare a var, then you receive the error to declare a var,

then you receive the error message, variable not defined at

runtime.

Math Operations :

Addition +

Subtractim -

Multiplication *

Division /

Integer Division \

Modulus mod


35/119

Exponeatiotim ^

Ex.

CmdResult

CmdResult click ()

Dim MFCS As integer, VP As integer, JP As integer, DBMS As

integer, DCN As integer, Result as single

MFCS = Val (txtMFCS.text)

VP = Val (taxVP.text

JP = Val(txtJp.text)

DCN = Val (txtDCN.text)

Total % = MFCS + VP + JP + DBMS + DCN

Result = Total / 5

txt total.text = Result & %

MFCS II

Visual Programming

Java Programming

DBMS

DCN

Total Percentage

Class

Calculate Result


36/119

If result > 70 then

Lbl class.captim Distinction

Else If Result > 60 Then

lblclass.captim = First class


lblclass.captim = Second class


lblclass.captim = Pass class

Else

lblclass.capion = Fail

End If.

ORSelect case Result

Case Is > 70

lblclass.capion =

Case 61 t0 70

lblclass.capion =

Case 51 t0 60

lblclass.capion =

Case iS > 40

lblclass.capion =

Case is < 40

lblclass.capion =

End case.

Example

First No.

Second No.

Simple Division

Integer division

Calculate


37/119

To search a string within any string, use insert function insert

returns starting position of the word.

Syntax

Instr (Start position, source string, search string, flag)

Optional

Flag = 0 default case sensitive search

Flag = 1

case insensitive search

String Factions :

(1) String $ ()

Retinues a string made up of a specific number of characters.

Syntax

String $ (No. of chair, Character)

(2) Instr() Instr(Stat pos, string, serch str, flag

(3) InstrRev()-

(4) Left$ () Left $ (String, No. of char)

(5) Right $() Right $ (String, No. of char)

(6) Mid $ - Mid $ (String, Start pos, No. of char)

(7) Len () Len (String)

(8) Replace Replace (String, original, New)

(9) Trin $ - Trim $ (String)

(10) LTim$ - LTRim$ (String)

(11) RTrim$ - Rtcase$ (String)

(12) Ucase $ - Ucase$ (String)

(13) Lcase $ - Lcase$ (String)

(14) Space $ - Space $ (No of blank spaces)

(15) Str () - Str $ (Numeric value)


38/119

(16) Str Reverse $ ()

Ex :

Str$ = Input Box (Enter your Branch Name)

Ustr = VCase$ (Str)

Msg Box Oh! You are the student of BCA!!

End If

DATE FUNCTIONS :

(1) Now Returns a varicant type of date/time stamp in timer

event with interval of 1000 ms.

Ex. lb/time caption = Now(2) Date() / Date $() Returns date, In dd/mm/yy format & date

$ returns is string in dd-mm-yyyy format

Lbl.capim = data

(3) Time / Time$()

H : M : Second (AM/PM)

(4) Date Part () Return specified part of date.

Dim dtToday As Date

dtToday = Now

month % = Date Part (m, dt today )

day % = Date Part (d, dt today )

year % = year Part (yyyy, dt today )

dotweek % = Date Part (q, dt today )

week % = Date Part (w, dt today )

(5) Dim dt as date

Date = now

Date + 1

Date + 5

(6) Date : Diff() dtz = # 09/13/1981#

Dim dt1, dt2 as date dt = now

Date Diff. (d, dt2, dt1 )


39/119

(m, dt2, dt1 )

(w, dt2, dt1 )

(yyyy, dt2, dt1 )

(7) Date Add ()

dt = now

Date Add (m, 6, dt )

Date Add (ww, 9, dt )

Date Add (d, 180, dt )

- Ve number substracts date from given date

Format Functions :

(1) Format Currency ()Format Currency (Expression, Num Dig its After Decinal,

Include leading digit, Use parents for negative, nums,

Group Digits)

Format Currency (100) = $100.00

(100, 3) = $100.000

(100, -1) = $100.00 [-1 for default]

(0.100, -1, Vbfalse) = $100

(0.100, -1, Vbfalse/Default) = $ 0.10

(-100, -1, Vbfalse) = -$100.00

(-100, -1, Vb True) = ($100.00)

(1100, -1, , Vb Default) = $1,100.00

(1100, -1, , ,Vb False) = $1100.00

(2) Format Number ()

Same as currency but without currency symbol

(3) Format percent ()

Format percent (1/3) = 33.33%

Format percent (1/3, 3) = 33.333%

(4) Format Date time ()

Vb General Date mm/dd/yy H:M:Se AM/PM

Vb Long Date - Thursday, January 29, 2009


40/119

Vb Long Time - 8 : 38 : 07 AM

VB Short Date - 1 / 29 / 09

Vb Short time - 18 : 38

(5) Round ()

If no is odd Round up

If no is even Round down

Round (1.5) 2

Round (2.5) 2

Round (2.865, 2) 2.87

Round (2.865, 1) 2.9

Example txt name 1 txt Age 1

Enter Name Birth Date

Enter Name Birth Date

txt name 1 txt Age 1

Cmd age

lblresul

cmdage click () event

Dim dt1, dt2 As date, diff as Integer

dt1 = txt Age1.text

dt2 = txt Age2.text

diff = Date diff (m, dt1, dt2)

If diff > 0 then

lbl Result. captim = txt name.text & is elder than &

txtname2.text

Compare


41/119

Else diff < 0 then

lblResult.caption = txtName2 txt & is elder than &

txtname2.text

Else

lblResult.caption = txtName1 txt & txt name2.txt & both are

of same age

Class of then

Total Amount

Discount

Bill with discount

Din bill, amt as single

Amt = val (txtamt.text)

Select case

A

B

C

D

Calculate Bill


42/119

List view

To add a list view control to a from follow these steps

1. Select project Components2. Select Microsoft windows common controls 6.03. Select list view control and draw and draw it on the form4. Set properties of list view and add code.

Adding items to list view :

List view items can be added to its list items collection using

add method. Each item added is a list item object of list item type

and then add it to the list view [Name of list view is list view1]Private sub form Local ()

Dim Ls1 as list items

Set ls1 = list view1. list items. Add()

Ls1.Text = item1

[Here lst is a list item object and that we are adding t olist

items collection of listview1 suing add method. After that we are

adding text to lst object, using its text property. This text will be

displayed in list view.]

Each item in a list view is a lsit item object, and each such

object has on Icon property. This property is used to display icons

with list items. This icons can be accessed from imagelist. So create

image list for that.

Prepare an image list by adding an icon that you want to

display with of List view control allows to add 3 types of limagelist

normal, small and Header control.

To connect imagelist with list view, go to property pages of

list view and select image lists tab. In normal field, set the name of

created image list.


43/119

This image will be displayed as icons with list items when list

views view property is set to = o [IVV Icon]. For other values of

view property set small icons property.

Ls1.Icon = 1 1 is index of image in image list

Klhen view property is set to any other value than IVV Icon

then small icons will be displayed with list items. To add small icons

to list views items, first create an imagelist having icons that you

want to display with list view items.

Then go to property pages of list view and in image lists tab

set mall field to the name of imagelist

After that in code for each items added to list view, add

following statement.Ls1.smallzcon = 1 index of image

Set list views view property to IVV small Icon and run the

appn.

VIEW TYPES IN LIST VIEW :

1. IVV Icon 0 : It displays large icons and allows user fo chag& drop & rearrange objects.

2. IVV Small Icon : I use small icons so more list item objectscan be viewed. Objects can be rearranged.

3. IVV list 2 : Presents sorted view of list items4. IVV Report 3 : Presents sorted view with sub items and

allows extra information to be displayed

Adding column Headers :

Klhen list views view property is set fo IVV report, it can

display lists arranged in columns headers to a list view, add column

headers with list views column headers collection.

Dim co/Hd As column Header

Set Co/Hd = List view 1. column Headers.Add()

Co/Hd.Text = Name


44/119


45/119

Object.Add (I ndex, Key, text, icon small icon)

List view name.list items

1. Index a number that position list item within list itemscollection (optional)

2. Key to assign name to list item3. Text string that is displayed in list view window4. Icon Index of icon in Image list for normal view5. Smallicon index of icon in for small view

Ex. For Simple list :

Computer Engineering Faculty of Technology

EC Engineering

Faculty of TechnologyCivil Engineering Faculty of Technology

Mechanical Engineering Faculty of Technology

IT Engineering Faculty of Information Science

BCA Faculty of Information Science

MCA Faculty of Information Science

To add items of other columns

Object name.subitems(index) = value

Ex. Ls/.subitems(1) = 04

Ls/.subitems(2) = 00

Or list view/.listitems(1).subitem (1) = 04

list view/.listitems(1).subitem (2) = 00

o/p India 04 00


46/119

TREE VIEW :

DDU

Faculty of Technology

Computer Engineering

EC Engineering

Chemical Engineering

Civil Engineering

Faculty of Information Science

IT

BCA

MCA

Faculty of Pharmacy- D.Pharm- B.pharm

Faculty of Dental Science

Form load ()

Dim temp as node

Set temp = treeview/.nodes.add( , , Root. DDU)

Add (Relative, Relationship, key, text, image, selected image)

Relationship tvwlast

- tvwNext

- tvwPrevious

- tvw child

Control Array :

A control away is a group of controls, all of the same type and

that have the same name are identified by an idex.

Each element must be of same type Each control must have the same name Each control is identified by unique index up to 32767

Advantages :


47/119

1. Requires fewer system resources then adding an individualcontrol of same type.

2. Controls of a array share a common set of event procedures.3. Its the only way to add controls to a forms at runtime.4. More number of controls can be added in a form.

After crating a control away, a single piece of code can be

written to handle a particular event for all the controls in away.

Whenever that event is fired by any of the controls in array that

code is executed.

Ex. txtArray lost focus (Index as integer)

txtArray (index) = Ucase (txtArray(index).text)

txtArray

Name : 0

Address : 1

City : 2

E-mail : 3

txtArray lostfocus (Index as integer)

Infront index = 3 then

Txt Array (Index) = Ucase(txtArray(Inxed)text)

End if

To set right alignment for lblArray(i).Alignment,

Form load ()

For i = 0 to 3

lblArrayy(i).Alignment = 2

Next i

To highlight the lable when matching textbox is having focus,

txtArray gotfocus

lblArray(index).font.bold = true

txtArray Lost focus


48/119


49/119

CODE:

Option ExplicitDim i As Integer

Private Sub Combo1_Click()If Combo1.List(Combo1.ListIndex) = "CH" Then

i = txtArray.UBound + 1Load lblArray(i)

Load txtArray(i)txtArray(i).Top = txtArray(i - 1).Top + 500lblArray(i).Top = lblArray(i - 1).Top + 500lblArray(i).Alignment = 2lblArray(i).Caption = "Subject 6"txtArray(i).Visible = TruelblArray(i).Visible = True

End IfEnd Sub

Private Sub Command1_Click()Dim total As IntegerFor i = txtArray.LBound To txtArray.UBound

total = total + Val(txtArray(i).Text)Next iText1.Text = totalEnd Sub

PARALLEL ARRAYS

Two or more difficult control arrays whose elements match

with each other in some way are known as parallel Array.


50/119

TABSTRIP :

Example

Frame1 (Personal) Frame 2 (Education)

Experience

Client Area

Client Area

Personal Educationa

Experience

Name Gender

Address Male

City Female

Ph. No.

Hobbies

Music Reading

Traveling

S.S.C.

H.S.C.

Graduation

Post Graduation

Other

Name of firm :

Post :


51/119

Form load ()

Dim i as integer

For i = o to 2

Framel (i).move Tabstripl.clientleft, Tabstripl.clieatiop,

Tabstripl.clientwideth,Tabstripl.client HeightNext i

Frame 1 (0).Zorder 0

Tabstrip . click ()

Dim index, I as integer

Index = tabstripl.selected item. Index

For I = 0 + 2

Framel(i) . visible = false

Next i

Frame 1 (index 1).Visible = True

End sub.


52/119

CONTROL ARRAY

CODE:

Option ExplicitDim i As Integer


53/119

Private Sub Combo1_Click()If Combo1.List(Combo1.ListIndex) = "CH" Then

i = txtArray.UBound + 1

Load lblArray(i)Load txtArray(i)txtArray(i).Top = txtArray(i - 1).Top + 500lblArray(i).Top = lblArray(i - 1).Top + 500lblArray(i).Alignment = 2lblArray(i).Caption = "Subject 6"txtArray(i).Visible = TruelblArray(i).Visible = True

End If

End Sub

Private Sub Command1_Click()Dim total As IntegerFor i = txtArray.LBound To txtArray.UBound

total = total + Val(txtArray(i).Text)Next iText1.Text = totalEnd Sub


54/119

Visual Basic & ADO TutorialLevel:Level2

This tutorial describes how you can use ADO objects in VB6. Now days, almost anytime you write full fledged database application you will want to use ADO. Alongwith this, as your applications become more and more complex you will probably notwant to rely on Visual Basic's data controls, but instead use the ADO objects directly.Read on to find out exactly how this can be done.

Originally Written By TheVBProgramer.

The "Alphabet Soup" of Database Access

Prior to VB6 and the introduction of ADO (ActiveX Data Objects), VB programmerswould generally use DAO (Data Access Objects) to interact with local databases suchas MS Access and use RDO (Remote Data Objects) to interact with client/serverdatabases such as Oracle and SQL Server. The concept behind Visual Basic ADO wasUniversal Data Access (UDA), where one database access method could be used forany data source; it was designed to replace both DAO and RDO. DAO remains aviable technology for interacting with MS Access databases as it is faster than ADOfor that purpose; however, ADO is more flexible using ADO, one could develop aprototype database application using MS Access in the back-end, and with a "flick ofthe wrist" (i.e., with very little coding changes) "upsize" that same application to useOracle or SQL Server. As far as RDO is concerned, no new versions of it have beendeveloped beyond the version that shipped with Visual Basic, and there are no future

plans for it.

In the VB4 and VB5 worlds, RDO was the main method used to interact withclient/server databases. RDO works perfectly fine with VB6, so when folks migratedtheir VB5 applications over to VB6, little or no coding changes were required.However, ADO is the preferred method of database access for new VB6 applications .

About this Tutorial

This tutorial presents three small sample applications using ADO. All threeapplications use a local MS Access database.

The first sample application introduces the ADO Data Control (ADODC) whichdemonstrates a "quick and dirty" way to connect to a remote database. The second and


55/119

third applications use ADO code: the second allows navigation and searching of adatabase table; the third allows navigation and updating on a database table. All threeconnect to an ODBC Data Source, which must be set up through the WindowsControl Panel. How to do this is described below.

Note: If you have previously set up a DSN for the Biblio database as described in theprevious topic on RDO, you can skip the section on setting up an ODBC data sourceand resume here.

Setting Up an ODBC Data Source

Follow the steps below to set up an ODBC Data Source (this process is also called

"setting up a DSN", where "DSN" stands for "Data Source Name"). These stepsassume Windows 2000 for the operating system. On other versions of Windows, somesteps may vary slightly.

Via Windows Control Panel, double-click on Administrative Tools, then DataSources (ODBC). The ODBC Data Source Administrator screen is displayed, asshown below. Click on the System DSN tab.

Click the Add button. The Create New Data Source dialog box will appear.Select Microsoft Access Driver (*.mdb) from the list and click the Finish button.

The ODBC Microsoft Access Setup dialog box will appear. For Data SourceName, type Biblio. If desired, you can type an entry for Description, but this is not

required.

Click the Select button. The Select Database dialog box appears. On a defaultinstallation of VB6 or Visual Studio 6, the BIBLIO.MDB sample database should


56/119


57/119


58/119

adoBiblio

Au_ID

txtYearBorn

adoBiblio

Year Born

Save and run the program. Notice how it works just like the other data control.

Now change the SQL property of the data control to select * from authors orderby author and run the program again. Notice the difference.

Change the SQL property back to what it was and add three command buttonsto the form, and set their Name and Caption properties as follows:

Name

Caption

cmdNameOrder

Order by Name

cmdYearOrder

Order by Year

cmdIDOrder

Order by ID


59/119

Put the following code in the cmdNameOrder_Click event:

adoBiblio.SQL = "select * from authors order by author"

adoBiblio.Refresh

Put the following code in the cmdYearOrder_Click event:

adoBiblio.SQL = "select * from authors order by [year born]"

adoBiblio.Refresh

Put the following code in the cmdIDOrder_Click event:

adoBiblio.SQL = "select * from authors order by au_id"

adoBiblio.Refresh

Save and run the program and see what happens when you click the buttons.

A screen-shot of the sample app at run-time is shown below:

Download the project files for this sample application here.


60/119

Sample Applications 2 and 3: Using ADO Code

Note: If you have previously downloaded and set up a DSN for the Property database

as described in the previous topic on RDO, you can skip the set up steps below andresume here.

Sample applications 2 and 3 use a database called PROPERTY.MDB and can bedownloaded here.

The Property database contains just one table called "Property". The columns of thistable are defined as follows:

Column Name

Data Type

Notes

PROPNO

Number (Long Integer)

A number that uniquely identifies the property in the table. Should be treated as thePrimary Key (although it is not defined as such in the sample database).

EMPNO


A number that identifies the real estate agent selling the property. In a real system,this would be the foreign key to the employee number in an Employee table (such atable is not present in the sample database).

ADDRESS


61/119

Text (20)

The street address of the property.

CITY

Text (15)

The city where the property is located.

STATE

Text (2)

The state where the property is located (2-character US state abbreviation).

ZIP

Text (5)

The zip code where the property is located.

NEIGHBORHOOD

Text (15)

The descriptive name of the neighborhood in which the property is located.

HOME_AGE


Age in years of the home. (A better table design choice would be to have this field bethe date in which the property was built and have the application compute the agebased on the current date.)

BEDS


62/119


Number of bedrooms in the property.

BATHS

Number (Single)

Number of bathrooms in the property (allows for a decimal value such as 2.5,indicating 2 bathrooms i.e. 2 full bathrooms and 1 "powder room").

FOOTAGE


The footage of the property.

ASKING


Asking price of the property in whole dollars.

BID


Bid amount of the potential buyer in whole dollars.

SALEPRICE


Sale price (amount the property actually sold for) in whole dollars.

Before coding or running sample application 2 or 3, you must set up an ODBC datasource as was done for the previous sample application.


63/119

After downloading the file, move it to the folder of your choice. Then follow the exactsame steps as before to set up the DSN, with these two exceptions:

(1) On the ODBC Microsoft Access Setup dialog box, type PropDB for the DataSource Name.

(2) In the Select Database dialog box, navigate to the location where you have placedthe PROPERTY.MDB file.

Sample Application 2

To build Sample Application 2, start a new VB project and perform the followingsteps.

From the Project -> References menu, check Microsoft ActiveX Data Objects2.x (where x is the highest version that you have on your system) and click OK.

This project uses the StatusBar control, so include the Microsoft WindowsCommon Controls 6.0 (SP6) from the Components dialog box, accessed from theProject -> Components menu.

Create the form shown below. The names of the text boxes in the top frame areshown in the form. Set the Enabled property of the frame to False, which willautomatically disable all of the textboxes within it, which is desired because thisapplication does not allow updating of the data. The settings for the other controls aregiven below.


64/119

The navigation buttons have the following properties:

Name

Caption

cmdMoveFirst

cmdMoveLast

>>

The text box in the middle of the form has the following properties:

Name

txtCurrentQuery

MultiLine

True

Locked


65/119

True

The command buttons have the following properties:

Name

Caption

Enabled

cmdAllData

Reload All Records

True

cmdGetData

Run Query Now

False

In the "Select Criteria" frame:

The check boxes are an array:

Name

Caption

chkCriteria(0)

EmpNo


66/119

chkCriteria(1)

City

chkCriteria(2)

State

The labels are also an array:

Name

Caption

Enabled

lblCriteria(0)

=

False

lblCriteria(1)

Like

False

lblCriteria(2)

Like

False

The textboxes are also an array:


67/119

Name

Caption

Enabled

txtCriteria(0)

EmpNo

False

txtCriteria(1)

City

False

txtCriteria(2)

State

False

Place the StatusBar on the form and set its Style property to 1 sbrSimple.

2. Code the General Declarations section as shown below. Here, two ADO objects,ADODB.Connection and ADODB.Recordset, are defined at the form level.

The ADODB.Connection object represents an open connection to a data source and aspecific database on that data source, or an allocated but as yet unconnected object,which can be used to subsequently establish a connection.


68/119

The ADODB.Recordset object represents the rows that result from running a query,

Option Explicit

Dim mobjADOConn As ADODB.Connection

Dim mobjADORst As ADODB.Recordset

Dim mstrSQL As String

3. Code the Form_Load event. Here, the connection object variable mobjADOConn ismade available for use by setting it to a new instance of ADODB.Connection. Then,

the ConnectionString property and the Open method of the ADODB.Connectionobject are used.

The ConnectionString property takes a string with various arguments delimited bysemicolons. When using a DSN as we are in this sample application, you typicallyneed just the DSN name, the user id, and the password. The Open method then opensthe connection to the database.

'-----------------------------------------------------------------------------

Private Sub Form_Load()

'-----------------------------------------------------------------------------

'set up the form and connect to the data source

On Error GoTo LocalError

'center the form:

Me.Top = (Screen.Height - Me.Height) / 2

Me.Left = (Screen.Width - Me.Width) / 2

' Connect to the Property database:


69/119

Set mobjADOConn = New ADODB.Connection

mobjADOConn.ConnectionString = "DSN=PropDB;Uid=admin;Pwd=;"

mobjADOConn.Open

Call cmdAllData_Click

Exit Sub

LocalError:

MsgBox Err.Number & " - " & Err.Description

End Sub

4. Code the cmdAllData_Click event, which sets or resets the ADODB.Recordsetobject with a query to display all the rows in the table. The opening of the recordsettakes place in the OpenNewRecordset subprocedure, called from this event procedure.

'-----------------------------------------------------------------------------

Private Sub cmdAllData_Click()

'-----------------------------------------------------------------------------


Dim lngX As Long

'select or reload the data to be displayed:

mstrSQL = "select * from property"

Call OpenNewRecordset

'load data into the text boxes


70/119

Call DataLoad

' reset the state of the search criteria controls

For lngX = 0 To 2

chkCriteria(lngX).Value = vbUnchecked

Next

Exit Sub

LocalError:


End Sub

5. Create the user-defined subprocedure OpenNewRecordset.

Here, the recordset object mobjADORst is made available for use by setting (orresetting) it to a new instance of ADODB.Recordset.

The CursorLocation property is then set to the built-in constant adUseClient. The term"cursor" refers to the temporary rows of a recordset. The cursor location determineswhether the cursor is stored on the client or the server, specified by the valuesadUseClient and adUseServer, respectively. Server-side cursor (adUseServer) is thedefault. There are tradeoffs using both types of cursors. Client-side cursors can take along time to build because the data must be pulled over to the client, but once built,traversing the cursor is usually very fast. Client-side cursors often support morefeatures than server-side cursors (the reason this sample application is using a client-

side cursor is because we want to use AbsolutePosition property later, which onlyworks with a client-side cursor). On the other hand, server-side cursors usually buildfaster but often support fewer features that client-side cursors.

The Open method of the recordset is then executed. The Open method has thefollowing syntax:


71/119

RecordsetObject.Open Source, ActiveConnection, CursorType, LockType, Options

The Source argument is an optional variant that evaluates to a valid Command object,SQL statement, table name, stored procedure call, or filename of a persisted recordset.

The ActiveConnection argument is an optional variant that evaluates to a validConnection object variable name or a string containing connection string parameters.

The CursorType argument is an optional value that determines the type of cursor thatthe provider should use when opening the recordset. The possible values and their

descriptions are given below:

Value

Description

adOpenForwardOnly

(default) Used to open a forward-only cursor. Forward-only cursors create staticsnapshots of data. A recordset that uses a forward-only cursor is not directlyupdateable and can only be scrolled from beginning to end (i.e., "MoveNext" is theonly "Move" method that can be used with this type of cursor). Forward-only cursorsoffer optimal performance in exchange for feature limitations. Forward-only cursorsare sometimes referred to as firehose cursors.

adOpenStatic

Used to open a static cursor. A static cursor is a static copy of the data in the data

source. Once created, no changes made by other users propagate to the recordset; therecordset never changes. Note: Client side cursors (like the one used in this sampleapplication) use only adOpenStatic for CursorTypes regardless of which CursorTypeyou select.

adOpenDynamic


72/119

Used to open a dynamic cursor. A dynamic cursor is a "live" recordset, meaning thatany and all additions, changes, and deletions by other users affect the recordset.Dynamic-cursor recordsets support all types of navigation, including bookmarks (ifbookmarks are supported by the provider). Dynamic cursors offer the most features ofany cursor type, but at the expense of increased overhead.

adOpenKeyset

Used to open a keyset cursor. Keyset cursors are like dynamic cursors, exceptadditions made by other users are not visible in the recordset. The recordset isaffected by changes and deletions, however.

The LockType argument is an optional value that determines the type of locking thatthe provider should use when opening the recordset. The possible values and theirdescriptions are given below:

Value

Description

adLockReadOnly

(default) Specifies read-only locking. Records can be read, but data cannot be added,changed, or deleted. This is the locking method used with static cursors and forward-only cursors.

adLockPessimistic

Specifies pessimistic locking. The provider does what is necessary to ensuresuccessful editing of records, usually by locking records at the data sourceimmediately upon editing.

adLockOptimistic

Specifies optimistic locking. The provider locks records only when you call theUpdate method, not when you start editing.

adLockBatchOptimistic

Specifies optimistic batch locking. Records are locked in batch update mode, asopposed to immediate update mode. This option is required for client-side cursors.


73/119

The Options argument is an optional Long value that indicates how the Source shouldbe evaluated. The possible values and their descriptions are given below:

Value

Description

adCmdText

Indicates that the provider should evaluate CommandText as a textual definition of acommand. This options is used SQL statements.

adCmdTable

Indicates that the provider should evaluate CommandText as a table.

adCmdStoredProc

Indicates that the provider should evaluate CommandText as a stored procedure.

adCmdUnknown

Indicates that the type of command in the CommandText argument is not known andthat the provider should attempt to interpret it. Typically results in poor performance.

adExecuteAsync

Indicates that the command should execute asynchronously.

adFetchAsync

Indicates that the remaining rows after the initial quantity specified in the CacheSize

property should be fetched asynchronously.

'-----------------------------------------------------------------------------

Private Sub OpenNewRecordset()

'-----------------------------------------------------------------------------

Set mobjADORst = New ADODB.Recordset


74/119

mobjADORst.CursorLocation = adUseClient

mobjADORst.Open mstrSQL, mobjADOConn, adOpenStatic, , adCmdText

' display current query

txtCurrentQuery.Text = mstrSQL

End Sub

6. Create the user-defined subprocedure DataLoad. This subprocedure gets the datafrom the recordset and puts each field into a text box. Data from the recordset isaccessed via the Fields collection.

The Fields collection in ADO works identically to the Fields collection in DAO. Afield can be referenced with or without specifying Fields, either by the field name inquotes or by its ordinal position in the resultset. The field can also be referenced withthe bang (!) operator. All of the following would be valid ways of referencing thefield "propno":

mobjADORst.Fields("propno")

mobjADORst ("propno")

mobjADORst.Fields(0)

mobjADORst(0)

mobjADORst!propno

'-----------------------------------------------------------------------------

Private Sub DataLoad()

'-----------------------------------------------------------------------------


75/119


'copy the data from the recordset to the text boxes:

txtPropNo.Text = mobjADORst.Fields("propno")

txtEmpNo.Text = mobjADORst.Fields("empno")

txtAddress.Text = mobjADORst.Fields("address")

txtCity.Text = mobjADORst.Fields("city")

txtState.Text = mobjADORst.Fields("state")

txtZip.Text = mobjADORst.Fields("zip")

Call SetRecNum

Exit Sub

LocalError:


End Sub

7. Create the user-defined subprocedure SetRecNum. This sub displays the number ofthe current record at the bottom of the screen. The AbsolutePosition and RecordCountproperties of the Recordset are used here.

The AbsolutePosition property specifies the current row in a recordset. Note: ForAbsolutePosition to return a valid value with Access (Jet) databases (like the one used

in the sample application), the CursorLocation must be set to adUseClient. An invalidvalue (-1) will be returned if adUseClient is specified.

The RecordCount property the total number of rows in the recordset. Note:RecordCount will not return a valid value with all cursor types (for example,RecordCount will return -1 with a forward-only cursor.) To ensure a valid


76/119

RecordCount value, use either adOpenKeyset or adOpenStatic as the CursorType forserver side cursors or use a client side cursor.

'-----------------------------------------------------------------------------

Private Sub SetRecNum()

'-----------------------------------------------------------------------------

StatusBar1.SimpleText = "row " & mobjADORst.AbsolutePosition _

& " of " & mobjADORst.RecordCount

End Sub

8. Code the events for the navigation buttons as shown below, using the recordset"Move" methods to move to the first, last, next, or previous record, respectively.

'-----------------------------------------------------------------------------

Private Sub cmdMoveFirst_Click()

'-----------------------------------------------------------------------------


mobjADORst.MoveFirst

Call DataLoad

Exit Sub

LocalError:


End Sub

'-----------------------------------------------------------------------------

Private Sub cmdMoveLast_Click()


77/119

'-----------------------------------------------------------------------------


mobjADORst.MoveLast

Call DataLoad

Exit Sub

LocalError:


End Sub

'-----------------------------------------------------------------------------

Private Sub cmdMoveNext_Click()

'-----------------------------------------------------------------------------


mobjADORst.MoveNext

If mobjADORst.EOF Then

Beep

mobjADORst.MoveLast

End If

Call DataLoad

Exit Sub

LocalError:


End Sub


78/119

'-----------------------------------------------------------------------------

Private Sub cmdMovePrevious_Click()

'-----------------------------------------------------------------------------


mobjADORst.MovePrevious

If mobjADORst.BOF Then

Beep

mobjADORst.MoveFirst

End If

Call DataLoad

Exit Sub

LocalError:


End Sub

9. When one of the check boxes is clicked, the label and text box next to it should beenabled (or disabled, if clicking the check box unchecks it). Note also that thecmdGetData button (the one with the "Run Query Now" caption) should only beenabled if one of the checkboxes is checked.

'-----------------------------------------------------------------------------

Private Sub chkCriteria_Click(Index As Integer)

'-----------------------------------------------------------------------------

' disable the 'Run Query Now' button


79/119

cmdGetData.Enabled = False

'when the user clicks on a check box, enable the label and text

'box that go with it.

If chkCriteria(Index).Value = vbChecked Then

txtCriteria(Index).Enabled = True

lblCriteria(Index).Enabled = True

txtCriteria(Index).SetFocus

txtCriteria(Index).SelStart = 0

txtCriteria(Index).SelLength = Len(txtCriteria(Index).Text)

' enable the 'Run Query Now' button only if a box is checked.

cmdGetData.Enabled = True

Else

txtCriteria(Index).Enabled = False

lblCriteria(Index).Enabled = False

End If

End Sub

10. After the user has selected which fields to use and entered values in the text boxes,they click the cmdGetData button to create a new recordset with new data. Note that ifthe user selects (checks) a field, but does not enter search criteria in the correspondingtextbox, an error message is generated and the query is not run.

'-----------------------------------------------------------------------------

Private Sub cmdGetData_Click()

'-----------------------------------------------------------------------------


80/119


81/119

mstrSQL = mstrSQL & " and"

End If

blnFirstOne = False

mstrSQL = mstrSQL & " city like '" & txtCriteria(1).Text & "'"

End If

If chkCriteria(2).Value = vbChecked Then

If txtCriteria(2).Text = "" Then

MsgBox "State criteria is missing. Query not run.", _

vbExclamation, _

"ADO Example"

Exit Sub

End If

If blnFirstOne = False Then

mstrSQL = mstrSQL & " and"

End If

blnFirstOne = False

mstrSQL = mstrSQL & " state like '" & txtCriteria(2).Text & "'"

End If

OpenNewRecordset

'make sure that the query did not return 0 rows:

If mobjADORst.EOF Then

MsgBox "Your query (" & mstrSQL & ") returned no records! " _

& "The default query to return all records will now be rerun.", _

vbExclamation, _

"ADO Example"


82/119

'reload the form with all the records

cmdAllData_Click

Else

MsgBox "Your query returned " & mobjADORst.RecordCount & " records.", _

vbInformation, _

"ADO Example"

'load data into the text boxes

Call DataLoad

End If

Exit Sub

LocalError:


End Sub

11. Save and run. Note: When entering the "Like" criteria for City and/or State, youcan use the wildcard character % to represent any number of characters and thewildcard character _ (underscore) the represent a single character. For example,entering "M%" for the City criteria would return all rows where the city field beginswith the letter "M".

Download the project files for this sample application here.

Sample Application 3

Sample Application 3 demonstrates how to add, update, and delete records with ADO.


83/119

When the application is first run, the user is prompted to enter a minimum askingprice to possibly limit the number of records they want to work with (i.e., "I only wantto work with properties that are selling for $200,000 or more). If the user wants towork with all properties, they would simply accept the default of 0 from the prompt.If the user clicks the Cancel button, the application will end.

Once the user has entered the minimum asking price, the main screen of theapplication is displayed. Initially, the screen is in "browse" mode, where the user canuse the navigation buttons to move to the first, previous, next or last record. The datacannot be edited in this mode. If they want to initiate an add or an update, delete arecord, or exit the application, they may do so via the appropriate button. Saving orcancelling is not applicable in this mode, so those buttons are disabled.

If the user clicks the Add button, the fields on the screen are enabled and cleared, andthe user can enter the information for the new property. All buttons except Save andCancel are now disabled. After the user has made entries in the fields, he or she wouldclick Save to add the new record to the database table, or, if they changed their mind,would click Cancel to discard the new record. In either case (clicking Save or Cancel)the user is returned to browse mode. When Save is clicked, the application validatesthe entries and will only save the record if all fields pass edit (otherwise, a messagewill appear indicating the problem entry and focus will be set to the problem field).

If the user clicks the Update button, the fields on the screen are enabled and the usercan modify any or all of the fields (except for the Property Number, which is theprimary key of the table). All buttons except Save and Cancel are now disabled. Afterthe user has made modifications in the desired fields, he or she would click Save toupdate the record to the database table, or, if they changed their mind, would clickCancel to discard the changes. In either case (clicking Save or Cancel) the user isreturned to browse mode. When Save is clicked, the application validates the entries

and will only save the record if all fields pass edit (otherwise, a message will appearindicating the problem entry and focus will be set to the problem field).


84/119

If the user clicks the Delete button, the user is asked to confirm that they want todelete the current record. If they respond Yes, the record is deleted from the databasetable, and the main screen shows the next record in the table.

To build Sample Application 3, start a new VB project and perform the followingsteps.

From the Project -> References menu, check Microsoft ActiveX Data Objects2.x Library and click OK.

This project uses the StatusBar control, so include the Microsoft WindowsCommon Controls 6.0 (SP6) from the Components dialog box, accessed from theProject -> Components menu. Check this item and click OK.

Create the form shown below. The settings for the various controls are givenbelow.

There are nine textboxes in the main frame of the form. The names andMaxLength settings for these are given below:

Name

Properties

txtPropNo


85/119

MaxLength: 5

txtEmpNo

MaxLength: 4

txtAddress

MaxLength: 20

txtCity

MaxLength: 15

txtState

MaxLength: 2

txtZip

MaxLength: 5

txtBeds

MaxLength: 1

txtBaths

MaxLength: 3 (allows fractional amount, like 1.5)

txtAsking

MaxLength: 0 (not specified)

Set up the Command Buttons as follows:


86/119

Name

Caption

cmdMoveFirst

cmdMoveLast

>>

cmdAdd

Add

cmdUpdate

Update

cmdDelete

Delete

cmdSave

Save

cmdCancel

Cancel


87/119


88/119

Option Explicit

Public Const gstrNUMERIC_DIGITS As String = "0123456789"

Public Const gstrUPPER_ALPHA_PLUS As String ="ABCDEFGHIJKLMNOPQRSTUVWXYZ,'-"

Public gblnPopulating As Boolean

'------------------------------------------------------------------------

Public Sub CenterForm(pobjForm As Form)

'------------------------------------------------------------------------

With pobjForm

.Top = (Screen.Height - .Height) / 2

.Left = (Screen.Width - .Width) / 2

End With

End Sub

'------------------------------------------------------------------------

Public Function ValidKey(pintKeyValue As Integer, _

pstrSearchString As String) As Integer

'------------------------------------------------------------------------


89/119


90/119

' when called from the KeyPress event.

' Typical call:

' KeyAscii = ConvertUpper(KeyAscii)

'

If Chr$(pintKeyValue) >= "a" And Chr$(pintKeyValue)


91/119

'-----------------------------------------------------------------------------

Public Sub TabToNextTextBox(pobjTextBox1 As TextBox, pobjTextBox2 AsTextBox)

'-----------------------------------------------------------------------------

use of all basic controls@

Documents