Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 4th, 2003, 09:07 AM
Authorized User
 
Join Date: Jun 2003
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default Access project refresh

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?
 
Old June 5th, 2003, 07:47 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 120
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old June 5th, 2003, 08:18 AM
Authorized User
 
Join Date: Jun 2003
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old June 6th, 2003, 03:20 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 120
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
 
Old June 6th, 2003, 08:08 AM
Authorized User
 
Join Date: Jun 2003
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
 
Old June 6th, 2003, 11:23 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 120
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
 
Old July 16th, 2003, 11:59 AM
Registered User
 
Join Date: Jul 2003
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Access project luissilva755 Pro VB.NET 2002/2003 0 October 24th, 2008 11:46 AM
Access project and Subforms amg51 Access 1 May 17th, 2007 11:49 AM
Access good for a Project? Maxss280 Access 1 March 6th, 2007 08:40 AM
Remote access to an Access Project Database bright_mulenga Access 0 February 9th, 2006 10:51 AM
From 1 project to get access to another project hplim18 Pro VB.NET 2002/2003 0 March 30th, 2004 10:33 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.