85931564.

doc

Quick Summary Code Book Visual Basic for Applications By Professor Paine

[Link] CONNECT TO A DATABASE:.........................................................................................3 OPEN A RECORDSET:......................................................................................................3 PROCESS A RECORDSET:...............................................................................................4 To get values from recordset and assign them to variables:............................................4 To set values in a recordset:.............................................................................................4 To loop through all records in a recordset:......................................................................4 To add a new record to a recordset:.................................................................................5 To delete a record from a recordset:................................................................................5 To update a record in a recordset:....................................................................................5 FORMS:...............................................................................................................................5 To get values from a form control:..................................................................................5 To set values on a form:...................................................................................................5 SUB PROCEDURES:..........................................................................................................5 Calling a sub procedure (stored in a module)..................................................................6 Defining a sub procedure to match call...........................................................................6 MORE ADVANCED PROGRAMMING: .........................................................................7 How to add multiple values to a combo box....................................................................7 Sample Code (combo box):.........................................................................................8 How to pass values from form to form using arguments:................................................9 How set a filter on a form so that it displays only records with matching filter conditions.........................................................................................................................9 Module Utilities...............................................................................................................9 Procedure to connect to a database..............................................................................9 Login Function:..........................................................................................................10 How to create a new user and open a GUI based on user type:.................................11

[Link]

CONNECT TO A DATABASE:
1. Define a database connection object Dim dbsConnection As Database 2. Define a variable to store a string containing the path to the database Dim dbString as string 3. Set dbString to point to the database dbstring = "C:\SUNY Morrisville \Grade Calculator [Link]" OR using a network path (recommended) dbString = "\\engernt1\painefa$\Customer [Link]" 4. Establish Connection Set dbsConnection = OpenDatabase(dbstring) 5. When finished, close the database connection [Link]

OPEN A RECORDSET:
A recordset is a set of records that are returned when accessing a database. It can be all records from a single table or a filtered list from a SQL query. 1. Define a recordset object Dim rsUser As Recordset 2. Define a variable to store the value of the recordset object to be retrieved Dim sqlString as String 3. Determine the set of records to be retrieved If a single table then

[Link]

Set recordset string to point to the table name sqlString = tblUsers Set rsUser = [Link](sqlString) If using a SQL statement (used to combine multiple tables or get particular data only) then sqlString = "SELECT tblUsers.* & _ " FROM tblUsers" & _ " WHERE ((([Link])='" & UserName & "') AND (([Link])='" & Pwd & "'));" Set rsUser = [Link](sqlString) 4. When finished close the recordset object [Link]

PROCESS A RECORDSET:
To get values from recordset and assign them to variables:
FirstName = rsUser!FirstName (note that the field FirstName must exist in the recordset)

To set values in a recordset:

rsUser!FirstName = FirstName

To loop through all records in a recordset:

Do while not [Link] Do something. [Link] Loop

[Link]

To add a new record to a recordset:

[Link] rsUser!FirstName = FirstName [Link]

To delete a record from a recordset:

[Link] rsUser!FirstName = FirstName [Link]

To update a record in a recordset:

rsUser!FirstName = FirstName [Link]

FORMS:
To get values from a form control:
1. Set up Variables to store form information Dim UserName As String Dim Pwd As String 2. Get the values from the form and assign to variables UserName = Forms!frmLogin![Link] Pwd = Forms!frmLogin![Link]

To set values on a form:

Forms![Form Name]![Name of Control].Value Example: Forms!frmGPA![Link] = TotalQualityPoints / TotalCreditHours

SUB PROCEDURES:

[Link]

Calling a sub procedure (stored in a module)

' Pass the user name and password to mdlLogin Call procLogin(UserName, Pwd) NOTE: If you are passing more than 1 parameter to a sub procedure then you must use the Call keyword. If you are passing zero or one then you can NOT use the Call keyword

Defining a sub procedure to match call

NOTE: Datatypes must match between what is passed and what is defined. Here two strings are passed. Sub procLogin(UserName As String, Pwd As String) You can now use these variables anywhere within your code module.

[Link]

MORE ADVANCED PROGRAMMING:

Most forms capture data as strings. You may not want that. If you need to get a value from a form and convert it to an integer value then: UserID = CInt([Link])

How to add multiple values to a combo box

ProfessorID = rsUser!ProfessorID ProfessorName = rsUser!ProfessorName Key is to build an itemstring with all values separated by a semicolon itemstring = ProfessorID & ";" & ProfessorName Forms!frmbuildcourses![Link] Item:=itemstring This will produce the following result when executed in a loop:

[Link] Note that both the ProfessorID and their name are displayed. To only see the Professors name then set the Property setting for Column Widths for the Combo Box on the form to 0;1. This will only hide the primary key ProfessorID, not delete it.

Sample Code (combo box):

