Form & Subform Requery Problem - ADO Related?

[mrowe]

I am using Access 2003. (I am also using ADO in the vast majority of my code. I recently read a post that indicated that ADO is not all that is was initially cracked up to be. In the back of my mind I am wonder if this is causing my problem, but I don’t want to go through the work to convert to DAO unless I know it is truly in my best interest.)

I am having problems getting a requery to show up consistently on a couple of forms. I have read all kinds of post on this subject, but I don’t begin to understand why I can’t get this to work. I have the following setup:

Form A = CourseInfoForm
Form B = OutcomeQuestionsForm
Subform C = OutcomeQuestionsSubform
Subform C Control Name = sbfOutcomeQuestionsSubform
Form B & Subform C are linked by a field called ClassID.

Form A contains a list of courses. You can select a course and click a button to go to Form B. Form B contains detail information for the selected course, along with Questions associated with the course that reside on Subform C.

On Form B the user can click a button to run a query to copy questions from another course into the current course. These questions are inserted into a table that Subform C queries against. After a successful insert I have tried several approaches to requery or redisplay the data on Subform C. On rare occasions the newly insert rows will appear in Subform C, but usually I have to mannually close Form B and Subform C and reopen them to see the changes.

Here are some of my attempts:
1. Me.sbfOutcomeQuestionsSubform.Form.Requery ‘Subform C
2. Me.sbfOutcomeQuestionsSubform.Form.Recalc ‘Subform C
3. Me.Requery ‘Form B – hoping it requery Subform C
4. Me.OutcomeQuestionsForm.Requery ‘Form B – hoping it requery Subform C
5. Call Me.sbfOutcomeQuestionsSubform.Form.Form_Load ‘attempting to force a load subform C to reload.
6. ‘ I have even tried to automatically close and reopen Form B & Subform C
DoCmd.Close acForm, "OutcomeQuestionsForm"
DoCmd.OpenForm "OutcomeQuestionsForm"

I have included the code from the button click in case it might expose the problem.

Private Sub btnCopyClassPairQuestions_Click()
On Error GoTo HandleError
 
    ' create a new connection instance and open it using the connection string.
    Set cnConnection = New ADODB.Connection
    ' This sets the strConnection for the current database.
    strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                    "Data Source=" & CurrentProject.FullName & ";"
    cnConnection.Open strConnection
    Set cmdCommand = New ADODB.Command
    Set cmdCommand.ActiveConnection = cnConnection
 
    '*************************************************************************
    '*** InsertClassPairQuestions                                          ***
    '*************************************************************************
 
    Dim objImportOutcomeQuestion As ImportOutcomeQuestion
    Set objImportOutcomeQuestion = New ImportOutcomeQuestion
 
 
    strCurrentSQLName = "InsertClassPairQuestions"
 
    intReturnCode = objImportOutcomeQuestion.InsertClassPairQuestions(cmdCommand, Me.ClassID, Me.ClassPairID)
 
    Select Case intReturnCode
        Case rcSuccessful
            MsgBox "Questions successfully copied."
            Call updateStatus(scQuestionsUnfinished)
            bFinishedButtonClicked = True
            bOkToClose = True ' See Page 435 of 'Access 2002 Desktop Devloper's Handbook' by Litwin, Getz, and Gunderloy
            DoCmd.Close acForm, "OutcomeQuestionsForm"
            DoCmd.OpenForm "OutcomeQuestionsForm"
 
        Case rcFatalError
            strErrorMessage = "Fatal Error - " & strCurrentSQLName
            GoTo HandleError
    End Select
 
 
Exit_btnCopyClassPairQuestions_Click:
    Exit Sub
End Sub
 

In addition, when I close Form B I call a query to update a status field for the course. This value needs to be displayed on Form A. Right now I am calling both a requery and the Load_Form method on Form A and the value is not being changed.

Private Sub btnFinished_Click()
On Error GoTo HandleError
 
    Call updateStatus(scQuestionsCreated)
    Forms("CourseInfoForm").Requery
    Call Form_CourseInfoForm.Form_Load
 
Exit_btnFinished_Click:
    bFinishedButtonClicked = True
    ' See Page 435 of 'Access 2002 Desktop Devloper's Handbook' by Litwin, Getz, and Gunderloy
    bOkToClose = True
    DoCmd.Close
    DoCmd.Hourglass False
    Exit Sub
 
