How to Clear/Reset an ADODC recordset
Posted on 2003-10-31
I am having some difficulty here with an ADODC control on my VB6 form. I have a couple of textboxex and I can perform a search on my database using some of these textboxes. My First Search result is perfect. If I have more than one record it returns all the found set and displays the ADODC data control and I can browse through the records that match my search criteria.
Next I have another button that clears all textboxes on that form so that I can perform another search. The problem arises there. I enter my search criteria and then click on search. The results have nothing to do with my search fields. It always displays the found set from the first search when I originally launched the program.
Therefore I tried to do a ADODC.RECORDSET.CLOSE This works for 2 searches, on the third search I get the error message, Cannot perform operation when object is closed.
If I comment out the Recordset.close I get the following message after a while of doing new searches even if I get the wrong answers:
The changes you requested to the table were not successful because they would create duplicate values in the index, primary key or relationship...
I am not performing any changes or applying any changes. I am just clicking on the ADODC Data Control buttons.
The Lock type is Optimistic.
I would like to post my code but I use a lot of modules and a lot of forms so I think that it might get messy. However I am going to post how I bind and unbind the control to the ADODC data control.
I think that I just need to reset the recordset in the ADODC each time I perform a search.
Your help would be greatly appreciated.
Public Sub ActivateControl()
FormEmployee.AdodcEmployee.Visible = True
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=X:\Ipecac\Main.mdb;Persist Security Info=False;Jet OLEDB:Database Password=sesco"
.RecordSource = SearchString
Set FormEmployee.TextPrenom.DataSource = FormEmployee.AdodcEmployee
FormEmployee.TextPrenom.DataField = "FirstName"
Set FormEmployee.TextNom.DataSource = FormEmployee.AdodcEmployee
FormEmployee.TextNom.DataField = "LastName"
Set FormEmployee.TextAdresse.DataSource = FormEmployee.AdodcEmployee
FormEmployee.TextAdresse.DataField = "Address"
Set FormEmployee.TextVille.DataSource = FormEmployee.AdodcEmployee
FormEmployee.TextVille.DataField = "Ville"
Set FormEmployee.TextCodePostale.DataSource = FormEmployee.AdodcEmployee
FormEmployee.TextCodePostale.DataField = "CodePostal"
Set FormEmployee.TextDepartement.DataSource = FormEmployee.AdodcEmployee
FormEmployee.TextDepartement.DataField = "Department"
Set FormEmployee.TextDateEmbauche.DataSource = FormEmployee.AdodcEmployee
FormEmployee.TextDateEmbauche.DataField = "DateEmployed"
Set FormEmployee.TextNumeroEmploye.DataSource = FormEmployee.AdodcEmployee
FormEmployee.TextNumeroEmploye.DataField = "EmployeeNumber"
Set FormEmployee.TextSalaire.DataSource = FormEmployee.AdodcEmployee
FormEmployee.TextSalaire.DataField = "Salary"
Set FormEmployee.TextTelephone.DataSource = FormEmployee.AdodcEmployee
FormEmployee.TextTelephone.DataField = "Telephone"
Set FormEmployee.TextAutreNumero.DataSource = FormEmployee.AdodcEmployee
FormEmployee.TextAutreNumero.DataField = "AutreNumero"
Set FormEmployee.TextDateDeNaissance.DataSource = FormEmployee.AdodcEmployee
FormEmployee.TextDateDeNaissance.DataField = "DOB"
Set FormEmployee.TextEmail.DataSource = FormEmployee.AdodcEmployee
FormEmployee.TextEmail.DataField = "Email"
Set FormEmployee.TextAssuranceSociale.DataSource = FormEmployee.AdodcEmployee
FormEmployee.TextAssuranceSociale.DataField = "SIN"
Set FormEmployee.RichTextBoxNotes.DataSource = FormEmployee.AdodcEmployee
FormEmployee.RichTextBoxNotes.DataField = "Notes"
Set FormEmployee.CheckUtilisateur.DataSource = FormEmployee.AdodcEmployee
FormEmployee.CheckUtilisateur.DataField = "Utilisateur"
'Set FormEmployee.PicturePhoto.DataSource = FormEmployee.AdodcEmployee
'FormEmployee.PicturePhoto.Picture = LoadPicture(Rs.Fields("Picture").Value)
Public Sub DeactivateControl()
FormEmployee.AdodcEmployee.Visible = False
Set FormEmployee.TextPrenom.DataSource = Nothing
Set FormEmployee.TextNom.DataSource = Nothing
Set FormEmployee.TextAdresse.DataSource = Nothing
Set FormEmployee.TextVille.DataSource = Nothing
Set FormEmployee.TextCodePostale.DataSource = Nothing
Set FormEmployee.TextDepartement.DataSource = Nothing
Set FormEmployee.TextDateEmbauche.DataSource = Nothing
Set FormEmployee.TextNumeroEmploye.DataSource = Nothing
Set FormEmployee.TextSalaire.DataSource = Nothing
Set FormEmployee.TextTelephone.DataSource = Nothing
Set FormEmployee.TextAutreNumero.DataSource = Nothing
Set FormEmployee.TextDateDeNaissance.DataSource = Nothing
Set FormEmployee.TextEmail.DataSource = Nothing
Set FormEmployee.TextAssuranceSociale.DataSource = Nothing
Set FormEmployee.RichTextBoxNotes.DataSource = Nothing
Set FormEmployee.CheckUtilisateur.DataSource = Nothing
' Set FormEmployee.AdodcEmployee.Recordset.ActiveConnection = Nothing
' Set FormEmployee.AdodcEmployee.Recordset = Nothing