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.


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.

I would appreciate any help that I can get.
Mark

Apr 5 '07 #1

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

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

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

Apr 6 '07 #4

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?

Apr 6 '07 #5

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.
I placed the requery of Form A
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

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

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:

Apr 12 '07 #8

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.

Apr 12 '07 #9

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"

Apr 12 '07 #10

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:

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.

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

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