The Management of Customers and Suppliers

Let’s see how to implement card registration data of customers and suppliers in stock in Access program we are creating. In this lesson we will see how to display data of customers and suppliers within a form or a dialog. The form in Access are windows that can be optimized for displaying data to the user program. We see that in design view, as they should be organized within the controls of our windows. The preparation of the controls inside the window (layout) is customizable and can distribute them like everyone wants.

Even formatting, color and font of the text inside controls, is customizable, Access just use the menu “Format“.

To take actions to load the window, or only to respond to user actions, we must work to programmatically in VBA (Visual Basic for Application). In form design view press the button on the toolbar “Code” and we will be screened in a text editor, in which we can create routines with a real programming language. In this tutorial we will see how to create an application to store and we will not dwell on the details of language, concepts that should already be known.
The code necessary to the operation of the masks of customers and suppliers is the same for both masks, having the same controls and same data.

Option Compare Database
Option Explicit

Private Sub Abi_DblClick(Cancel As Integer)
    Bank_DblClick (Cancel)
End Sub

Private Sub Abi_NotInList(NewData As String, Response As Integer)
    MsgBox "Double-click the field to enter a new bank!"
    Response = acDataErrContinue
End Sub

Private Sub Agency_DblClick(Cancel As Integer)
    Bank_DblClick (Cancel)
End Sub

Private Sub Agency_NotInList(NewData As String,
            Response As Integer)
    MsgBox "Double-click the field to enter a new bank!"
    Response = acDataErrContinue
End Sub

Private Sub Bank_DblClick(Cancel As Integer)
    On Error GoTo Err_Bank_DblClick

    If Not IsNull(Me.OpenArgs) Then Exit Sub

    Dim lBan As Long
    lBan = 0

    If Not IsNull(Me!IDBank) Then lBan = Me!IDBank
    If lBan = 0 Then
        DoCmd.OpenForm "Banks", , , , , acDialog, "GotoNew"
    Else
        DoCmd.OpenForm "Banks", , , , , acDialog, lBan
    End If

    lBan = GetSetting("Calus", "RetVal", "Last", 0)
    If lBan <> 0 Then Me!IDBank = lBan

    Me.Bank.Requery
    Me.ABI.Requery
    Me.CAB.Requery
    Me.Agency.Requery
    Me.Address_Bank.Requery
    Me.City_Bank.Requery

Exit_Bank_DblClick:
    Exit Sub
Err_Bank_DblClick:
    MsgBox Err.Description
    Resume Exit_Bank_DblClick
End Sub

Private Sub Bank_NotInList(NewData As String,
            Response As Integer)
    MsgBox "Double-click the field to enter a new bank!"
    Response = acDataErrContinue
End Sub

Private Sub Cab_DblClick(Cancel As Integer)
    Banca_DblClick (Cancel)
End Sub

Private Sub Cab_NotInList(NewData As String,
          Response As Integer)
    MsgBox "Double-click the field to enter a new bank!"
    Response = acDataErrContinue
End Sub

Private Sub Cap_AfterUpdate()
    Dim myData As dao.Database, myRec As dao.Recordset
    Set myData = CurrentDb
    Set myRec = myData.OpenRecordset("Cities", dbOpenDynaset)

    Dim strSQL As String

    If Not IsNull(CAP.Value) Then
        strSQL = "CAP = '" & CAP.Value & "'"
        myRec.FindFirst strSQL
        If Not myRec.NoMatch Then
            City.Value = myRec!City.Value
            State.Value = myRec!State.Value
        End If
    End If

    myRec.Close
    Set myData = Nothing
End Sub

Private Sub City_AfterUpdate()
    Dim myData As dao.Database, myRec As dao.Recordset
    Set myData = CurrentDb
    Set myRec = myData.OpenRecordset("Cities", dbOpenDynaset)

    Dim strSQL As String

    If Not IsNull(City.Value) Then
        strSQL = "City = '" & City.Value & "'"
        myRec.FindFirst strSQL
        If Not myRec.NoMatch Then
            CAP.Value = myRec!CAP.Value
            State.Value = myRec!State.Value
        End If
    End If

    myRec.Close
    Set myData = Nothing
End Sub

Private Sub City_Bank_DblClick(Cancel As Integer)
    Bank_DblClick (Cancel)
End Sub

