Solved Help with form VBA (1 Viewer)

[slharman1]

I have a form with a couple of sub-forms on it. When I modify the main form and then try to go to the subform, the main form looks like it is being edited (pencil in the record selector) I cannot move to the subform and edit it without closing the form and re-opening it. clicking on the record selector for the main form record doesn't save it (still has pencil in record selector)
What could i have set wrong?
Thanks

 

[theDBguy]

Any way you can share a sample copy of your db?

 

[slharman1]

Any way you can share a sample copy of your db?

How do I do that?
Also lots of records in it.
I can make a copy and delete a bunch of records to make it smaller.

 

[theDBguy]

How do I do that?
Also lots of records in it.
I can make a copy and delete a bunch of records to make it smaller.

Exactly! You only need to include enough objects to demonstrate the problem you're trying to solve. Also, remove any sensitive data but add enough test data to keep the sample db functional. Do a Compact & Repair then zip it up and attach it to your post.

 

[Pat Hartman]

Are you getting an error message? Perhaps it is on a different screen or hidden under the database window.

You could try hitting cntl-break and looking to see where the code stopped.

 

[slharman1]

Exactly! You only need to include enough objects to demonstrate the problem you're trying to solve. Also, remove any sensitive data but add enough test data to keep the sample db functional. Do a Compact & Repair then zip it up and attach it to your post.

Ok, Finally got this ready to post. Open the frmEstimateList, double click on an estimate, edit the frmEstimates main form and you will see you can not get the record to save and cannot edit the subforms.

 

[Pat Hartman]

You created an infinite loop by dirtying the record in the form's AfterUpdate event. Move the code from that event to the form's BeforeUpdate event.

The form's AfterUpdate event runs AFTER the record has been saved so if you dirty the record in this event, you create an infinite loop.

You also have an error in the AfterUpdate event of the note control. That's as far as I looked.

 

[slharman1]

Thank you. I will check it out. We don’t like infinite loops.

 

[slharman1]

You created an infinite loop by dirtying the record in the form's AfterUpdate event. Move the code from that event to the form's BeforeUpdate event.

The form's AfterUpdate event runs AFTER the record has been saved so if you dirty the record in this event, you create an infinite loop.

You also have an error in the AfterUpdate event of the note control. That's as far as I looked.

How did you find the error in the note field?

 

[Pat Hartman]

That was the field I elected to change So, when it didn't work even after I fixed the infinite loop, I looked at the code.

 

[slharman1]

That was the field I elected to change So, when it didn't work even after I fixed the infinite loop, I looked at the code.

Thank you

 

[arnelgp]

That was the field I elected to change So, when it didn't work even after I fixed the infinite loop, I looked at the code

the fix is remove the code from the AfterUpdate and instead use BeforeUpdate
to set the DateModified field to just Date (not Now()).

also the property Default value is =Date(), not just Date()

see your form.

 

[slharman1]

the fix is remove the code from the AfterUpdate and instead use BeforeUpdate
to set the DateModified field to just Date (not Now()).

also the property Default value is =Date(), not just Date()

see your form.

VBE keeps removing the parentheses after Date. So Date() becomes Date. It is working but why does it do that?

 

[Pat Hartman]

You're welcome.

VBE keeps removing the parentheses after Date. So Date() becomes Date. It is working but why does it do that?

Beats me. It is inconsistent (and that makes it bad) since Date() is the only function where the parentheses are automatically removed.

 

[slharman1]

You're welcome.

Beats me. It is inconsistent (and that makes it bad) since Date() is the only function where the parentheses are automatically removed.

So is this a known issue?

 

[Pat Hartman]

I don't believe MS considers it an issue. It is more like a "feature". It does not have any ill effects. It is simply inconsistent and therefore confusing. Access has far worse issues that have existed for 20 years that will never get fixed. Don't hold your breath waiting for action on this one.

 

[arnelgp]

if you already add Default Value to your Table/Form using:

Default Value: =Date()

there is no need to set the value again through VBE.

 

[Pat Hartman]

Using a default value works only for new records. If you want to update the date each time you update the record, you need to use the Form's BeforeUpdate event or a Data Macro.

 

[slharman1]

I don't believe MS considers it an issue. It is more like a "feature". It does not have any ill effects. It is simply inconsistent and therefore confusing. Access has far worse issues that have existed for 20 years that will never get fixed. Don't hold your breath waiting for action on this one.

Thanks, I did not know this was a regular occurrence.

if you already add Default Value to your Table/Form using:

Default Value: =Date()

there is no need to set the value again through VBE.

when I update pricing I want to save the date.