Double-click the Command Button control on the Visual Basic toolbox to add a new button to the form.
Set the following properties for the new button:
Name cmdExit
Drag the new button to align it with the Add and Delete buttons.
Caption E&xit
Enter the following code in the cmdExit_Click procedure:
Sub cmdExit_Click() End End Sub
Save your changes and execute your program.
Click on the Exit button to stop the program.
Sub Command1_Click() datTitles.Recordset.AddNew ' Add a new record to the table Text1.SetFocus ' Set the focus to the Text1 control (added chp3 exercise #3 End Sub
| Field | Data Type | VISUAL BASIC Type |
|---|---|---|
| SSNo | Text | String |
| Last | Text | String |
| First | Text | String |
| Age | Byte | Integer |
| City | Text | String |
| St | Text | String |
| Comments | Memo | String |
| Property | Setting |
|---|---|
| DataSource | datFieldTypes |
| DataField | BOOLEAN |
| Name | chkBoolean |
Run your program and check the BOOLEAN box. Notice that nothing happens to the BOOLEAN text field. Now move to the subsequent record, and then return. You should see -1 displayed in the BOOLEAN text field.
This example shows how to use a checkbox to enter values into fields. Your program can now reference this field and get the value as -1 (yes) or 0 (no), which are the only two values that can be in a BOOLEAN type data field.
Complete the following steps to build your data table and enter data:
It is recommended that you not depend on the combination of the LastName and the FirstName fields as the unique identifier for the record. It is quite possible to have two people with the same first and last name. Also, complex multifield primary keys make query design much more difficult.
If you have followed the exercises in this chapter closely, you may remember that we did build a key for a table using the last name, first name field combination. This was for illustrative purposes only and was meant only to show how to combine fields in a key. This practice is not recommended for combinations of fields that could have multiple occurrences (How many John Smiths are there in the world?), and is definitely taboo for primary keys. In fact, a good rule of thumb to remember is that the need for a multifield primary key is indicative of a database that needs to be redesigned.
Sub Form_Load() 'Create a database and dynaset object Dim Dat as Database Dim dyn1 as Dynaset 'Declare standard variables Dim cDBN ame as String Dim cTable as String 'Initialize variables cDBName = "c:\DATAS\ACCTPAY.MDB" cTable = "Vendors" 'Set values set Dat = OpenDatabase(cDBName) Set dyn1 = Dat.CreateDynaset(cTable) End Sub
Your code could look as follows:
Sub Form_Load() 'Create a database and snapshot object Dim Dat as Database Dim snpObject as Snapshot 'Declare standard variables Dim cDBName as String Dim cTable as String 'Initialize variables cDBName = "c:\DATAS\ACCTPAY.MDB" cTable = "Vendors" 'Set values set Dat = OpenDatabase(cDBName) Set snpObject = Dat.CreateSnapshot(cTable) End Sub
Sub Form_Load() 'Create a database and table object Dim Dat as Database Dim tblObject as Table 'Declare standard variables Dim cDBName as String Dim cTable as String 'Initialize variables cDBName = "c:\DATAS\ACCTPAY.MDB" cTable = "Vendors" 'Set values set Dat = OpenDatabase(cDBName) Set tblObject = Dat.OpenTable(cTable) End Sub
Data1.DatabaseName = "C:\DATAPATH\XYZ.MDB"
Data1.RecordSource = "Vendors"
It is better form, however, to assign the RecordSource to a variable that has been defined and points to the data table. Here's an example:
Dim cTable as String' Declare the variable cTable = "Vendors" ' Establish the name of the table Data1.RecordSource = cTable ' Set the data control Data1.Refresh ' Update the data control
Sub OpenDB() 'Declare the variable for the name of the database Dim cDBName as String 'Assign the variable to a database, including the path cdbName = App.Path + " \Students.MDB" 'Set the name of the database used by the data control Data1.DatabaseName = cDBName 'Refresh and update the data control Data1.Refresh End Sub
Sub OpenDB() 'Declare the variable for the name of the database Dim cDBName as String 'Declare the variable for the table Dim cTable as String 'Assign the variable to a database, including the path cdbName = App.Path + "\Students.MDB" 'Assign the variable to the appropriate table cTable = "Addresses" 'Set the name of the database used by the data control Data1.DatabaseName = cDBName 'Set the name of the table used by the data control Data1.RecordSource = cTable 'Refresh and update the data control Data1.Refresh End Sub
Sub OpenDB() Dim cDBName as String Dim cTable as String Dim cField1 as String Dim cField2 as String Dim cField3 as String Dim cField4 as String Dim cField5 as String 'Assign variables cdbName = App.Path + "\Students.MDB" cTable = "Addresses" cField1 = "StudentID cField2 = "Address" cField3 = "City" cField4 = "State" cField5 = "Zip" 'Set the data control properties Data1.DatabaseName = cDBName Data1.RecordSource = cTable 'Bind the text fields txtStudentID.DataField = cField1 txtAddress.DataField = cField2 txtCity.DataField = cField3 txtState.DataField = cField4 txtZip.DataField = cField5 'Refresh and update the data control Data1.Refresh End Sub
Sub FieldName_KeyPress(KeyAscii as Integer)
If KeyAscii >26 then 'If anything other than a control code
If Chr(KeyAscii) >= "a" and Chr(KeyAscii) <= "z" Then
KeyAscii = KeyAscii - 32 ' Capitalize small letters
Else
KeyAscii = 0 ' No input from keyboard
End if
End if
End Sub
Sub cmdOK_Click ()
Dim nOK as Integer ' Declare a test variable
nOK = True
If Len(Trim(txtDate)) = 0 then ' Check for entry (exclusive of spaces)
MsgBox "Input is required in the txtDate field before this record can be saved" ' Issue a message if no data is entered
nOK = False 'Set test variable to False
txtDate.SetFocus ' Place cursor in txtDate
End if
If nOK = True then
Unload Me ' Exit form if data is entered
End if
End sub
Sub Form_Load()
'Load the combo box
cboEmployees.AddItem "Smith"
cboEmployees.AddItem "Andersen"
cboEmployees.AddItem "Jones"
cboEmployees.AddItem "Jackson"
End sub
You set the Sorted property of the combo box to True to alphabetically sort the information displayed in the combo box. This property can only be set at design time.
Count({NameLast})
File | New | JET Engine MDB | Version 2.0 MDB
Next, enter the path and the name of the database and save.
Once the table is copied, you should then go into the table design and add a primary key. Build this index the same way you built the primary key for the tblCustomers table.
Review the file. Notice that empty fields in a record are denoted by the use of two commas (,,).
SELECT * FROM Customers
SELECT * FROM Customers ORDER BY CustomerID
SELECT CustomerID AS Customer FROM Customers
DBEngine.RepairDatabase DatabaseName
DBEngine.CompactDatabase oldDatabase, newDatabase, locale, options
Please note that the oldDatabase and newDatabase require the database name and path.
Database.CreateTableDef(table name)
Drop a command button onto a form, name it cmdCreate, and then enter the following code:
Private Sub cmdCreate_Click()
On Error Resume Next
'Define Variables
Dim dbFile As DATABASE
Dim cDBName As String
Dim tdTemp As TableDef
Dim fldTemp As Field
Dim idxTemp As Index
Dim relTemp As Relation
Dim ctblCustomers As String
Dim ctblCustomerTypes As String
Dim cidxCustomers As String
Dim cidxCustomerTypes As String
Dim crelName As String
'Set variables
cDBName = App.Path + "\10ABCEX.MDB"
ctblCustomers = "Customers"
ctblCustomerTypes = "CustomerTypes"
cidxCustomers = "PKCustomers"
cidxCustomerTypes = "PKCustomerTypes"
crelName = "relCustomerType"
'Delete the database if it already exists
Kill cDBName
'Create the database
Set dbFile = CreateDatabase(cDBName, dbLangGeneral, dbVersion20)
'Create the Customers table
Set tdTemp = dbFile.CreateTableDef(ctblCustomers)
'Insert fields into the Customers table
Set fldTemp = tdTemp.CreateField("CustomerID", dbText, 10)
tdTemp.Fields.Append fldTemp
Set fldTemp = tdTemp.CreateField("Name", dbText, 50)
tdTemp.Fields.Append fldTemp
Set fldTemp = tdTemp.CreateField("Address1", dbText, 50)
tdTemp.Fields.Append fldTemp
Set fldTemp = tdTemp.CreateField("Address2", dbText, 50)
tdTemp.Fields.Append fldTemp
Set fldTemp = tdTemp.CreateField("City", dbText, 25)
tdTemp.Fields.Append fldTemp
Set fldTemp = tdTemp.CreateField("StateProv", dbText, 25)
tdTemp.Fields.Append fldTemp
Set fldTemp = tdTemp.CreateField("Zip", dbText, 10)
tdTemp.Fields.Append fldTemp
Set fldTemp = tdTemp.CreateField("Phone", dbText, 14)
tdTemp.Fields.Append fldTemp
Set fldTemp = tdTemp.CreateField("CustomerType", dbText, 10)
tdTemp.Fields.Append fldTemp
'Build the Primary Key index to the Customers table
Set idxTemp = tdTemp.CREATEINDEX(cidxCustomers)
idxTemp.PRIMARY = True
idxTemp.Required = True
Set fldTemp = tdTemp.CreateField("CustomerID")
idxTemp.Fields.Append fldTemp
tdTemp.Indexes.Append idxTemp
'Add the Customers table to the databases
dbFile.TableDefs.Append tdTemp
'Create the Customer Types table
Set tdTemp = dbFile.CreateTableDef(ctblCustomerTypes)
'Insert fields into the Customer Types table
Set fldTemp = tdTemp.CreateField("CustomerType", dbText, 10)
tdTemp.Fields.Append fldTemp
Set fldTemp = tdTemp.CreateField("Description", dbText, 10)
tdTemp.Fields.Append fldTemp
'Build the Primary Key index for the Customer Types table
Set idxTemp = tdTemp.CREATEINDEX(cidxCustomerTypes)
idxTemp.PRIMARY = True
idxTemp.Required = True
Set fldTemp = tdTemp.CreateField("CustomerType")
idxTemp.Fields.Append fldTemp
tdTemp.Indexes.Append idxTemp
'Add the CustomerTypes table to the database
dbFile.TableDefs.Append tdTemp
'Create the relationship
Set relTemp = dbFile.CreateRelation(crelName)
relTemp.TABLE = ctblCustomerTypes
' The tabl that contains the validation information
relTemp.ForeignTable = ctblCustomers
' The table that utilizes the validation table
Set fldTemp = relTemp.CreateField("CustomerType")
fldTemp.ForeignName = "CustomerType"
relTemp.Fields.Append fldTemp
dbFile.Relations.Append relTemp
'Issue a message when the procedure is completed
MsgBox "Database build is complete"
End Sub
Perform the following steps to complete the addition of the ZipCity form:
Private Sub mnuListZip_Click()
'Open the ZipCity form
frmZipCity.Show 1
End Sub
Option Explicit Dim dbFile As DATABASE Dim cDBName As String Dim rsFile As Recordset Dim cRSName As String Dim nBtnAlign As Integer Dim nResult As Integer
Sub StartProc()
'
' open db and rs
'
' on error goto StartProcErr
'
cDBName = App.Path + "\master.mdb"
cRSName = "ZipCity"
'
nResult = RSOpen(cDBName, cRSName, dbOpenDynaset, dbFile, rsFile)
If nResult = recOK Then
nResult = RecInit(Me)
End If
'
If nResult = recOK Then
nResult = RecRead(Me, rsFile)
End If
'
GoTo StartProcExit
'
StartProcErr:
RecError Err, Error$, "StartProc"
GoTo StartProcExit
'
StartProcExit:
'
End Sub
Private Sub Form_Load()
' initialize and start up
'
StartProc ' open files
nResult = RecEnable(Me, False) ' turn off controls
nBtnAlign = btnAlignBottom ' set aligment var
BtnBarInit Me, nBtnAlign ' create button set
BtnBarEnable Me, "11111111" ' enable all buttons
End Sub
Private Sub Form_Resize()
BtnBarInit Me, nBtnAlign ' repaint buttons
End Sub
Private Sub Form_Unload(Cancel As Integer)
dbFile.Close ' safe close
End Sub
From this point, save your work and run the project. Please note that most of this code can be copied directly from the StateProv example.
gphCopy copies the graph to the Windows Clipboard.
gphDraw draws the graph on-screen.
Dim nPoints as Integer Dim rsData as Recordset rsData.Movelast nPoints = rsData.RecordCount
You can complete this project by performing the following steps:
Private Sub cmdPie_Click()
Dim rsFile As Recordset
Dim dbFile As DATABASE
Dim cSQL As String
Dim cField As String
Dim cTitle As String
Dim cLegend As String
Dim cLabel As String
Dim dbName As String
cSQL = "Select * from Activity WHERE month =1"
cField = "Passengers"
cLegend = ""
cLabel = "Airline"
cTitle = "Market Share for January"
dbName = "c:\abc\ch12\12abcex.mdb"
Set dbFile = DBEngine.OpenDatabase(dbName)
Set rsFile = dbFile.OpenRecordset(cSQL, dbOpenSnapshot)
ShowGraph gphPie3D, rsFile, cField, cTitle,
cFldLegend:=cLegend, cFldLabel:=cLabel
End Sub
Private Sub cmdLine_Click()
Dim rsFile As Recordset
Dim dbFile As DATABASE
Dim cSQL As String
Dim cField As String
Dim cTitle As String
Dim cLegend As String
Dim cLabel As String
Dim dbName As String
cSQL = "Select Month, Sum(Passengers) as TotPassengers from
Activity Group by Month;"
cField = "TotPassengers"
cLegend = ""
cLabel = "Month"
cTitle = "Total Activity"
dbName = "c:\abc\ch12\12abcex.mdb"
Set dbFile = DBEngine.OpenDatabase(dbName)
Set rsFile = dbFile.OpenRecordset(cSQL, dbOpenSnapshot)
ShowGraph gphLine, rsFile, cField, cTitle,
cFldLegend:=cLegend, cFldLabel:=cLabel,
cLeftTitle:="Passengers", cBottomTitle:="Month"
End Sub
Private Sub cmdBar_Click()
Dim rsFile As Recordset
Dim dbFile As DATABASE
Dim cSQL As String
Dim cField As String
Dim cTitle As String
Dim cLegend As String
Dim cLabel As String
Dim dbName As String
cSQL = "Select * from Activity WHERE Airline='ABC';"
cField = "Passengers"
cLegend = ""
cLabel = "Month"
cTitle = "ABC Airlines Annual Activity"
dbName = "c:\abc\ch12\12abcex.mdb"
Set dbFile = DBEngine.OpenDatabase(dbName)
Set rsFile = dbFile.OpenRecordset(cSQL, dbOpenSnapshot)
ShowGraph gphBar3D, rsFile, cField, cTitle,
cFldLegend:=cLegend, cFldLabel:=cLabel,
cLeftTitle:="Passengers", cBottomTitle:="Month"
End Sub
Complete the following steps to build this form:
| DatabaseName | C:\VB4\BIBLIO.MDB (include appropriate path) |
| RecordSource | Publishers |
Private Sub DBList1_Click()
Dim cFind As String
cFind = "PubID=" + Trim(DBList1.BoundText)
Data2.Recordset.FindFirst cFind
End Sub
Private Sub Data2_Reposition()
Dim cSQL As String
cSQL = "Select * from Titles WHERE PubID=" + Trim(Text1(0))
Data3.RecordSource = cSQL ' filter the data set
Data3.Refresh ' refresh the data control
DBGrid1.ReBind ' refresh the data grid
End Sub
Private Sub Command1_Click()
On Error GoTo Command1Clickerr
Dim cMsg As String ' Declare string
Open "C:\ABC.TXT" For Input As 1 'Open file
GoTo Command1ClickExit
'Error handler
Command1Clickerr:
If Err = 53 Then
cMsg = "Unable to open ABC.TXT" + Chr(13)
MsgBox cMsg, vbCritical, "Command1Click"
Unload Me
End
Else
MsgBox Str(Err) + " - " + Error$, vbCritical, "Command1Click"
Resume Next
End If
'Routine exit
Command1ClickExit:
End Sub
Private Sub Command2_Click()
On Error GoTo Command2ClickErr
'Declare variables
Dim cFile As String
Dim cMsg As String
Dim nReturn As Integer
'Define the file to open
cFile = "C:\ABC.TXT"
'Open the file
Open cFile For Input As 1
MsgBox "ABC.TXT has been opened."
GoTo Command2ClickExit
'Error handler
Command2ClickErr:
If Err = 53 Then
cMsg = "Unable to open ABC.TXT!" + Chr(13)
cMsg = cMsg + "Select OK to locate this file. "
cMsg = cMsg + "Select CANCEL to exit this program." + Chr(13)
nReturn = MsgBox(cMsg, vbCritical + vbOKCancel, "Command2Click")
If nReturn = vbOK Then
CommonDialog1.filename = cFile
CommonDialog1.DefaultExt = ".txt"
CommonDialog1.ShowOpen
Resume
Else
Unload Me
End If
Else
MsgBox Str(Err) + " - " + Error$
Resume Next
End If
'Routine exit
Command2ClickExit:
End SubCREATE TABLE TableName (Field1 TYPE(SIZE), Field2 TYPE(SIZE), ...);
You first enter CREATE TABLE, followed by the name of the table, and then the fields in parentheses. The field types and sizes (sizes apply to TEXT columns only) are entered after each field.
ALTER TABLE <Name of Table> ADD COLUMN <Name of column> <Type> <Size>;
DROP TABLE <Table Name>;
// Create the database
dbmake C:\CUSTOMER\CH15EX.MDB;
// Build the Customer Types Table
CREATE TABLE CustomerType(
CustomerType TEXT(6) CONSTRAINT PKCustomerType PRIMARY KEY,
Description TEXT(30));
// Build the Customers table
CREATE TABLE Customers(
CustomerID TEXT(10) Constraint PKCustomerID PRIMARY KEY,
Name TEXT(30),
CustomerType TEXT(6) CONSTRAINT FKCustomerType
REFERENCES CustomerType(CustomerType),
Address TEXT(30),
City TEXT(30),
State TEXT(30),
Zip TEXT(10),
Phone TEXT(14),
Fax TEXT(14));
// Build the index on Zip
CREATE INDEX SKZip on Customers(Zip);
//Display the results
SELECT * FROM CustomerType;
SELECT * FROM Customers;
INSERT INTO TableName(field1, field2,...) VALUES(value1, value2,...);
INSERT INTO TargetTable SELECT field1, field2 FROM SourceTable;
UPDATE <table name> SET <field to update> = <New Value>;
SELECT field1, field2 INTO DestinationTable FROM SourceTable;
In this statement, field1 and field2 represent the field names in the source table.
DELETE FROM TableName WHERE field = value;
INSERT INTO CustomerType VALUES('INDV', 'Individual');
INSERT INTO CustomerType VALUES('BUS', 'Business - Non-corporate');
INSERT INTO CustomerType VALUES('CORP', 'Corporate Entity');
INSERT INTO Customers VALUES('SMITHJ', 'John Smith', 'INDV',
'160 Main Street', 'Dublin', 'Ohio', '45621',
'614-569-8975', '614-569-5580');
INSERT INTO Customers VALUES('JONEST', 'Jones Taxi', 'BUS',
'421 Shoe St.', 'Milford', 'Rhode Island', '03215',
'401-737-4528', '401-667-8900');
INSERT INTO Customers VALUES('JACKSONT', 'Thomas Jackson', 'INDV',
'123 Walnut Street', 'Oxford', 'Maine', '05896',
'546-897-8596', '546-897-8500');
// Create the database
dbmake C:\CUSTOMER\CH15EX.MDB;
// Build the Customer Types Table
CREATE TABLE CustomerType(
CustomerType TEXT(6) CONSTRAINT PKCustomerType PRIMARY KEY,
Description TEXT(30));
// Build the Customers table
CREATE TABLE Customers(
CustomerID TEXT(10) Constraint PKCustomerID PRIMARY KEY,
Name TEXT(30),
CustomerType TEXT(6) CONSTRAINT FKCustomerType REFERENCES
CustomerType(CustomerType),
Address TEXT(30),
City TEXT(30),
State TEXT(30),
Zip TEXT(10),
Phone TEXT(14),
Fax TEXT(14));
// Build the index on Zip
CREATE INDEX SKZip on Customers(Zip);
// Insert Data
INSERT INTO CustomerType VALUES('INDV', 'Individual');
INSERT INTO CustomerType VALUES('BUS', 'Business - Non-corporate');
INSERT INTO CustomerType VALUES('CORP', 'Corporate Entity');
INSERT INTO Customers Values('SMITHJ', 'John Smith', 'INDV',
'160 Main Street', 'Dublin', 'Ohio', '45621',
'614-569-8975', '614-569-5580');
INSERT INTO Customers Values('JONEST', 'Jones Taxi', 'BUS',
'421 Shoe St.', 'Milford', 'Rhode Island', '03215',
'401-737-4528', '401-667-8900');
INSERT INTO Customers Values('JACKSONT', 'Thomas Jackson', 'INDV',
'123 Walnut Street', 'Oxford', 'Maine', '05896',
'546-897-8596', '546-897-8500');
// Copy data into the localities table
SELECT CustomerID, City, State INTO Localities FROM Customers;
// Display the results
SELECT * FROM CustomerType;
SELECT * FROM Customers;
SELECT * FROM Localities;
DELETE FROM Customers WHERE CustomerID = 'SMITHJ';
You would use the DROP TABLE command to delete an entire table. To delete the Customers table, you would issue the following statement:
DROP TABLE Customers;
| Customers Table | Vehicles Table |
|---|---|
| CustomerID (Primary Key) | SerialNumber (Primary Key) |
| CustomerName | CustomerID (Foreign Key) |
| License | VehicleType |
| Address | Make |
| City | Model |
| State | Color |
| Zip | Odometer |
| Phone |
Please note that by separating the VehicleTypes into a separate table, you can have any number of vehicles for a customer. Also note that SerialNumber makes a better primary key than license because the serial number of an automobile will not change, whereas a license plate can change on an annual basis.
Next, you need to reach the second normal form. This requires you to take the Customer and Vehicle tables and remove any redundant data. There is no redundant data in the Customers table. The Vehicles table, on the other hand, has redundant data describing the VehicleType. You should move the type information into a separate table to yield the following structure:
| Customers | Vehicles | VehicleTypes |
|---|---|---|
| CustomerID (Primary Key) | SerialNumber (Primary Key) | VehicleType (Primary Key) |
| CustomerName | CustomerID (Foreign Key) | Make |
| Address | License | Model |
| City | VehicleType (Foreign Key) | |
| State | Color | |
| Zip | Odometer | |
| Phone |
To reach the third normal form, you must delete any fields that do not describe the primary key. A review of all fields shows that you have already eliminated any fields that do not describe the entire primary key.
To achieve the fourth normal form, you need to separate any independent one-to-many relationships that can potentially produce unusual answers when you query the data. The Vehicles table does have several one-to-many relationships with the CustomerID and the VehicleType fields. The combination of these two fields in the same table would not, however, lead to misleading results further down the line. Therefore, you do not need to make any changes to reach the fourth normal form.
Similarly, no changes need to be made to reach the fifth normal form because you have no dependent many-to-many relationships in your tables. Most data structures will not require you to use the fourth and fifth rules of normalization to optimize your structure.
As a final point, you might want to add a Comments field to each table. This allows users to store any miscellaneous data they choose to track. Adding a memo field to track comments is a good idea in almost every table, because memo fields do not take up room when empty, and they provide great flexibility to your system.
NOTE: Please note that you need to create the VehicleTypes table before the Vehicles table. This is required because the Vehicles table has a foreign constraint to the VehicleTypes table. In such situations, the foreign key must be defined prior to its use in another table, or an error will occur.
Create Table Customers
(CustomerID TEXT (10),
CustomerName TEXT (40),
Address TEXT (40),
City TEXT (40),
State TEXT (20),
Zip TEXT (10),
Phone TEXT (14),
Comments MEMO,
CONSTRAINT PKCustomers Primary Key (CustomerID));
Create Table VehicleTypes
(VehicleType TEXT (10),
Make TEXT (25),
Model TEXT (25),
Comments MEMO,
CONSTRAINT PKVehicleTypes Primary Key (VehicleType));
Create Table Vehicles
(SerialNumber INTEGER,
CustomerID TEXT (10),
License TEXT (10),
VehicleType TEXT (10),
Color TEXT (15),
Odometer INTEGER,
Comments MEMO,
CONSTRAINT PKVehicles Primary Key (SerialNumber),
CONSTRAINT FKCustomer Foreign Key (CustomerID)
REFERENCES Customers(CustomerID),
CONSTRAINT FKType Foreign Key (VehicleType)
REFERENCES VehicleTypes(VehicleType));
Private Sub Form_Load()
Dim DB As Database
Dim dbName As String
On Error GoTo FormLoadErr
dbName = App.Path + \abc.mdb"
Set DB = DBEngine.OpenDatabase(dbName, True) ' Open database exclusive
MsgBox "Database opened successfully"
GoTo FormLoadExit
FormLoadErr:
MsgBox "Unable to load database ABC.MDB"
GoTo FormLoadExit
FormLoadExit:
Unload Me
End Sub
Private Sub Form_Load()
Dim db As Database
Dim rs As Recordset
Dim dbName As String
Dim tabName As String
dbName = App.Path + "\abc.mdb"
tabName = "Customers"
On Error GoTo FormLoadErr
Set db = DBEngine.OpenDatabase(dbName)
Set rs = db.OpenRecordset(tabName, dbOpenTable,
dbDenyRead + dbDenyWrite) ' table opened exclusively
MsgBox "Table opened exclusively"
GoTo FormLoadExit
FormLoadErr:
MsgBox "Unable to load table exclusively"
GoTo FormLoadExit
FormLoadExit:
Unload Me
End Sub
Option Explicit 'Declaration of global variables Dim DB As Database Dim wsUpdate As Workspace Dim nErrFlag As Integer
Next, start a new procedure and insert the following code. This code creates a workspace and opens the database.
Public Sub OpenDB()
On Error GoTo OpenDBErr
Dim dbName As String
nErrFlag = 0 'Reset the error flag
dbName = App.Path + "\abc.mdb"
'Open the workspace and database
Set wsUpdate = DBEngine.CreateWorkspace("WSUpdate", "admin", "")
Set DB = wsUpdate.OpenDatabase(dbName, True)
GoTo OpenDBExit
OpenDBErr:
MsgBox Trim(Str(Err)) + " " + Error$(Err), vbCritical, "OpenDB"
nErrFlag = Err
OpenDBExit:
End Sub
Now build the following procedure to perform the posting:
Public Sub Post()
On Error GoTo PostErr
Dim cSQL As String
wsUpdate.BeginTrans
'Create the SQL statement to insert the records.
'Note that we do not use the TransNo field
'as it is a counter field necessary only
'for the Transactions table
cSQL = "INSERT INTO History Select CustID, InvoiceNo,
Amount FROM Transactions"
DB.Execute cSQL
'Delete the temporary transactions data
cSQL = "DELETE FROM Transactions"
DB.Execute cSQL
'Commit the transactions
wsUpdate.CommitTrans
MsgBox "Transactions have been committed"
'Set the error flag and exit the program
nErrFlag = 0
GoTo PostExit
PostErr:
'Display the error and rollback the transactions
MsgBox Trim(Str(Err)) + " " + Error$(Err), vbCritical, "Post"
wsUpdate.Rollback
MsgBox "Post routine has been aborted"
PostExit:
End Sub
Finally, insert the following code into the cmdPost_Click event:
Private Sub cmdPost_Click()
OpenDB
If nErrFlag = 0 Then
Post
End If
If nErrFlag <> 0 Then
MsgBox "Error Reported", vbCritical, "cmdPost"
End If
Unload Me
End Sub
You can test this program by building the database in VisData or Data Manager and then inserting some sample records into the Transactions table.
To complete this assignment, you must first register a new ODBC data source on your workstation. To do this, complete the following steps:
Now you need to call up the 16-bit version of Visual Basic and create the data entry form project by completing the following steps:
cDSN = "EXER19" ' ODBC data source
cUser = "Admin" ' login ID
cPW = " " ' empty password
cTable = "Transactions" ' default base table
cKeyFld = "OrderNbr" ' default key field
cRefresh = "SELECT * FROM Transactions ORDER BY OrderNbr"
' default SQL
cFormTitle = "Chapter 19 Exercise" ' form title
Perform the following steps to complete this process:
If accGetSet(usrUserID, dbUsers, rsAccess) = accOK Then
frmFixedAssets.Show vbModal
'usrMaint
End If
Private Sub Data1_Validate(Action As Integer, Save As Integer)
On Error GoTo Data1Err
errProcStack errPush, "LibUser.logChanged"
'Declare variables
Dim cField As String
Dim lFlag As Boolean
'Reset the flag for changing of data
lFlag = False
'Set flag if data changes
If Text1.DataChanged Then lFlag = True
If Text2.DataChanged Then lFlag = True
If Text3.DataChanged Then lFlag = True
If Text4.DataChanged Then lFlag = True
If Text5.DataChanged Then lFlag = True
If Text6.DataChanged Then lFlag = True
'Write log if record changes
If lFlag = True Then
logWriteFile "RecUpdate", cRecordSet:="CH20EX.MDB.Assets",
cKey:="AssetID=" + Trim(Text1.Text)
End If
GoTo Data1Exit
Data1Err:
nResult = errHandler(Err, Error$, errResume)
GoTo Data1Exit
Data1Exit:
errProcStack errPop, ""
End Sub
This code checks to see whether any data has been changed in the current record. If so, an entry record is made in the audit log.
Contents = ContextString
In this entry, the context string is the topic you want displayed as the contents page. The first topic of your help file will default as the contents page if none is declared.
Note that the jump text is double underlined. Also note that the context string, NewProject, should be formatted as hidden text.
