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
Mark