473,289 Members | 2,040 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,289 software developers and data experts.

Form & Subform Requery Problem - ADO Related?

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
11 7110
MMcCarthy
14,534 Expert Mod 8TB
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
14,534 Expert Mod 8TB
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
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
14,534 Expert Mod 8TB
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
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
32,554 Expert Mod 16PB
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
8,834 Expert 8TB
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
12,516 Expert Mod 8TB
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
1,356 Expert 1GB
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
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
32,554 Expert Mod 16PB
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

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

Similar topics

2
by: Fionnbarr Kennedy | last post by:
Hi all I have posted this problem before but am having no joy! I have an app written in Access 2000 which is based around a Form/Subform. The problem is with a new XP box only - the same app...
4
by: Dave Boyd | last post by:
Hi, I have two very similar forms each with a subform. The main form gets a few fields from the user and passes this back to a query that the subform is bound to. The requery is done when the...
2
by: F. Michael Miller | last post by:
I need to requery a subform from a third form and can't seem to get it to work. frmForm1 has frmAddress as a subform. The button cmdReviseAddress opens the form frmUpdateAddress where all of my...
3
by: ken | last post by:
Hi, I have a main form with a text box and a filter button. I also have a subform in the main form. When I make an entry into the filter text box and click the filter button, I change the query...
2
by: David W. Fenton | last post by:
I think at various times we've all encountered this problem: A subform is on a main form. From the code of the main form we refer to some property of/control on the child form thus: ...
3
by: john | last post by:
In my form (table A) I have subform (table B (only 2 fieds: ID and App_name) where table A -Table B are linked 1XM. To be able to add a record in the subform I want to use a lookup form since the...
4
by: Macbane | last post by:
Hi, I have a 'main' form called frmIssues which has a subform control (named linkIssuesDrug) containing the subform sfrmLink_Issues_Drugs. A control button on the main form opens a pop-up form...
3
by: wquatan | last post by:
Hi I have a Form with a SubForm The Form is bound to a Query, and is used for navigation. In the event Form_Current there is : wrkField = Me!TableField SubForm.requery
3
by: mapster | last post by:
Greetings All, just started out leaning relational DB and access a few weeks ago, and seems that i need some pointers as i can not find any obvious answers (obvious to me). If any one can answer...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.