How to Clear/Reset an ADODC recordset

Hello,

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.

Thanks,

Mark

*****************************

Public Sub ActivateControl()
   
    FormEmployee.AdodcEmployee.Visible = True
   
   
    With FormEmployee.AdodcEmployee
    .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=X:\Ipecac\Main.mdb;Persist Security Info=False;Jet OLEDB:Database Password=sesco"
    .RecordSource = SearchString
    End With
   
    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)
    'LoadPicture(Rs.Fields("Picture").Value)
   
   
End Sub

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
   
End Sub

******************************************************
markrhymanAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
leonstrykerConnect With a Mentor Commented:
You need to have a button named cmdFirst

You can change the names based on the ones you give.  Just place the Navigate Action.MoveFirst in the Click event.
0
 
leonstrykerCommented:
Try using the Filter method of the Recordset to do your search

In the code behind the Button you should add:

something like this
FormEmployee.AdodcEmployee.Filter = "([LastName] = '')"                                                    <----- This will reset the filter to all

FormEmployee.AdodcEmployee.Filter = "([LastName] = '" & Trim(txtLastName.Text) & "')"        <----- This should return only the record with the name entered
0
 
markrhymanAuthor Commented:
Thanks for the suggestion but I do not have that method and I get the error message when trying to use what you wrote.  I get Method or Data Member not found.  FILTER does not even appear in the list of data members for ADODC.

Here is the code behind the search button

*******************************************

            strSQL = SearchString()
           
            Set Rs = Cn.Execute(strSQL)
           
            FormEmployee.AdodcEmployee.Filter = "([LastName] = '" & Trim(TextNom.Text) & "')"
           
            If Not (Rs.BOF And Rs.EOF) Then
           
                Call EnableTextBoxes(FormEmployee)
               
                'Display Related Employee Fields for a search
                Call DisplayEmployeeFields
               
                'Display all fields and their appropriate labels
                Call RetourChampNormal
               
                If Rs.Fields("Utilisateur").Value Then
               
                    FormEmployee.CheckUtilisateur.Value = 1
                   
                Else
               
                    FormEmployee.CheckUtilisateur.Value = 0
                   
                End If
           
                FormEmployee.RichTextBoxNotes.TextRTF = Rs.Fields("Notes").Value
               
                FormEmployee.PicturePhoto.Picture = LoadPicture(Rs.Fields("Picture").Value)
           
                FormEmployee.CommandEmployee.Enabled = True
               
                FormEmployee.CommandEmployeeRecherche.Enabled = False
            Else
           
                MsgBox "Aucun Employ√© Trouv√©"
                Call ClearAllText(FormEmployee)
                FormEmployee.TextPrenom.SetFocus
                   
            End If

*********************

Public Function SearchString() As String

    SearchString = "SELECT * FROM EmployeesQuery WHERE Employees.LastName = '" _
                    & FormEmployee.TextNom.Text & "' OR Employees.FirstName = '" _
                    & FormEmployee.TextPrenom.Text & "' OR Employees.Department = '" _
                    & FormEmployee.TextDepartement.Text & "' OR Employees.EmployeeNumber = '" _
                    & FormEmployee.TextNumeroEmploye.Text & "';"

End Function
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
leonstrykerCommented:
Would you be willing to switch from ADODC to regular ADO  and a grid (I would take you throught the steps to do so)?

Leon
0
 
markrhymanAuthor Commented:
I am actually willing to do almost anything.  What would be different though to use ADO instead?  Just before we do the switch I just want to mention that I read on Microsoft that this was caused when not using the acmdTable in the command type of the ADODC data control.  I switched to AcmdTable and now I get systax error in FROM clause which is the from clause above.  I thus replaced the SELECT * FROM EmployeesQuery to SELECT * from Employees since Employees is the table name but I still get the SYNTAX error...

Anyway if you say ado is better let's go, I'm Ready to roll :)

0
 
leonstrykerCommented:
Well basically all you have to do is:

1. Make a reference to ADO
2. Place a msgrid on your form
3. Create and open a ADODB.Connection
4. Create and open a ADODB.Recordset
5. Bind the the recordset to the grid
6. Use the text boxes to navigate the data

I will go through each step in the posts
0
 
leonstrykerCommented:

1. Go to Projet/Properties and click on Microsoft ActiveX DataObjects 2.5 (or higher) library
2. Go to Projet/Controls and click on Microsoft DataGrid Control

3. Declare as a Global level variable: Public cnConn as ADODB.Connection

What kind of database you are using? That will determine you connection string
0
 
markrhymanAuthor Commented:
I am a bit Puzzled.  I did put a grid on my form.   The grid looks like a sort of Spreadsheet right?  How you want me to navigate my records using this grid?
0
 
markrhymanAuthor Commented:
I am using MS access right now and I do have globals already like:

Global Rs As ADODB.Recordset
Global Cn As ADODB.Connection

    Set Cn = New ADODB.Connection
    Set Rs = New ADODB.Recordset
0
 
