I am working on an Access project (.adp) using Access2k and SQL Server2k as the back-end. I apologize if this isn't the correct forum for this post.
I have a main form and a subform in my project that I am using for data entry into 2 tables that are in a one to many relationship. My problem is that I can't figure out a way to get the main part of the form to refresh after a record has been submitted into the subform. What I would like would to have happen is that after the user submits a record into the subform a field in the main form would get updated for the user to see. Right now all I have is a count field in the main form that shows how many records are in the subform but it doesn't update as new records are added in the subform. I would also like to add a text box in the main form that is either visible or not based on what the user enters in the subform.
To me this problem seems like it would be an easy one to solve if I was working with ASP or some other kind of web based db because I would have a big button that says 'Submit' on the subform and after it was clicked I would 'draw' another page based on the new data that went in to the table. But how can I accomplish this effect within an Access form?

I'm assuming that your forms are bound to the tables?

The standard way to do this is to hold the results of any calculations in hidden controls on the subform. These hidden controls can then be referenced on the main form and displayed to the user.

Here's a Knowledgebase article that gives more details:

http://support.microsoft.com/default...b;en-us;208998

Brian

Both my main and sub forms are based on tables that are in a 1-many relationship. The count field in the main form is based on a field that gets updated via a trigger on the table that the subform is based on. So the flow goes as follows:
So the flow goes as follows:
New record entered in the main form
new record filled out in the subform
click command button in the subform to add another record
sub form record gets entered into 'sub table'
'sub table' trigger gets fired
trigger updates count field in the main form table

and the missing step would be:
main form gets refresh with the correct value in the count field

I understand your suggestion and I think it would work. The problem I am having is that the main form does not refresh itself while the user is in the subform. All attempts at things like
Me.Parent.Refresh or Me.Parent.Requery
result in the main form (and subform) being totally refreshed to a new record

A relational purist would frown at holding calculated data as a field in your database - but assuming there's agood reason for doing it this way, I can see a couple of possibilities:

1) After the sub form saves the record, save the UID of the main record, requery both forms and then move back to the correct record.

2) Unbind the control that displays the calculations from your recordset. Write a function to retrieve the count from the database. You can then run this function and update the control whenever you need to.


Brian Skelton
Braxis Computer Services Ltd.

Those are great suggestions Braxis. Thank You.

I agree with you about not storing calculated values like counts. The reasoning this time is that these tables are often exported separately and used for different purposes elsewhere. And mostly because that is what the boss wants.

Option 2 that you mentioned seems like less work to me. Can you use DCount against the server tables or would I have to write a stored proc?

And thanks again.

Option 2 is definitely simpler!

I don't know about DCount - not something I've used in any version of Access.

I would use a SP and return the count in the SP's RETURN value.


Brian Skelton
Braxis Computer Services Ltd.

I am having trouble with forms refreshing too but for your application I would suggest refreshing the control on the main form on the AfterUpdate Event of the subform if that is possible. for instance if you have a control called txtNoOfOrders and it is based on say a DCOUNT of the fields in the subform. I'd just refresh that field or recalculate that field. But if the underlying data source for the form is a table and not a query this will not work. Refreshing the data source depends on the if the source is dynamicly opened and it cannot be a table, can only be an SQL statement, or Query.