frm_search.frm

library management system that help in runnning a file

               Format          =   ""
               HaveTrueFalseNull=   0
               FirstDayOfWeek  =   0
               FirstWeekOfYear =   0
               LCID            =   1033
               SubFormatType   =   0
            EndProperty
         EndProperty
         BeginProperty Column06 
            DataField       =   "Deposite"
            Caption         =   "Deposite"
            BeginProperty DataFormat {6D835690-900B-11D0-9484-00A0C91110ED} 
               Type            =   0
               Format          =   ""
               HaveTrueFalseNull=   0
               FirstDayOfWeek  =   0
               FirstWeekOfYear =   0
               LCID            =   1033
               SubFormatType   =   0
            EndProperty
         EndProperty
         BeginProperty Column07 
            DataField       =   "Birthdate"
            Caption         =   "Birthday"
            BeginProperty DataFormat {6D835690-900B-11D0-9484-00A0C91110ED} 
               Type            =   0
               Format          =   ""
               HaveTrueFalseNull=   0
               FirstDayOfWeek  =   0
               FirstWeekOfYear =   0
               LCID            =   1033
               SubFormatType   =   0
            EndProperty
         EndProperty
         BeginProperty Column08 
            DataField       =   "Dojoin"
            Caption         =   "Join at"
            BeginProperty DataFormat {6D835690-900B-11D0-9484-00A0C91110ED} 
               Type            =   0
               Format          =   ""
               HaveTrueFalseNull=   0
               FirstDayOfWeek  =   0
               FirstWeekOfYear =   0
               LCID            =   1033
               SubFormatType   =   0
            EndProperty
         EndProperty
         BeginProperty Column09 
            DataField       =   "Doexpire"
            Caption         =   "Expire at"
            BeginProperty DataFormat {6D835690-900B-11D0-9484-00A0C91110ED} 
               Type            =   0
               Format          =   ""
               HaveTrueFalseNull=   0
               FirstDayOfWeek  =   0
               FirstWeekOfYear =   0
               LCID            =   1033
               SubFormatType   =   0
            EndProperty
         EndProperty
         BeginProperty Column10 
            DataField       =   "Sex"
            Caption         =   "Sex"
            BeginProperty DataFormat {6D835690-900B-11D0-9484-00A0C91110ED} 
               Type            =   0
               Format          =   ""
               HaveTrueFalseNull=   0
               FirstDayOfWeek  =   0
               FirstWeekOfYear =   0
               LCID            =   1033
               SubFormatType   =   0
            EndProperty
         EndProperty
         BeginProperty Column11 
            DataField       =   "Noted"
            Caption         =   "Note"
            BeginProperty DataFormat {6D835690-900B-11D0-9484-00A0C91110ED} 
               Type            =   0
               Format          =   ""
               HaveTrueFalseNull=   0
               FirstDayOfWeek  =   0
               FirstWeekOfYear =   0
               LCID            =   1033
               SubFormatType   =   0
            EndProperty
         EndProperty
         SplitCount      =   1
         BeginProperty Split0 
            BeginProperty Column00 
               ColumnWidth     =   824.882
            EndProperty
            BeginProperty Column01 
               ColumnWidth     =   1604.976
            EndProperty
            BeginProperty Column02 
               ColumnWidth     =   1620.284
            EndProperty
            BeginProperty Column03 
               ColumnWidth     =   3404.977
            EndProperty
            BeginProperty Column04 
               ColumnWidth     =   1665.071
            EndProperty
            BeginProperty Column05 
               ColumnWidth     =   2505.26
            EndProperty
            BeginProperty Column06 
               ColumnWidth     =   1170.142
            EndProperty
            BeginProperty Column07 
               ColumnWidth     =   1230.236
            EndProperty
            BeginProperty Column08 
               ColumnWidth     =   1184.882
            EndProperty
            BeginProperty Column09 
               ColumnWidth     =   1140.095
            EndProperty
            BeginProperty Column10 
               ColumnWidth     =   734.74
            EndProperty
            BeginProperty Column11 
               ColumnWidth     =   2429.858
            EndProperty
         EndProperty
      End
   End
   Begin VB.Image Image1 
      Height          =   480
      Left            =   120
      Top             =   0
      Width           =   495
   End
   Begin VB.Label lbl_status 
      BackStyle       =   0  'Transparent
      BeginProperty Font 
         Name            =   "MS Sans Serif"
         Size            =   9.75
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   375
      Left            =   600
      TabIndex        =   0
      Top             =   120
      Width           =   8895
   End
