By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,694 Members | 1,795 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,694 IT Pros & Developers. It's quick & easy.

Form & Subform Requery Problem - ADO Related?

P: 7
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
  1. Private Sub btnCopyClassPairQuestions_Click()
  2. On Error GoTo HandleError
  3.  
  4.     ' create a new connection instance and open it using the connection string.
  5.     Set cnConnection = New ADODB.Connection
  6.     ' This sets the strConnection for the current database.
  7.     strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  8.                     "Data Source=" & CurrentProject.FullName & ";"
  9.     cnConnection.Open strConnection
  10.     Set cmdCommand = New ADODB.Command
  11.     Set cmdCommand.ActiveConnection = cnConnection
  12.  
  13.     '*************************************************************************
  14.     '*** InsertClassPairQuestions                                          ***
  15.     '*************************************************************************
  16.  
  17.     Dim objImportOutcomeQuestion As ImportOutcomeQuestion
  18.     Set objImportOutcomeQuestion = New ImportOutcomeQuestion
  19.  
  20.  
  21.     strCurrentSQLName = "InsertClassPairQuestions"
  22.  
  23.     intReturnCode = objImportOutcomeQuestion.InsertClassPairQuestions(cmdCommand, Me.ClassID, Me.ClassPairID)
  24.  
  25.     Select Case intReturnCode
  26.         Case rcSuccessful
  27.             MsgBox "Questions successfully copied."
  28.             Call updateStatus(scQuestionsUnfinished)
  29.             bFinishedButtonClicked = True
  30.             bOkToClose = True ' See Page 435 of 'Access 2002 Desktop Devloper's Handbook' by Litwin, Getz, and Gunderloy
  31.             DoCmd.Close acForm, "OutcomeQuestionsForm"
  32.             DoCmd.OpenForm "OutcomeQuestionsForm"
  33.  
  34.         Case rcFatalError
  35.             strErrorMessage = "Fatal Error - " & strCurrentSQLName
  36.             GoTo HandleError
  37.     End Select
  38.  
  39.  
  40. Exit_btnCopyClassPairQuestions_Click:
  41.     Exit Sub
  42. End Sub
  43.  

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
  1. Private Sub btnFinished_Click()
  2. On Error GoTo HandleError
  3.  
  4.     Call updateStatus(scQuestionsCreated)
  5.     Forms("CourseInfoForm").Requery
  6.     Call Form_CourseInfoForm.Form_Load
  7.  
  8. Exit_btnFinished_Click:
  9.     bFinishedButtonClicked = True
  10.     ' See Page 435 of 'Access 2002 Desktop Devloper's Handbook' by Litwin, Getz, and Gunderloy
  11.     bOkToClose = True
  12.     DoCmd.Close
  13.     DoCmd.Hourglass False
  14.     Exit Sub
  15.  
  16. HandleError:
  17.     MsgBox Err.Description
  18.     GoTo Exit_btnFinished_Click
  19. End Sub
  20.  
I would appreciate any help that I can get.
Mark
Apr 5 '07 #1
Share this Question
Share on Google+
11 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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
  1. 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
Apr 6 '07 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Apr 6 '07 #3

P: 7
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
Apr 6 '07 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
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?
Apr 6 '07 #5

P: 7
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
  1.  Forms![OutcomeQuestionsForm]![sbfOutcomeQuestionsSubform].Requery 
I placed the requery of Form A
Expand|Select|Wrap|Line Numbers
  1. 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
Apr 11 '07 #6

NeoPa
Expert Mod 15k+
P: 31,277
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 :)).
Apr 12 '07 #7

