Solved

Control Updating Records using a Form - MS Access

Posted on 2016-10-19
10
31 Views
Last Modified: 2016-10-20
I have an Access database with a bound form for adding records to a table.  The form has a bunch of textboxes and comboboxes on it for entering and selecting data.  Once all required boxes are populated, the user clicks a command button to add a record.

Before a record can be added, many checks for valid data in the boxes are performed under the on click event of the ‘add record’ command button. Some of the checks include comparing what was entered into one box with what was entered into another box; as there are restrictions on what combinations can be used.

I’m now trying to add record update capability to the form.  The problem is in its current state, I’m able to go into any box I want to, make a change and that change is automatically saved.  That’s no good; as it bypasses all of the data checks that are performed when a record is added.

Ideally I’d only like changes made to values in boxes to be saved if I click a command button. If the command button is not clicked, then the changes don’t get saved.  This way, I can run all of the same code in the update button's on click event as I do for the add button's on click event

So, for example, let’s say an existing record is displayed through the various boxes on the form. I then make changes to some of the values in the boxes but DO NOT click the ‘update record’ command button.  If I then navigate to a different record and then come back to the record in question, I’d like to see that the values reverted back to what they originally were.  Or…if I change values but do not click the update button and then close the application, if I open it back up, I’d like to see the original values still there.  In other words, I’m trying to control updates through the use of an ‘update’ command button.  How can I accomplish this? Thanks.
0
Comment
Question by:dbfromnewjersey
  • 6
  • 4
10 Comments
 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
ID: 41850282
The validation code belongs in the form's BeforeUpdate event.  It should not be coded into other events.  The button on the form should be labeled "Save" rather than "Add" since you want to use the same form for adding and saving.

This is the same advice I gave you in your other thread.  As long as the validation code is in the CORRECT event, it will run for both adds and updates.  Occasionally, you might need slightly different logic for adds than updates.  In that case, use the Me.NewRecord property to identify which record is an "add".

If you do not ever want the record to be saved unless the user presses YOUR save button, then you need to add a form level variable.  In the Form's Current event, set the boolean to False.  In the click event of the button set it to True and then force the record to save by using either :
If Me.Dirty Then
    DoCmd.RunCommand acCmdSaveRecord
End If

OR

If Me.Dirty Then
    Me.Dirty = False
End If

Then in the BeforeUpdate event, the first code will be:

If bSave = True Then
Else
    Cancel = True
    Msgbox "Please Press the save button to save the record.",vbOKOnly
    Exit Sub
Endif

In the Form's AfterUpdate event, set the bSave variable back to False

You will also need code in the Unload event to cancel the closing of the form if the record is dirty but unsaved.
0
 

Author Comment

by:dbfromnewjersey
ID: 41850630
OK. I rearranged the placement of the code the way you suggested.  Here's the latest problem:

From my monitoring the flow of the code, it looks like when the save button is clicked (and at least one of the boxes on the form has data in it...ie the form is dirty), the DoCmd.RunCommand acCmdSaveRecord line is hit. Then it jumps to the code in the Before Update event.

Within that Before Update event, it's currently attempting to save the record whether all fields that require data actually have data in them or not.  

The way I have the code set up is, let's say for example the user entered data into textbox1 but failed to enter data into textbox2.  My code, which I originally had under the save button's on click event,  set focus to textbox2, sent a message to the user that data is required to be entered into that box and exited the sub. The "save record" code was all the way at the bottom of the on click event, so it was never hit until the user properly populated all required boxes.

So, now if a user fails to enter valid data in a required box, I need to somehow cancel the DoCmd.RunCommand acCmdSaveRecord command, which I don't know how to do.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41850737
Your validation code should not depend on the sequence of data entry unless one field is required for another to accept a value.  In that case, fldB's BeforeUpdate event should ensure that fldA has valid data.  You will also need to repeat at least some of this code in the Form's BeforeUpdate event since if a user clicks into fields rather than tabbing into them, not all controls might have received the focus and so not all of the individual events would have been triggered.