leonstrykerCommented:
No the grid is going to display your information.  You can use the same one you had before.  It is just easier for me to talk to something new, since I do not see you form.
0
 
markrhymanAuthor Commented:
To Bind the Grid to the recordset I would use something like DataGrid1.RecordSource = Recordset?

I cannot bind the Grid.
0
 
leonstrykerCommented:
Your connection string should look something like this:

 strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & strFilePath & ";"

    With Cn
        .CursorLocation = adUseClient
        .Open strConn
    End With
0
 
leonstrykerCommented:
I tshould be

Set DataGrid1.DataSource = Recordset?
0
 
leonstrykerCommented:
Or more like

Set Rs = New ADODB.Recordset

Rs.Open strSQl, Cn, adOpenKeyset, adLockOptimistic
Set DataGrid1.DataSource = Rs
0
 
markrhymanAuthor Commented:
The connection is as you said

   
    ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=X:\Ipecac\Main.mdb;Jet OLEDB:Database Password=sesco"
   
    With Cn
        .CursorLocation = adUseClient
        .Open ConnStr
    End With


And This

Set FormEmployee.DataGrid1.DataSource = Rs

Does Work.  I get all my information in the grid pertaining to the Search Criteria.





0
 
markrhymanAuthor Commented:
Now my question is about the same as the beginning.... When I get multiple records back from my search... How can I navigate through them using some sort of Data Control so that when I click Next, Then theTextBoxes changes to the Next Record in the found Set and when I click back the same happens?
0
 
leonstrykerCommented:
Great, now to do the searches, how many text boxes do you have to search?
0
 
markrhymanAuthor Commented:
I have 4 textboxes that I can perform a search  on a TOTAL of 15.

First Name

Last Name

Department

And Employee Number

All form part of my search Independantly.  I use SQL with OR to get the results
0
 
leonstrykerCommented:
Well you can bind the text boxes to the recordset as well

txtFirstName.DataSource = Rs
txtFirstName.DataField = "FirstName"

Then you can place a buttons for navigation and code them something like:

Private Sub cmdFirst_Click()
    Navigate Action.MoveFirst
End Sub

Private Function Navigate(Action As Integer) As Boolean
    Navigate = True
    On Error Resume Next
    With rsCmdMaster
        If .EditMode > adEditNone Then RecCheck
        Select Case Action
            Case 1  '/ Next
                .MoveNext
                If .EOF Then
                    .MovePrevious
                End If
            Case 2  '/ Previous
                .MovePrevious
                If .BOF Then
                    .MoveNext
                End If
            Case 3  '/ First
                .MoveFirst
            Case 4  '/ Last
                .MoveLast
        End Select
        lblRecCt = .AbsolutePosition & " OF " & .RecordCount
    End With
EXIT_FUNCTION:
    Exit Function
ERROR_FUNCTION:
    Navigate = False
    GoTo EXIT_FUNCTION
End Function
0
 
leonstrykerCommented:
For searches you can go on the change event or use a button and you either a find or filter method

Rs.Filter = "([LastName] = '')"                              l
Rs.Filter = "([LastName] = '" & Trim(txtLastName.Text) & "')"  
0
 
markrhymanAuthor Commented:
I think it looks Pretty Good.  I need to take into consideration EOF and BOF because it works and I can browse the records even on multiple records found. Except I go EOF at some point hehe :)

0
 
markrhymanAuthor Commented:
Well Thanks a lot.  you've been of a great help.  I really appreciate your time.  So to release the Points I just click on Accept near your name right ?
0
 
leonstrykerCommented:
Looks like you can handle it from here.  Let me know if you need more help.

Leon
0
 
leonstrykerCommented:
Correct.  

Leon
0
 
markrhymanAuthor Commented:
Oh I see you already have the EOF and BOF in your Sub above but calling

Private Sub cmdFirst_Click()
    Navigate Action.MoveFirst
End Sub

Give me an error that Action is not defined
0
 
leonstrykerCommented:
Thanks for the Grade.
0
 
markrhymanAuthor Commented:
No problem you have been of a great help and I really appreciate it.  I am still strugling with the Button a bit though :) I have the button named exactly as you have

Private Sub cmdFirst_Click()
    Navigate Action.MoveFirst
End Sub

I click and I get variable not defined.

0
 
leonstrykerCommented:
Sorry forgot this:

Public Enum Action
    MoveNext = 1
    MovePrevious = 2
    MoveFirst = 3
    MoveLast = 4
End Enum
0
 
markrhymanAuthor Commented:
Nope I get this time

Compile Error

Expected Variable or procedure, Not Module
0
 
leonstrykerCommented:
Instead of Action.MoveFirst  you can just code a number

Private Sub cmdFirst_Click()
    Navigate 3
End Sub


This way you will not need to Type in your code
0
 
markrhymanAuthor Commented:
Ok it works.  Great.  Thanks a lot.

Man I had a headache with this....
0
 
leonstrykerCommented:
Ok, take care.

Leon
0
All Courses

From novice to tech pro — start learning today.