ADezii
Expert 5K+
P: 8,610
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
  1. Private Sub btnCopyClassPairQuestions_Click()
  2. On Error GoTo HandleError
  3.  
  4.     ' create a new connection instance and open it using the connection string.
  5.     Set cnConnection = New ADODB.Connection
  6.     ' This sets the strConnection for the current database.
  7.     strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  8.                     "Data Source=" & CurrentProject.FullName & ";"
  9.     cnConnection.Open strConnection
  10.     Set cmdCommand = New ADODB.Command
  11.     Set cmdCommand.ActiveConnection = cnConnection
  12.  
  13.     '*************************************************************************
  14.     '*** InsertClassPairQuestions                                          ***
  15.     '*************************************************************************
  16.  
  17.     Dim objImportOutcomeQuestion As ImportOutcomeQuestion
  18.     Set objImportOutcomeQuestion = New ImportOutcomeQuestion
  19.  
  20.  
  21.     strCurrentSQLName = "InsertClassPairQuestions"
  22.  
  23.     intReturnCode = objImportOutcomeQuestion.InsertClassPairQuestions(cmdCommand, Me.ClassID, Me.ClassPairID)
  24.  
  25.     Select Case intReturnCode
  26.         Case rcSuccessful
  27.             MsgBox "Questions successfully copied."
  28.             Call updateStatus(scQuestionsUnfinished)
  29.             bFinishedButtonClicked = True
  30.             bOkToClose = True ' See Page 435 of 'Access 2002 Desktop Devloper's Handbook' by Litwin, Getz, and Gunderloy
  31.             DoCmd.Close acForm, "OutcomeQuestionsForm"
  32.             DoCmd.OpenForm "OutcomeQuestionsForm"
  33.  
  34.         Case rcFatalError
  35.             strErrorMessage = "Fatal Error - " & strCurrentSQLName
  36.             GoTo HandleError
  37.     End Select
  38.  
  39.  
  40. Exit_btnCopyClassPairQuestions_Click:
  41.     Exit Sub
  42. End Sub
  43.  

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
  1. Private Sub btnFinished_Click()
  2. On Error GoTo HandleError
  3.  
  4.     Call updateStatus(scQuestionsCreated)
  5.     Forms("CourseInfoForm").Requery
  6.     Call Form_CourseInfoForm.Form_Load
  7.  
  8. Exit_btnFinished_Click:
  9.     bFinishedButtonClicked = True
  10.     ' See Page 435 of 'Access 2002 Desktop Devloper's Handbook' by Litwin, Getz, and Gunderloy
  11.     bOkToClose = True
  12.     DoCmd.Close
  13.     DoCmd.Hourglass False
  14.     Exit Sub
  15.  
  16. HandleError:
  17.     MsgBox Err.Description
  18.     GoTo Exit_btnFinished_Click
  19. End Sub
  20.  
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
  1. Me.OutcomeQuestionsSubform.Requery
Apr 12 '07 #8

Rabbit
Expert Mod 10K+
P: 12,327
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.
Apr 12 '07 #9

Denburt
Expert 100+
P: 1,356
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"
Apr 12 '07 #10