For example, if I have a form that takes a start date and an end date and the start date must be <= the end date but not by more than two weeks, I would put the validation code into the form's BeforeUpdate event.  Otherwise, I would need to put it in both the start and end date's BeforeUpdate events AND I would have to account for the other field possibly not having yet been entered.  How complicated do you want to make the code in order to give the error message as close to the point of entry as possible?  You can create little subs that do this type of validation so as to not duplicate the code but you would need to call the date validation from three events.  Calling the code from just the start and end date BeforeUpdate event is insufficient if both dates are required since those events would not fire if those controls were never entered.  So, the bottom line is - most if not all validation code belongs in the Form's BeforeUpdate event.  Additionally, you might elect to duplicate some of the code in control level events to put the error message closer to the point of data entry.

Remember, when validation fails, you MUST cancel the event.

If Me.txtStartDT <= Me.txtEndDT Then
Else
    Cancel = True
    Msgbox "Start Date must be <= End Date.",vbokOnly
    Me.txtStartDT.SetFocus      'only if this code is in an event OTHER THAN an event associated with txtStartDT
    Exit Sub
End If
0
 

Author Comment

by:dbfromnewjersey
ID: 41850871
I didn't review and absorb your last thread which may wind up answering what I was going to ask but I'll post it just in case.

Here is the command button's on click event:

Private Sub cmd_Save_Record_Click()
If Me.Dirty Then
   DoCmd.RunCommand acCmdSaveRecord
Else
   MsgBox "You did not enter any data or navigate to an existing record. There is nothing to save.", vbInformation, ""
   txt_Date_Received.SetFocus
   Exit Sub
End If
 End Sub

Here is one of the data validation checks I now have under the form's BeforeUpdate event. In the VB YES NO section, if I choose NO, the application fails because the code continues to attempt to save a record even though the field in the table bound to the txt_Date_Received textbox requires a value.  What I'm attempting to do is, when the user fails to enter a value, cancel the save record event, send a message to user prompting him/her to enter a value and setfocus to the txt_Date_Received textbox.

Private Sub Form_BeforeUpdate(Cancel As Integer)

If bSave = True Then
   Else
      Cancel = True
      MsgBox "Please press the save button to save the record.", vbOKOnly
      Exit Sub
End If

If txt_Date_Received.Value < (Date - 7) And Date_Received_Verified <> 1 Then
   If MsgBox("You entered a Date Received that is more than 7 days ago. Documents are supposed to be processed within " & _
             "24 hours of receipt. Is the date you entered correct?", vbYesNo + vbInformation, "") = vbYes Then
             Hold_Date_Received = txt_Date_Received
             Date_Received_Verified = 1
   Else
      Date_Received_Verified = 0
      txt_Date_Received.SetFocus
      Exit Sub
      End If
Else
End If
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41850962
I don't see a question.

What is the purpose of saving the form field to a variable?  What is the purpose of keeping a verified flag for each individual field?  I think you are making this harder than it has to be.

You should validate each field and if the validation succeeds, move on to the next one.  If the validation fails
Cancel = True
Me.failingfield.SetFocus
Msgbox "appropriate error messate",vbOKOnly
Exit Sub

So each time an error is found the code displays a message, cancels the save, and exits the procedue after placing focus on the control that the error is complaining about.

Recommendation.  Use the following syntax to reference form fields --

Me.txt_Date_Received

That reduces the amount of work the interpreter has to do as it parses the code since you have specified where the definition of txt_Date_Received will be found (i.e. in the class module for "me" which is the active form)  Also using "Me." gives you intellisense which will reduce typing and also immediately identify typos.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:dbfromnewjersey
ID: 41851047
I'll keep playing with it but what I meant by my last post (because you asked where the question is) was that the save event is not getting cancelled.  

I just tried it again...including with the Cancel = True code and it doesn't work, so I'm obviously doing something wrong.

In the Form's BeforeUpdate event, I have this exact code:

If cmb_Document_Type.Value = "" Or IsNull(cmb_Document_Type.Value) Then
   Cancel = True
   MsgBox "You must indicate the document type. Click the down arrow in the box to display the list of choices and make the appropriate selection.", vbInformation, ""
   Me.cmb_Document_Type.SetFocus
   Exit Sub
