**Simple VB Knowledge Required!**
Alright the first thing we need to do is open a DB where our tables will be stored. We need to declare a Variable to Hold the DB first.
CODE
Dim dbMyDB As Database
This gives you a variable/object that can hold a reference to your database. To open a simple Access database named "Shinigamiexample.mdb" type the following..
CODE
Set dbMyDB = OpenDatabase("Shinigamiexample.mdb")
Normally i would suggest typing out the complete path to the database, but if your directory is where the database is at, this will work.
Alright so we got a Database Wohoo! Now This isnt going to give us any Information(Data) so we need to open a Table. Were going to open a Single Table, You could do more but we wont get into that..
Lets Use VB's Object Named Record Set To Hold our Table.
CODE
Dim rsMyRS As RecordSet
Set rsMyRS = dbMyDB.OpenRecordSet("MyTable", dbOpenDynaset)
Yeay! we delcared a recordset Variable and used Openrecordset to get a Dynaset Type Table. This is not the only way to open a Record set, And online tutorials(other then this) and books(ect) can give you more iinformation. For the rest of this tutorial we will use the Dynaset Mode.
Ok we opend a Table, now lets access some data on it. The RecordSet object lets us move
in some records using Movefirst, Movenext, Move Last, Move previous.
Ok to get this tutorial working, lets make a DB, Make sure you have acess, And call it "Shinigamiexample.mdb" with a table named "ExampleTable" in it. Lets have the Fields,
"ID" (counter Type) Set this field to be the primary key. Then the field, "Name"(Text type) and field "Number of Days Working"(text type), Lets add some records to this Table and put a list box on a form named "Actions"(for previous wrong doings and what not)
CODE
Dim dbMyDB As Database
Dim rsMyRS As RecordSet
Private Sub Form_Load()
Set dbMyDB = OpenDatabase("Shinigamiexample.mdb")
Set rsMyRS = dbMyDB.OpenRecordSet("ExampleTable", dbOpenDynaset)
If Not rsMyRS.EOF Then rsMyRS.MoveFirst
Do While Not rsMyRS.EOF
lstRecords.AddItem rsMyRS!Name
lstRecords.ItemData(lstRecords.NewIndex) = rsMyRS!ID
rsMyRS.MoveNext
Loop
End Sub
Alright the code should make the listbox fill up with records when the form loads. I added some new concepts in this part of the tutorial, we talked earlier about opening a table, the line "If not rsMyRS.EOF then rsMyRS.M oveFirst" Tells the DB to move the first record just incase there are not any records. EOF is a 'boolean property' that is true if your selected record is the last, or if there are no records in the Recordset.
Then we make the program add the "Name" field of all records to the list box by adding the current records field "Name" and moving to the next record. You ask for a field of a RecordSet by putting a ! between the name of the RecordSet object and the name of the field. The while loop checks to see if there are more records to add.
Alright, Hopefully you know that the reason the value of field "ID" is in the list box for the ItemData property is so that we would know the primary key for the records, so we could search for one =D
Put a text box somewhere on the form and call it "DaysWorked". Then copy the following code to the project.
CODE
Private Sub lstRecords_Click()
rsMyRS.FindFirst "ID=" & Str(lstRecords.ItemData(lstRecords.ListIndex))
daysworked.Text = rsMyRS!Number Of Days Working
End Sub
This will display the phone number of the selected person when clicking in the list box. It uses the FindFirst method of the RecordSet object. This takes a string parameter that is like what is after WHERE in a SQL expression. You state the field that you want to search in (here "ID"), then the evaluation criteria (here "=") and last the value to search for (here the ItemData of the selected item in the list box). Phew, Hope i Didnt loose you there, as we port over to the more complicated Bit =O.
So what we did was to search for the record with the "ID" field value that was the same as the ItemData property of the selected item in the list box. Then we show the value of the "Number Of Days Worked" field in the text box.
Ok so you most likly want to update one of the values of whatever field when doing Databases. You can do this with Edit and Update, we will attempt to change the value of "Number of Days Worked" by editing the text in its box and clicking a button =)
Put a command button on the form and name it "cmdUpdate". Then copy the following code to the project.
CODE
Private Sub cmdUpdate_Click()
rsMyRS.Edit
rsMyRS!Number Of Days Worked = daysworked.Text
rsMyRS.Update
End Sub
Hmm well that was easy, We changed the Number of Days worked for that employee (Hope you didnt Cheat him out of money >=O). now when we type
CODE
rsMyRS!Number Of Days Worked = daysworked.Txt
We will replace the value of "Number of Days wored" box with the value that is in the Text box.
Ok deleting records is really simple, to delete the current record we will use the Delete method of the RecordSet obect.Make another command button named "CmdDelete" and the following coe will do all the work of deleting our selected employee ( YOUR FIREED!!!!111oneone!...sorry i had to..)
CODE
Private Sub cmdDelete_Click()
rsMyRS.Delete
lstRecords.RemoveItem lstRecords.ListIndex
End Sub
Ok Adding somone is just like updating, but we use Addnew. Lets add anotherrrr Command button to the program. Lets call it "Cmdnew". Now once again add the code for adding a new record.
CODE
Private Sub cmdNew_Click()
rsMyRS.AddNew
rsMyRS!Name = "A New Employee"
lstRecords.AddItem rsMyRS!Name
lstRecords.ItemData(lstRecords.NewIndex) = rsMyRS!ID
rsMyRS!Number Of Days Worked = "0"
rsMyRS.Update
End Sub
And Boom, this wraps up my tutorial. I hope i have left you with enough knowledge to mess around and maybe create a DB for use in your Simple Life while i go Jump in the pool of my mansion...(joke). Hope you found this guide usefull.
**Shinigami**
DIC's Newest Forumer!!