HandleError:
    MsgBox Err.Description
    GoTo Exit_btnFinished_Click
End Sub
 

I would appreciate any help that I can get.
Mark

[MMcCarthy]

1. Check the name of the subform object as it is not always the same as the subform name. In the main form design click on the frame around the subform and open the properties. Check the name property under the Other tab. This is the name of the subform object.

2. Try this code ...

Forms![MainFormName]![SubformObjectName].Requery

Form A will only requery when it is open so don't call the form load. Just requery Form A in the close event of Form B.

Forms!CourseInfoForm.Requery

In addition, when I close Form B I call a query to update a status field for the course. This value needs to be displayed on Form A. Right now I am calling both a requery and the Load_Form method on Form A and the value is not being changed. Also make sure the status field on Form A is properly bound to the table or query.

Mary

[MMcCarthy]

Regarding DAO vs. ADO. Have a look at some of the previous discussions on the subject.

TheScripts Tip of the Week #6 (DAO or ADO - Why Not Both?)

Changing from DAO to MS ActiveX ADO

[mrowe]

Mary,

Thank you for your help. I implemented the Requery changes that you suggested. The first test that I ran worked, but every subsequent test I ran the requery did not show up until I manually closed and reopened the window. I don't get it.

Do you think this is related to ADO? ...Or do you think it is something else? I am curious to hear your thought.

Sincerely,
Mark

[MMcCarthy]

Mary,

Thank you for your help. I implemented the Requery changes that you suggested. The first test that I ran worked, but every subsequent test I ran the requery did not show up until I manually closed and reopened the window. I don't get it.

Do you think this is related to ADO? ...Or do you think it is something else? I am curious to hear your thought.

Sincerely,
Mark

Mark

Did you put the requery in the close event of Form B?

[mrowe]

Hi Mary,

Sorry for my slow response. I have been side-tracked from working on this for the last couple of days.

I placed the requery for Subform C at the end of the button click code on Form B after the new values are successfully inserted into the database.

 Forms![OutcomeQuestionsForm]![sbfOutcomeQuestionsSubform].Requery 

I placed the requery of Form A

Forms!CourseInfoForm.Requery

in the code that is invoked when the user closes Form B. Form B is closed by clicking either 'Finish' or 'Finish Later'. Then a status field is updated, then the requery of Form A is perform. Please note that Form A has been open the whole time.

I am curious to hear your thoughts.
Mark

[NeoPa]

Mark,
To be honest, I think you have pretty well everything covered here already (Doesn't help much, I know :().
This link (Referring to Items on a Sub-Form) may help if there are any possibilities missed and may be worth a quick scan. Otherwise, I may get an opportunity to chat with Mary about this this evening (It will have to be quite late as Tottenham are live on the telly again :)).

[ADezii]

I am using Access 2003. (I am also using ADO in the vast majority of my code. I recently read a post that indicated that ADO is not all that is was initially cracked up to be. In the back of my mind I am wonder if this is causing my problem, but I don’t want to go through the work to convert to DAO unless I know it is truly in my best interest.)

I am having problems getting a requery to show up consistently on a couple of forms. I have read all kinds of post on this subject, but I don’t begin to understand why I can’t get this to work. I have the following setup:

Form A = CourseInfoForm
Form B = OutcomeQuestionsForm
Subform C = OutcomeQuestionsSubform
Subform C Control Name = sbfOutcomeQuestionsSubform
Form B & Subform C are linked by a field called ClassID.

Form A contains a list of courses. You can select a course and click a button to go to Form B. Form B contains detail information for the selected course, along with Questions associated with the course that reside on Subform C.

On Form B the user can click a button to run a query to copy questions from another course into the current course. These questions are inserted into a table that Subform C queries against. After a successful insert I have tried several approaches to requery or redisplay the data on Subform C. On rare occasions the newly insert rows will appear in Subform C, but usually I have to mannually close Form B and Subform C and reopen them to see the changes.

Here are some of my attempts:
1. Me.sbfOutcomeQuestionsSubform.Form.Requery ‘Subform C
2. Me.sbfOutcomeQuestionsSubform.Form.Recalc ‘Subform C
3. Me.Requery ‘Form B – hoping it requery Subform C
4. Me.OutcomeQuestionsForm.Requery ‘Form B – hoping it requery Subform C
5. Call Me.sbfOutcomeQuestionsSubform.Form.Form_Load ‘attempting to force a load subform C to reload.
6. ‘ I have even tried to automatically close and reopen Form B & Subform C
DoCmd.Close acForm, "OutcomeQuestionsForm"
DoCmd.OpenForm "OutcomeQuestionsForm"

