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

Subform refresh and query view.

P: 23
Hello again,

I have a subform that gets populated from a records query. I needed the subform to refresh once a record is added via another form. I accomplished this by re-running the query for the subform upon closing the record addition form. It works like a champ, except I also the get the query datasheet view in addtion to the subform.

I am sure there is an obvious answer, that I am missing. Please let me know what other information you may need to help illustrate the problem.

Regards,
Willie
Sep 30 '09 #1

✓ answered by ChipR

@ChipR
Note that it is a good idea to check that the form you want to reference is open before you go and try to use it. To prevent errors, you can do something like:
Expand|Select|Wrap|Line Numbers
  1. If CurrentProject.AllForms("FormToLog").IsLoaded Then
  2.   'Do your stuff
  3. End If

Share this Question
Share on Google+
28 Replies


Expert 100+
P: 1,287
Save the query, set the subform source to the query, and after the data set is changed, call
Expand|Select|Wrap|Line Numbers
  1. subformControl.Form.Requery
Or, just wait the preset amount of time for Acess to update itself.
Sep 30 '09 #2

P: 23
Thank you for the reply.

I did that, but I still get the query data sheet view on top of the subform being refreshed. I guess I didnt put it in the right spot. Here is the button code.

Expand|Select|Wrap|Line Numbers
  1. On Error GoTo Err_addrecord_Click
  2.  
  3.  
  4.     DoCmd.GoToRecord , , acNewRec
  5.  
  6. Exit_addrecord_Click:
  7.     Exit Sub
  8.  
  9. Err_addrecord_Click:
  10.     MsgBox Err.Description
  11.     Resume Exit_addrecord_Click
  12.  
  13. End Sub
  14. Private Sub OpenAddRecords_Click()
  15. On Error GoTo Err_OpenAddRecords_Click
  16.  
  17.     Dim stDocName As String
  18.     Dim stLinkCriteria As String
  19.  
  20.     stDocName = "tologentry"
  21.     DoCmd.OpenForm stDocName, , , stLinkCriteria
  22.  
  23. Exit_OpenAddRecords_Click:
  24.     Exit Sub
  25.  
  26. Err_OpenAddRecords_Click:
  27.     MsgBox Err.Description
  28.     Resume Exit_OpenAddRecords_Click
  29.  
  30.     subformControl.Form.Requery
  31.  
  32. End Sub
Sep 30 '09 #3

Expert 100+
P: 266
The line will only execute OnError where it is right now, put it as follows..

@Rotorian
Sep 30 '09 #4

Expert 100+
P: 1,287
First, replace "subformControl" with the actual name of the subform control on your form. Second, what is this "tologentry" you are opening?
Sep 30 '09 #5

P: 23
Hmmm okay, did this:




"code deleted to save space, sorry"

Still get datasheet ivew, and now I get error "Object Required" upon clicking the button to add record.
Sep 30 '09 #6

P: 23
You know I think I am going about this the wrong way. I posted the vb code for the button that is open a records entry form. Where I think i should post the code for the records entry form for an "OnClose" event. Let me double ck.
Sep 30 '09 #7

Expert 100+
P: 266
Also I think you need to address what ChipR is asking, before we can help you further.

-AJ
Sep 30 '09 #8

P: 23
Okay form "tologentry" is the medium for updating records in table "tolog" in the "OnClose" event for "tologentry" I put this code:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Close()
  2.  
  3. historyrequest SubForm.Form.Requery
  4.  
  5. End Sub
This should refresh "historyrequest subform" within another form, right? However, it is not happening, and I am getting the following error:

"Run-time error: 424 Object required"

When you guys ask for control I assume you mean the query that populates the subform? Under record source? for the subform properties. I think I am missing the obvious somewhere.

Sorry.
Sep 30 '09 #9

P: 23
Please note I replaced the error code. In case anyone read it before my edit, apologies.
Sep 30 '09 #10

NeoPa
Expert Mod 15k+
P: 31,476
It's not a great idea to have names with embedded spaces in. This causes the interpreter to get confused unless you make it explicit :
Expand|Select|Wrap|Line Numbers
  1. [historyrequest SubForm].Form.Requery
Sep 30 '09 #11

