Can't update unbound textbox

[ellenr]

My form has a button that opens another form on which I enter meeting RSVPs. Then that form is exited. I have an unbound text box on the main form that I want to show the total number coming. I have a summation query that totals the number. I have tried everything I can think of to force the text box to requery. I tried putting the dlookup in the textbox itself, then I removed that and tried all of the things below, both in the gotfocus event and the onCurrent event. What else can I do to trigger the recalc?


Private Sub Form_GotFocus()
Me.numcomingtxtbox.Value = DLookup("howmanycoming", "numcoming") & " Coming"
Me.numcomingtxtbox.Requery
Me.Recalc
End Sub

 

[DavidAtWork]

have you tried refreshing the main form when the other form is exited.
i.e. on the other form's close event, try Forms!frmMain.Refresh ... edit frmMain to the name of your main form

David

 

[ellenr]

Nope! That was a good idea but didn't work either.

 

[pr2-eugin]

How about Requerying the TextBox and then Refreshing the Form when the second form is closed? I would use DCount rather than DLookUp.

Private Sub Form_Unload(Cancel As Integer)
    Forms!ManiFormName!totalComingTxtBox = DCount("fieldName", "TableName") & " Coming"
    Forms!ManiFormName.Refresh
End Sub

 

[RainLover]

Add some message boxes in the code to see if the code runs and does it run to the end.

 

[Bilbo_Baggins_Esq]

hmmm, just some idle thoughts:
1. the "summation query"
Assuming this is a fixed query, are you sure the field name is correct?
I'd think it would fire an error if not, but unless you've specfically changed it, it could be "SumOf..."

2. I like the suggestion to add a msgbox break so you can step through it.

3. as a diagnostic, different approach, you could try a pulling a recordset in the VBA to get the total coming without using the query.
Assign the total to a variable and then apply that to the textboxes value.

 

[ipisors]

EllenR:
Syntactically speaking, there is nothing wrong with this code:
Me.numcomingtxtbox.Value = DLookup("howmanycoming", "numcoming") & " Coming"
IF howmanycoming is the name of the COLUMN from the query, and numcoming is the name of the query itself. I have to guess that may not be the case ...

And you do not need requery, recalc, repaint or anything else.
Also, it is correct only assuming that you have no criteria need in your dlookup (typically the 3rd argument which you are omitting, which means your query is intended to only produce one single record - is that correct ??)

 

[ellenr]

To answer ipisors: First, thank you for your help. I am just back home and will start in on this again shortly and will post whatever works, assuming something does! And, a big thanks to the rest of you who are willing to help.

I left all of the requery, recalc, etc. there to show what all I had tried.

IF howmanycoming is the name of the COLUMN from the query, and numcoming is the name of the query itself. I have to guess that may not be the case ...

...but it is the case I think. The only column in the query is: "HowManyComing: coming" and then it is set as a summation query. When the program is first opened, the field is populated correctly, and would not be if the dlookup were incorrect. Also, I can change the screen in question to design view and back to form view and it populates the text box correctly. I guess I could close the main screen when I open the memberlist screen, then reopen it when I come back to it, but that is clunky and inelegant and I shouldn't have to do it that way!

OK, now back to work!

 

[ellenr]

Hooray! In the onClose event of the membership screen, I did a setValue of the textbox on the main screen, using the dlookup function. Guess I should have started with this. I did put message boxes in the onCurrent and the gotFocus events of the main screen, however, and they never fired. If becoming the only screen open doesn't cause these events to fire, what does?

Thank you again to all who helped!