Article source code: my1st_accessdb.zip

Continued from My First Access Database Program. Part 1

Binding the Controls

Click the "lblNavNum" label which reads "No Records" once to select it as shown in Figure 24.

(Figure 24)

Go to the Properties window and find the property "(DataBindings)" as shown in Figure 25.

(Figure 25)

Expand this node by clicking the "+" sign. This should look as shown in Figure 26.

(Figure 26)

Click the "(None)" on the right from the "Text" property once to activate the drop down box. Click the arrow button to expand the property as shown in Figure 27.

(Figure 27)

Expand the "dsContacts" node by clicking the "+" sign. This should look as shown in Figure 28.

(Figure 28)

Expand the "tblContacts" node and click "ID" as Figure 29 suggests.

(Figure 29)

Scroll to see the "Text" property. If you can see a golden cylinder as shown in Figure 30, then you have made everything correctly.

(Figure 30)

Repeat the same procedure with the text boxes - not the labels. We used the label here because it is going to display the current entry number and the number of total entries. It cannot be edited, that's why we used a label for this item and not a text box. The binding of the remaining controls should be according to what data the controls are supposed to hold. For example, the txtFirstName text box should be linked to FirstName of our table. When you're through with this, it would be a good idea to build the solution. To do so, press Ctrl + Shift + B.

Coding

I do hope you are a patient programmer because the amount of code here can be hefty for some beginners. So if things won't work, then you have made something different from what's written here. In the past, I received tons of e-mails telling me my code wouldn't work. Those people just didn't tell me that they had named things differently from the suggestions, or that they had written code in a different place, etc., etc. If you absolutely insist on naming things differently or writing code wherever you think appropriate, fine! But please don't blame me if it's not working.

Note: Although the buttons for a backup utility, an about form, and a help file are present, I haven't included the necessary code. Neither have I included the forms/file. An earlier article on how to make a help file should get you going with at least one of the three missing parts. Including the three missing parts here would make this tutorial awfully large, so I have omitted them for today. That doesn't mean that I will leave you on your own forever in terms of making an About form or a backup utility.

Press F7 to go into code view. From the Class Name list, shown in Figure 31, select "(Base Class Events)".

(Figure 31)

From the Method Name list, shown in Figure 32, select "Load".

(Figure 32)

Edit the code so that it looks like Code 01.

(Code 01)

Private Sub frmSample_Load(ByVal sender As Object, _
    ByVal e As System.EventArgs) Handles MyBase.Load

    '[Change dbconnection to application startup path]
    Try
        Me.odcContacts.ConnectionString = _
          "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source= dbSample.mdb"
    Catch eConnection As System.Exception
        MessageBox.Show(eConnection.Message)
    End Try
    '[Change dbconnection to application startup path/]

End Sub

Looking at Code 01

Code 01 changes the database connection to the application startup path. The application startup path is the directory in which your application is actually built. It's what we call the bin folder. Doing so will avoid some severe headaches later. If you don't change the path, the program will try to connect to your database with the generated path. The generated path could look like this on Windows XP:

C:\Documents and Settings\UserName\My Documents\Visual Studio Projects\Database Sample Program\bin\dbSample.mdb

On a different machine, a person might not have the C:\ directory. It's certain that not all systems have this "Documents and Settings". Even if they do, the user name will be different. And how about those who have a different language installed? As for instance, "My Documents" is "Meus documentos" in Portuguese, or "Eigene Dateien" in German. Hardly anybody will have a "Visual Studio Projects" folder. You see, it's really necessary because the application startup path is a virtual path. It doesn't matter whether the user installs this program on drive C, F, or some other weird location.

The "Try", "Catch", "End Try" is simple error handling. In cases where the code can't do what it is supposed to do, it will display a message box telling the user about the error instead of crashing the entire program. Yes, this could be done much better and with more details, but our subject here is database programming, not error handling. But I could think of getting back to that subject in the future.

Add Code 02 right before the "End Sub" of Code 01.

(Code 02)

    '[Load the database]
    Try
        odaContacts.Fill(dsContacts)
        lblNavNum.Text = _
          Me.BindingContext(dsContacts.tblContacts).Position
    Catch eLoad As System.Exception
        MessageBox.Show(eLoad.Message)
    End Try
    '[Load the database/]