NeoPa
Expert Mod 15k+
P: 31,476
@Rotorian
No, this code (even my fixed version) assumes this is a subform on the current form.
@Rotorian
Again no.

I believe they're talking about the subform control on your form. A subform control contains a form, which acts as a subform. Referring to Items on a Sub-Form gives a bit of an explanation. Please read it so that you can reply here as required.

Good luck.
Sep 30 '09 #12

P: 23
Thank you Neo,

I found that post while searching for "form control" earlier today. I will go back to study it, as the answer still escaped even after reading it. Which could only mean that I did not pay much attention. Hopefully, tomorrow, I'll see the light :).

Regards
Oct 1 '09 #13

NeoPa
Expert Mod 15k+
P: 31,476
One of the main keys to understanding it is that the form you're using as a subform is not, but is contained within, the SubForm control on your main form.

People often refer blithely to their subform without realising that they are not saying what they think they are. Often they continue to do this even after it's been explained, but we normally get there in the end.
Oct 1 '09 #14

P: 23
Okay, I reread your insight again. The part that I am missing is where within the code does the information reside? If I open the form "tolog" go to design view and press {code} to open the VB editor, I get the following:

Expand|Select|Wrap|Line Numbers
  1. On Error GoTo Err_addrecord_Click
  2.  
  3.     DoCmd.GoToRecord , , acNewRec
  4.  
  5. Exit_addrecord_Click:
  6.     Exit Sub
  7.  
  8. Err_addrecord_Click:
  9.     MsgBox Err.Description
  10.     Resume Exit_addrecord_Click
  11.  
  12. End Sub
  13.  
  14. Private Sub historyrequest_subform_Enter()
  15.  
  16. End Sub
  17.  
  18. Private Sub OpenAddRecords_Click()
  19. On Error GoTo Err_OpenAddRecords_Click
  20.  
  21.     Dim stDocName As String
  22.     Dim stLinkCriteria As String
  23.  
  24.     stDocName = "tologentry"
  25.     DoCmd.OpenForm stDocName, , , stLinkCriteria
  26.  
  27. Exit_OpenAddRecords_Click:
  28.     Exit Sub
  29.  
  30. Err_OpenAddRecords_Click:
  31.     MsgBox Err.Description
  32.     Resume Exit_OpenAddRecords_Click
  33.  
  34. End Sub
Now, this is a very crude and simple form so it shouldnt be hard for me to find it. However, I guess I cant see the forest from the tree :|.
Oct 1 '09 #15

NeoPa
Expert Mod 15k+
P: 31,476
@Rotorian
What information are you talking about?

PS. You've chopped off the first lines of your module. Your code starts in the middle of a procedure.
Oct 1 '09 #16

P: 23
I am looking for the "control" of the subform. As far as the code, that is all there is, well except for this;

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Private Sub addrecord_Click()
  4.  
  5.  
Which is just above;

Expand|Select|Wrap|Line Numbers
  1. # On Error GoTo Err_addrecord_Click
  2. #  
  3. #     DoCmd.GoToRecord , , acNewRec
Oct 1 '09 #17

NeoPa
Expert Mod 15k+
P: 31,476
@Rotorian
The SubForm IS a control.

It looks like the name of your SubForm control may be [historyrequest subform]. It's hard to be sure from the code if you've used spaces in your names. It's best to look at the design of the form to find the name of the control. Open the Properties Pane (Alt-Enter) and look at the name when you select the control.
Oct 1 '09 #18

P: 23
Okay, I deleted the subform and started from scratch. In the form "tolog" I created a subform using the wizard. I asked the wizard to use query "historyrequest" to populate the subform. The query gets the records for the past 24hrs from table "tolog" to display in the subform. When doing Alt+Enter with the subform selected, the name is "historyrequestsubform" this is also the name I gave it in the wizard. Now I went to "tologentry" which is the form used to input records into "tolog" table. In the OnClose event for the form I inserted the following;

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Private Sub Form_Close()
  4.  
  5.     historyrequestsubform.Form.Requery
  6.  
  7. End Sub
Should this not refresh the subform?

I am getting the following error;

Run-time error '424':
Object Required