P: 7
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
  1. Private Sub btnCopyClassPairQuestions_Click()
  2. On Error GoTo HandleError
  3.  
  4.     Dim strErrorMessage As String
  5.     Dim strRelevantData As String
  6.     Dim intReturnCode As Integer
  7.     Dim strCurrentSQLName As String
  8.     Dim strSQL10 As String
  9.  
  10.     ' create a new connection instance and open it using the connection string.
  11.     Set cnConnection = New ADODB.Connection
  12.  
  13.     ' This sets the strConnection for the current database.
  14.     strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  15.                     "Data Source=" & CurrentProject.FullName & ";"
  16.     'Debug.Print strConnection
  17.  
  18.     cnConnection.Open strConnection
  19.  
  20.     ' These two lines of code declare and setup the cmdCommand object,
  21.     ' so that it is ready to perform updates inside the loop that follows.
  22.     Set cmdCommand = New ADODB.Command
  23.     Set cmdCommand.ActiveConnection = cnConnection
  24.  
  25.     '*************************************************************************
  26.     '*** CursorClassPairQuestions                                          ***
  27.     '*************************************************************************
  28.  
  29.     strCurrentSQLName = "CursorClassPairQuestions"
  30.     Set rsSelectQuestions = New ADODB.Recordset
  31.  
  32.     Dim intClassID As Integer
  33.     Dim intClassPairID As Integer
  34.     intClassID = Me.ClassID
  35.     intClassPairID = Me.ClassPairID
  36.  
  37.     strSQL10 = "SELECT " & intClassID & ", ImportOutcomeQuestion.QstnID, ImportOutcomeQuestion.QstnText, " & _
  38.                        "ImportOutcomeQuestion.QstnType, ImportOutcomeQuestion.MultChoiceQstn " & _
  39.                  "FROM [Course Info] INNER JOIN ImportOutcomeQuestion " & _
  40.                    "ON [Course Info].ClassID = ImportOutcomeQuestion.ClassID " & _
  41.                 "WHERE ((([Course Info].ClassID) <> " & intClassID & ") " & _
  42.                   "And  (([Course Info].ClassPairID) = " & intClassPairID & ") " & _
  43.                   "And  (([Course Info].StatusCode) >= 100) " & _
  44.                   "And  ((ImportOutcomeQuestion.QstnType) = " & Chr(34) & "Outcome" & Chr(34) & ") " & _
  45.                   "And  ((ImportOutcomeQuestion.MultChoiceQstn) = True)) " & _
  46.              "Order By    ImportOutcomeQuestion.QstnID"
  47.  
  48.     'A debugging tool that prints the number of records in the Immediate Window (Ctrl + G)
  49.     Debug.Print "strSQL10 = " & strSQL10
  50.  
  51.     With rsSelectQuestions
  52.         .CursorType = adOpenStatic
  53.         .Open strSQL10, cnConnection
  54.     End With
  55.  
  56.     '******************************************************************************
  57.     '******************************************************************************
  58.     Dim rs As DAO.Recordset
  59.     Set rs = Me![sbfOutcomeQuestionsSubform].Form.RecordsetClone
  60.  
  61.  
  62.     If Not rsSelectQuestions.BOF And Not rsSelectQuestions.EOF Then
  63.         Do While Not rsSelectQuestions.EOF
  64.  
  65.             'A debugging tool that prints the records in the Immediate Window (Ctrl + G)
  66.             'Debug.Print "rsSelectQuestions = " & rsSelectQuestions!QstnID & " " & _
  67.             '             rsSelectQuestions!QstnText & " " & rsSelectQuestions!QstnType & " " & _
  68.             '             rsSelectQuestions!MultChoiceQstn
  69.  
  70.             strCurrentSQLName = "InsertQuestionResults"
  71.  
  72.             rs.AddNew
  73.             rs!ClassID = intClassID
  74.             rs!QstnID = rsSelectQuestions!QstnID
  75.             rs!QstnText = rsSelectQuestions!QstnText
  76.             rs!QstnType = rsSelectQuestions!QstnType
  77.             rs!MultChoiceQstn = rsSelectQuestions!MultChoiceQstn
  78.             rs.Update
  79.             rs.Move 0, rs.LastModified
  80.  
  81.             rsSelectQuestions.MoveNext
  82.         Loop
  83.     End If
  84.  
  85.     rsSelectQuestions.Close
  86.     Set rsSelectQuestions = Nothing
  87.  
  88. Exit_btnCopyClassPairQuestions_Click:
  89.     Exit Sub
  90.  
  91. HandleError:
  92.            ...
  93.            ...
  94.     GoTo Exit_btnCopyClassPairQuestions_Click
  95. End Sub
  96.  
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
  1. Call Form_CourseInfoForm.UpdateStatus(scQuestionsUnfinished)
  2.  
Expand|Select|Wrap|Line Numbers
  1. Public Sub UpdateStatus(intStatusCode As Integer)
  2.  
  3.     Dim rs As DAO.Recordset
  4.     Set rs = Me.RecordsetClone
  5.     rs.MoveFirst
  6.  
  7.     If Not rs.EOF Then
  8.         Do Until rs.EOF
  9.             ' If the Course is selected, then update it's status to the passed in value.
  10.             If (rs!Select = True) Then
  11.                 rs.Edit
  12.                 rs!StatusCode = intStatusCode
  13.                 rs.Update
  14.             End If
  15.             rs.MoveNext
  16.         Loop
  17.     End If
  18.  
  19. End Sub
  20.  
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
Apr 13 '07 #11

NeoPa
Expert Mod 15k+
P: 31,277
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 :)
Apr 16 '07 #12

Post your reply

Sign in to post your reply or Sign up for a free account.