473,320 Members | 1,865 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,320 software developers and data experts.

Count text box on Subform not updating

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?
Sep 1 '15 #1

✓ answered by NeoPa

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 :
  1. 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.
  2. 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 :
  1. 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.
  2. 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 :
    Expand|Select|Wrap|Line Numbers
    1. Private Sub Form_AfterDelConfirm(Status As Integer)
    2.     If Status <> acDeleteOK Then Exit Sub
    3.     Call DoRequery
    4. End Sub
    5.  
    6. Private Sub Form_AfterInsert()
    7.     Call DoRequery
    8. End Sub
    9.  
    10. Private Sub DoRequery()
    11.     Call MsgBox("Call DoRequery()")
    12. End Sub
  3. 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.
  4. Try out the .Requery code in the DoRequery(). It should now look like :
    Expand|Select|Wrap|Line Numbers
    1. Private Sub DoRequery()
    2.     Call Me.Requery
    3. 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.
Sep 2 '15 #2
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
Sep 5 '15 #3
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.
Sep 7 '15 #4
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.
Sep 7 '15 #5
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.
Sep 12 '15 #6
Hi NeoPa

Thanks for continuing to comment on this thread.

On my main form I have added a Command Button with the code

Expand|Select|Wrap|Line Numbers
  1. Me.Medical1.Requery
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
Expand|Select|Wrap|Line Numbers
  1. Me.Requery
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
Expand|Select|Wrap|Line Numbers
  1. Me.Medical1.Requery
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
Expand|Select|Wrap|Line Numbers
  1. 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.
Sep 13 '15 #7
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 :
  1. 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.
  2. 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 :
  1. 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.
  2. 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 :
    Expand|Select|Wrap|Line Numbers
    1. Private Sub Form_AfterDelConfirm(Status As Integer)
    2.     If Status <> acDeleteOK Then Exit Sub
    3.     Call DoRequery
    4. End Sub
    5.  
    6. Private Sub Form_AfterInsert()
    7.     Call DoRequery
    8. End Sub
    9.  
    10. Private Sub DoRequery()
    11.     Call MsgBox("Call DoRequery()")
    12. End Sub
  3. 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.
  4. Try out the .Requery code in the DoRequery(). It should now look like :
    Expand|Select|Wrap|Line Numbers
    1. Private Sub DoRequery()
    2.     Call Me.Requery
    3. 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.
Sep 14 '15 #8
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
Sep 22 '15 #9
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.
Sep 23 '15 #10
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.
Sep 23 '15 #11
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.
Sep 24 '15 #12
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?
Sep 24 '15 #13
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 :
  1. A clumsy approach, but you could bypass the info on the form completely and code the TextBox to use a DCount() expression.
  2. 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.
Sep 24 '15 #14
hvsummer
215 128KB
"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.
Sep 24 '15 #15
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.
Sep 24 '15 #16
hvsummer
215 128KB
@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.
Sep 25 '15 #17
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.
Sep 25 '15 #18
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):

Expand|Select|Wrap|Line Numbers
  1. Public Sub RequeryMedical()
  2. Me.Medical1.Requery
  3. End Sub
Then on the Medical subform, I have added the following code to the After Update event of one of the text boxes:

Expand|Select|Wrap|Line Numbers
  1. Dim lngMedicalIDBookmark As Long
  2. lngMedicalIDBookmark = Me.Medical_ID
  3.  
  4. DoCmd.RunCommand acCmdSaveRecord
  5. Call Form_Students.RequeryMedical
  6.  
  7. With Me.RecordsetClone
  8. .FindFirst "Medical_ID = " & lngMedicalIDBookmark
  9. If .NoMatch Then
  10. MsgBox "Record not found!", vbCritical
  11. Else
  12. Me.Bookmark = .Bookmark
  13. End If
  14. 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.
Sep 26 '15 #19
hvsummer
215 128KB
@laura: did you try onclick of subform ? I suggest you try onclick, onload, onactive, ongotfocus and ondata change
Sep 26 '15 #20
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?
Sep 27 '15 #21
hvsummer
215 128KB
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 ?
Sep 27 '15 #22
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.
Sep 27 '15 #23
hvsummer
215 128KB
let test onkeypress, use "enter" as signal
Expand|Select|Wrap|Line Numbers
  1. Private Sub texboxnamehere_KeyPress(KeyAscii As Integer)
  2. If KeyAscii = 13 Then
  3. DoCmd.RunCommand acCmdSaveRecord
  4. Me.medical.requery
  5. End If
  6. End Sub
Sep 27 '15 #24
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).
Sep 27 '15 #25
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.
Sep 27 '15 #26
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?
  1. The correct events must be identified for the form to recognise when to take action.
  2. The form properties must be set to point to your code for these events.
  3. The code must (should) be contained within the module of the related form.
  4. The code must exist and be shown to run. It should do if step #2 is completed correctly.
  5. 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.
Sep 27 '15 #27

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

Similar topics

2
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...
2
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...
1
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...
0
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.
1
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,...
2
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) ...
4
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
7
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...
1
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...
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
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: 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...
0
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...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
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....
0
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
0
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...

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.