End
Attribute VB_Name = "Frm_search"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Dim fnd As String
Dim mflag As Boolean
Dim bflag As Boolean
Dim conn As ADODB.Connection
Dim MR As ADODB.Recordset
Dim BR As ADODB.Recordset

Private Sub cmb_bfield_click()
If (cmb_bfield.Text = "All") Then
txt_bvalue.Enabled = False
Else
txt_bvalue.Enabled = True
End If
lbl_status.Caption = " Search for book's Record field."
txt_bvalue.Text = ""
End Sub

Private Sub cmb_mfield_click()
If (cmb_mfield.Text = "All") Then
txt_mvalue.Enabled = False
Else
txt_mvalue.Enabled = True
End If
txt_mvalue.Text = ""
lbl_status.Caption = " Search for Member's Record field."
End Sub
Private Sub cmd_bsearch_Click()
On eror GoTo errlable:
'write code for validity
again:
bpbar.Value = 0
If (cmb_bfield.Text = "All" Or txt_bvalue.Text = "") Then
fnd = "select Author1,Author2,Author3,Avano,Bookid,Edition,ISBNNumber,Issno,Pages,Price,Publication,Subject,Title,Totalno from Book order by Bookid"
lbl_status.Caption = " Search for Book's Record field Alldata."
bpbar.Value = 30
ElseIf (cmb_bfield.Text = "Author") Then
fnd = "select Author1,Author2,Author3,Avano,Bookid,Edition,ISBNNumber,Issno,Pages,Price,Publication,Subject,Title,Totalno from Book where Author1 like'" & Trim(txt_bvalue.Text) & "%' or Author2 like'" & Trim(txt_bvalue.Text) & "%' or Author3 like'" & Trim(txt_bvalue.Text) & "%'"
lbl_status.Caption = " Search for Book's Record field Author."
bpbar.Value = 30
ElseIf (cmb_bfield.Text = "Price" Or cmb_bfield.Text = "Pages" Or cmb_bfield.Text = "Bookid") Then
    If IsNumeric(txt_bvalue.Text) Then
    fnd = "select Author1,Author2,Author3,Avano,Bookid,Edition,ISBNNumber,Issno,Pages,Price,Publication,Subject,Title,Totalno from Book where " & Trim(cmb_bfield) & " = " & Trim(txt_bvalue)
    lbl_status.Caption = " Search for Book's Record field " & Trim(cmb_bfield.Text) & " of book."
    bpbar.Value = 30
    Else
    txt_bvalue.Text = ""
    Exit Sub
    End If
    
Else
fnd = "select Author1,Author2,Author3,Avano,Bookid,Edition,ISBNNumber,Issno,Pages,Price,Publication,Subject,Title,Totalno from Book where " & Trim(cmb_bfield) & " like '" & Trim(txt_bvalue) & "%'"
lbl_status.Caption = " Search for Book's Record field " & Trim(cmb_bfield.Text) & " of book."
bpbar.Value = 30
End If
 If (bflag = False) Then
            BR.Open fnd, conn, adOpenStatic, adLockOptimistic
            bpbar.Value = 50
            bdatagrid.Visible = True
            Set bdatagrid.DataSource = BR
            bpbar.Value = 70
            bdatagrid.ReBind
            bflag = True
            bpbar.Value = 85
            Else
            bflag = False
            BR.Close
            GoTo again
            bpbar.Value = 90
              End If
bpbar.Value = 100
bpbar.Value = 0
Exit Sub
errlable:
bpbar.Value = 0
MsgBox Err.Description
End Sub

Private Sub Form_Load()
 On Error GoTo errlable
      If (view = 1) Then
     Me.Top = 50
     Me.Left = 50
     ElseIf (view = 2) Then
     Me.Top = 700
     Me.Left = (Screen.Width - Me.Width) / 2
     End If
Image1.Picture = mdi_start.ImageList1.ListImages(2).Picture
 Set conn = New ADODB.Connection
 conn.CursorLocation = adUseClient
 conn.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & App.Path & "\Database\Library.mdb;Jet OLEDB:Database Password=Library;"