Looking at Code 02

Simplified speaking, this code will fill the text boxes with data from the table. Press F5 to run the program. If you did everything correctly, you will see Figure 33.

(Figure 33)

Close the program to get back to the code window. You can collapse the Sub by clicking the "-" sign on the left as shown in Figure 34.

(Figure 34)

Type "# Region "Custom Procedures"" between the "Windows Form Designer generated code" region and the "Private Sub frmSample_Load" and press Enter. Actually, where you put this region doesn't matter; it's just my sense of order. This might be totally different with you. Add Code 03 so that it goes between the "# Region "Custom Procedures"" and the "# End Region".

(Code 03)

Private Sub dsContacts_PositionChanged()

    '[Display the changed position]
    Me.lblNavNum.Text = _
      (((Me.BindingContext(dsContacts, _
      "tblContacts").Position + 1).ToString + " of ") + _
      Me.BindingContext(dsContacts, _
      "tblContacts").Count.ToString)
    '[Display the changed position/]

End Sub

Looking at Code 03

It will display the changed position in the label. Since we need this code quite often, it is better to write it once and then call it where we need it. Otherwise we would have to type this code over and over again. How we call the code you will see when we need to do so. Important is that you remember the expression that goes after "Private Sub".

Add Code 04 right after the "End Sub" of Code 03.

(Code 04)

Private Sub ReadOnlyOn()

    '[Enable read-only mode]
    txtFirstName.ReadOnly = True
    txtSurname.ReadOnly = True
    txtEmail.ReadOnly = True
    tbrSave.Enabled = False
    tbrCancel.Enabled = False
    tbrEdit.Enabled = True
    '[Enable read-only mode/]

End Sub

Looking at Code 04

We need to protect data from being overwritten by accident. That's why we have to make all the text boxes read-only. When data is read-only, there is no good reason to let the user click the save or cancel button. But of course, the edit button must be enabled so that a user can make changes to an existing entry. Also here, we will need this code tons of times, so we write it once and call it when needed.

Add Code 05 right after the "End Sub" of Code 05.

(Code 05)

Private Sub ReadOnlyOff()

    '[Cancel read-only mode]
    txtFirstName.ReadOnly = False
    txtSurname.ReadOnly = False
    txtEmail.ReadOnly = False
    tbrSave.Enabled = True
    tbrCancel.Enabled = True
    tbrEdit.Enabled = False
    '[Cancel read-only mode/]

End Sub

Looking at Code 05

It would be incredibly smart if I said that this code is exactly the opposite of Code 04, wouldn't it? So let me spare you my dumb comments and let's go ahead.

Add Code 06 right after the "End Sub" of Code 05.

(Code 06)

Private Sub SetNavButtons(ByVal sender As System.Object, _
    ByVal e As System.EventArgs)

    '[Enable or disable navigation buttons when appropriate]
    If Me.BindingContext(dsContacts, _
      "tblContacts").Position = _
      Me.BindingContext(dsContacts, _
      "tblContacts").Count - 1 Then
        tbrNext.Enabled = False
        tbrLast.Enabled = False
    Else
        tbrNext.Enabled = True
        tbrLast.Enabled = True
    End If
    If Me.BindingContext(dsContacts, _
      "tblContacts").Position = 0 Then
        tbrPrevious.Enabled = False
        tbrFirst.Enabled = False
    Else
        tbrPrevious.Enabled = True
        tbrFirst.Enabled = True
    End If
    '[Enable or disable navigation buttons when appropriate/]

End Sub

Looking at Code 06

Now, this seems logical, doesn't it? If the user is at the first record, why should the first/previous button be enabled? I don't have the slightest idea. If you do, let me know. And if the user is at the last record ... C'mon, you know the rest.

We will call this procedure in a different way when we need it. It's called a delegate procedure then. But I don't want to bore you stiff with technical vocabulary. Ah, you find that interesting? Interesting is driving a car in Dallas - boy, I bet you've never seen that many middle fingers in your life than you can see there within the first ten minutes.

This region is complete, so please close it. It works the same way as you previously closed the Sub.

Now we can go back to the "Private Sub frmSample_Load". Add Code 07 right before the "End Sub" in the "Private Sub frmSample_Load".

