I
Igor Muryjas
Hello
I'm new to access programming so I'm probably making some obvious mistake.
The following code comes from 'Beginning Access 2003 VBA' by Denise
Cosnell.
On page 130 of the book you can read:
1. Create a new database by selecting File ?New ?Blank Database and
specifying Ch5CodeExamples as the filename.
2. Create a new table in the database by selecting Tables from the Database
Window and clicking the Design button. Alternatively, you can select the New
button and then choose Design View
from the list. The table should be named tblContacts and should have the
fields illustrated in Figure 5.3. Note that the field sizes are listed as
part of the description for convenience
purposes only, so that, when creating the table, you can see what size to
set for each field.
3. Open the table from the Database Window and add at least one record to
the table.
4. Create a new form named frmContactsBound. Use the toolbox to drag and
drop 12 text box controls onto the form. Modify the Name property for each
text box to the following: txtLastName, txtFirstName, txtMiddleName,
txtTitle, txtAddress1, txtAddress2, txtCity, txtState, txtZip, txtWorkPhone,
txtHomePhone, and txtCellPhone, respectively. Also rename the Caption
property for the corresponding label of each text box, as shown in Figure
5.4.
5. Add the following code to the Form_Load procedure of the frmContactsBound
form. You can do so by selecting the form in the Designer Window, viewing
the Properties dialog box for the form, selecting the Events tab, and then
selecting the Code Builder option from the On Load event.
Private Sub Form_Load()
Dim cnCh5 As ADODB.Connection
Dim rsContacts As ADODB.Recordset
Dim strConnection As String
'specify the connection string for connecting to the database
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & CurrentProject.Path & "\Ch5CodeExamples.mdb"
'create a new connection instance and open it using the connection
string
Set cnCh5 = New ADODB.Connection
cnCh5.Open strConnection
'create a new instance of a recordset
Set rsContacts = New ADODB.Recordset
'set various properties of the recordset
With rsContacts
'specify a cursortype and lock type that will allow updates
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.LockType = adLockOptimistic
'open the recordset based on tblContacts table using the existing
connection
.Open "tblContacts", cnCh5
End With
'if the recordset is empty
If rsContacts.BOF And rsContacts.EOF Then
MsgBox "There are no records in the database."
Exit Sub
'if the recordset is not empty, then bind the
'recordset property of the form to the rsContacts recordset
Else
Set Me.Recordset = rsContacts
End If
'bind the controls of the form to the proper field in the recordset
(which has
'the same field names as the tblContacts table from which it was
generated)
Me.txtLastName.ControlSource = "txtLastName"
Me.txtFirstName.ControlSource = "txtFirstName"
Me.txtMiddleName.ControlSource = "txtMiddleName"
Me.txtTitle.ControlSource = "txtTitle"
Me.txtAddress1.ControlSource = "txtAddress1"
Me.txtAddress2.ControlSource = "txtAddress2"
Me.txtCity.ControlSource = "txtCity"
Me.txtState.ControlSource = "txtState"
Me.txtZip.ControlSource = "txtZip"
Me.txtWorkPhone.ControlSource = "txtWorkPhone"
Me.txtHomePhone.ControlSource = "txtHomePhone"
Me.txtCellPhone.ControlSource = "txtCellPhone"
End Sub
6. Save the VBA code from the Visual Basic Editor by selecting the Save
button from the toolbar.
7. Save the form from the Form Designer by selecting the Save button from
the toolbar.
8. Open the form. You should see a screen similar to Figure 5.5.
9. Modify one of the existing records.
But when I open the form I get an error saying that the file is already in
use and cannot be used.
What can be wrong?
Thanks in advance
I'm new to access programming so I'm probably making some obvious mistake.
The following code comes from 'Beginning Access 2003 VBA' by Denise
Cosnell.
On page 130 of the book you can read:
1. Create a new database by selecting File ?New ?Blank Database and
specifying Ch5CodeExamples as the filename.
2. Create a new table in the database by selecting Tables from the Database
Window and clicking the Design button. Alternatively, you can select the New
button and then choose Design View
from the list. The table should be named tblContacts and should have the
fields illustrated in Figure 5.3. Note that the field sizes are listed as
part of the description for convenience
purposes only, so that, when creating the table, you can see what size to
set for each field.
3. Open the table from the Database Window and add at least one record to
the table.
4. Create a new form named frmContactsBound. Use the toolbox to drag and
drop 12 text box controls onto the form. Modify the Name property for each
text box to the following: txtLastName, txtFirstName, txtMiddleName,
txtTitle, txtAddress1, txtAddress2, txtCity, txtState, txtZip, txtWorkPhone,
txtHomePhone, and txtCellPhone, respectively. Also rename the Caption
property for the corresponding label of each text box, as shown in Figure
5.4.
5. Add the following code to the Form_Load procedure of the frmContactsBound
form. You can do so by selecting the form in the Designer Window, viewing
the Properties dialog box for the form, selecting the Events tab, and then
selecting the Code Builder option from the On Load event.
Private Sub Form_Load()
Dim cnCh5 As ADODB.Connection
Dim rsContacts As ADODB.Recordset
Dim strConnection As String
'specify the connection string for connecting to the database
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & CurrentProject.Path & "\Ch5CodeExamples.mdb"
'create a new connection instance and open it using the connection
string
Set cnCh5 = New ADODB.Connection
cnCh5.Open strConnection
'create a new instance of a recordset
Set rsContacts = New ADODB.Recordset
'set various properties of the recordset
With rsContacts
'specify a cursortype and lock type that will allow updates
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.LockType = adLockOptimistic
'open the recordset based on tblContacts table using the existing
connection
.Open "tblContacts", cnCh5
End With
'if the recordset is empty
If rsContacts.BOF And rsContacts.EOF Then
MsgBox "There are no records in the database."
Exit Sub
'if the recordset is not empty, then bind the
'recordset property of the form to the rsContacts recordset
Else
Set Me.Recordset = rsContacts
End If
'bind the controls of the form to the proper field in the recordset
(which has
'the same field names as the tblContacts table from which it was
generated)
Me.txtLastName.ControlSource = "txtLastName"
Me.txtFirstName.ControlSource = "txtFirstName"
Me.txtMiddleName.ControlSource = "txtMiddleName"
Me.txtTitle.ControlSource = "txtTitle"
Me.txtAddress1.ControlSource = "txtAddress1"
Me.txtAddress2.ControlSource = "txtAddress2"
Me.txtCity.ControlSource = "txtCity"
Me.txtState.ControlSource = "txtState"
Me.txtZip.ControlSource = "txtZip"
Me.txtWorkPhone.ControlSource = "txtWorkPhone"
Me.txtHomePhone.ControlSource = "txtHomePhone"
Me.txtCellPhone.ControlSource = "txtCellPhone"
End Sub
6. Save the VBA code from the Visual Basic Editor by selecting the Save
button from the toolbar.
7. Save the form from the Form Designer by selecting the Save button from
the toolbar.
8. Open the form. You should see a screen similar to Figure 5.5.
9. Modify one of the existing records.
But when I open the form I get an error saying that the file is already in
use and cannot be used.
What can be wrong?
Thanks in advance