Updating an ACCESS database

1. Updating an ACCESS database • Three Actions • Add a new record to a table • Change information in an existing record in a table • Delete an existing record from a table

2. Adding a New Record to an ACCESS Database • Appends a blank record to the end of the table • datControl.Recordset.Addnew • Use the fields option to insert a value into each (required) field on the blank record • datControl.Recordset.Fields(“field name”) = txtbox.text • datControl.Recordset.Fields(“field name”) = variable • Update the database • datControl.Recordset.Update

3. AddNew • You may want to do a FindFirst to verify that the “new record” does not already exist • Use the fields options to give all required fields a value • strLookup = txtSSN.text strFind = “[ssn] = ‘” & strLookup & “’” datStudent.Recordset.FindFirst strFind If datStudent.Recordset.NoMatch = False then datStudent.Recordset.Addnew datStudent.Recordset.Fields(“Major”) = txtMajor.text datStudent.Recordset.Fields(“SocialSecurity”) = txtSSN.text datStudent.Recordset.Fields(“Name”) = txtName.text datStudent.recordset.Update Else MsgBox “already on file” End If

4. Practice Problem - ADD • Assume the user is entering data on the screen below txtName Customer Name txtStreet Customer Street Customer City picCity Customer State picState txtZip Customer ZIP

5. Practice Problem - ADD • Assume the user is entering data on the screen on the previous slide • The zip code is a keyed in txtZip • If the Zip code in txtZip is in the zip code table • The city/state should be printed in picCity and picState picture boxes • If the zip code in txtZIP is not in the zip code table • The user should be prompted to enter a city and state in input boxes. Then this information should be added to the zip code table [consisting of fields of Zip, City, State] and displayed in the picture boxes picCity and picState

6. Practice Problem - ADD On the lost focus event for txtZip: Dim strFind as String Dim response as Integer Dim city as String Dim state as String StrFind = “[Zip] = ‘” & txtZip.text & “’” datZip.recordset.findfirst strFind If datZip.recordset.nomatch = true then response =Msgbox (“zip not found – is it correct?”, vbYesNo) If response = 6 then ‘response = 6 means the user clicked on YES city = inputbox (“enter the city”) state = inputbox (“enter the state”) datZip.recordset.addnew datZip.recordset.fields(“City”) = city datZip.recordset.fields(“State”) = state datZip.recordset.fields(“Zip”) = txtZip.text datZip.recordset.update lblCity.caption = city lblState.caption = state else txtZip.setfocus ‘ resets the cursor on the zip textbox for correction endif else lblCity.caption = datZip.recordset.fields(“City”) lblState.caption = datZip.recordset.fields(“State”) endif

7. Update • Find the desired record strFind = “[CourseID] = ‘” & txtCourseID.text & “’” datClass.Recordset.FindFirst strFind • Use the field option to change the desired field and then update the database If datClass.Recordset.NoMatch = False then datClass.Recordset.Fields(“enrolled”).value = datClass.Recordset.Fields(“enrolled”).value + 1 datClass.Recordset.Update End If • The last record read is the record which is updated • I use the value option whenver I want to do mathematics

8. Update – example 2 • Find the desired record • strFind = “[SSN] = ‘” & txtSSN.text & “’” datStudent.Recordset.FindFirst strFind • Use the field option to change the desired field and then update the database If datStudent.Recordset.NoMatch = False then datStudent.Recordset.Fields(“Name”) = txtName.text datClass.Recordset.Update End If • The last record read is the record which is updated

9. Delete • Generally, before I delete a record I display some information and ask for the delete to be verified • There is usually a FindFirst or FindNext before the delete • The actual delete statement is then merely datTable.Recordset.Delete • For example: • DatSudent.Recordset.Delete will delete the record which was read last from the student table

10. Delete Example • Print the names of all students with enrolled in txtCourseID and txtSection. Delete them from this class – delete from the student class table strFind =“[CourseID] ='” &txtCourseID.text& “'and [Section] = '” &txtSection.text& “'” datStuClass.Recordset.FindFirst strFind Do While datStuClass.Recordset.NoMatch = False strFind2 = “[SSN] = ‘” & datStuClass.recordset.fields(“SSN”) & “’” datStudent.Recordset.FindFirst strFind2 If datStudent.Recordset.NoMatch = False then picNames.print datStudent.Recordset.Fields(“Name”) End If datStuClass.Recordset.Delete datStuClass.Recordset.FindNext strFind Loop

11. Practice Problem 1 • Assume that txtSSN.text contains a social security number • Write a routine which will delete all records on the student class file for this student • Display the CourseID and Section from the student class record which you are deleting

12. Answer strFind2 =“[SSN] ='” & txtSSN.text & “’” datStuClass.Recordset.FindFirst strFind2 Do While datStuClass.Recordset.NoMatch = False picClasses.print datStuClass.Recordset.Fields(“CourseID”); tab(12); datStuClass.Recordset.Fields(“Section”) datStuClass.Recordset.Delete datStuClass.Recordset.FindNext strFind2 Loop

13. Practice Problem 2 • Re-do practice problem 1 but also: • Read the class file and update (decrement by 1) the number enrolled

14. Answer strFind2 =“[SSN] ='” & txtSSN.text & “’” datStuClass.Recordset.FindFirst strFind2 Do While datStuClass.Recordset.NoMatch = False picClasses.print datStuClass.Recordset.Fields(“CourseID”); tab(12); datStuClass.Recordset.Fields(“Section”) strFind = “[CourseID] ='” & datStuClass.Recordset.Fields(“CourseID”) & “'and [Section] = '” &txtSection.text& “'” datClass.Recordset.FindFirst StrFind If datClass.Recordset.NoMatch = False then datClass.recordset.Edit datClass.Recordset.Fields(“enrolled”).value = datClass.Recordset.Fields(“enrolled”).value + 1 datClass.Recordset.Update End If datStuClass.Recordset.Delete datStuClass.Recordset.FindNext strFind2 Loop Refresher: An IF statement was used to check for nomatch on the class file as it was read by PK; A DO LOOP was used with the student class file as it was NOT read by PK

15. Practice Problem 3 • Assume the user has entered a social security number in txtSSN • And a Course ID and Section in txtCourseID and txtSection • Write the VB that will: • Check that the SSN is found on the Student Table • Check that the Course ID/Section are found on the Class table • Add 1 to the number enrolled in the CourseID/Section and update the class table • Add a new record to the student class table for the SSN/CourseID/Section in the textboxes.