Simple Maintenance

FORM1 CODE:


Private Sub S_LOAD_FLEX()

Dim strsql As String
Dim rs_load As New ADODB.Recordset
Dim a As Integer

a = 1

S_SET_FLEX

strsql = "select * from book_tab"

With rs_load
.Open strsql, conn

If .EOF = False Then

Do While Not .EOF

flxBook.TextMatrix(a, 1) = !ISBN
flxBook.TextMatrix(a, 2) = !bookdesc
flxBook.TextMatrix(a, 3) = !bkpub
flxBook.TextMatrix(a, 4) = !bkaut
flxBook.Rows = flxBook.Rows + 1
a = a + 1

.MoveNext
Loop
.Close
End If
End With

If flxBook.Rows > 2 Then
flxBook.Rows = flxBook.Rows - 1
End If
End Sub

Private Sub S_SET_FLEX()

With flxBook
.Clear
.Cols = 5
.Rows = 2
.AllowUserResizing = flexResizeBoth
.SelectionMode = flexSelectionByColumn
.ColWidth(0) = 200
.ColWidth(1) = 1500
.TextMatrix(0, 1) = "ISBN"
.ColWidth(2) = 1500
.TextMatrix(0, 2) = "BOOK NAME"
.ColWidth(3) = 1500
.TextMatrix(0, 3) = "PUBLISHER"
.ColWidth(4) = 1500
.TextMatrix(0, 4) = "AUTHOR"

End With




End Sub



Private Sub cmdAdd_Click()
cmdAdd.Visible = False
cmdSave.Visible = True

End Sub

Private Sub cmdDelete_Click()
Dim strsql As String
Dim rs_del As New ADODB.Recordset
Dim ans As Integer

ans = MsgBox("Are you sure you want to delete this record?", vbYesNo)

If ans = vbYes Then
strsql = " delete from book_tab" & _
        " where isbn ='" & txtISBN & "'"
       
        rs_del.Open strsql, conn
        MsgBox "record deleted"
       
        S_LOAD_FLEX
       
        CLEAR_ALL_TXTBOX Me
       
        End If
       

End Sub

Private Sub cmdEdit_Click()
cmdEdit.Visible = False
cmdEsave.Visible = True

End Sub



Private Sub cmdEsave_Click()
Dim strsql As String
Dim rs_save As New ADODB.Recordset

strsql = "update book_tab set " & _
        "bookdesc = '" & txtBkName & "'," & _
        "bkpub = '" & txtPub & "'," & _
        "bkaut = '" & txtAut & "'" & _
        " where isbn = '" & txtISBN & "'"
       
        rs_save.Open strsql, conn
       
        MsgBox "Record Updated"
       
        S_LOAD_FLEX
       
        cmdEdit.Visible = True
       
        CLEAR_ALL_TXTBOX Me
End Sub

Private Sub cmdExit_Click()
End
End Sub

Private Sub cmdSave_Click()
Dim strsql As String
Dim rs_add As New ADODB.Recordset

strsql = "insert into book_tab " & _
        "(isbn,bookdesc,bkpub,bkaut) " & _
        "values(" & _
        "'" & txtISBN & "'," & _
        "'" & txtBkName & "'," & _
        "'" & txtPub & "'," & _
        "'" & txtAut & "') "
       
        rs_add.Open strsql, conn
       
        MsgBox "Record Added"
       
        S_LOAD_FLEX
       
        cmdAdd.Visible = True
       
        CLEAR_ALL_TXTBOX Me
       
End Sub

Private Sub flxBook_Click()

txtISBN = flxBook.TextMatrix(flxBook.Row, 1)
txtBkName = flxBook.TextMatrix(flxBook.Row, 2)
txtPub = flxBook.TextMatrix(flxBook.Row, 3)
txtAut = flxBook.TextMatrix(flxBook.Row, 4)


End Sub

Private Sub Form_Load()
S_LOAD_FLEX
End Sub


Module code(connection String)

Public conn As New ADODB.Connection

Public Sub main()
Set conn = New Connection
conn.ConnectionString = "Provider = MSDASQL.1; Data Source = Book_cn"

conn.Open
Form1.Show

End Sub

Public Sub CLEAR_ALL_TXTBOX(frm As Object)

Dim obj As Object

For Each obj In frm
    If TypeOf obj Is TextBox Then
    obj.Text = " "
    End If
    Next obj
   


End Sub




Note:
here's a simple vb program that can add, update and delete record. 
Provided with code, you just have to copy and paste it in your code window then provide the right interface and object names for you to be able to run it smoothly. 
as for the database, you need the MyODBC 3.51 driver and you have to create the fields on SQL table. field names are given, make sure that you entered the right field name to avoid errors while running the program. just refer on the pic. above for database setting. 
btw this program doesn't include restrictions and validations on it. 


questions? feel free to pm or mail me:


mail: antisocialnoob@gmail.com
YM: alucardallan@yahoo.com