Accessing a database through ADODB and editig/deleteing/filtering results

' Created by Ioannis on 16 May 2005

Option Explicit



Public myDB As New ADODB.Connection

' Connect to database (ADO) - Ioannis



Public currentID As Integer

' Create a variable to hold the current record's ID number, it is created
automatically for each record - Ioannis





Private Sub populate()

' Initiate a connection to the database

myDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _

& "Data Source=DB\Database.mdb"



'Setting up a recordset.. - Ioannis

Dim myRS As New ADODB.Recordset



'Setting up a second recordset for the customer type combobox

Dim MyRS2 As New ADODB.Recordset



'Query the database and return all the customers table - Ioannis

myRS.Open "SELECT Country.Country, Customers.*, CustomerType.CustomerType,
JobTitle.JobTitle FROM Country INNER JOIN (JobTitle INNER JOIN (CustomerType
INNER JOIN Customers ON CustomerType.ID = Customers.CustomerTypeID) ON
JobTitle.ID = Customers.JobTitleID) ON Country.ID = Customers.CountryID", myDB



' clear old results

customers_list.Clear



' place results in the list - Ioannis

While Not myRS.EOF

customers_list.AddItem myRS.Fields("name").Value

' customers_list.ItemData(customers_list.ListIndex) = myRS.Fields("name").ID





' go to next record in the results - Ioannis

myRS.MoveNext



Wend



lblrecordcount = customers_list.ListCount

myRS.Close

End Sub



Private Sub Command1_Click()

' edit a record in the database , save it - Ioannis



Call cmdUpdate

End Sub



Private Sub Command2_Click()

'Exit without making any changes to the database - Ioannis

myDB.Close

Unload frmcustomers

End Sub



Private Sub cmdUpdate()

'This command will update a customer that is selected





Dim myRS As New ADODB.Recordset



' open a recordset from the Customers' table - Ioannis

myRS.Open "SELECT Country.Country, Customers.*, CustomerType.CustomerType,
JobTitle.JobTitle FROM Country INNER JOIN (JobTitle INNER JOIN (CustomerType
INNER JOIN Customers ON CustomerType.ID = Customers.CustomerTypeID) ON
JobTitle.ID = Customers.JobTitleID) ON Country.ID = Customers.CountryID", myDB,
adOpenKeyset, adLockOptimistic



' find the record to update - Ioannis

myRS.Find "CustomerID=" & currentID



' change fields to update them - Ioannis



If IsNull(txtAddress.Text) Then myRS.Fields("BillingAddress").Value = "" Else
myRS.Fields("BillingAddress").Value = txtAddress.Text

If IsNull(txtcustomercode.Text) Then myRS!CustomerCode = "" Else
myRS.Fields("CustomerCode").Value = txtcustomercode.Text

If IsNull(txtcompany.Text) Then myRS!Name = "" Else myRS.Fields("Name").Value =
txtcompany.Text

If IsNull(txtfirstname.Text) Then myRS!ContactFirstName = "" Else
myRS.Fields("ContactFirstName").Value = txtfirstname.Text

If IsNull(txtlastname.Text) Then myRS!ContactLastName = "" Else
myRS.Fields("ContactLastName").Value = txtlastname.Text

If IsNull(txtcustomertype.Text) Then myRS!CustomerType = "" Else
myRS.Fields("customertype").Value = txtcustomertype.Text

If IsNull(txtcustomertitle.Text) Then myRS!ContactTitle = "" Else
myRS.Fields("ContactTitle").Value = txtcustomertitle.Text

If IsNull(txtcity.Text) Then myRS!City = "" Else myRS.Fields("City").Value =
txtcity.Text

If IsNull(txtstateprovince.Text) Then myRS!StateOrProvince = "" Else
myRS.Fields("StateOrProvince").Value = txtstateprovince.Text

If IsNull(txtpostalcode.Text) Then myRS!PostalCode = "" Else
myRS.Fields("PostalCode").Value = txtpostalcode.Text

If IsNull(txtcountry.Text) Then myRS!CountryName = "" Else
myRS.Fields("Country").Value = txtcountry.Text

If IsNull(txtphonenumber.Text) Then myRS!PhoneNumber = "" Else
myRS.Fields("PhoneNumber").Value = txtphonenumber.Text

If IsNull(txtfaxnumber.Text) Then myRS!FaxNumber = "" Else
myRS.Fields("FaxNumber").Value = txtfaxnumber.Text

If IsNull(txtemail.Text) Then myRS!Email = "" Else myRS.Fields("Email").Value =
txtemail.Text

If IsNull(txtjobtitle.Text) Then myRS!JobTitle = "" Else
myRS.Fields("JobTitle").Value = txtjobtitle.Text

If IsNull(txtdepartment.Text) Then myRS!Department = "" Else
myRS.Fields("Department").Value = txtdepartment.Text

