frmvendormaster.frm

This a complete inventory management system that performs the functions of Purchases, Sales and paym

    "','" & fnEscapeQuote(Trim(txtCountry)) & "','" & fnEscapeQuote(Trim(txtPhone)) & _
    "','" & fnEscapeQuote(Trim(txtFax)) & "','" & fnEscapeQuote(Trim(txtEmail)) & "',0,'" & fnEscapeQuote(Trim(txtNotes)) & "')"
    
    gQueryBM1 = "insert into aBranchManager(r_Type, r_ID, r_BFName,r_BLName,r_BSex,r_BMartial,r_BDob , r_BDoj,r_BAdd1,r_BAdd2,r_BCity,r_BState,r_BPincode,r_BCountry,r_BOffPhone,r_BResPhone,r_BMobile,r_BFax,r_BEmail,r_BEducation, r_BImage, r_Notes) values (1,'" & Trim(txtBranchId) & _
    "','" & fnEscapeQuote(Trim(txtBFirstName)) & "','" & fnEscapeQuote(Trim(txtBLastName)) & _
    "','" & fnEscapeQuote(Trim(txtBSex)) & "','" & fnEscapeQuote(Trim(txtBMarital)) & _
    "','" & fnEscapeQuote(Trim(txtBDoB)) & "','" & fnEscapeQuote(Trim(txtBDoJ)) & _
    "','" & fnEscapeQuote(Trim(txtBAdd1)) & "','" & fnEscapeQuote(Trim(txtBAdd2)) & _
    "','" & fnEscapeQuote(Trim(txtBCity)) & "','" & fnEscapeQuote(Trim(txtBState)) & _
    "','" & fnEscapeQuote(Trim(txtBPincode)) & "','" & fnEscapeQuote(Trim(txtBCountry)) & _
    "','" & fnEscapeQuote(Trim(txtBPhoneOff)) & "','" & fnEscapeQuote(Trim(txtBPhoneRes)) & _
    "','" & fnEscapeQuote(Trim(txtBMob)) & "','" & fnEscapeQuote(Trim(txtBFax)) & _
    "','" & fnEscapeQuote(Trim(txtBEmail)) & "','" & fnEscapeQuote(Trim(txtBEducation)) & "','" & fnEscapeQuote(Trim(strImageName)) & "','" & fnEscapeQuote(Trim(txtBNotes)) & "')"
    
    gQueryBM2 = "insert into aBranchManager(r_Type, r_ID, r_BFName,r_BLName,r_BSex,r_BMartial,r_BDob , r_BDoj,r_BAdd1,r_BAdd2,r_BCity,r_BState,r_BPincode,r_BCountry,r_BOffPhone,r_BResPhone,r_BMobile,r_BFax,r_BEmail,r_BEducation, r_BImage, r_Notes) values (2,'" & Trim(txtBranchId) & _
    "','" & fnEscapeQuote(Trim(txtB2FirstName)) & "','" & fnEscapeQuote(Trim(txtB2LastName)) & _
    "','" & fnEscapeQuote(Trim(txtB2Sex)) & "','" & fnEscapeQuote(Trim(txtB2Marital)) & _
    "','" & fnEscapeQuote(Trim(txtB2DoB)) & "','" & fnEscapeQuote(Trim(txtB2DoJ)) & _
    "','" & fnEscapeQuote(Trim(txtB2Add1)) & "','" & fnEscapeQuote(Trim(txtB2Add2)) & _
    "','" & fnEscapeQuote(Trim(txtB2City)) & "','" & fnEscapeQuote(Trim(txtB2State)) & _
    "','" & fnEscapeQuote(Trim(txtB2Pincode)) & "','" & fnEscapeQuote(Trim(txtB2Country)) & _
    "','" & fnEscapeQuote(Trim(txtB2PhoneOff)) & "','" & fnEscapeQuote(Trim(txtB2PhoneRes)) & _
    "','" & fnEscapeQuote(Trim(txtB2Mob)) & "','" & fnEscapeQuote(Trim(txtB2Fax)) & _
    "','" & fnEscapeQuote(Trim(txtB2Email)) & "','" & fnEscapeQuote(Trim(txtB2Education)) & "','" & fnEscapeQuote(Trim(strImageName2)) & "','" & fnEscapeQuote(Trim(txtB2Notes)) & "')"
    
   
    gQuery1 = "Update aCompanySetup set a_LastMft=a_LastMft + 1 where a_sno=1"
    'MsgBox gQuery
    CNimanager.Execute (gQuery)
'    CNimanager.Execute (gQueryBM1)
  '  CNimanager.Execute (gQueryBM2)
   CNimanager.Execute (gQuery1)
'    CNimanager.Execute (gQueryOH)


        MsgBox "Vendor Added", vbInformation, "iManager"
        Unload Me
        frmVendorMaster.Show