I have included the code from the button click in case it might expose the problem.

Private Sub btnCopyClassPairQuestions_Click()
On Error GoTo HandleError
 
    ' create a new connection instance and open it using the connection string.
    Set cnConnection = New ADODB.Connection
    ' This sets the strConnection for the current database.
    strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                    "Data Source=" & CurrentProject.FullName & ";"
    cnConnection.Open strConnection
    Set cmdCommand = New ADODB.Command
    Set cmdCommand.ActiveConnection = cnConnection
 
    '*************************************************************************
    '*** InsertClassPairQuestions                                          ***
    '*************************************************************************
 
    Dim objImportOutcomeQuestion As ImportOutcomeQuestion
    Set objImportOutcomeQuestion = New ImportOutcomeQuestion
 
 
    strCurrentSQLName = "InsertClassPairQuestions"
 
    intReturnCode = objImportOutcomeQuestion.InsertClassPairQuestions(cmdCommand, Me.ClassID, Me.ClassPairID)
 
    Select Case intReturnCode
        Case rcSuccessful
            MsgBox "Questions successfully copied."
            Call updateStatus(scQuestionsUnfinished)
            bFinishedButtonClicked = True
            bOkToClose = True ' See Page 435 of 'Access 2002 Desktop Devloper's Handbook' by Litwin, Getz, and Gunderloy
            DoCmd.Close acForm, "OutcomeQuestionsForm"
            DoCmd.OpenForm "OutcomeQuestionsForm"
 
        Case rcFatalError
            strErrorMessage = "Fatal Error - " & strCurrentSQLName
            GoTo HandleError
    End Select
 
 
Exit_btnCopyClassPairQuestions_Click:
    Exit Sub
End Sub
 

In addition, when I close Form B I call a query to update a status field for the course. This value needs to be displayed on Form A. Right now I am calling both a requery and the Load_Form method on Form A and the value is not being changed.

Private Sub btnFinished_Click()
On Error GoTo HandleError
 
    Call updateStatus(scQuestionsCreated)
    Forms("CourseInfoForm").Requery
    Call Form_CourseInfoForm.Form_Load
 
Exit_btnFinished_Click:
    bFinishedButtonClicked = True
    ' See Page 435 of 'Access 2002 Desktop Devloper's Handbook' by Litwin, Getz, and Gunderloy
    bOkToClose = True
    DoCmd.Close
    DoCmd.Hourglass False
    Exit Sub
 
HandleError:
    MsgBox Err.Description
    GoTo Exit_btnFinished_Click
End Sub
 

I would appreciate any help that I can get.
Mark

Requery the Subform Control NOT the Subform itself as in:

Me.OutcomeQuestionsSubform.Requery

[Rabbit]

I've run into this problem before where I couldn't get the subform to requery or refresh. So I ended up refreshing the entire form and that seemed to work.

[Denburt]

So many options I ran into a similar problem once and the ONLY way I was able to get it going was to reset the recordsource. It was a while back but I think it was something like this.

Me.recordsource = "YourQueryName"

[mrowe]

Thank you everyone for your help. I tried all your suggestions. Some of them worked a couple of times then stopped working. I'm not sure why. Finally, I scrapped the 'requery' approached and did the following.

On the button click of Form B, I implemented the following code to update the recordsetclone on subform C:

