Updating =Count(*)

[123James]

Hi everyone

I have a form based on a table

The form includes a subform displaying data from a related table

The subform has a record count in the footer =Count(*)

I have limited the number of records that can be entered into the subform to 3 by looking at the =count(*) in the BeforeInsert event

This works fine however...

If I enter a new record in the subform, the count in the footer does not update until the main form has been refreshed. I am not able to get the form to refresh or the count to update automatically!!

Please help!

 

[boblarson]

The problem lies in that the record doesn't actually exist until after the form's AfterUpdate. So, it can't be counted since it technically doesn't exist. You might need to investigate setting your own variable to the current count when you enter the subform and in the form's ON Dirty event increment that count by one and then do your check.

 

[CEH]

James,
I'm not sure why you cannot get your results.... It worked for me.... I have a supply order form..... Customer info on main form, items on subform.... I added an unbound textbox on the subform (not footer)... and used =Count(*) as source.....
It updates the number with every record I enter. If I wanted to show it on the main form I would just used another unbound and reference the subform. Then you could code to show error if it exceeds 3......

 

[boblarson]

James,
It updates the number with every record I enter. If I wanted to show it on the main form I would just used another unbound and reference the subform. Then you could code to show error if it exceeds 3......

Does it show the count with each record you START entering, or does it show up AFTER the record has been updated/saved? I think, from what I saw of his post, that he wants to know the count when the record is STARTED, not after it is updated.

 

[CEH]

Well, as I said mine is a supply order form..... Pick the customer on the main form and then enter the items they want to purchase or order in the subform. After you enter each item and move to the next entry... or off of the subform, the count updates. It sounded to me like he was having to refresh the main form for the update to occur.... I wouldn't see any point in having it count a record that was not complete... And might cause problems.. If you hit the tab key after the last field... it would add another record that was not there.. But ey.... Maybe I'm reading it wrong.