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.

Expand|Select|Wrap|Line Numbers

 
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.

Expand|Select|Wrap|Line Numbers

 
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 ...

Expand|Select|Wrap|Line Numbers

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.

Expand|Select|Wrap|Line Numbers

Forms![OutcomeQuestionsForm]![sbfOutcomeQuestionsSubform].Requery

I placed the requery of Form A

Expand|Select|Wrap|Line Numbers

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.

Expand|Select|Wrap|Line Numbers

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.

Expand|Select|Wrap|Line Numbers

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:

Expand|Select|Wrap|Line Numbers

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:

Expand|Select|Wrap|Line Numbers

 
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.

Expand|Select|Wrap|Line Numbers

Call Form_CourseInfoForm.UpdateStatus(scQuestionsUnfinished)

Expand|Select|Wrap|Line Numbers

 
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 :)

Form & Subform Requery Problem - ADO Related?

Post your reply

Similar topics