Attribute VB_Name = "conn"
Public Declare Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer

Public annual2010 As ADODB.Connection

Public rsusers As New ADODB.Recordset
Public rsSystem As New ADODB.Recordset
Public rsbrgy As New ADODB.Recordset
Public rsindex As New ADODB.Recordset
Public rsrenewal As New ADODB.Recordset
Public rsregistration As New ADODB.Recordset
Public rsarrears As New ADODB.Recordset


Public usernamelogin As String
Public in_outdate As String
Public u_timein As String
Public u_timeout As String
Public PW As String
Public copy As Integer
Public agebldg As String
Public yer As Integer

Public tbldgfee As Integer
Public tplumfee As Integer
Public telecfee As Integer
Public tmechfee As Integer
Public tSCfee As Integer
Public tfee As Integer

Public u As String
Public p As String

Public Const APP_VERSION = "Version 0.0.1"
Public Const CURRENT_BUILD = "4 February 2011"
Public Const PROGRAMMER_LY = "LYMARK CONTANTE"
Public Const APP_PROGRAM = "Annual Inspection Section"
Public Const EMAILADd = "ly.mark_contante08@yahoo.com.ph"
Public Const LANGUAGE_USED = "Visual Basic 6.0"




Public Function annual2010Dir() As String
    If Right$(App.Path, 1) = "/" Then
        annual2010Dir = App.Path
    Else
        annual2010Dir = App.Path & "/"
    End If
End Function

Sub dbconnect()
    Set annual2010 = New ADODB.Connection
    annual2010.Open "Provider=Microsoft.Jet.Oledb.4.0; data source =" & annual2010Dir & "\Database\dbAnnual.mdb; Persist Security Info=False;Jet OLEDB:Database Password=annual2010"

Set rsSystem = New ADODB.Recordset
rsSystem.Open "Select * from system", annual2010, adOpenStatic, adLockOptimistic

Set rsindex = New ADODB.Recordset
rsindex.Open "Select * from ind", annual2010, adOpenStatic, adLockOptimistic

Set rsregistration = New ADODB.Recordset
rsregistration.Open "Select * from newbldg", annual2010, adOpenStatic, adLockOptimistic

Set rsbrgy = New ADODB.Recordset
rsbrgy.Open "Select * from brgy", annual2010, adOpenStatic, adLockOptimistic

Set rsusers = New ADODB.Recordset
rsusers.Open "Select * from UN_PW", annual2010, adOpenStatic, adLockOptimistic

Set rsrenewal = New ADODB.Recordset
rsrenewal.Open "Select * from brenewal", annual2010, adOpenStatic, adLockOptimistic

Set rsarrears = New ADODB.Recordset
rsarrears.Open "Select * from arrears", annual2010, adOpenStatic, adLockOptimistic
End Sub

Public Function Generate_New_No(strFieldQuiz As String, strTableName As String) As Long

    Dim tempRs As New ADODB.Recordset
    Dim Con As New ADODB.Connection

        If Con.State = adStateOpen Then Con.Close
        Con.ConnectionString = "Provider=Microsoft.Jet.Oledb.4.0; data source =" & annual2010Dir & "\Database\dbAnnual.mdb; Persist Security Info=False;Jet OLEDB:Database Password=annual2010"

        Con.Open

    Dim Sql As String
    Sql = "Select Max([" & strFieldQuiz & "]) From " & strTableName
    With tempRs
        .ActiveConnection = Con
        .Source = Sql
        .Open
    End With
    
    On Error Resume Next
    
    Generate_New_No = tempRs(0) + 1


End Function

Public Function Generate_New_ID(strFieldQuiz As String, strTableName As String) As Long

    Dim tempRs As New ADODB.Recordset
    Dim Con As New ADODB.Connection

        If Con.State = adStateOpen Then Con.Close
        Con.ConnectionString = "Provider=Microsoft.Jet.Oledb.4.0; data source =" & libraryDir & "\Database\maindatabase.mdb; Persist Security Info=False;Jet OLEDB:Database Password=5markly5"

        Con.Open

    Dim Sql As String
    Sql = "Select Max([" & strFieldQuiz & "]) From " & strTableName
    With tempRs
        .ActiveConnection = Con
        .Source = Sql
        .Open
    End With

    Generate_New_ID = tempRs(0) + 1


End Function

Public Sub RefreshEntries(oForm As Form)
    'On Error Resume Next
    Dim oControls As Object
    For Each oControls In oForm.Controls
       If TypeOf oControls Is ComboBox Then
            If oControls.Style = 0 Then
               oControls.Text = ""
            Else
               oControls.ListIndex = -1
            End If
       ElseIf TypeOf oControls Is CheckBox Then
            If oControls.value = 1 Then
               oControls.value = 0
            Else
               oControls.value = 0
            End If
       ElseIf TypeOf oControls Is TextBox Then
            If TypeOf oControls Is TextBox Then
               oControls.Text = ""
            End If
       End If
       'End If
       
    Next
    
End Sub

Private Sub DisplayCapsState()
CapsState = GetKeyState(vbKeyCapital)
If CapsState = 1 Then
MsgBox "Caps Lock is On"
Else
MsgBox "The Caps Lock Is Off"
End If
End Sub

