Attribute VB_Name = "DBConnection"
Option Explicit
Dim adoitemlist As ADODB.Recordset
Public Conn As New ADODB.Connection
Public recset As New ADODB.Recordset
Public Const hl = "{HOME}+{END}"
Public SQLTEXT As String

Public Function connect(ByVal sql As String) As String
On Error Resume Next
Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Database\VideoRentalDatabase.mdb;Persist Security Info=true;Jet OLEDB:Database Password=dbrentals"
Conn.Open

Conn.CursorLocation = adUseClient
recset.ActiveConnection = Conn
recset.CursorType = adOpenKeyset
recset.CursorLocation = adUseClient
recset.Open sql
End Function

Public Sub due()

Set recset = New ADODB.Recordset

On Error Resume Next
  'items in
   Set recset = New ADODB.Recordset
   recset.Open "SELECT * FROM videoinfos where status = 'IN'", Conn, adOpenStatic, adLockReadOnly
   
   frmMain.lblItemsIN.Caption = connect("select * from videoinfos where status = 'IN'")
   frmMain.lblItemsIN.Caption = recset.RecordCount
   Set recset = Nothing
   'items out
   Set recset = New ADODB.Recordset
   recset.Open "SELECT * FROM videoinfos where status = 'OUT'", Conn, adOpenStatic, adLockReadOnly
   frmMain.lblItemsOut.Caption = recset.RecordCount
   Set recset = Nothing
   'total items
   Set recset = New ADODB.Recordset
   recset.Open "SELECT * FROM videoinfos", Conn, adOpenStatic, adLockReadOnly
   frmMain.lblTotalItems.Caption = recset.RecordCount
   Set recset = Nothing
End Sub

Public Sub print_or(reqtext As String)

  Dim SQLTEXT As String
  SQLTEXT = "SELECT * FROM rentreturn WHERE ItemCode >= '" & reqtext & "';"
  Set recset = New ADODB.Recordset
  recset.Open SQLTEXT, Conn, adOpenStatic, adLockPessimistic
  Set DataReport.DataSource = recset
  DataReport.Show
End Sub

Public Sub SearchMovieTitle(reqtext As String)
    On Error Resume Next
    adoitemlist.Close
    Dim SQLTEXT As String
    SQLTEXT = "SELECT * FROM VideoInfos WHERE Left(Title," & Len(reqtext) & ")='" & reqtext & "';"
    Set adoitemlist = New ADODB.Recordset
    adoitemlist.Open SQLTEXT, Conn, adOpenStatic, adLockPessimistic
    adoitemlist.Find "Title  = " & "'" & reqtext & "'", , adSearchForward, 1
    Set frmVideoSearch.DataGrid1.DataSource = adoitemlist
    Call recnoIL
    If adoitemlist.RecordCount < 1 Then
     MsgBox "Movie Title does not exist", vbInformation, "Find"
     frmVideoSearch.txtMovieTitle.Text = ""
    End If
End Sub

Public Sub recnoIL()
  On Error Resume Next
 If recset.AbsolutePosition < 1 Then
    frmVideoSearch.lblRecordNoA.Caption = " Record " & recset.AbsolutePosition + 1 & " of " & recset.RecordCount
  Else
    frmVideoSearch.lblRecordNoA.Caption = " Record " & recset.AbsolutePosition & " of " & recset.RecordCount
  End If
End Sub