Private Sub City_Bank_NotInList(NewData As String,
          Response As Integer)
    MsgBox "Double-click the field to enter a new bank!"
    Response = acDataErrContinue
End Sub

Private Sub Form_Load()
    If Not IsNull(Me!IDCustomers) And Not IsNull(Me.OpenArgs) Then
        If Me.OpenArgs = "GotoNew" Then
            DoCmd.DoMenuItem acFormBar, 3, 0, , acMenuVer70
            DoCmd.GoToRecord , , acNewRec
        Else
            Dim myRec As dao.Recordset
            Set myRec = Me.RecordsetClone
            myRec.FindFirst "IDCustomers = " & Me.OpenArgs
            If Not myRec.NoMatch Then Me.Bookmark = myRec.Bookmark
            myRec.Close
        End If
    Else
        DoCmd.GoToRecord , , acLast
    End If
End Sub

Private Sub Form_Unload(Cancel As Integer)
    If Not IsNull(Me!IDCustomers) Then
        SaveSetting "Calus", "RetVal", "Last", Me!IDCustomers
    Else
        SaveSetting "Calus", "RetVal", "Last", 0
    End If

    Forms![Commands Form].Visible = True
End Sub

Private Sub Address_Bank_DblClick(Cancel As Integer)
    Bank_DblClick (Cancel)
End Sub

Private Sub Address_Bank_NotInList(NewData As String,
                 Response As Integer)
    MsgBox "Double-click the field to enter a new bank!"
    Response = acDataErrContinue
End Sub

Private Sub Type_Address_DblClick(Cancel As Integer)
    On Error GoTo Err_Type_Address_DblClick

    If Not IsNull(Me.OpenArgs) Then Exit Sub

    Dim strType As String
    strType = ""

    If Not IsNull(Me![Type Address]) Then
        strTipo = Me![Type Address]
    End If
    If strTipo = "" Then
        DoCmd.OpenForm "Types of Address",
              , , , , acDialog, "GotoNew"
    Else
        DoCmd.OpenForm "Types of Address",
              , , , , acDialog, DLookup("ID", "Type Address",
             "[Name Type Address] = '" & strType & "'")
    End If

    If GetSetting("Calus", "RetVal", "Last", 0) > 0 Then
        strTipo = DLookup("[Name Type Address]",
            "Type Address", "ID = " &
            GetSetting("Calus", "RetVal", "Last", 0))
    Else
        strType = ""
    End If

    If strType <> "" Then Me![Type Address] = strType

    Me.Type_Address.Requery

Exit_Type_Address_DblClick:
    Exit Sub
Err_Type_Address_DblClick:
    MsgBox Err.Description
    Resume Exit_Type_Address_DblClick
End Sub

Private Sub Type_Address_NotInList(NewData As String,
          Response As Integer)
    MsgBox "Double-click the field to enter a new type of address!"
    Response = acDataErrContinue
End Sub

Private Sub Title_DblClick(Cancel As Integer)
    On Error GoTo Err_Title_DblClick

    If Not IsNull(Me.OpenArgs) Then Exit Sub

    Dim strTit As String
    strTit = ""

    If Not IsNull(Me!Title) Then strTit = Me!Title
    If strTit = "" Then
        DoCmd.OpenForm "Titles", , , , , acDialog, "GotoNew"
    Else
        DoCmd.OpenForm "Titles", , , , ,
               acDialog, DLookup("ID", "Titles",
                "[Name Title] = '" & strTit & "'")
    End If

    If GetSetting("Calus", "RetVal", "Last", 0) > 0 Then
        strTit = DLookup("[Name Title]", "Titles", "ID = " &
               GetSetting("Calus", "RetVal", "Last", 0))
    Else
        strTit = ""
    End If

    If strTit <> "" Then Me!Title = strTit

    Me.Title.Requery

Exit_Title_DblClick:
    Exit Sub
Err_Title_DblClick:
    MsgBox Err.Description
    Resume Exit_Title_DblClick
End Sub

Private Sub Title_NotInList(NewData As String,
              Response As Integer)
    MsgBox "Double-click the field to enter a new title!"
    Response = acDataErrContinue
End Sub

The code is very simple to understand, in practice we only need to show a list of banks, titles and address types that double click the control to open a modal window to make changes, insertions and deletions in the list of values.

<< First Lesson – Start Tutorial – Next Lesson