When I select debug and the code window emerges, the historyrequestsubform.Form.Requery is highlighted with an arrow pointing to it.
Oct 1 '09 #19

NeoPa
Expert Mod 15k+
P: 31,476
You could try Me.historyrequestsubform.Form.Requery.

Unfortunately, IntelliSense won't easily show that it's recognized as you use all lower case. In truth, I suspect the name is wrong.
Oct 2 '09 #20

P: 23
Okay, I tried the above and did not work. Since I may have had some naming issues, I deleted the whole project (it was small so no worry there) and started again. Now, I recreated the tables and forms and inside of "FormToLog" is "SubFormShowPastDay" which displays past 24hr records using "QueryPastDay".

Also in "FormToLog" I have a button, this button calls up "FormNewLogEntry" from this form records are added to "TableLogEntries". In the OnClose event of "FormNewLogEntry" I inserted the following code;

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Close()
  2.     Me.SubFormShowPastDay.Form.Requery
  3. End Sub
When I close "FormNewLogEntry" I get the error

"Compile Error: Method or data member not found"

when the VB window is opened after choosing "Debug" .SubFormShowPastDay is highlited in blue, not the usual yellow.

If I open "FormToLog" in design view, and choose "SubFormShowPastDay" (I.e. it has the edit form highlight) and do Alt+Enter in the subsequent window titled "Subform/Subreport: SubFormShowPastDay" under Data tab in the field Source Object "SubFormShowPastDay" is displayed. Under the Other tab in the field Name "SubFormShowPastDay" is displayed. So, am I correct the subform control is "SubFormShowPastDay" ?

If I remove "Me." from the above code, then the following error is displayed;

"Run-timer error '424': Object required"

Upon selecting Debug the VB window displays the event with the line "SubFormShowPastDay.Form.Requery" highlighted in yellow with a yellow arrow pointing to it.

I dont know what other information to give to help solve the problem or pinpoint my error :(. I may just have to create a button to refresh "FormToLog" for the user to refresh the records display in "SubFormShowPastDay"

All help appreciated..."Help me ObeeWan, You're my only hope" :)

Regards,
Oct 2 '09 #21

Expert 100+
P: 1,287
If this code is in the form named FormNewLogEntry, then that form doesn't have a SubFormShowPastDay, since it is actually on the form named FormToLog. You would need:
Expand|Select|Wrap|Line Numbers
  1. Forms![FormToLog]![SubFormShowPastDay].Form.Requery
Oct 2 '09 #22

Expert 100+
P: 1,287
@ChipR
Note that it is a good idea to check that the form you want to reference is open before you go and try to use it. To prevent errors, you can do something like:
Expand|Select|Wrap|Line Numbers
  1. If CurrentProject.AllForms("FormToLog").IsLoaded Then
  2.   'Do your stuff
  3. End If
Oct 2 '09 #23

P: 23
OMG!

ChipR for the win.

That was the Michael Jordan shot.

May your life be blessed with the fruit of Laurelin.

Thanks everyone for the help. What a great place.

Now if I may ask, with that code you are actually pointing the requery to go to the subform in "FormToLog", where as with the previous code it was assuming that the subform was within "FormNewLogEntry"?

I just want to edumacate myself, if possible, not just plug in a code and "voila" it works.

Again, thank you all for everything. Hope not to bug you too much again.

Regards,
Oct 2 '09 #24

NeoPa
Expert Mod 15k+
P: 31,476
I haven't the time to check through the logic of this now, but I think you can assume Chip has the solution for you. He's pretty sound.

If you still experience problems then let us know. I'll check back later when I have a little more time.
Oct 2 '09 #25

NeoPa
Expert Mod 15k+
P: 31,476
Sorry, didn't catch this earlier.
@Rotorian
Absolutely.

PS. You're welcome to post your queries in future. That's what we're here for :)
Oct 2 '09 #26

Expert 100+
P: 1,287
If you're patient, and it seems you are, you should find this link very useful:
Access Object Model Reference.
Oct 2 '09 #27

P: 23
Bookmarked for usage. Again, thank you.
Oct 2 '09 #28

NeoPa
Expert Mod 15k+
P: 31,476
@ChipR
Me too. That's going into my Bytes database :)
Oct 3 '09 #29

Post your reply

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