(Code 07)

    '[See custom procedures]
    dsContacts_PositionChanged()
    '[See custom procedures/]

    '[See custom procedures]
    ReadOnlyOn()
    '[See custom procedures/]

    '[Enable or disable the navigation buttons]
    Dim bmContacts As BindingManagerBase = _
      Me.BindingContext(dsContacts, "tblContacts")
    AddHandler bmContacts.PositionChanged, _
      AddressOf SetNavButtons
    '[Enable or disable the navigation buttons/]

    '[Manually disable these buttons at startup]
    tbrFirst.Enabled = False
    tbrPrevious.Enabled = False
    '[Manually disable these buttons at startup/]

Looking at Code 07

The code of the first two items is actually written in the region we named "Custom Procedures". We just call the code by writing the same expression we used after the "Private Sub". Remember that we wrote "Private Sub dsContacts_PositionChanged ()" and "Private Sub ReadOnlyOn ()"? No? Go into the corner and say, "I'm a hell of a programmer!" Repeat that about 150 times and you'll be forgiven.

The next item is our binding manager, responsible for enabling or disabling our navigation buttons. We previously wrote a Sub in the region "Custom Procedures", right? I told you that we will use this in a different way. It also uses the Sub you wrote. Important to know here is that it will enable/disable the navigation buttons when the position has changed. And that's where the last piece of code comes in handy.

How can the navigation buttons be enabled/disabled when you haven't clicked them? Guess what! They can't. If you don't click them, the position can't change - unless you have a little built-in wizard to do that. You have to disable the buttons manually. This code shows you how it can be done. However, it's not the perfect solution. Why? I'm sure you'll discover it soon and (try to) improve it.

The "Private Sub frmSample_Load" is complete. If you like, write a region around this part and close the region. Those regions really do look organized when they're closed, don't they?

Now, we want that a user provides complete data without interrupting him/her with nagging message boxes. C'mon, some programs have more message boxes than anything else. I wonder when people are going to switch to error providers. They certainly will if those guys at Microsoft improve the error provider. One way to guide the user is to disable the save button as long as the necessary information is not complete.

Create a new region named "Text Boxes". Add Code 08 to the region.

(Code 08)

Private Sub Textbox_TextChanged(ByVal sender As Object, _
    ByVal e As System.EventArgs) Handles _
    txtFirstName.TextChanged, txtSurname.TextChanged, _
    txtEmail.TextChanged

    '[Guide the user to provide all information]
    If txtFirstName.TextLength = 0 Or _
      txtSurname.TextLength = 0 Then
        tbrSave.Enabled = False
    Else
        tbrSave.Enabled = True
    End If

    If txtEmail.Text.Length = 0 Then
        tbrEmail.Enabled = False
    Else
        tbrEmail.Enabled = True
    End If
    '[Guide the user to provide all information/]

End Sub

Looking at Code 08

The essential information here is the first name and the last name. Some people still haven't stepped into the new century and don't have an e-mail address, so we can't simply disable the save button until the user invents an e-mail address just because the programmer thought it a good idea. But we can disable the "Send E - mail" button if there is no text. That way, a user couldn't accidentally click and open an e-mail to be sent to no one.

Now we've come so far, and it's about time we coded the buttons. However, we will not code them directly; we will call our code for the buttons later similar to the way we did before. Why? Once our toolbar is coded, you can forget it for most of the program's life. Any changes you might want to make will be in the actual code. The bottom line is that it actually doesn't matter. It's in fact my preferred way to code. I usually do it that way with every single bit of code. I've also started putting things into classes and modules whenever possible so I don't get confused. But in a tutorial, I change habits to make my readers' lives easier; especially at the beginning.

I believe the code is sufficiently commented so you can understand what's going on. Great! So I can spare you further brainless comments.

Create a region named "Common Buttons". The completed region should look like Code 09.

(Code 09)

