Update Form / Query data in real time

[Pete-RM]

I have created a database to develop meal plans and apply them to different days.

I have a form called 'frmMainDay' which allows me to apply different meals to specific days, along with 4 total fields for each meal (this is done by navigating the subform 'frmSubDay') (there are 4 totals because I am going to use this as a template for various databases). Note that the 4th total simply sums the other 3 totals together.

My main form contains fields that sum the total fields in the subform. This may sound confusing so I will provide an example (assuming that you have my db. file open).

For example, the 'DayTotal' field on the main form sums the 'Totaltest' field on the subform. So, for example, on Day 1 the 'DayTotal' field would equal 102+550 because there are 2 meals on this day.

The total fields on the main form (DayTotal, etc) are generated through the query 'qryDayTotal'.

Here is my issue:

When I enter a new meal into my form (i.e. a new row in the subform) the fields 'DayTotal', 'DayTotal2', etc on the main form do not update with the new totals. They only update if I close the form and then re-open it.

Please help me to figure out a way to make the fields on the main form update in real time without having to close the form or go to the next record.



Also please note that I am very new to Access and have basically no knowedge of Visual Basic or writing code.

All help is appreciated.

the db. file is attached

[rpeare]

This not a super great design for you but you can get what you want by clicking on the subform and adding this to the AFTER UPDATE event of the subform:

forms!frmmainday!list27.requery

This should force a refresh after a new item is added to your table storing the items in the subform.

[Pete-RM]

This not a super great design for you but you can get what you want by clicking on the subform and adding this to the AFTER UPDATE event of the subform:

forms!frmmainday!list27.requery

This should force a refresh after a new item is added to your table storing the items in the subform.

Thanks that helped a lot!

Just putting the formula into the AFTER UPDATE event didn't work but it worked when I also added it to GOT FOCUS or LOST FOCUS. (Note: I also had to add an equals sign to the front of the formula)

I have a small new problem now, the first time I select a combo box it closes automatically.

This does not happen if the field is already selected (i.e. if I tab to the field first).

I assume it closes because the form is refreshing when the field 'Gets Focus'.

Is there any way around this?

Also note that I did not have to apply the formula to the whole subform, it worked when I just applied it to the first 'MealID' field in the subform.

[rpeare]

the GOT FOCUS/LOST FOCUS events happen when the form becomes or loses it's 'main' item status. For instance if you have two forms open and you tab between them, the GOT FOCUS for the form will trigger every time you tab to the form. If you are using code you do not need an equals sign, that's why I didn't put one in. To play around with WHEN your code is triggering you can put notes in your code like

msgbox "GOT FOCUS IS TRIGGERING HERE"
msgbox "LOST FOCUS IS TRIGGERING HERE"

and so on, so you can pick out the appropriate 'break' point to issue your refresh command.

This is, of course assuming you're using vb code and not a macro.

[Pete-RM]

Is there a way to requery as soon as I select an item from the 'MealID' combobox and prevent getting the issue I described above?

[rpeare]

You can use the ON CHANGE event of MealID, however I would most likely use the ON EXIT event, ON CHANGE doesn't always function the way I want and ON EXIT or AFTER UPDATE might give you a better result.

[Pete-RM]

You can use the ON CHANGE event of MealID, however I would most likely use the ON EXIT event, ON CHANGE doesn't always function the way I want and ON EXIT or AFTER UPDATE might give you a better result.

I tried this but got the same issue that I got with the got focus command. I guess it isnt that big a problem anyway, thankyou for getting me this far