1.) build first this objects and do not rename the names of the objects
-3 Textbox -
-3 Command Button
-1 Combo Box {put MALE & FEMALE on property list}
-1 Listview {it must be Report View ,4 Column or check it here in step 3 }
-1 Module
2.)Create a database
-"DB" name of the database
-create table "tblData"
-put this field
*ID - autonumber
*Fullname - text
*Age - number
*Gender - text
3.)After making all of this Save you projects in 1 folder also your database.so here's the view
of our database and project.
4.) Check if the Microsoft Activex Data Object is already selected. If you don't know what it is check here.
5.) Copy and Paste the code
****Go to module and paste this code .****
Public con As New ADODB.Connection
Public rs As New ADODB.Recordset
Public view As ListItem 'this is for listitem subitem
Public key As Integer
Sub conn() '<--- for easy database connection in every form
con.Provider = "microsoft.jet.oledb.4.0;" '<--- if your using mdb extension of your database
'<----"microsoft.ace.oledb.12.0;" this for accb extension
con.ConnectionString = App.Path & "/db.mdb"
con.Open
'MsgBox "CONNECTED" '<---- after successfull connecttion erase this message box
End Sub
***********************************************************************
****Go to form double click and clear the code and paste this in you form.****
Private Sub Command1_Click()
Dim sql As String 'declare string variable
sql = "Insert into tblData (fullname,age,gender) Values ('" & Text1.Text & "' , " & Text2.Text & " , '" & Combo1.Text & "') "
con.Execute sql
Call displayData 'for automatic display of data
MsgBox "successfully added!"
End Sub
Private Sub Command2_Click()
sql = "Update tblData set fullname = '" & Text1.Text & "' , age = " & Text2.Text & " , gender= '" & Combo1.Text & "' where id = " & key & ""
con.Execute sql
Call displayData 'for automatic display updated data
MsgBox " successfully updated!"
End Sub
Private Sub Command3_Click()
sql = "DELETE from tblData where id = " & key & ""
con.Execute sql
Call displayData 'automatic delete from display without refreshing
MsgBox "successfully deleted"
End Sub
Private Sub Form_Load()
Call conn '<--- requesting form for the connection
Call displayData '<----- calling the displayData function to display the records automatically when form load
End Sub
Function displayData()
Set rs = New ADODB.Recordset '<-- declaring new recordset
sql = "SELECT * from tblData" '<--- SELECT statement
rs.Open sql, con, adOpenStatic, adLockOptimistic '<--- opening recordset ,connection source
ListView1.ListItems.Clear
If rs.RecordCount > 0 Then 'checking if the record is greater than zero
rs.MoveFirst
Do While Not rs.EOF 'looping the data until goes to end of file
Set view = ListView1.ListItems.Add(, , rs.Fields(0)) 'this is number of fields
view.SubItems(1) = rs!Fullname 'or you can do it like this
view.SubItems(2) = rs.Fields(2)
view.SubItems(3) = rs.Fields(3)
rs.MoveNext
Loop
End If
End Function
Private Sub ListView1_DblClick()
Set rs = New ADODB.Recordset
sql = "SELECT * from tblData where id = " & ListView1.SelectedItem & ""
rs.Open sql, con, adOpenStatic, adLockOptimistic
key = rs.Fields(0)
Text1 = rs.Fields(1) 'you can do like this or
Text2 = rs!Age 'you can do it like this
Combo1 = rs.Fields(3)
End Sub
Private Sub Text3_Change()
Call searchh
End Sub
Function searchh()
Set rs = New ADODB.Recordset
sql = "SELECT * from tblData where fullname like '%" & Text3.Text & "%'" '
rs.Open sql, con, adOpenStatic, adLockOptimistic
ListView1.ListItems.Clear
If rs.RecordCount > 0 Then
rs.MoveFirst
Do While Not rs.EOF
Set view = ListView1.ListItems.Add(, , rs.Fields(0))
view.SubItems(1) = rs!Fullname
view.SubItems(2) = rs.Fields(2)
view.SubItems(3) = rs.Fields(3)
rs.MoveNext
Loop
End If
End Function
***********************************************************************
6.) RUN the program..
Enjoy :)
No comments:
Post a Comment