#Region "Common Buttons"

    Private Sub Add()

        '[See custom procedures]
        ReadOnlyOff()
        '[See custom procedures/]

        '[Add a new entry]
        Try
            Me.BindingContext(dsContacts, _
              "tblContacts").EndCurrentEdit()
            Me.BindingContext(dsContacts, _
              "tblContacts").AddNew()
        Catch eAdd As System.Exception
            MessageBox.Show(eAdd.Message)
        End Try
        '[Add a new entry/]

        '[See custom procedures]
        Me.dsContacts_PositionChanged()
        '[See custom procedures/]

        '[Focus the first textbox]
        txtFirstName.Focus()
        '[Focus the first textbox/]

    End Sub

    Private Sub Edit()

        '[See custom procedures]
        ReadOnlyOff()
        '[See custom procedures/]

    End Sub

    Private Sub Save()

        '[Update]
        Try
            Me.BindingContext(dsContacts, _
              "tblContacts").EndCurrentEdit()
            odaContacts.Update(dsContacts, _
              "tblContacts")
        Catch eSave As System.Exception
            MessageBox.Show(eSave.Message)
        End Try
        '[Update/]

        '[See custom procedures]
        Me.dsContacts_PositionChanged()
        '[See custom procedures/]

        '[See custom procedures]
        ReadOnlyOn()
        '[See custom procedures/]

    End Sub

    Private Sub Cancel()

        '[Ask for confirmation]
        If MessageBox.Show("This will cancel the current entry." _
          & ControlChars.NewLine & "Do you want to continue?", _
          "Sample", MessageBoxButtons.YesNo, _
          MessageBoxIcon.Question) = DialogResult.Yes Then
            '[Ask for confirmation/]

            '[If the user confirms, cancel the entry]
            Me.BindingContext(dsContacts, _
            "tblContacts").CancelCurrentEdit()
            '[If the user confirms, cancel the entry/]

            '[See custom procedures]
            Me.dsContacts_PositionChanged()
            '[See custom procedures/]

            '[See custom procedures]
            ReadOnlyOn()
            '[See custom procedures/]

        End If

    End Sub

    Private Sub Delete()

        '[Ask for confirmation]
        If MessageBox.Show("This will delete the current entry." _
          & ControlChars.NewLine & "Do you want to continue?", _
          "Sample", MessageBoxButtons.YesNo, _
          MessageBoxIcon.Question) = DialogResult.Yes Then
            '[Ask for confirmation/]

            '[Delete the entry]
            If (Me.BindingContext(dsContacts, _
              "tblContacts").Count > 0) Then
                Me.BindingContext(dsContacts, _
                "tblContacts").RemoveAt(Me.BindingContext(dsContacts, _
                "tblContacts").Position)
                '[Delete the entry/]

                '[See custom procedures]
                Me.dsContacts_PositionChanged()
                '[See custom procedures/]

                '[See custom procedures]
                ReadOnlyOn()
                '[See custom procedures/]

            End If

        End If

    End Sub

    Private Sub SendMail()

        '[Open an empty e - mail with address]
        System.Diagnostics.Process.Start("mailto:" _
          & txtEmail.Text)
        '[Open an empty e - mail with address/]

    End Sub

    Private Sub Shutdown()

        '[See Private Sub Save( )]
        Save()
        '[See Private Sub Save( )/]

        '[Close the program]
        Dispose(True)
        '[Close the program/]

    End Sub

#End Region

Create another region named "Navigation Buttons". The completed region should look like Code 10.

(Code 10)

#Region "Navigation Buttons"

    Private Sub First()

        '[Go to the first entry]
        Me.BindingContext(dsContacts, _
          "tblContacts").Position = 0
        '[Go to the first entry/]

        '[See custom procedures]
        Me.dsContacts_PositionChanged()
        '[See custom procedures/]

        '[See custom procedures]
        ReadOnlyOn()
        '[See custom procedures/]

    End Sub

    Private Sub NavNext()

        '[Go to the next entry]
        Me.BindingContext(dsContacts, _
          "tblContacts").Position = _
        (Me.BindingContext(dsContacts, _
          "tblContacts").Position + 1)
        '[Go to the next entry/]

        '[See custom procedures]
        Me.dsContacts_PositionChanged()
        '[See custom procedures/]

        '[See custom procedures]
        ReadOnlyOn()
        '[See custom procedures/]

    End Sub

    Private Sub Previous()

        '[Go to the previous entry]
        Me.BindingContext(dsContacts, _
          "tblContacts").Position = _
        (Me.BindingContext(dsContacts, _
          "tblContacts").Position - 1)
        '[Go to the previous entry/]

        '[See custom procedures]
        Me.dsContacts_PositionChanged()
        '[See custom procedures/]

        '[See custom procedures]
        ReadOnlyOn()
        '[See custom procedures/]

    End Sub

    Private Sub Last()

        '[Go to the last entry]
        Me.BindingContext(dsContacts, _
        "tblContacts").Position = _
        (Me.dsContacts.Tables("tblContacts").Rows.Count - 1)
        '[Go to the last entry/]

        '[See custom procedures]
        Me.dsContacts_PositionChanged()
        '[See custom procedures/]

        '[See custom procedures]
        ReadOnlyOn()
        '[See custom procedures/]

    End Sub

