Access project refreshes?

[VBAhole22]

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?

 

[dcx693]

To refresh the main form after a new record has been entered into the subform, try entering this code in the After Insert event of your subform:
Me.Parent.Refresh

To change the visibility of a control on a main form from a subform use this type of code:
Me.Parent!NameOfYourControl.Visible = False
Just use some code in the appropriate event in your subform to check for the correct conditions using an If...Then structure.

 

[VBAhole22]

Not exactly the desired result from that.
What it did was refresh everything, moving me to a new record on the main form and sub form.

What i did was add the me.parent.refresh line at the end of some code that I have on a command button that says 'add another record' on the subform. What the command does is some validation on the entered fields and if they pass it says

DoCmd.GotoRecord , , acNewRecord

This gets me a new blank record on the subform.
I then put the parent refresh code after that. But it gives me a new blank record on the main form. I would like the main form data to remain static aside form the fields that change due to the addition of the new record (these fields change as a result of a trigger on the table that the subform is based on that makes updates to the table that the main form is based on).

 

[dcx693]

How about using Requery instead of Refresh?

 

[VBAhole22]

Same result.

Funny thing that Intelli-nonSense doesn't pickup on the methods of Me.Parent after you type the period after parent, but it will capitalize the R after you exit the line.

Anyhow, not sure what else to try. Could I do the Requery and then query again to get the right record in the main form? This seems like overkill for a simple task.

Boy, I am going to scream when they come to me in 6 weeks and say, "we want it to be web based now, can you do it in ASP".!!!!

It seems like that would be easier.

 

[dcx693]

You said you had a count field in the main form that lists the number of records in the subform. What is the control source for that field?

 

[VBAhole22]

The control source for the count field is a field in the table that the main form is based on.

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

and I do appreciate you helping me out with this.

 

[dcx693]

No prob.

The control source for the count field is a field in the table that the main form is based on.

Hmm...so the count is a stored value coming from the table that is the recordsource for your form. Now how does that field get updated in the original table? Seems to me that table would need updating before the form would reflect the change.

I would suggest using a DCount function instead of trying to reference a stored value for the count. One big reason is that storing something a calculated value is usually not a good idea. The DCount function (albeit slow) would be dynamic and should recalculate without needing refreshing. Look it up in the Access help.

 

[VBAhole22]

I agree with you that storing a count is not a very wise idea, but that's what the bossman wants in this case. We often split these tables apart and use them for different purposes so I guess that is the reasoning behind that.
I do know that the table behind the main form is getting updated via the trigger everytime a new record goes in the subform. If I exit these forms and then go back into the main record using my 'edit records' form then this count field is correct. It's just that the main form in my 'add records' setup never gets refreshed while the user is fiddling in the subform.
I'll give DCount a try but I'm unsure if that works with server tables. If it does then I'll be happy because the field won't need to be bound at all.
The rationale behind the trigger method of updating this count field was to move the coding for the process off of the front-end (as VBA) and onto the server (as a trigger). I did this because I didn't want the counts to get out of synch if someone subverted my forms and went right to the tables to edit something. Does that sound like it makes sense? The only problem I have now is if I make bulk deletes in the table the trigger sometimes freaks out.

 

[atrapp]

Disable Trigger

I know this is an old post but the solution to stop the trigger from screaming on a bulk delete would be:

alter table [TableName] disable trigger [DeleteTriggerName]
Delete statement here
alter table [dbo].[TableName] enable trigger [DeleteTriggerName]

HTH