Private Sub btnCopyClassPairQuestions_Click()
On Error GoTo HandleError
 
    Dim strErrorMessage As String
    Dim strRelevantData As String
    Dim intReturnCode As Integer
    Dim strCurrentSQLName As String
    Dim strSQL10 As String
 
    ' create a new connection instance and open it using the connection string.
    Set cnConnection = New ADODB.Connection
 
    ' This sets the strConnection for the current database.
    strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                    "Data Source=" & CurrentProject.FullName & ";"
    'Debug.Print strConnection
 
    cnConnection.Open strConnection
 
    ' These two lines of code declare and setup the cmdCommand object,
    ' so that it is ready to perform updates inside the loop that follows.
    Set cmdCommand = New ADODB.Command
    Set cmdCommand.ActiveConnection = cnConnection
 
    '*************************************************************************
    '*** CursorClassPairQuestions                                          ***
    '*************************************************************************
 
    strCurrentSQLName = "CursorClassPairQuestions"
    Set rsSelectQuestions = New ADODB.Recordset
 
    Dim intClassID As Integer
    Dim intClassPairID As Integer
    intClassID = Me.ClassID
    intClassPairID = Me.ClassPairID
 
    strSQL10 = "SELECT " & intClassID & ", ImportOutcomeQuestion.QstnID, ImportOutcomeQuestion.QstnText, " & _
                       "ImportOutcomeQuestion.QstnType, ImportOutcomeQuestion.MultChoiceQstn " & _
                 "FROM [Course Info] INNER JOIN ImportOutcomeQuestion " & _
                   "ON [Course Info].ClassID = ImportOutcomeQuestion.ClassID " & _
                "WHERE ((([Course Info].ClassID) <> " & intClassID & ") " & _
                  "And  (([Course Info].ClassPairID) = " & intClassPairID & ") " & _
                  "And  (([Course Info].StatusCode) >= 100) " & _
                  "And  ((ImportOutcomeQuestion.QstnType) = " & Chr(34) & "Outcome" & Chr(34) & ") " & _
                  "And  ((ImportOutcomeQuestion.MultChoiceQstn) = True)) " & _
             "Order By    ImportOutcomeQuestion.QstnID"
 
    'A debugging tool that prints the number of records in the Immediate Window (Ctrl + G)
    Debug.Print "strSQL10 = " & strSQL10
 
    With rsSelectQuestions
        .CursorType = adOpenStatic
        .Open strSQL10, cnConnection
    End With
 
    '******************************************************************************
    '******************************************************************************
    Dim rs As DAO.Recordset
    Set rs = Me![sbfOutcomeQuestionsSubform].Form.RecordsetClone
 
 
    If Not rsSelectQuestions.BOF And Not rsSelectQuestions.EOF Then
        Do While Not rsSelectQuestions.EOF
 
            'A debugging tool that prints the records in the Immediate Window (Ctrl + G)
            'Debug.Print "rsSelectQuestions = " & rsSelectQuestions!QstnID & " " & _
            '             rsSelectQuestions!QstnText & " " & rsSelectQuestions!QstnType & " " & _
            '             rsSelectQuestions!MultChoiceQstn
 
            strCurrentSQLName = "InsertQuestionResults"
 
            rs.AddNew
            rs!ClassID = intClassID
            rs!QstnID = rsSelectQuestions!QstnID
            rs!QstnText = rsSelectQuestions!QstnText
            rs!QstnType = rsSelectQuestions!QstnType
            rs!MultChoiceQstn = rsSelectQuestions!MultChoiceQstn
            rs.Update
            rs.Move 0, rs.LastModified
 
            rsSelectQuestions.MoveNext
        Loop
    End If
 
    rsSelectQuestions.Close
    Set rsSelectQuestions = Nothing
 
Exit_btnCopyClassPairQuestions_Click:
    Exit Sub
 
HandleError:
           ...
           ...
    GoTo Exit_btnCopyClassPairQuestions_Click
End Sub
 

Then to update the StatusCode on Form A, I added a 'public' method to update the recordsetclone of the selected record and used the following code in Form B to call it in the unload method.

Call Form_CourseInfoForm.UpdateStatus(scQuestionsUnfinished)
 

Public Sub UpdateStatus(intStatusCode As Integer)
 
    Dim rs As DAO.Recordset
    Set rs = Me.RecordsetClone
    rs.MoveFirst
 
    If Not rs.EOF Then
        Do Until rs.EOF
            ' If the Course is selected, then update it's status to the passed in value.
            If (rs!Select = True) Then
                rs.Edit
                rs!StatusCode = intStatusCode
                rs.Update
            End If
            rs.MoveNext
        Loop
    End If
 
End Sub
 

Note: Form A and therefore also the recordsetclone have a checkbox called 'Select' that is checked for the record the user is working on in Form B.

It now works. Thank you again everyone for your help.
Mark

[NeoPa]

I'm pleased to hear you got a resolution to this Mark. And thanks for posting the solution you came up with for the benefit of anyone else with similar problems :)