Continuous form not updating table (1 Viewer)

[mcomp72]

I have a continuous form, but when I make a change to it, it doesn't update the table that is tied to it. I decided it was easiest to make a video showing what was happening.

I'm new to Access & databases, so I'm guessing the fix is something really basic that I don't know about yet.

 

[Gasman]

Record has not been saved yet?
Move to a new record and see what happens then.

Excellent description of issue BTW

 

[mcomp72]

Ah... yes, that was it. When I clicked into another record on the form, that fixed it.

Is there a way to have Access update the record immediately after the user clicks into a checkbox? I am guessing I could put some VBA code into the AfterUpdate sub, but I'm not sure what code I would put in.

 

[mcomp72]

I did some digging around the internet and found the code. If I put the below code in the AfterUpdate sub, it solves the issue.

If Me.Dirty Then Me.Dirty = False

Thank you for the help!

 

[CJ_London]

Just curious - what if the user has clicked a checkbox, and the clicks it again - what do you want to happen?

 

[mcomp72]

Just curious - what if the user has clicked a checkbox, and the clicks it again - what do you want to happen?

When they click that checkbox, it will trigger some code to make a change to another YES/NO field on the Member table called "CurrentOnDues". If they have checked the "Board Member" checkbox, "CurrentOnDues" should change to True. If they have unchecked the "Board Member" check box, it should run some other code. In that case "CurrentOnDues" could be True, but also could be False, so it has to check the date in the "LastDuesPaid" field to determine which it is.

 

[CJ_London]

Sorry I asked
I’m sure it makes sense to you but without any context, not to me

I was thinking along the lines of if the user unticked the checkbox, does the record need to be deleted?

 

[Pat Hartman]

I prefer to not force a record to be saved this way. Usually, the user will tab or progress through the controls in a logical fashion so if the checkbox is the "last" control in the tab order, it seems like this code would not be harmful. But what happens if for some reason, the user clicks the checkbox a the first change to the form? Now, you are going to force an empty record to be saved. Of course if you have solid validation code in the Form's BeforeUpdate event, the bad record will be prevented from being saved. You do validate the data in the form's BeforeUpdate event to prevent this, don't you?

If the user wants to be able to force a save, I give him a button. That way he KNOWS that he is attempting to save a record and will not be mystified if he gets some error message about missing or invalid data.

 

[mcomp72]

My continuous form doesn't allow new records to be created. So I don't think there is a worry of an empty record being saved. Unless I am not properly understanding what you mean.

If you go to :17 of the video I posted in the first post, you'll see the form. The only way the user can create a new record that would appear on that form is by clicking the ADD MEMBER button. That will open another form where they can type in data for a single record, but only that one record. There's a SAVE button on that they must click before the record is saved. There's also a CANCEL button, which will disregard anything they've typed into the form.

 

[Pat Hartman]

Is there a way to have Access update the record immediately after the user clicks into a checkbox? I am guessing I could put some VBA code into the AfterUpdate sub, but I'm not sure what code I would put in.

This is the statement I was referring to and I was explaining why I think it is a poor solution. Your focus is too narrow. At some point that form allows additions and you would NOT want code in the AfterUpdate event of a checkbox that forces the record to be saved. You are seeing a problem that doesn't exist. Also, the AfterUpdate event doesn't run until the control loses the focus so your "problem" would still exist.

Access makes it a personal mission to never lose data and so it takes it upon itself to save records when it things they need to be saved even if you didn't give it a specific save command. For example as @Gasman suggested, clicking into a different record caused the previous record to be saved. YOU didn't have to do anything. Most people get a little crazy about trying to keep Access from saving a record that they don't want to save. That's where knowledge of how form events work comes in handy. The form's BeforeUpdate event is the LAST event to run before a record is saved and is the best place to put your validation code so you can ensure that only valid records are saved. Valid is wishful thinking in some cases because humans can still make data entry errors. If something is red but black is a valid option, there is no way for the application to determine that the user made a mistake.