Option Compare Database
Public DupeEntry As Boolean

Private Sub address_Enter()
    
    'Get the value in Agency
    If IsNull(agency.Value) Then
        Exit Sub
    End If
    
    'Create the SQL String to put in Agency
    Dim theSQL As String
    theSQL = "SELECT Customer.address FROM Customer WHERE (((Customer.agency)='" & agency.Value & "')) GROUP BY Customer.address ORDER BY Customer.address;"
    'Put the String in Agency
    address.RowSource = theSQL
    
    
    'Hope to god it works
    
    
    
End Sub

Private Sub address_Exit(Cancel As Integer)
    
    'Fill in City, State, and Zip phone, fax, member
    
    'First go into customer table, find highest rst with same agency match
    'This will match the information with the same agency last entered.
    
    Dim rst As Recordset
    Set rst = CurrentDb.OpenRecordset("Customer")
    
    'if agency is blank, skip
    If (IsNull(Me.agency) Or Len(Me.agency) < 1) Then
        Exit Sub
    End If
    'if address is blank, skip
    If (IsNull(Me.address) Or Len(Me.address) < 1) Then
        Exit Sub
    End If
    
    
    'Move through Customer and find match
    Dim didFind As Boolean
    Dim cCustID As Integer
    
    If (Not rst.BOF) Then
        rst.MoveLast
    Else
        Exit Sub
    End If
    
    Do While (Not rst.BOF() And didFind = False)
        
        If (Me.agency.Value = rst![agency]) And (Me.address.Value = rst![address]) Then
            didFind = True
            cCustID = rst![CustID]
        Else
            rst.MovePrevious
        End If
    Loop
    
    If (didFind = False) Then
        Exit Sub
    End If
    
    'Set City State Zip.. =)
    Me.city = IIf(IsNull(rst![city]), "", rst![city])
    Me.state = IIf(IsNull(rst![state]), "", rst![state])
    Me.zip = IIf(IsNull(rst![zip]), "", rst![zip])
    Me.phone = IIf(IsNull(rst![phone]), "", rst![phone])
    Me.fax = IIf(IsNull(rst![fax]), "", rst![phone])
    Me.Member = IIf(IsNull(rst![Member]), "", rst![Member])
    
End Sub

Private Sub agency_Change()
    
    'CustID of first match of person with same agency (going backwards)
  '  Dim agCustID As String
    
    'Get the current string in field agency entered by user
 '   Dim agSoFar As String
  '  Dim agLen As Integer
 '   Me.agency.Refresh
    
 '   agSoFar = Me.agency.Value
    
'    agLen = Len(agSoFar)
    
 '   If (agLen < 1) Then
 '       Exit Sub
 '   End If
    'agency.SetFocus
    
    
    
    
    
    
End Sub

Private Sub agency_Exit(Cancel As Integer)
    
    'Fill in Division, Address, City, State, and Zip from previous agency match.
        'Version 1.3.. phone, fax, member
    'First go into customer table, find highest rst with same agency match
    'This will match the information with the same agency last entered.
    
    Dim rst As Recordset
    Set rst = CurrentDb.OpenRecordset("Customer")
    
    'if agency is blank, skip
    If (IsNull(Me.agency) Or Len(Me.agency) < 1) Then
        Exit Sub
    End If
    
    'Move through Customer and find match
    Dim didFind As Boolean
    Dim cCustID As Integer
    
    If (Not rst.BOF) Then
        rst.MoveLast
    Else
        Exit Sub
    End If
    
    Do While (Not rst.BOF() And didFind = False)
        
        If (Me.agency.Value = rst![agency]) Then
            didFind = True
            cCustID = rst![CustID]
        Else
            rst.MovePrevious
        End If
    Loop
    
    If (didFind = False) Then
        Exit Sub
    End If
    
    'Set Division Address City State Zip.. =)
    Me.division = IIf(IsNull(rst![division]), "", rst![division])
    Me.address = IIf(IsNull(rst![address]), "", rst![address])
    Me.city = IIf(IsNull(rst![city]), "", rst![city])
    Me.state = IIf(IsNull(rst![state]), "", rst![state])
    Me.zip = IIf(IsNull(rst![zip]), "", rst![zip])
    Me.phone = IIf(IsNull(rst![phone]), "", rst![phone])
    Me.fax = IIf(IsNull(rst![fax]), "", rst![fax])
    Me.Member = IIf(IsNull(rst![Member]), "", rst![Member])
    
    
    
    