Set BR = New ADODB.Recordset
Set MR = New ADODB.Recordset
 lbl_status.Caption = " Choose the options for Datamember,Field and values for search."
Exit Sub
errlable:
MsgBox Err.Number & "  " & Err.Description
End Sub

Private Sub Form_Unload(Cancel As Integer)
Unload Me
End Sub


Private Sub txt_msearch_Click()
'write a code validity
On Error GoTo errlable
again:
 mpbar.Value = 0
 lbl_status.Caption = " Search for Member's Record field " & Trim(cmb_mfield.Text) & " of Member."
If (cmb_mfield.Text = "All" Or txt_mvalue.Text = "") Then
 fnd = "select Address,Birthdate,Bookinhand,Deposite,Doexpire,Dojoin,Email,Fname,Lname,Memid,Noted,Phone,Sex from Member Order by Memid"
 lbl_status.Caption = " Search for Member's Record field Alldata."
mpbar.Value = 40
ElseIf (cmb_mfield.Text = "First name") Then
 fnd = "select Address,Birthdate,Bookinhand,Deposite,Doexpire,Dojoin,Email,Fname,Lname,Memid,Noted,Phone,Sex from Member where Fname like '" & Trim(txt_mvalue.Text) & "%'"
mpbar.Value = 40
ElseIf (cmb_mfield.Text = "Last name") Then
 fnd = "select Address,Birthdate,Bookinhand,Deposite,Doexpire,Dojoin,Email,Fname,Lname,Memid,Noted,Phone,Sex from Member where Lname like '" & Trim(txt_mvalue.Text) & "%'"
mpbar.Value = 40
ElseIf (cmb_mfield.Text = "Member id") Then
 fnd = "select Address,Birthdate,Bookinhand,Deposite,Doexpire,Dojoin,Email,Fname,Lname,Memid,Noted,Phone,Sex from Member where Memid=" & Trim(txt_mvalue.Text)
mpbar.Value = 40
ElseIf (cmb_mfield.Text = "Address") Then
 fnd = "select Address,Birthdate,Bookinhand,Deposite,Doexpire,Dojoin,Email,Fname,Lname,Memid,Noted,Phone,Sex from Member where Address like '" & Trim(txt_mvalue.Text) & "%'"
mpbar.Value = 40
ElseIf (cmb_mfield.Text = "Phone") Then
 fnd = "select Address,Birthdate,Bookinhand,Deposite,Doexpire,Dojoin,Email,Fname,Lname,Memid,Noted,Phone,Sex from Member where Phone like'" & Trim(txt_mvalue.Text) & "%'"
mpbar.Value = 40
ElseIf (cmb_mfield.Text = "Email") Then
 fnd = "select Address,Birthdate,Bookinhand,Deposite,Doexpire,Dojoin,Email,Fname,Lname,Memid,Noted,Phone,Sex from Member where Email like'" & Trim(txt_mvalue.Text) & "%'"
mpbar.Value = 40
ElseIf (cmb_mfield.Text = "Birth date") Then
 fnd = "select Address,Birthdate,Bookinhand,Deposite,Doexpire,Dojoin,Email,Fname,Lname,Memid,Noted,Phone,Sex from Member where Birthdate like'" & Trim(txt_mvalue.Text) & "%'"
mpbar.Value = 40
ElseIf (cmb_mfield.Text = "Date of join") Then
 fnd = "select Address,Birthdate,Bookinhand,Deposite,Doexpire,Dojoin,Email,Fname,Lname,Memid,Noted,Phone,Sex from Member where Dojoin like'" & Trim(txt_mvalue.Text) & "%'"
mpbar.Value = 40
End If
 If (mflag = False) Then
            MR.Open fnd, conn, adOpenStatic, adLockOptimistic
            mpbar.Value = 65
            mdatagrid.Visible = True
            Set mdatagrid.DataSource = MR
            mpbar.Value = 80
            mdatagrid.ReBind
            mflag = True
            mpbar.Value = 90
            Else
            mflag = False
            MR.Close
            GoTo again
              End If
mpbar.Value = 100
mpbar.Value = 0
Exit Sub
errlable:
mpbar.Value = 0
MsgBox Err.Number & "  " & Err.Description
End Sub