ElseIf gMod = 1 Then
    gQuery = "Update aVendorMaster set r_Name='" & fnEscapeQuote(Trim(txtName)) & _
    "', r_PromotingId='" & fnEscapeQuote(Trim(txtPromotingId)) & "', r_Add1='" & fnEscapeQuote(Trim(txtAdd1)) & _
    "', r_Add2='" & fnEscapeQuote(Trim(txtAdd2)) & "', r_City='" & fnEscapeQuote(Trim(txtCity)) & _
    "', r_State='" & fnEscapeQuote(Trim(txtState)) & "', r_Pin='" & fnEscapeQuote(Trim(txtPincode)) & _
    "', r_Country='" & fnEscapeQuote(Trim(txtCountry)) & "', r_Phone='" & fnEscapeQuote(Trim(txtPhone)) & _
    "', r_Fax='" & fnEscapeQuote(Trim(txtFax)) & "', r_Email='" & fnEscapeQuote(Trim(txtEmail)) & _
    "',r_OH=0, r_Notes='" & fnEscapeQuote(Trim(txtNotes)) & "' where r_Id='" & Trim(txtBranchId) & "'"
    
gQueryBM1 = "Update aBranchManager set r_BFname = '" & fnEscapeQuote(Trim(txtBFirstName)) & "', r_BLName = '" & fnEscapeQuote(Trim(txtBLastName)) & _
"', r_BSex = '" & fnEscapeQuote(Trim(txtBSex)) & "', r_BMartial = '" & fnEscapeQuote(Trim(txtBMarital)) & _
"', r_BDob  = '" & fnEscapeQuote(Trim(txtBDoB)) & "', r_BDoj = '" & fnEscapeQuote(Trim(txtBDoJ)) & _
"', r_BAdd1 = '" & fnEscapeQuote(Trim(txtBAdd1)) & "', r_BAdd2 = '" & fnEscapeQuote(Trim(txtBAdd2)) & _
"', r_BCity = '" & fnEscapeQuote(Trim(txtBCity)) & "', r_BState = '" & fnEscapeQuote(Trim(txtBState)) & _
"', r_BPincode = '" & fnEscapeQuote(Trim(txtBPincode)) & "', r_BCountry ='" & fnEscapeQuote(Trim(txtBCountry)) & _
"', r_BOffPhone = '" & fnEscapeQuote(Trim(txtBPhoneOff)) & "',r_BResPhone = '" & fnEscapeQuote(Trim(txtBPhoneRes)) & _
"', r_BMobile = '" & fnEscapeQuote(Trim(txtBMob)) & "',r_BFax ='" & fnEscapeQuote(Trim(txtBFax)) & _
"', r_BEmail = '" & fnEscapeQuote(Trim(txtBEmail)) & "', r_BEducation ='" & fnEscapeQuote(Trim(txtBEducation)) & _
"', r_BImage='" & fnEscapeQuote(Trim(strImageName)) & "',r_Notes='" & fnEscapeQuote(Trim(txtBNotes)) & "' where r_Id='" & Trim(txtBranchId) & "' and r_Type=1"
    
gQueryBM2 = "Update aBranchManager set r_BFname = '" & fnEscapeQuote(Trim(txtB2FirstName)) & "', r_BLName = '" & fnEscapeQuote(Trim(txtB2LastName)) & _
"', r_BSex = '" & fnEscapeQuote(Trim(txtB2Sex)) & "', r_BMartial = '" & fnEscapeQuote(Trim(txtB2Marital)) & _
"', r_BDob  = '" & fnEscapeQuote(Trim(txtB2DoB)) & "', r_BDoj = '" & fnEscapeQuote(Trim(txtB2DoJ)) & _
"', r_BAdd1 = '" & fnEscapeQuote(Trim(txtB2Add1)) & "', r_BAdd2 = '" & fnEscapeQuote(Trim(txtB2Add2)) & _
"', r_BCity = '" & fnEscapeQuote(Trim(txtB2City)) & "', r_BState = '" & fnEscapeQuote(Trim(txtB2State)) & _
"', r_BPincode = '" & fnEscapeQuote(Trim(txtB2Pincode)) & "', r_BCountry ='" & fnEscapeQuote(Trim(txtB2Country)) & _
"', r_BOffPhone = '" & fnEscapeQuote(Trim(txtB2PhoneOff)) & "',r_BResPhone = '" & fnEscapeQuote(Trim(txtB2PhoneRes)) & _
"', r_BMobile = '" & fnEscapeQuote(Trim(txtB2Mob)) & "',r_BFax ='" & fnEscapeQuote(Trim(txtB2Fax)) & _
"', r_BEmail = '" & fnEscapeQuote(Trim(txtB2Email)) & "', r_BEducation ='" & fnEscapeQuote(Trim(txtB2Education)) & _
"', r_BImage='" & fnEscapeQuote(Trim(strImageName2)) & "',r_Notes='" & fnEscapeQuote(Trim(txtB2Notes)) & "' where r_Id='" & Trim(txtBranchId) & "' and r_Type=2"
    
   
    
    CNimanager.Execute (gQuery)
    
        MsgBox "Vendor Edited", vbInformation, "iManager"
        Unload Me
        frmVendorMaster.Show