End If

I monitored it...when the program runs, it hits all of the code within the IF statement above.

After it hits the Exit Sub, the next code it hits is the  DoCmd.RunCommand acCmdSaveRecord line under the Save Record command button's on click event.   It then fails and gives the message:

run time error 3021 No current record.

So, it looks to me like save record event is not being cancelled.  Maybe the error is for some other reason.  Anyway, this was just an FYI. I'll keep trying.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41851144
The save is being canceled and that is why you are getting the 3021 error.  Whenever an event is cancelled, the procedure that calls it is returned an error.  That means that the calling procedure requires an error trap to catch the expected error that happens when the save is cancelled and ignores it.
Here is code from one of my Save buttons.  Notice that it checks for several errors and ignores them.  These errors arise when the BeforeUpdate event is cancelled.  Based on the comment in the code, you can see that the error switched from 3270 or 3021 to 3709 when we moved the BE to SQL Server.

Private Sub cmdSave_Click()
   On Error GoTo Err_Proc

    If Me.Dirty Then
        DoCmd.RunCommand acCmdSaveRecord
    End If

Exit_Proc:
   On Error GoTo 0
   Exit Sub

Err_Proc:

    Select Case Err.Number
        Case 3270, 3021       'save cancelled
            Resume Next
        Case 2501
            Resume Next
        Case 3709               'strange error returned when save is cancelled once we switched to SQL
            Resume Next
        Case Else
            MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdSave_Click of VBA Document Form_frmClients"
    End Select
    Resume Exit_Proc
    Resume
End Sub

Open in new window

Here is the code from the form's Unload event.  You need this because if the user simply presses the form's "x" or the application's "x" to close, the dirty record is automatically saved but you want code that will cancel the save if they didn't push your save button so you have to prevent the form/app from closing or let it close gracefully without attempting to save.
Private Sub Form_Unload(Cancel As Integer)
   On Error GoTo Err_Proc

    If Forms!frmLogin!chkAllowClose = True Then
        Exit Sub
    Else
        If MsgBox("An error has been detected that is preventing the changed record from being saved.  Press Yes to return to fix it.  Press No to cancel the update and exit without saving.", vbYesNo) = vbYes Then
            Cancel = True
            Exit Sub
        Else
            Me.Undo
            MsgBox "Update has been cancelled.", vbOKOnly
            Exit Sub
        End If
    End If
Exit_Proc:
   On Error GoTo 0
   Exit Sub

Err_Proc:

    Select Case Err.Number
        Case Else
            MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Form_Unload of VBA Document Form_frmClients"
    End Select
End Sub

Open in new window

0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41851153
Most people start out with simpler requirements and grow into understanding how the form events work.  You are creating a very complex process for your first attempt so you have a steeper hill to climb since you need to understand the interrelationships of several events at once.
0
 

Author Comment

by:dbfromnewjersey
ID: 41852108
Thanks.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41852139
You're welcome.

PS - using fieldname.value isn't necessary since the value property is the default.  The only time you must use the property is when you are in the change event and want to examine the data that has been typed but is not yet moved to the control and in that case, you would use the .text property.  The .text property is available ONLY if the control has the focus so don't use it except in events that fire before the BeforeUpdate event.  As I suggested earlier, to take advantage of all that Access has to offer, use the proper reference style for form controls -

Me.controlName

That is the syntax that will give you intellisense and allow you to instantly discover typos.

Me.fieldname bypasses the control and refers directly to the bound value of the control.  I always give my controls unique names using a standardi naming scheme.  That way, I know if I am referring to a control or referring directly to the field.

Me.controlname.text = the characters in the input buffer
Me.controlname or Me.controlname.value = the characters visible in the control
Me.controlname.OldValue = Null for a new record or the value from the existing record before it was changed.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
ust html in ms/access WebBrowswer 11 43
Resize text 4 16
Search field on a form 7 14
How to calcualate lateness in Access 2010 11 25
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now