frmcustomer_new.frm

英文版Access数据库编程

      Height          =   255
      Index           =   7
      Left            =   5400
      TabIndex        =   22
      Top             =   1560
      Width           =   1215
   End
   Begin VB.Label Label1 
      Caption         =   "Phone (2):"
      Height          =   255
      Index           =   8
      Left            =   5400
      TabIndex        =   21
      Top             =   1920
      Width           =   1215
   End
   Begin VB.Label Label1 
      Caption         =   "Fax (1):"
      ForeColor       =   &H000000FF&
      Height          =   255
      Index           =   9
      Left            =   5400
      TabIndex        =   20
      Top             =   2280
      Width           =   1215
   End
   Begin VB.Label Label1 
      Caption         =   "Fax (2):"
      Height          =   255
      Index           =   10
      Left            =   5400
      TabIndex        =   19
      Top             =   2640
      Width           =   1215
   End
   Begin VB.Line Line1 
      X1              =   7200
      X2              =   7320
      Y1              =   1680
      Y2              =   1680
   End
   Begin VB.Line Line2 
      X1              =   7200
      X2              =   7320
      Y1              =   2040
      Y2              =   2040
   End
   Begin VB.Line Line3 
      X1              =   7200
      X2              =   7320
      Y1              =   2400
      Y2              =   2400
   End
   Begin VB.Line Line4 
      X1              =   7200
      X2              =   7320
      Y1              =   2760
      Y2              =   2760
   End
End
Attribute VB_Name = "frmCustomer_New"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit
Private Sub CheckCustomerFields()
If (Len(txtName.Text) = 0) Or (Len(txtAddress.Text) = 0) Or (Len(cmbCountry.Text) = 0) Or _
(Len(cmbState.Text) = 0) Or (Len(cmbCity.Text) = 0) Or (Len(txtPhone1(0).Text) = 0) Or (Len(txtPhone1(1).Text) = 0) Or _
(Len(txtFax1(0).Text) = 0) Or (Len(txtFax1(1).Text) = 0) Or (Len(txtLimit.Text) = 0) Or (Len(txtTerm.Text) = 0) Then
    cmdSave.Enabled = False
Else
    cmdSave.Enabled = True
End If
End Sub

Private Sub cmbCity_Change()
CheckCustomerFields
End Sub

Private Sub cmbCity_GotFocus()
If cmbCity.Text = "[PLEASE SELECT ONE]" Then
    cmbCity.Text = ""
End If
SelText cmbCity
End Sub

Private Sub cmbCity_LostFocus()
CapCon cmbCity
End Sub

Private Sub cmbCountry_Change()
CheckCustomerFields
End Sub

Private Sub cmbCountry_Click()
FillComboState cmbState, cmbCountry.Text
End Sub

Private Sub cmbCountry_GotFocus()
If cmbCountry.Text = "[PLEASE SELECT ONE]" Then
    cmbCountry.Text = ""
End If
SelText cmbCountry
End Sub

Private Sub cmbCountry_LostFocus()
CapCon cmbCountry
End Sub

Private Sub cmbState_Change()
CheckCustomerFields
End Sub

Private Sub cmbState_Click()
FillComboCity cmbCity, cmbState.Text
End Sub

Private Sub cmbState_GotFocus()
If cmbState.Text = "[PLEASE SELECT ONE]" Then
    cmbState.Text = ""
End If
SelText cmbState
End Sub

Private Sub cmbState_LostFocus()
CapCon cmbState
End Sub

Private Sub cmdCancel_Click()
Unload Me
End Sub

Private Sub cmdSave_Click()
If CCur(txtLimit.Text) <= 0 Then
    ValidMsg "Credit limit has to been more than $0.", "Invalid value"
    txtLimit.SetFocus
Else
    Dim saveSQL As String, tempChar As String
    Dim newID As Long
    'Get unique ID
    tempChar = Left$(txtName.Text, 1)
    If IsNumeric(tempChar) = True Then
        tempChar = "#"
    End If
    'define query
    saveSQL = "SELECT Ref_Number FROM Customer_IDs WHERE InitialID='" & tempChar & "';"
    On Error GoTo ErrHandler
    'execute query
    Dim SaveRS As Recordset, tempRS As Recordset
    RSOpen tempRS, saveSQL, dbOpenDynaset
    'Obtain the reference number needed to form unique ID
    newID = tempRS("Ref_Number")
    
    saveSQL = "SELECT * FROM Customers;"
    RSOpen SaveRS, saveSQL, dbOpenDynaset
    SaveRS.AddNew
    SaveRS("CustomerID") = tempChar & Format$(newID, "0000")
    SaveRS("Name") = txtName.Text
    SaveRS("Address") = txtAddress.Text
    SaveRS("City") = cmbCity.Text
    SaveRS("State") = cmbState.Text
    SaveRS("Zip") = txtZip.Text
    SaveRS("Country") = cmbCountry.Text
    SaveRS("ACPhone1") = txtPhone1(0).Text
    SaveRS("ACPhone2") = txtPhone2(0).Text
    SaveRS("Phone1") = txtPhone1(1).Text
    SaveRS("Phone2") = txtPhone2(1).Text
    SaveRS("ACFax1") = txtFax1(0).Text
    SaveRS("ACFax2") = txtFax2(0).Text
    SaveRS("Fax1") = txtFax1(1).Text
    SaveRS("Fax2") = txtFax2(1).Text
    SaveRS("Email") = txtEmail.Text
    SaveRS("CreditLimit") = txtLimit.Text
    SaveRS("CreditTerm") = txtTerm.Text
    SaveRS("CurrentBalance") = "0.00"
    SaveRS.Update

    tempRS.Edit
    tempRS("Ref_Number") = newID + 1
    tempRS.Update

    tempRS.Close
    SaveRS.Close
    Set tempRS = Nothing
    Set SaveRS = Nothing
    'Insert into systems log
    insertLog "Customer ID: " & newID & " account has been created."
    InfoMsg "Customer ID: " & tempChar & Format$(newID, "0000") & vbCrLf & "New customer record has been successfully created.", "Record saved"
    
    Unload Me