Sub procFillComboBox() Dim dbsConnection As Database Dim rsUser As Recordset Dim dbstring As String Dim sqlString As String ' Set SQL string to get professors and their unique id's sqlString = "SELECT [Link], [Link]" & _ " FROM tblProfessor ORDER BY [Link];" ' Connect to database by calling a module procedure. Set dbsConnection = procConnectToDatabase() ' Get tblProfessors Set rsUser = [Link](sqlString) If [Link] Then ' if eof then user was found with that username/ password MsgBox ("No professors available.") ' close recordsets and database connection [Link] [Link] Exit Sub Else ' Get records from recordset and put them in combo box Do While Not [Link] ProfessorID = rsUser!ProfessorID ProfessorName = rsUser!ProfessorName itemstring = ProfessorID & ";" & ProfessorName Forms!frmbuildcourses![Link] Item:=itemstring [Link] Loop ' Clean up database components [Link] [Link] End If

[Link]

How to pass values from form to form using arguments:

Example: To pass a variable to a form: [Link] "frmStudentMain", , , , , , rsUser!UserID In this case the UserID is passed to the form frmStudentMain. This value can then be retrieved via the OpenArgs property which is invoked by the Open event for a form. txtUserID = Forms![Link]. You can create a control on your form with its visible property set to No. Here you can temporarily store this value and then pass it as needed to other procedures.

How set a filter on a form so that it displays only records with matching filter conditions
Create form with either a source of a single table or a SQL query. In the example below the field named UserID is part of the data source for the form. Open form with condition property set [Link] "frmPreferences", acNormal, , "[EmployeeID] =" & Forms! [Link]

Module Utilities
Procedure to connect to a database
Function procConnectToDatabase() As Database Dim dbsConnection As Database Dim dbstring As String ' Set database location dbstring = "C:\SUNY Morrisville\Fall 2003\CITA 220\Login [Link]"

[Link] ' Connect to database and assign database object to function Set procConnectToDatabase = OpenDatabase(dbstring) End Function

Login Function:
Sub procLogin(UserName As String, Pwd As String) Dim dbsConnection As Database Dim rsUser As Recordset Dim dbstring As String Dim sqlString As String Dim UserType As String ' Set SQL string to only get one record corresponding to username/password sqlString = "SELECT [Link], [Link], [Link]" &_ " FROM tblUsers" & _ " WHERE ((([Link])='" & UserName & "') AND (([Link])='" & Pwd & "'));" ' Connect to database by calling a module procedure. Set dbsConnection = procConnectToDatabase() ' Get tblUsers record with username password Set rsUser = [Link](sqlString) ' Was a single record found that matches username/ password entered? If [Link] Then ' if eof then no user was found with that username/ password MsgBox ("Wrong user name and/or password") Forms!frmLogin![Link] Else [Link] ' open form passing the userid of the user logged in [Link] "frmMain", , , , , , rsUser!UserID End If [Link] [Link] End Sub

10

[Link]

How to create a new user and open a GUI based on user type:
Sub procCreateNewUser(FirstName As String, LastName As String, Username As String, Pwd As String, UserType As String) Dim dbsConnection As Database Dim rsEmployee As Recordset Dim dbstring As String Dim sqlString As String ' Set SQL string to check for record corresponding to username/password entered sqlString = "SELECT [Link], [Link], [Link], [Link]" & _ " FROM tblEmployee" & _ " WHERE ((([Link])='" & Username & "') AND (([Link])='" & Pwd & "'));" ' Connect to database by calling a module procedure. Set dbsConnection = procConnectToDatabase() ' Get tblEmployee record with username password Set rsEmployee = [Link](sqlString) ' Was a record found that matches username/ password entered? ' If yes then tell user and have them enter another one If Not [Link] Then ' if not eof then user was found with that username/ password MsgBox ("Username and Password already taken. Please select another.") ' close recordsets and database connection [Link] [Link] Exit Sub Else ' Close recordset and open another to add new record for new user [Link] sqlString = "tblEmployee" ' Enter record Set rsEmployee = [Link](sqlString) ' Add new record

11

[Link] [Link] rsEmployee!FirstName = FirstName rsEmployee!LastName = LastName rsEmployee!Username = Username rsEmployee!Password = Pwd rsEmployee!UserType = UserType ' Commit [Link] ' If succesful then provide message MsgBox ("Registration successful! Welcome!") ' Close New User Form [Link] ' Clean up database components [Link] [Link] ' And open appropriate GUI Select Case UserType Case 1: ' Student logged in, open Student GUI [Link] ("frmStudentMain") Case 2: ' Professor Logged In, open Professor GUI [Link] ("frmProfessorMain") Case 3: ' Admin logged in, open Admin GUI [Link] ("frmAdminMain") Case 4: ' Customer logged in, open Customer GUI [Link] ("frmCustomerMain") End Select End If End Sub

12