|
Now that you've gone through four automated forms articles, it's time to talk about linking
your AutoForm to a database.
This fifth article in the series explains, the
basics of how you can use ADO (ActiveX Data
Objects) and VBA code to link your AutoForm to a
database. Using this technique, you can
automatically populate a form from an existing database
record and automatically input data from your form
into a database. This article assumes you have read
the first four articles in this series and know
Access 2000 and because database connectivity can
become complex, a more detailed version of this article and sample code is available online at
www.mousetrax.com.
1 Set up the database
For this sample, you need to create a
basic Access 2000 database. Name the database ClientInfo. Create one table with the
following fields: ID_Client (AutoNumber and make this
the Primary Key), FName (text), LName (text), Company (text), Address (text), City (text),
State (text), Zip (number), Phone (text), Notes (memo). Enter some sample data for a few
clients, but leave some record fields empty, as a
user probably would do.
2 Create the form
Use your AutoForm skills to create a
basic Client Info form with form fields for each of
the ClientInfo fields in your database. Choose View|Toolbars|Control ToolBox to
access ActiveX controls and add two CommandButtons (one to activate the code for checking
names against your database and one for adding new records). Click the Design toggle button (it
looks like a T-square) and select a command
button. Right click and choose Properties to set any
properties. Note that ActiveX controls do not work
if the form is Protected and the reverse is true
for Form Fields. So click Insert|Break|Continuous Break. When you lock your AutoForm, click
Sections and do not lock the section with the
ActiveX command buttons
3 Connect using ADO
Office 2000 uses Active Data Objects (ADO) to let you connect to a remote
database. To use ADO, you first need to
reference it (i.e., tell your computer you have it). Bring your
AutoForm to the screen and open the Visual Basic Editor
or VBE (Alt+F11). Click Tools| Reference and select
the Microsoft ActiveX Data Object 2.5 Library from
the list.
4 Start coding
Because ADO can connect to different types of databases, it uses different types of
providers. For an Access database, you use the
Jet OLEDB Provider. The code resides under the CommandButton1 Click Event. Display
your form and click the Design Mode button. Double click CommandButton1. The basics for
connecting to a database are: declare the new connection:
Dim vConnection As New ADODB.Connection
Dim vRecordSet As New ADODB.Recordset
Dim vClientFName As String
Dim vClientLName As String
Dim vCompany As String
Dim vAddress As String
Dim vCity As String
Dim vState As String
Dim vZip As String
Dim vPhone As String
Dim vNotes As String
provide the connection string, set some parameters, and open the database.
vConnection.ConnectionString = _
"data source=c:\computorcompanion\ _
Client_Info.mdb;" & _
"Provider=Microsoft.Jet.OLEDB.4.0;"
vConnection.Open
You can then check the state of the database to ensure you connected. You usually query the
database, for which you need to declare a
RecordSet to temporarily hold your query results.
(See www.mousetrax.com for ALL the source code and a more detailed explanation.)
5 Automatically Fill Fields
You ran the query and now you need to
ask the user if the results are the ones they want. So
you display a message box with the name and address
to help identify this person. If the record is the
correct person, you automatically fill the form from the
database information. If you don't find a match, you
remind the user to enter the field data and add
the records to the database. The AddNew code
resides under the Update CommandButton2 click event.
6 Add a new record
If the record did not match any first/last
name record in your current database, then fill out
whatever information you have available on this client
and click Update (CommandButton2). The code should be under that button, so that when you click the
button, the Click Event processes the code. The
code opens a connection, sets variables to any data in
the AutoForm field, and enters that data into the
matching fields in your database. (See
www.mousetrax.com for the source code and a more detailed explanation.)
7 Clean up your mess!
This final section of code is needed to
end both command button code sequences. You need
to close the RecordSet and the open Connection.
And then it's a good idea to set all Objects to Nothing
to remove them from memory. It's also a good idea while debugging to restart your computer
occasionally to ensure you force out any object that was not
removed because your code didn't work right.
If you wrote the code correctly, you should be
able to enter a first and last name into those form
fields. Click CommandButton1 to see if they are in your
database. If they are, your form should fill itself. If
not, you should be able to add this new record. Open
your database and see if you were successful!
For more information on forms, be sure to check out all of Dian's articles
at this link http://www.mousetrax.com/techpage.html#autoforms.
|
