Hi
I have a main form with a tab control, and each tab has a subform linked to the main form. Each subform has an unbound text box with the control source =Count([Sub_ID]).
When the main form opens the count text boxes are correct, but if I add a record to the subform the count does not update. I have tried VBA on the subform's after update and after insert to requery the count text box but this is not making any difference.
Any suggestions please?
NeoPa:
For now, please show me your code as explained above and I'll see if I can see anything wrong with it. In this case I would need to see the whole procedure of course.
I'll see if I can make some progress without that, but it's easier with the information. Laura:
I wondered if I need to create a standard module as I didn't know whether the issue was that I am trying to do it through a class module?
Let's start with the easy stuff first. No. This would be heading off at a tangent and won't get you anywhere helpful.
Reviewing what we know : - A
.Requery call of the Sub-Form control does what you want. This is indicated by the Command Button working as desired. Very important work to confirm this. - The event code to call the
.Requery doesn't appear to be working. It's harder to know why this is as we don't have the code (including the procedure header).
We could probably manage a complicated approach of assuming the form is being run from within a Sub-Form control, but that's unlikely to be required and would simply leave the code more complicated than it need be. You would probably be happy just getting it to work and hang the consequences, but that's only because it seems that nothing's working as expected right now and your confidence is low. Let me say that's a fallback option but I very much doubt you'll need it.
What we need to do is : - Consider when exactly it is that we require the
.Requery to be executed. I would suggest we need to see this occur if either a record is added or one is deleted. As such, the event procedures we probably need are AfterInsert and AfterDelConfirm. If you can think of other points it may be required then please say so.
NB. I'm talking here about the form in the Sub-Form. I don't believe I know what that's called yet. Sorry if I missed it in one of your posts. - Create code for both procedures that do nothing but call a separate procedure (
DoRequery() ) in the same form's module. This should be done using the properties sheet of the form. This is useful because it both sets the property correctly and creates a code stub for you to use and insert your code into. We will start with a simplified procedure that just indicates that it has been triggered.
When done the new section of the code should look like : - Private Sub Form_AfterDelConfirm(Status As Integer)
-
If Status <> acDeleteOK Then Exit Sub
-
Call DoRequery
-
End Sub
-
-
Private Sub Form_AfterInsert()
-
Call DoRequery
-
End Sub
-
-
Private Sub DoRequery()
-
Call MsgBox("Call DoRequery()")
-
End Sub
- Test the new code and make sure the MessageBox comes up when you either add or delete a record. Once you're happy that it does that then move on to the next step.
- Try out the
.Requery code in the DoRequery() . It should now look like : - Private Sub DoRequery()
-
Call Me.Requery
-
End Sub
If that doesn't work then I need you to tell me what went wrong and where. The better I understand exactly what happened the more likely I am to be able to help further.
26 3875 NeoPa 32,556
Expert Mod 16PB
It's not the TextBox that needs to be requeried Laura, but the form itself in the subform.
Thanks, NeoPa. I've been experimenting with this but requesting the requery from the subform does not seem to work.
I created a command button on my main form which had vba to requery the subform control and that manually updates the count, but obviously I would like it to be automate when the subform updates/inserts. I could hide the command button and set focus on it in vba from the subform's after update/insert, but thought there must be a 'cleaner' way? Perhaps using a module?
Thanks
NeoPa 32,556
Expert Mod 16PB Laura:
Thanks, NeoPa. I've been experimenting with this but requesting the requery from the subform does not seem to work.
I hope you don't think that's what I was suggesting. You may need to reread my post if so.
Are you getting the required results when your code runs (regardless of how the code is triggered)?
Your original question indicated that's what you're after. If what you need has changed then we need to know, of course. Often that requires a new thread but sometimes not. Knowing what you need help with is obviously crucial to being able to provide such help though.
Hi NeoPa. I understood from your first post that you were suggesting something like Me.Requery on the subform's after update / after insert but this did not do anything. I also tried requerying the main form but this also did not do anything. The only thing that has updated the count is when I requery the subform control from the main form. I'm just not sure how to do this in the subform's after update / after insert. Thanks.
NeoPa 32,556
Expert Mod 16PB
Hi Laura.
Subform controls have no such events so I'm going to assume you're talking about doing a Me.Requery from the AfterUpdate event of the form that has been associated with the Subform control. You also mention the AfterInsert event, but that's a different animal completely. Read up about it in the Help system if you want to know more. For now we can concentrate on the AfterUpdate event.
Essentially, you're correct in your statement. Doing a Me.Requery from the AfterUpdate event of the form that has been associated with the Subform control should work. Can you show me the code that you tried and that failed? Maybe there's something not quite right about it. We will also need to confirm that it's being triggered at all of course. Sometimes people show perfect code that only doesn't work because the event has not been set up to call the code in the first place.
For now, please show me your code as explained above and I'll see if I can see anything wrong with it. In this case I would need to see the whole procedure of course.
NB. It seems that the process is working as you report it does when called from the main form. We probably just need to determine why it isn't being triggered when the event fires.
Hi NeoPa
Thanks for continuing to comment on this thread.
On my main form I have added a Command Button with the code
Medical1 is the name of the subform control on the tab page. When I manually click this the count updates on the subform. The count also updates when I navigate backwards and forwards on records on the main form.
I have tried
on the subform and main form After Update but this does nothing.
I have also experimented with Recalc and Refresh.
It seems the key is to requery the subform control from the main form, as I have done with the Command Button. However I would like to achieve this via vba and not have to manually click the Command Button. My knowledge then of procedures is minimal. I have tried putting the
code in the main form's On Current code, made it a Public Sub and tried calling it from the subform with vba such as - Forms!Students.Form_Current
but this does nothing. (Students is the name of the main form.)
I wondered if I need to create a standard module as I didn't know whether the issue was that I am trying to do it through a class module?
Thanks.
NeoPa 32,556
Expert Mod 16PB NeoPa:
For now, please show me your code as explained above and I'll see if I can see anything wrong with it. In this case I would need to see the whole procedure of course.
I'll see if I can make some progress without that, but it's easier with the information. Laura:
I wondered if I need to create a standard module as I didn't know whether the issue was that I am trying to do it through a class module?
Let's start with the easy stuff first. No. This would be heading off at a tangent and won't get you anywhere helpful.
Reviewing what we know : - A
.Requery call of the Sub-Form control does what you want. This is indicated by the Command Button working as desired. Very important work to confirm this. - The event code to call the
.Requery doesn't appear to be working. It's harder to know why this is as we don't have the code (including the procedure header).
We could probably manage a complicated approach of assuming the form is being run from within a Sub-Form control, but that's unlikely to be required and would simply leave the code more complicated than it need be. You would probably be happy just getting it to work and hang the consequences, but that's only because it seems that nothing's working as expected right now and your confidence is low. Let me say that's a fallback option but I very much doubt you'll need it.
What we need to do is : - Consider when exactly it is that we require the
.Requery to be executed. I would suggest we need to see this occur if either a record is added or one is deleted. As such, the event procedures we probably need are AfterInsert and AfterDelConfirm. If you can think of other points it may be required then please say so.
NB. I'm talking here about the form in the Sub-Form. I don't believe I know what that's called yet. Sorry if I missed it in one of your posts. - Create code for both procedures that do nothing but call a separate procedure (
DoRequery() ) in the same form's module. This should be done using the properties sheet of the form. This is useful because it both sets the property correctly and creates a code stub for you to use and insert your code into. We will start with a simplified procedure that just indicates that it has been triggered.
When done the new section of the code should look like : - Private Sub Form_AfterDelConfirm(Status As Integer)
-
If Status <> acDeleteOK Then Exit Sub
-
Call DoRequery
-
End Sub
-
-
Private Sub Form_AfterInsert()
-
Call DoRequery
-
End Sub
-
-
Private Sub DoRequery()
-
Call MsgBox("Call DoRequery()")
-
End Sub
- Test the new code and make sure the MessageBox comes up when you either add or delete a record. Once you're happy that it does that then move on to the next step.
- Try out the
.Requery code in the DoRequery() . It should now look like : - Private Sub DoRequery()
-
Call Me.Requery
-
End Sub
If that doesn't work then I need you to tell me what went wrong and where. The better I understand exactly what happened the more likely I am to be able to help further.
Hi NeoPa
Sorry for the delay in responding to your detailed post; I have been very busy at work this week.
I tried your test code tonight and it revealed that the After Insert event is not firing when I add a new record and am still on that new record - it only fires once I navigate to another record on the main form.
The After Del Confirm works fine. And I also noticed that the count does update correctly when I delete a record.
One thing I have now noticed however is that, although the code on the Command Button click updates the count, it causes the subform to revert to the first record which is not ideal.
Thanks
NeoPa 32,556
Expert Mod 16PB Laura:
I tried your test code tonight and it revealed that the After Insert event is not firing when I add a new record and am still on that new record - it only fires once I navigate to another record on the main form.
Indeed. This is something that shouldn't have surprised you had you followed the instructions in post #6 and read up on this particularly awkward pair of events. BeforeInsert and AfterInsert do not balance as you might expect. AfterInsert only fires after the record has been inserted into the table. Very much as you would need I suspect. BeforeInsert, on the other hand, fires as soon as the new record buffer is dirtied. I doubt that'd be much use to you. Laura:
One thing I have now noticed however is that, although the code on the Command Button click updates the count, it causes the subform to revert to the first record which is not ideal.
This is to be expected. It's what a .Requery() does. Saving and renavigating to the previous record is another matter entirely. Much more complicated and fraught with gotchas.
Otherwise, it sounds like you have what you asked for if I'm not misreading your situation.
Unfortunately I still haven't been able to get it to work. Are you saying it can't be done, NeoPa?
So I think I need to put the code in the Before Insert event of the subform. I thought I should DoCmd save record in the first line. Then I'm stuck. Me Requery and Refresh on the main form are causing the record order to change, and Recalc and Repaint don't do anything.
NeoPa 32,556
Expert Mod 16PB Laura:
Unfortunately I still haven't been able to get it to work. Are you saying it can't be done, NeoPa?
Absolutely not. I'm not getting a clear understanding of what isn't working. What you describe appears to me to be what you need.
That is to say, if we're still talking about the original requirement, and that's all we are allowed to talk about in this thread.
If you want to go off-topic and discuss returning to a specific record after a .Requery() then that is the topic of another thread. Feel free to PM me if you choose to go there (Normally not allowed), but I warn you it's beyond the basics.
Assuming the original request thhough, where are we on that? From what you've described already we have a solution. BeforeInsert is another animal. Not the same as AfterInsert at all. Not where you want to handle anything as far as I understand from what we've already discussed.
If I misunderstand you then please explain to me how and where and try to explain exactly what it is you need that is different from what you have.
Hi NeoPa. My goal is still to get the Count textbox updating automatically when I either add or delete a record. Is the only way to do this via a Requery which will resort the data? Or is there a way I can force the Count textbox to update without affecting the record order please?
NeoPa 32,556
Expert Mod 16PB
Simple, clear, questions are easy to answer. Unfortunately, you may not like those answers, I'm afraid. Let me at least be very clear for you. Laura:
My goal is still to get the Count textbox updating automatically when I either add or delete a record.
Notice my underline there. I believe this is the first mention in the thread of deleting records. Nevertheless, this hardly complicates matters at all. It simply means we need to include the AfterDelConfirm event along with the other two already covered. Laura:
Is the only way to do this via a Requery which will resort the data?
I believe so. Yes.
It may not be so important when you update a record, in fact you could probably just ignore that scenario with your existing formula. If a formula needed more than the simple presence of the updated record, and some of the data within it too, then you could still get away with a .Refresh() instead of a .Requery(). This is because an update doesn't change the set of records used, whereas additions and deletions clearly do.
For the latter two, even the deletion, the set of records in use doesn't change unless you run a .Requery() or reopen the whole form. All the .Refresh() does is reload the latest data from the table(s) behind the form for the current set of records. It would leave the form with no updated knowledge of how many records in the table(s) as it would still be counting the original set. Laura:
Or is there a way I can force the Count textbox to update without affecting the record order please?
Not really. Not the way you have it now without adding more complicated code.
Let's look at the options available : - A clumsy approach, but you could bypass the info on the form completely and code the TextBox to use a DCount() expression.
- You could do the .Requery() as discussed, but make a note of where the current record is on the screen and which it is and return to it after the .Requery() has completed.
As mentioned earlier, this would be the subject of a separate thread.
I'm sure this isn't what you wanted to hear Laura, but it's the facts as I see them. I believe you'll find everything here to be accurate.
"My goal is still to get the Count textbox updating automatically when I either add or delete a record."
laura, to do update (requery) automatically, you need afterupdate even!, onclick event and on change, on keypress event with the same counttextboxname.Requery
let say I assume that we have these case:
1----- field/lbox/cbox on main, counttextbox on sub.
to update the counttextbox on sub, you have to requery the combobox/listbox that your counttextbox bind to. I mean you count something base on ID/code (client, supplier..ect)
then to update the count, you need to requery the ID/code field (that have link between child subform and parent mainform, if you don't have link, set it up).
2---- field/lbox/cbox on sub, counttextbox on sub.
this very ez, since those all the same subform. just add afterupdate event, onclick event this code
me.cbox/lbox.requery
or
me.controlname.requery
(same as case 1, if you want to updatetextbox, you need to requery the source it bind to, I suggest you bind it to the ID/code combobox/listbox)
the key to do the count that you have to set it controlsource like this =Cboxname.[column](1)
and set your cbox query 2 field from table: 1 is ID/code field, 1 is count field (any formula like count(tablename.fieldname) as ABC - SQL, if you use query design, it look like expr: count(tablename.fieldname))
and set column count property to 2, bound column property to 1
then whenever you change/requery data on cbox, the textbox will update too.
Edit: you can either try my suggestion or follow other help, try and fail give you more experience than any help.
NeoPa 32,556
Expert Mod 16PB
I don't want to see this thread go off-topic, so I would simply say here that any attempts to help are always appreciated. However, I don't believe much of what you posted is actually true. There may be something in there but I couldn't find anything. Please be very careful before posting, that you actually know what you're talking about as many will believe it to be the result of your experience and understanding. When people post what they simply guess and assume, this can be very damaging to others' trust in the forums, and this can effect many more people than just those posting.
If you want a better understanding of how these things actually work and what does and doesn't work then you can get that with a quick read of the thread so far.
@neopa:
sir, yes sir, I just think give direct solution would end the confused. but as you said, it would damage many thing.
I just answer everything that I know, to let you guy free hand ;) more hard core question will show up and I need more help lol.
@Laura:
I think you should use some image to show us more detail, or give us some more code to see what have you done uptill now, before we can give more advice.
I suggest you give all the code you have in Form's VBA.
NeoPa 32,556
Expert Mod 16PB
I understand. I've also seen some of the other threads where you've offered your help, so I know you're doing your best.
NeoPa - thanks for confirming that Requery is the only way forward. I was getting very frustrated as I thought I was missing something.
I have searched for information on how to bookmark my record and return to it after Requery and came up with the following:
I have created a public sub in the main form "Students" as follows ("Medical1" is the name of the control of the subform): - Public Sub RequeryMedical()
-
Me.Medical1.Requery
-
End Sub
Then on the Medical subform, I have added the following code to the After Update event of one of the text boxes: - Dim lngMedicalIDBookmark As Long
-
lngMedicalIDBookmark = Me.Medical_ID
-
-
DoCmd.RunCommand acCmdSaveRecord
-
Call Form_Students.RequeryMedical
-
-
With Me.RecordsetClone
-
.FindFirst "Medical_ID = " & lngMedicalIDBookmark
-
If .NoMatch Then
-
MsgBox "Record not found!", vbCritical
-
Else
-
Me.Bookmark = .Bookmark
-
End If
-
End With
This does successfully requery the records and update the count textbox, and then return to the correct record.
However, I would rather assign this code to an event on the subform properties itself rather than individual controls on the subform. I have copied and pasted this code and tried it on various events on the subform (dirty, before update, after update, before insert, afterinsert) but none of these work.
@laura: did you try onclick of subform ? I suggest you try onclick, onload, onactive, ongotfocus and ondata change
hvsummer - I don't think on click, on load, on active or on got focus are relevant to me requiring the action when I add or delete a record? If I put the code on onclick my screen will be constantly flicking whilst I'm moving around the subform. Isn't on data change just related to pivot tables?
I mean after you addrecord to the subform and saved record with "DoCmd.RunCommand acCmdSaveRecord", you click on anything out side those textbox or comboxbox, you force it requery with 1 click on somewhere out side input area
put code "me.medical.requery" in onclick event of form or detail ?
The problem is that the subform does not recognise that a new record has been added until records are navigated, therefore the code you suggest won't fire when I need it to. I need the count textbox to update as soon as data is inputted into a new record.
let test onkeypress, use "enter" as signal - Private Sub texboxnamehere_KeyPress(KeyAscii As Integer)
-
If KeyAscii = 13 Then
-
DoCmd.RunCommand acCmdSaveRecord
-
Me.medical.requery
-
End If
-
End Sub
NeoPa 32,556
Expert Mod 16PB
I did attempt to be very clear on this matter earlier Laura. Let me repeat myself here.
You are perfectly at liberty to ask about the matter of returning to where you were before in a form after a .Requery is done, but it must not be in an existing thread of another topic. This is true even if you're the OP of that thread.
You need to create a new thread within which to ask this question. At which point I'll be happy to jump in (When I see it).
Hi NeoPa. The topic of my thread remains the same - I'm trying to get my code to fire on a form event (insert and delete) instead of a control event. I'm not seeking advice on returning to my record after requery as I found code to do this. My code now works fine on an after update on a control on the subform but I want it to work on a form event.
NeoPa 32,556
Expert Mod 16PB
I see Laura. Sorry for my misunderstanding. The previous situation has been restored.
I must say I'm still very confused as to why the earlier posts haven't enabled you to find a workable solution. Furthermore, if that is the case as it seems to be, why it is you haven't posted anything explaining what hasn't worked where.
Subforms should work no differently in this respect than main forms do. There is a chain of steps that need to be taken for this to work. Where does the chain break down? - The correct events must be identified for the form to recognise when to take action.
- The form properties must be set to point to your code for these events.
- The code must (should) be contained within the module of the related form.
- The code must exist and be shown to run. It should do if step #2 is completed correctly.
- The code must include a call to Me.Requery().
If you have problems then we need to know whereabouts in order to help you deal with them.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Jason |
last post by:
I am creating a form that has a subform. The main form is unbound
with a combo box, list box and a textbox. I need to get the subform
(a form that is bound to a query) to automatically update...
|
by: Greg Strong |
last post by:
Hello All,
Is there a simpler way to count text boxes with data in them when the
4 text boxes are in the header of a form?
I've written the code below and it works.
,-----
| Private Sub...
|
by: sekisho |
last post by:
I'm dynamically adding a column of labels and a column of text boxes to
a panel on a webform, based on data returned from an SQL query, which
the user builds by selecting options from a few...
|
by: simonjackson500 |
last post by:
I have a field as follows:
aa_comp
03, 04, 05
03, 07, 05, 20
03, 06, 07
It is imported from xml (along with other fields), and I am aware of
multiple values in a field is bad normalisation.
|
by: syedakr |
last post by:
Hi to all,
I facing a small problem here,
where i have successfully retrieve the value from the dtabases and now i want to update/modify the same value from the same table cell ,where am trying,...
|
by: Smurfas |
last post by:
What's wrong?
Compiletor don't show anything, but whet I start program show this error:
"Error updating the employee details!"
protected void Button1_Click(object sender, EventArgs e)
...
|
by: Davy |
last post by:
The actual expression is '=(Count())+(Count())+(Count())+(Count())+((Sum()))'
When Quiz value is null/zero, the total is not generated
|
by: J Hall |
last post by:
Here is one I've been struggling with for a couple of weeks.
On a report I have a query that has records with both Check box fields and Test Fields.
I was able to get a sum of all the records...
|
by: Sunil Patidar |
last post by:
Hi Guys,
I am facing a problem in which the basket link text is not getting updated on adding products into the cart. I have checked the item size, price and currency are correct in the...
|
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...
|
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...
|
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...
|
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...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| |