Text Box wont refresh

[music_al]

Hi

Can anyone tell me why the text box underlined below wont refresh when I re-open the form and refresh it.

DoCmd.OpenQuery "Pupil_Absenteeism_All_Query", acViewNormal, acReadOnly
DoCmd.Requery
DoCmd.OpenForm "frm_Pupils"
txt_Absence.Value = Me!Pupil_Absenteeism_Sum_Query_Subform.Form.sumofno_half_days
Me.Refresh

 

[Scooterbug]

Pupil_Absenteeism_Sum_Query_Subform.Form.sumofno_half_days
Me.Refresh

Correct syntax for referencing a control on a subform:

forms!FormName.SubFormContainerName!ControlName

Also, do a requery, not a refresh

 

[boblarson]

Correct syntax for referencing a control on a subform:

forms!FormName.SubFormContainerName!ControlName

Actually

Forms!FormName.SubFormContainerName.Form.ControlName

(red part should be in there too. It tells Access you want something on the subform and not on the subform control)

The .Form. part is explicitly .Form. not the name of a form or anything.

 

[music_al]

thanks bob

 

[music_al]

well I tried it and using "Forms" instead of "Me" has resulted in the form not being found ??

Also, the requery seems to put the code into a loop as it starts the Current event which in turn triggers the Sub I was in, hence the loop...

 

[boblarson]

If the code is ON the main form then you would use

Me.YourSubformControl.Form.Whatever

Also, what is this requery supposed to do for you? The initial post also makes me wonder why you are opening the query. It isn't necessary to open a query for a form to use it. What is the open query part for?

 

[music_al]

The query is to return the total number of days absent a student has had. After the query is run, the number isnt being refreshed in the text box.

I thought there might be an easier way of referencing a query without opening it. How do I do this ?

Thank you in advance


Al

 

[boblarson]

The query is to return the total number of days absent a student has had. After the query is run, the number isnt being refreshed in the text box.

Why would the text box be refreshed from the query? Is the form based on that query? If so, you don't need to open that query just requery the form.

And after looking again, I think you may need to requery the main form and the subform but also not have the code:

txt_Absence.Value = Me!Pupil_Absenteeism_Sum_Query_Subform.Form.sumofn o_half_days


But instead set the control source of txt_Absence to the subform box. But the better thing would be if you can post the database here and we can take a look. I think you're going to great lengths for something that could simply be done in another way.