Update data in table from Form

[jpl458]

I have non-data entry form that is based on a table. I would like the users to be able to change the data in the underlying table from changes made to the data in this form, particularly delete data from a text box then have that change made to the table. I already have a button that fills a listbox with data from the table, and the user can double click a row in the listbox and the data fills the columnar textboxes. The form Data Property looks like this:

But if I delete data from the columnar text boxes the table does not get updated. Can't find anything on the web,(that doesn't mean that it is not there)
Do I need a button to update..

Clueless

Thanks

 

[theDBguy]

Just to confirm, is your form bound or unbound?

 

[jpl458]

Just to confirm, is your form bound or unbound?

It is bound to a table, the table I would like to be able to update from the form. I just read an article that describes a complicated way of updating a table from a form with a lot of keyboard presses. I would like it to be simple, so that non-techs can use.

 

[Pat Hartman]

But if I delete data from the columnar text boxes the table does not get updated

Not sure what that means. Unless you are specifically preventing Access from saving your changes, Access will save them when you close the form or move to a different record.

There is something we are missing or the control in question isn't actually bound. Access takes it as a personal mission to always save all data. Therefore, newcomers are much more likely to not be able to stop Access from saving a dirty record than to have to force it to save since saving is automatic by design.

 

[theDBguy]

It is bound to a table, the table I would like to be able to update from the form. I just read an article that describes a complicated way of updating a table from a form with a lot of keyboard presses. I would like it to be simple, so that non-techs can use.

If the form is bound, then there's really nothing you need to do, since Access will automatically save the record for you when you close the form or move to a different record.

 

[The_Doc_Man]

But if I delete data from the columnar text boxes the table does not get updated.

Just for a point of clarification, that is not a deletion, it is an edit. (Removing a whole record would be a deletion.) However, I have to agree with Pat. Changing the contents of a bound field in a form bound to a table should result in a change to the underlying record. You were correct to show us the "Allow" settings because what they show SHOULD allow the edit. If there is no "Save" button then at least the change should occur when you navigate to another record OR when you close the form.

Is there any code in the Form_BeforeUpdate event that might try to undo certain changes or apply some defaults?

 

[jpl458]

Not sure what that means. Unless you are specifically preventing Access from saving your changes, Access will save them when you close the form or move to a different record.

There is something we are missing or the control in question isn't actually bound. Access takes it as a personal mission to always save all data. Therefore, newcomers are much more likely to not be able to stop Access from saving a dirty record than to have to force it to save since saving is automatic by design.

Pat, it's like that snake that keeps biting me. I just tried deleting a field then move to a different record, and Voila!
Thanks for all the help. The more I use Access the more I like it. I don't understand why some say it is dead(I have a son who is a Python wizard and he looks down on ACCESS.) I told him that when COBOL was being widely used I looked down on those folks because I was an assembler guy, and used to say that Cobol is nothing but second rate packaged Assembler code. Thanks again.

 

[jpl458]

Just for a point of clarification, that is not a deletion, it is an edit. (Removing a whole record would be a deletion.) However, I have to agree with Pat. Changing the contents of a bound field in a form bound to a table should result in a change to the underlying record. You were correct to show us the "Allow" settings because what they show SHOULD allow the edit. If there is no "Save" button then at least the change should occur when you navigate to another record OR when you close the form.

Is there any code in the Form_BeforeUpdate event that might try to undo certain changes or apply some defaults?

Just answerd Pat's post. You were both correct, as usual.

 

[theDBguy]

Just answerd Pat's post. You were both correct, as usual.

Glad to hear you got it sorted out. Good luck with your project.

 

[Pat Hartman]

It is the SQL Server people who keep spreading the rumor about "Access" being dead. But that's because they can't tell the diference between Jet/ACE and "Access". They think that if Jet is dead, Access must be dead. BTW, COBOL is alive and well and probably producing your phone bills

 

[The_Doc_Man]

I was an assembler guy myself. To me, it was the original C language (not C++, C#, or other variants) that was an overgrown assembler. Because it actually WAS. The predecessor of the C language was a set of macros for PDP-11 MAC assembler.

 

[jpl458]

Just answerd Pat's post. You were both correct, as usual.

I was just musing about the users, and have an update button might be easier for them to remember and manage. What would go into a button to update the table.

 

[jpl458]

It is the SQL Server people who keep spreading the rumor about "Access" being dead. But that's because they can't tell the diference between Jet/ACE and "Access". They think that if Jet is dead, Access must be dead. BTW, COBOL is alive and well and probably producing your phone bills

Back in the day I designed a general ledger system, and a physical inventory for a ball bearing company that supplied all the ball bearings to the auto industry. Thinking back, doing some of it in Access instead of 360/370 Assembler would have been a lot easier. But that was when mainframes had tape drives, and there were key punchers, and no monitors.

 

[theDBguy]

I was just musing about the users, and have an update button might be easier for them to remember and manage. What would go into a button to update the table.

You could try something like:

If Me.Dirty Then Me.Dirty = False

 

[jpl458]

You could try something like:

If Me.Dirty Then Me.Dirty = False

That's the ticket! What's the /code? is that where I am supposed to put some code? I just pasted it and it works great.

 

[theDBguy]

That's the ticket! What's the /code? is that where I am supposed to put some code? I just pasted it and it works great.

Glad to hear you got it to work. Good luck with your project.

 

[Pat Hartman]

Access has a personal mission to save data. It is very hard to get Access to NOT save data unless you know enough to use the form's BeforeUpdate event to do the validation and only allow valid data to be saved

I spent over 30 years designing and developing COBOL (mostly CICS and IMS) applications and there is not a single one of them that could not have been created in a fraction of the time using Access. The problem comes down to Access not being an environment that easily supports multiple developers.

I was so frustrated by the time it was taking the web team to build the FE application for one client (we're talking months here for a multi-programmer team) that I took the specs and built an Access app one weekend at home and showed it to the manager. Too bad the app had to be a web app or they would have gone with my Access version