How to put an update record button on an access form? (1 Viewer)

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)>

The behavior you are describing is non-standard. That means that you have code (or a macro) that is causing the problem. You will probably need to post the database (please remove any sensitive data) if you need help identifying the problem code.

I don't generally give the users a save button but it is not wrong to do so. When you want to employ a save button and force the users to use it, you will need code in several events to implement the feature but first you need to understand a couple of VERY IMPORTANT events.

The most important form level event is the BeforeUpdate event. This is the last event that runs BEFORE a record is saved. Think of it as the neck of a funnel. Regardless of what caused the record to be saved, this event executes for EVERY dirty record BEFORE that record is saved. Once you understand that, you can place your code at the bottom of the funnel (in this event) and catch all the validation errors. When you find an error there are several things you need to do.
1. display an appropriate error message
2. Cancel the update
3. Set focus to the control you want the user to fix
4. Exit the sub

sample code might look like:

To make the save button control the save, you need to define a form level variable. Call it bSave and define it as Boolean and there are lots of moving parts. Test carefully

In the form's Current event
bSave = False

In the form's AfterUpdate event
bSave = False

In the save button's Click event
bSave = True
Docmd.RunCommand acCmdSaveRecord

You also need error trapping code in this event because if the BeforeUpdate event is cancelled, this will raise an error. I think the code will be 2501 but you'll have to test to be sure.

In the form's BeforeUpdate event
If bSave = True Then
Else
If Msgbox("You did not press the save button. Press 'Yes' to Save and 'No' to Cancel and remove all changes.", vbYesNo) = vbNo Then
Me.Undo
Cancel = True
Exit Sub
End If
End If

In the Form's Unload event, you must prevent the form from unloading if there is an error.

If bSave = True Then
Cancel = True
Exit Sub
End If