End If

ErrHandler:
If Err.Number <> 0 Then
    ErrorNotifier Err.Number, Err.description
End If
End Sub

Private Sub Form_Load()
isOpen = False
FillComboCountry cmbCountry
lblHeader.Caption = "Please enter the details of the new customer accurately." & vbNewLine & _
"Entries will be converted to upper-case automatically."
DisableClose Me, True
End Sub

Private Sub Form_Unload(Cancel As Integer)
Set frmCustomer_New = Nothing
End Sub

Private Sub txtAddress_Change()
CheckCustomerFields
End Sub

Private Sub txtAddress_GotFocus()
SelText txtAddress
End Sub

Private Sub txtAddress_LostFocus()
CapCon txtAddress
End Sub

Private Sub txtEmail_Change()
CheckCustomerFields
End Sub

Private Sub txtEmail_GotFocus()
SelText txtEmail
End Sub

Private Sub txtFax1_Change(Index As Integer)
CheckCustomerFields
End Sub

Private Sub txtFax1_GotFocus(Index As Integer)
SelText txtFax1(Index)
End Sub

Private Sub txtFax1_KeyPress(Index As Integer, KeyAscii As Integer)
OnlyNum KeyAscii
End Sub

Private Sub txtFax1_LostFocus(Index As Integer)
If Index = 0 Then
    If (IsNull(txtFax1(Index).Text) = False) Or (txtFax1(Index).Text <> "") Then
        txtFax1(Index).Text = Format$(txtFax1(Index).Text, "000")
    End If
End If

End Sub

Private Sub txtFax2_Change(Index As Integer)
CheckCustomerFields
End Sub

Private Sub txtFax2_GotFocus(Index As Integer)
SelText txtFax2(Index)
End Sub

Private Sub txtFax2_KeyPress(Index As Integer, KeyAscii As Integer)
OnlyNum KeyAscii
End Sub

Private Sub txtFax2_LostFocus(Index As Integer)
If Index = 0 Then
    If (IsNull(txtFax2(Index).Text) = False) Or (txtFax2(Index).Text <> "") Then
        txtFax2(Index).Text = Format$(txtFax2(Index).Text, "000")
    End If
End If
End Sub

Private Sub txtLimit_Change()
CheckCustomerFields
End Sub

Private Sub txtLimit_GotFocus()
SelText txtLimit
End Sub

Private Sub txtLimit_KeyPress(KeyAscii As Integer)
If KeyAscii <> Asc(".") Then
    OnlyNum KeyAscii
End If
End Sub

Private Sub txtName_Change()
CheckCustomerFields
End Sub

Private Sub txtName_GotFocus()
SelText txtName
End Sub

Private Sub txtName_LostFocus()
CapCon txtName
End Sub

Private Sub txtPhone1_Change(Index As Integer)
CheckCustomerFields
End Sub

Private Sub txtPhone1_GotFocus(Index As Integer)
SelText txtPhone1(Index)
End Sub

Private Sub txtPhone1_KeyPress(Index As Integer, KeyAscii As Integer)
OnlyNum KeyAscii
End Sub

Private Sub txtPhone1_LostFocus(Index As Integer)
If Index = 0 Then
    If (IsNull(txtPhone1(Index).Text) = False) Or (txtPhone1(Index).Text <> "") Then
        txtPhone1(Index).Text = Format$(txtPhone1(Index).Text, "000")
    End If
End If
End Sub

Private Sub txtPhone2_Change(Index As Integer)
CheckCustomerFields
End Sub

Private Sub txtPhone2_GotFocus(Index As Integer)
SelText txtPhone2(Index)
End Sub

Private Sub txtPhone2_KeyPress(Index As Integer, KeyAscii As Integer)
OnlyNum KeyAscii
End Sub

Private Sub txtPhone2_LostFocus(Index As Integer)
If Index = 0 Then
    If (IsNull(txtPhone2(Index).Text) = False) Or (txtPhone2(Index).Text <> "") Then
        txtPhone2(Index).Text = Format$(txtPhone2(Index).Text, "000")
    End If
End If

End Sub

Private Sub txtTerm_Change()
CheckCustomerFields
End Sub

Private Sub txtTerm_GotFocus()
SelText txtTerm
End Sub

Private Sub txtTerm_KeyPress(KeyAscii As Integer)
KeyAscii = 0
End Sub

Private Sub txtZip_Change()
CheckCustomerFields
End Sub

Private Sub txtZip_GotFocus()
SelText txtZip
End Sub

Private Sub txtZip_KeyPress(KeyAscii As Integer)
OnlyNum KeyAscii
End Sub

Private Sub udTerm_Change()
CheckCustomerFields
End Sub