End Sub

Private Sub agency_GotFocus()
    Me.Refresh
End Sub

Private Sub CloseAddCustomer_Click()
    
    DoCmd.Close

End Sub

Private Sub EnrollInAClass_Click()
On Error GoTo Err_EnrollInAClass_Click

    nrvLookForDupes
    'Add the customer to the database
    If (DupeEntry = False) Then
        nrvAddCustomer
        nrvSetCustToDefaultValues
    End If
    Me.SSN.SetFocus
    
    'MsgBox ("Would you like to Enroll this person into a class?")
    

Exit_EnrollInAClass_Click:
    Exit Sub

Err_EnrollInAClass_Click:
    MsgBox Err.Description
    Resume Exit_EnrollInAClass_Click
    
End Sub

Private Sub nrvAddCustomer()
    Dim dbs As Database
    Dim rst As Recordset
    
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("Customer")

    rst.AddNew
    If Len(SSN) > 0 Then
        rst![SSN] = SSN
    End If
    If Len(fname) > 0 Then
        rst![fname] = fname
    End If
    If Len(middleInitial) > 0 Then
        rst![middle Initial] = middleInitial
    End If
    If Len(lname) > 0 Then
        rst![lname] = lname
    End If
    If Len(agency) > 0 Then
        rst![agency] = agency
    End If
    If Len(division) > 0 Then
        rst![division] = division
    End If
    If Len(address) > 0 Then
        rst![address] = address
    End If
    If Len(city) > 0 Then
        rst![city] = city
    End If
    If Len(state) > 0 Then
        rst![state] = state
    End If
    If Len(zip) > 0 Then
        rst![zip] = zip
    End If
    If Len(phone) > 0 Then
        rst![phone] = phone
    End If
    If Len(fax) > 0 Then
        rst![fax] = fax
    End If
    If Len(Email) > 0 Then
        rst![Email] = Email
    End If
    If Len(Member) > 0 Then
        rst![Member] = Member
    End If
    If Len(country) > 0 Then
        rst![country] = country
    End If
    If Len(notefield) > 0 Then
        rst![Notes] = notefield
    End If
    rst.Update
    
    MsgBox ("Customer Successfully Added")
End Sub

Private Sub nrvSetCustToDefaultValues()
    SSN = SSN.DefaultValue
    fname = fname.DefaultValue
    middleInitial = middleInitial.DefaultValue
    lname = lname.DefaultValue
    agency = agency.DefaultValue
    division = division.DefaultValue
    address = address.DefaultValue
    city = city.DefaultValue
    state = state.DefaultValue
    zip = zip.DefaultValue
    phone = phone.DefaultValue
    fax = fax.DefaultValue
    Email = Email.DefaultValue
    country = country.DefaultValue
    Member = Member.DefaultValue
    notefield = notefield.DefaultValue
End Sub
Private Sub Command72_Click()
On Error GoTo Err_Command72_Click

    'Look for empty fields + dupe SSN's..
        nrvLookForDupes
    'Add the customer to the database
    If (DupeEntry = False) Then
        nrvAddCustomer
    Else
        'Exit if was a dupe..
        Exit Sub
    End If
    
    'Find the CustID
    Dim cCustID As Integer
    Dim didFind As Boolean
    Dim rst As Recordset
    Set rst = CurrentDb.OpenRecordset("Customer")
    
    i = 0
    If (Not rst.EOF) Then
        rst.MoveLast
    Else
        MsgBox ("There are no customers?? ERROR ERROR ERROR")
        Exit Sub
    End If
    'Start didFind at false
    didFind = False
    Dim ccfname As String
    Dim cclname As String
    Dim ccssn As String
    
        'format data for comparisons
    Do While (Not rst.BOF() And didFind = False)
        'format data for comparisons
        If (Not IsNull(rst![fname])) Then
            ccfname = rst![fname]
        Else
            ccfname = ""
        End If
        If (Not IsNull(rst![lname])) Then
            cclname = rst![lname]
        Else
            cclname = ""
        End If
        If (Not IsNull(rst![SSN])) Then
            ccssn = rst![SSN]
        Else
            ccssn = ""
        End If
        

        'look for matching fname, lname, ssn
        If (Me.fname = ccfname And Me.lname = cclname And Me.SSN = ccssn) Then
            
            cCustID = rst![CustID]
            didFind = True
        End If
        
        rst.MovePrevious
    Loop
    
    'Just in case a match wasn't found
    If (didFind = False) Then
        MsgBox ("Customer not found? not good, **ERROR**, try again or something")
        Exit Sub
    End If
    
    'Open nrv - Enroll Page
    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "nrv - Enroll People"
    stLinkCriteria = "[CustID]=" & cCustID

    DoCmd.OpenForm stDocName, , , stLinkCriteria

    'Clear the data
    nrvSetCustToDefaultValues
    Me.SSN.SetFocus

