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