How to put an update record button on an access form?

Is it possible to add an update record button to my Access form? I want to be able to update an existing record in my database. I all read have a record search combo box on my form, that auto-fills the form with the info from my table. I would really appreciate the help.

If your Access form is bound the data will automatically be synchronised with the table.
If its unbound, why are you making life harder than you need to?

ridders said:

If your Access form is bound the data will automatically be synchronised with the table.
If its unbound, why are you making life harder than you need to?

Click to expand...

The access form is bound, however currently when I try to change a clients address it creates a new record on my table. I want to be able to add new clients with my form and update current client information.

Here is the trick. You have to diddle with the File >> Options >> Current Database settings to select that your form DOES NOT step to a new record if you tab through the last field on the form. Then you implement command buttons to do a Save or an Undo (they might call that a Cancel). The button wizards will build the scaffolding but there is absolutely zero reason to not customize it a bit.

As part of the save button event code, set a flag that is in the class module declaration area. Then have a BeforeUpdate event routine that checks the flag and if the flag is not set, cancel the event (which will cancel the update). Then if you DO allow the update to occur, the Form_Current routine must CLEAR the flag.

You can also if you wish build a NEW RECORD button (which, when I used it, I called Create, just to have a short word for it). You would NOT set the flag for a Create.

If you REALLY want to get picky, you have the Save button include code to validate all fields before it allows the Save action to occur. Again, this will fit nicely into any event code built for you by the Command Button wizards.

Here is a "gotcha." IF you have a form and sub-form together, if you try to enter data in the sub-form AND the parent and sub records have a formal relationship between them, Access will automagically create a parent-form record if you have enabled relational integrity. This is because of the requirement that you cannot store a child record unless it is accompanied by a parent.

Thanks The_Doc_Man, for your reply! I am new to Access and I don't know how to write event procedure codes. I don't want my form auto save records. I only want it to save records to my table when I hit the new record button. I also, want it to update existing records in my table, when I hit the update records button. I would greatly appreciate it if you could tell me exactly what codes I should write, and where should I put them. I have attached a screen shot of my form. Thank you for all of your help.

mgui said:

...I all read have a record search combo box on my form, that auto-fills the form with the info from my table...

...when I try to change a clients address it creates a new record on my table...

Click to expand...

When a scenario like this is posted the problem is usually that the search Combobox is Bound...it needs to be Unbound...i.e. its Control Source Property needs to be blank/empty.

In Access, there is no real need for a 'save' or 'update' button...in Bound Forms. Moving to another Record, closing the Form, or Closing Access, itself, will automatically save New Records or save changes made to existing Records, with no user interaction required.

Linq ;0)>