Exit_Command72_Click:
    Exit Sub

Err_Command72_Click:
    MsgBox Err.Description
    Resume Exit_Command72_Click
    
End Sub


Private Sub nrvLookForDupes()

    DupeEntry = False
    
    'Check for blanks in fields..
    If (Me.SSN = Me.SSN.DefaultValue And Me.fname = Me.fname.DefaultValue And Me.lname = Me.lname.DefaultValue) Then
        MsgBox ("SSN, first name, and last name all blank. Customer not added")
        DupeEntry = True
    End If

    'Check for dupe SSN
    Dim qdf As QueryDef
    Set rst = CurrentDb.OpenRecordset("Customer")
    
    If (rst.RecordCount <> 0) Then
        
        If (Not rst.EOF) Then
            rst.MoveFirst
        End If
        i = 0
        Do While (Not rst.EOF() And DupeEntry = False)
            
            If (Me.SSN = rst![SSN]) Then
                DupeEntry = True
                MsgBox ("SSN is a duplicate. Customer not added")
                SSN.SetFocus
            End If
            rst.MoveNext
        Loop
    End If
    
    
    
End Sub

Private Sub Clear_Click()
On Error GoTo Err_Clear_Click

    nrvSetCustToDefaultValues
   
    Me.SSN.SetFocus

Exit_Clear_Click:
    Exit Sub

Err_Clear_Click:
    MsgBox Err.Description
    Resume Exit_Clear_Click
    
End Sub

Private Sub fname_Exit(Cancel As Integer)

    If IsNull(fname.Value) Then
        Exit Sub
    End If
    
    fname.Value = Proper(fname.Value)

End Sub

Private Sub fname_GotFocus()
    Dim rst As Recordset
    Set rst = CurrentDb.OpenRecordset("Customer")
    
    If (rst.RecordCount <> 0) Then
        
        If (Not rst.EOF) Then
            rst.MoveFirst
        Else
            Exit Sub
        End If
        
        i = 0
        Do While (Not rst.EOF())
            
            If (Me.SSN = rst![SSN]) Then
                MsgBox ("SSN is a duplicate,enter another")
                SSN.SetFocus
                Exit Sub
            End If
            rst.MoveNext
        Loop
    End If
End Sub

Private Sub Form_GotFocus()
    Me.SSN.SetFocus
End Sub

Private Sub Form_Open(Cancel As Integer)
    nrvSetCustToDefaultValues
   
    Me.SSN.SetFocus
End Sub

Private Sub lname_Exit(Cancel As Integer)

    If IsNull(lname.Value) Then
        Exit Sub
    End If
    
    lname.Value = Proper(lname.Value)
End Sub

Private Sub SSN_Exit(Cancel As Integer)


    Dim rst As Recordset
    Set rst = CurrentDb.OpenRecordset("Customer")
    
    If (rst.RecordCount <> 0) Then
        
        If (Not rst.EOF) Then
            rst.MoveFirst
        Else
            Exit Sub
        End If
        
        i = 0
        Do While (Not rst.EOF())
            
            If (Me.SSN = rst![SSN]) Then
                MsgBox ("SSN is a duplicate,enter another")
                SSN.SetFocus
                MsgBox ("sigh")
                Exit Sub
            End If
            rst.MoveNext
        Loop
    End If
End Sub