End If
End Function

Private Sub Command1_Click()
frmCountry.Show
End Sub

Private Sub Command2_Click()
frmCountry.Show vbModal
End Sub

Private Sub Command3_Click()
frmCountry.Show vbModal
End Sub

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
Select Case KeyCode
    Case 27
    If MsgBox("Do you want to exit this form?", vbQuestion + vbYesNo, "iManager") = vbYes Then
        Unload Me
    End If
    Case 13
        SendKeys "{tab}"
        SendKeys "{home}+{end}"
End Select
End Sub

Private Sub Form_KeyPress(KeyAscii As Integer)
'        Select Case KeyAscii
'            Case 5
'                Call fn_Edit
'            Case 14
'                Call fn_New
'            Case 19
'                Call fn_Save
'            Case 24
'                fn_Exit
'        End Select
End Sub

Private Sub Form_Load()

Set fs = New FileSystemObject
gMod = 0

txtNo.Visible = False
Dim rstNew As ADODB.Recordset
Dim gQuery
Dim gNewID
    Set rstNew = New ADODB.Recordset
            rstNew.Open "select a_LastMft from aCompanySetup where a_sno=1", CNimanager
            gNewID = Trim(rstNew("a_LastMft")) + 1
            txtNo = gNewID
        For i = 1 To 5 - Len(gNewID)
            gNewID = "0" & gNewID
        Next
        gNewID = "V" & gNewID
        txtBranchId = gNewID
     

End Sub

Private Sub Form_Paint()
   Me.Visible = True
End Sub

Private Sub Tlb_Kit_ButtonClick(ByVal Button As MSComctlLib.Button)
Select Case Button.Index
    Case 1
        Call fn_new
    Case 3
        Call fn_Save
    Case 5
        Call fn_Edit
    Case 7
        Call fn_Exit
        'Call fn_Delete
    'Case 9
        
End Select
End Sub

Private Sub txtB2Country_Click()

End Sub





Private Sub txtB2FirstName_lostfocus()
txtB2FirstName = StrConv(Trim(txtB2FirstName), vbProperCase)
Call FillBM
End Sub
Private Sub txtBLastName_lostfocus()
txtBLastName = StrConv(Trim(txtBLastName), vbProperCase)
Call FillBM
End Sub
Private Sub txtB2LastName_lostfocus()
txtB2LastName = StrConv(Trim(txtB2LastName), vbProperCase)
Call FillBM
End Sub





Private Sub txtEmail_lostfocus()
If Not Trim(txtEmail) = "" Then
    If IsValidEmailAddress(txtEmail) = True Then
'        sstBranch.Tab = 1
        'SSTab1.Tab = 0
    Else
        MsgBox "Enter a valid Email ID", vbCritical
        txtEmail.SetFocus
    End If
Else
End If
End Sub

Private Sub txtName_LostFocus()
txtName = StrConv(Trim(txtName), vbProperCase)
End Sub

Private Sub txtOHCom_LostFocus()
If Not Trim(txtOHCom) = "" Then
If IsNumeric(txtOHCom) = True Then
    If txtOHCom >= 100 Or txtOHCom < 0 Then
        MsgBox "Commission should be between 0 and 100 only", vbCritical
        txtOHCom.SetFocus
    Else
        txtOHCom = Trim(Replace(FormatNumber(Val(txtOHCom), 2), ",", ""))
    End If
Else
    MsgBox "Enter a valid numeric value", vbCritical
    txtOHCom.SetFocus
End If
Else
txtOHCom = Trim(Replace(FormatNumber(0, 2), ",", ""))
End If
End Sub