#End Region

Note: You might find it strange that I used "Private Sub NavNext" and not "Private Sub Next". "Next" is a keyword in programming and would appear blue. That's why you can't name a Sub "Next".

We can now come to our toolbar. Some people love to use the If ... Then syntax. Hmm, if we did that here, we would get into the Guinness Book of Records for writing so many "ifs" and "thens". Another possibility is to use the index number of each toolbar button. But honestly, if you add or delete a button later, you will regret having done it that way. Or worse, you change the sequence of your buttons entirely. Oh-oh, to get that going again will take you longer than it took me to write this article. Hey, writing articles takes more than a few hours, believe me. Remember that I asked you at the beginning to look at least at the tag properties of the toolbar? Hmm, I hope you did that, otherwise you might not understand the comment in my code.

Create another region named "Toolbar". The completed region should look as shown in Code 11.

(Code 11)

#Region "Tool Bar"

    Private Sub tbrSample_ButtonClick(ByVal sender As _
        System.Object, ByVal e As _
        System.Windows.Forms.ToolBarButtonClickEventArgs) _
        Handles tbrSample.ButtonClick

        '[Use tag property to select button]
        Select Case e.Button.Tag

            Case "Add" 'This is the tag property
                Add() 'This is the previously written Sub
            Case "Edit"
                Edit()
            Case "Save"
                Save()
                '[See custom procedures]
                SetNavButtons(sender, e)
                '[See custom procedures/]
            Case "Cancel"
                Cancel()
                '[See custom procedures]
                SetNavButtons(sender, e)
                '[See custom procedures/]
            Case "Delete"
                Delete()
            Case "First"
                First()
            Case "Previous"
                Previous()
            Case "Next"
                NavNext()
            Case "Last"
                Last()
            Case "Email"
                SendMail()
            Case "Exit"
                Shutdown()
                '[Use tag property to select button/]

        End Select

    End Sub

#End Region

You can now run the program and try it out. Now, is that okay? No, it isn't. What if the user clicks the X Button? The thing would close and it would not save a possible current entry.

It's up to you if you want to interrupt the user with one of those pesky message boxes or not. Option 1 is if you just want to save any changes and exit without maddening the user.

Option 1

Select "(Base Class Events)" from the Class Name list and "Closing" from the Method Name list. Edit the code so that it looks like Code 12.

(Code 12)

Private Sub frmSample_Closing(ByVal sender As Object, _
    ByVal e As System.ComponentModel.CancelEventArgs) _
    Handles MyBase.Closing

    '[See Private Sub Save ()]
    Save()
    '[See Private Sub Save ()/]

End Sub

Option 1

This option is great if you want to save any changes but want to irritate the user with a message box. Edit the code so that it looks like Code 13.

(Code 13)

Private Sub frmSample_Closing(ByVal sender As Object, _
    ByVal e As System.ComponentModel.CancelEventArgs) _
    Handles MyBase.Closing

    '[Show a message when the X is clicked]
    If MessageBox.Show("Do you want to save and exit?", _
      "Sample", MessageBoxButtons.YesNo, _
      MessageBoxIcon.Information) = DialogResult.No Then
        '[Show a message when the X is clicked/]

        '[If user click NO then let form open]
        e.Cancel = True
        '[If user click NO then let form open]
    Else
        '[See Private Sub Save ()]
        Save()
        '[See Private Sub Save ()/]
    End If

I hope that this tutorial helped you with the very basics. Play with the code - it won't bite you - and see how you can improve it so that it works the way you want. As I already said, the About form and the backup utility for the database are not included here. Those will soon find their way to DevCity, so stay tuned.