If IsNull(txtvatnumber.Text) Then myRS!VATnumber = "" Else
myRS.Fields("VATnumber").Value = txtvatnumber.Text







' update and close the recordset , unload the form - Ioannis

myRS.Update

myRS.Close

myDB.Close



Call populate

End Sub



Private Sub Command3_Click()

' delete a record from the database



Dim answer As String

' confirm the user really wants to delete the record

answer = MsgBox("Are you sure you want to delete " & txtcompany.Text & "?",
vbYesNo, "Delete")



If answer = vbYes Then



' create a string variable that will hold an SQL statement

Dim mySQL As String



' Example: mySQL = "Delete * from address where name='Joe Blow' "

mySQL = "Delete * from Customers where name='" & txtcompany.Text & "'"



' execute the SQL command

myDB.Execute (mySQL)



' feedback for user

MsgBox txtcompany.Text & " was deleted from the database.", vbOKOnly, "Deleted"



' myRS.Close

myDB.Close





Call populate





'Select the first choise in the listbox

customers_list.ListIndex = 0

End If



End Sub



Private Sub customers_list_Click() 'Events when someone clicks on an item -
Ioannis



' check if something is selected in the list

If customers_list.ListIndex <> -1 Then



Dim myRS As New ADODB.Recordset 'Prepare a new recordset - Ioannis



' match the selected name to one in the database - Ioannis

myRS.Open "SELECT Country.Country, Customers.*, CustomerType.CustomerType,
JobTitle.JobTitle FROM Country INNER JOIN (JobTitle INNER JOIN (CustomerType
INNER JOIN Customers ON CustomerType.ID = Customers.CustomerTypeID) ON
JobTitle.ID = Customers.JobTitleID) ON Country.ID = Customers.CountryID where
name='" & customers_list.List(customers_list.ListIndex) &amp; "'", myDB



' load the record into the textboxes - Ioannis

If IsNull(myRS.Fields("BillingAddress").Value) Then txtAddress.Text = "" Else
txtAddress.Text = myRS.Fields("BillingAddress").Value

If IsNull(myRS!CustomerCode) Then txtcustomercode.Text = "" Else
txtcustomercode.Text = myRS.Fields("CustomerCode").Value

If IsNull(myRS!Name) Then txtcompany.Text = "" Else txtcompany.Text =
myRS.Fields("Name").Value

If IsNull(myRS!ContactFirstName) Then txtfirstname.Text = "" Else
txtfirstname.Text = myRS.Fields("ContactFirstName").Value

If IsNull(myRS!ContactLastName) Then txtlastname.Text = "" Else txtlastname.Text
= myRS.Fields("ContactLastName").Value

If IsNull(myRS!CustomerType) Then txtcustomertype.Text = "" Else
txtcustomertype.Text = myRS.Fields("customertype").Value

If IsNull(myRS!ContactTitle) Then txtcustomertitle.Text = "" Else
txtcustomertitle.Text = myRS.Fields("ContactTitle").Value

If IsNull(myRS!City) Then txtcity.Text = "" Else txtcity.Text =
myRS.Fields("City").Value

If IsNull(myRS!StateOrProvince) Then txtstateprovince.Text = "" Else
txtstateprovince.Text = myRS.Fields("StateOrProvince").Value

If IsNull(myRS!PostalCode) Then txtpostalcode.Text = "" Else txtpostalcode.Text
= myRS.Fields("PostalCode").Value

If IsNull(myRS!Country) Then txtcountry.Text = "" Else txtcountry.Text =
myRS.Fields("Country").Value

If IsNull(myRS!PhoneNumber) Then txtphonenumber.Text = "" Else
txtphonenumber.Text = myRS.Fields("PhoneNumber").Value

If IsNull(myRS!FaxNumber) Then txtfaxnumber.Text = "" Else txtfaxnumber.Text =
myRS.Fields("FaxNumber").Value

If IsNull(myRS!Email) Then txtemail.Text = "" Else txtemail.Text =
myRS.Fields("Email").Value

If IsNull(myRS!JobTitle) Then txtjobtitle.Text = "" Else txtjobtitle.Text =
myRS.Fields("JobTitle").Value

If IsNull(myRS!Department) Then txtdepartment.Text = "" Else txtdepartment.Text
= myRS.Fields("Department").Value

If IsNull(myRS!VATnumber) Then txtvatnumber.Text = "" Else txtvatnumber.Text =
myRS.Fields("VATnumber").Value



' save the ID in case we want to update - Ioannis

currentID = myRS.Fields("CustomerID").Value



'Close the recordset

myRS.Close



End If



End Sub



Private Sub Form_Load()

txtjobtitle.Enabled = False

txtcountry.Enabled = False

txtcustomertype.Enabled = False

Call populate







End Sub









'- Have a nice day