Private Sub txtPromotingId_Change()
Dim rstPopulate As ADODB.Recordset
Set rstPopulate = New ADODB.Recordset
rstPopulate.Open "select r_Name,(select r_BFName +' ' + r_BLName  from aBranchManager where r_Id='" & Trim(txtPromotingId) & "' and r_Type=1)as BM1, (select r_BFName +' ' + r_BLName from aBranchManager where r_Id='" & Trim(txtPromotingId) & "' and r_Type=2) as BM2   from aVendorMaster where r_Id='" & Trim(txtPromotingId) & "'", CNimanager
If Not rstPopulate.EOF Or Not rstPopulate.BOF Then
    lblPIDBName = Null2Blank(Trim(rstPopulate("r_Name")))
    lblPIDBMName = Null2Blank(Trim(rstPopulate("BM1")))
    If Not Null2Blank(Trim(lblPIDBMName)) = "" Then
        If Not Null2Blank(Trim(rstPopulate("BM2"))) = "" Then
            lblPIDBMName = lblPIDBMName & " / " & Null2Blank(Trim(rstPopulate("BM2")))
        End If
    Else
            lblPIDBMName = Null2Blank(Trim(rstPopulate("BM2")))
    End If
    If Not Trim(lblPIDBMName) = "" Then
        lblPIDBMName = "Manager - " & lblPIDBMName
    End If
End If
End Sub
Private Function FillList(pVal)
Dim gPromId
Dim aBreak
Dim aCount
gOH = ""
gOHName = ""
aBreak = 0
aCount = 1
gPromId = pVal
MSH.Clear
MSH.Cols = 4
MSH.Rows = 1
MSH.ColAlignment(0) = flexAlignCenterCenter
MSH.ColAlignment(3) = flexAlignRightCenter
MSH.ColWidth(0) = 1000
MSH.ColWidth(1) = 1000
MSH.ColWidth(2) = 3000
MSH.ColWidth(3) = 1000
MSH.TextMatrix(0, 0) = "Up Level"
MSH.TextMatrix(0, 1) = "Agent Id"
MSH.TextMatrix(0, 2) = "Name"
MSH.TextMatrix(0, 3) = "Profit %"
Do While Not aBreak = 1
    Dim rstFillList As ADODB.Recordset
    Set rstFillList = New ADODB.Recordset
    rstFillList.Open "select r_ID, r_Name, r_PromotingId, r_OH from aVendorMaster where r_ID='" & gPromId & "'", CNimanager
        
        If Not rstFillList.EOF Or Not rstFillList.BOF Then
            MSH.Rows = MSH.Rows + 1
            MSH.TextMatrix(aCount, 0) = aCount
            MSH.TextMatrix(aCount, 1) = Null2Blank(rstFillList("r_ID"))
            MSH.TextMatrix(aCount, 2) = Null2Blank(rstFillList("r_Name"))
            MSH.TextMatrix(aCount, 3) = Trim(Replace(FormatNumber(0, 2), ",", ""))
            gPromId = Null2Blank(rstFillList("r_PromotingId"))
            aCount = aCount + 1
        Else
            aBreak = 1
        End If
    rstFillList.Close
    Set rstFillList = Nothing
Loop
End Function

Private Sub FillBM()
    Dim lBM1
    Dim lBM2
    Dim lStr
    lBM1 = Trim(txtBFirstName) & " " & Trim(txtBLastName)
    lBM2 = Trim(txtB2FirstName) & " " & Trim(txtB2LastName)
    lStr = lBM1
    If Not Trim(lBM2) = "" Then
        If Not Trim(lBM1) = "" Then
            lStr = lStr & " / " & lBM2
        Else
            lStr = lBM2
        End If
    End If
    If Not Trim(lStr) = "" Then
        lStr = "Manager - " & lStr
    End If
lblBMName = Trim(lStr)
End Sub

Public Sub fn_Delete()
Dim gBranchid
Dim gSQL As String

If gMod = 1 Then
    gBranchid = Trim(txtBranchId)
    If Len(gBranchid) <> 0 Then
        If MsgBox("Are you sure you want to delete this Agent!!", vbQuestion + vbYesNo) = vbYes Then
            '/* Delete from  aVendorMaster */
           gSQL = "delete from aVendorMaster where r_Id='" & gBranchid & "'"
           CNimanager.Execute (gSQL)
            '/* Delete from  aBranchManager 1*/
            gSQL = "delete from aBranchManager where r_ID='" & gBranchid & "' and r_Type=1"
            CNimanager.Execute (gSQL)
            '/* Delete from  aBranchManager 2*/
            gSQL = "delete from aBranchManager where r_ID='" & gBranchid & "' and r_Type=2"
            CNimanager.Execute (gSQL)
            
            '/* Delete from  aCommission */
            gSQL = "delete from aCommission where BranchID='" & gBranchid & "'"
            CNimanager.Execute (gSQL)
            
            '/* Delete from  HeadOffice */
            gSQL = "delete from aOHCommission where BranchID='" & gBranchid & "'"
            CNimanager.Execute (gSQL)
        Else
            Exit Sub
        End If
        MsgBox "Agent Deleted!!", vbInformation
        Unload Me
        frmBranchMaster.Show vbModal
    Else
        MsgBox "Enter Branch Id to delete", vbCritical
        Exit Sub
    End If
End If
End Sub