Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511

    Unanswered: Edit a Field from A Form

    What is the correct way to edit a field from a form. I have the field on the form and I can type over the value, move to another record and back and the edited value remains. With a Save button, I can make sure the field is not blank, and use Me.Undo if it is blank, but when it is blanked out and I use the navigation controls, of course I get the dialog box telling me the key field can't be null.

    Also on the form is a list box showing a fixed small number of records. The source for this is a query. I need to get a handle on what should be behind the Save button to make the list box requery work. It does not show the change now, and the requery code does execute when viewed in debug. When I navigate off the record, the new value appears in the list box. Thanks.
    Jerry
    Last edited by JerryDal; 04-17-10 at 13:49. Reason: List box updates when I move off record.

  2. #2
    Join Date
    Mar 2007
    Posts
    277
    What is the RecordSource of your form? It sounds like it might be a non-updateable query.
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

  3. #3
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    The record source of the form is the table. I have the table's 3 fields on the form, and only one of them, a text field, can be edited. I believe I have just now solved this problem, being able to edit the field and see the change in the list box when the Save button is clicked. The action is performed in the field AfterUpdate event, and Me.Requery made it possible to refresh the list box.

    Code:
    Private Sub cmdSave_Click()
    
        Me.MY_FIELD_NAME.SetFocus
        Me.MY_FIELD_NAME.SelStart = Me.MY_FIELD_NAME.SelLength + 1
    
    End Sub
    
    
    Private Sub MY_FIELD_NAME_AfterUpdate()
    On Error GoTo Err_MY_FIELD_NAME_AfterUpdate
    
        Me.Requery
        Me.lstMyListBox.Requery
    
    Exit_MY_FIELD_NAME_AfterUpdate:
    
        Exit Sub
    
    Err_MY_FIELD_NAME_AfterUpdate:
    
        Me.Undo
        If IsNull(Me.MY_FIELD_NAME) Or Me.MY_FIELD_NAME.Value = "" Then
            MsgBox "You must type in a unique value for this key field." _
            & vbCrLf & "Data is restored."
        Else
            MsgBox "Duplicate values are not allowed. Choose another value." _
            & vbCrLf & "Data is restored."
        End If
    
        Resume Exit_MY_FIELD_NAME_AfterUpdate:
    
    End Sub
    Unless there are other suggestions, this is RESOLVED.

  4. #4
    Join Date
    Mar 2007
    Posts
    277
    It is of course your decision but here are some suggestions. Always use a query as the RecordSource of a form rather than a table directly - it avoids certain locking errors. Do control validation in the BeforeUpdate event rather than the AfterUpdate event and set Cancel = True to hold the focus in that control.
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

  5. #5
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    After deleting the fields, removing the BeforeUpdate event code and adding the AfterUpdate event with similar code and the statement: Cancel = True to this event, and making the source of the form a SELECT statement (the same source as the list box), then adding the fields back, I get this error when I edit the field and click Save:

    HTML Code:
    The macro or function set to the BeforeUpdate or Validation Rule property for this field is preventing Microsoft Office Access from saving the data in the field.
    If this error can be resolved, I would appreciate knowing what is going wrong.
    Sharing is not an issue here, if that is why you recommended the changes.

    My code seems to be error free since all the form does is allow editing of one text field in a 3-record table. Thanks.

  6. #6
    Join Date
    Mar 2007
    Posts
    277
    If you are using the same code you posted in the BeforeUpdate event of the same control then you need to understand that you are not validating the input and those two lines will cause the error you described.
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

  7. #7
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    I tested with the AfterUpdate code removed. What do you mean by "those two lines"? Current code:
    Code:
     Private Sub cmdSave_Click()
    
         Me.qryFIELD_NAME.SetFocus
         Me.qryFIELD_NAME.SelStart = Me.qryFIELD_NAME.SelLength + 1
    
     End Sub
    
     Private Sub qryFIELD_NAME_BeforeUpdate(Cancel As Integer)
     On Error GoTo Err_qryFIELD_NAME_BeforeUpdate
    
         Cancel = True
         Me.Requery
         Me.lstMyListBox.Requery
    
     Exit_qryFIELD_NAME_BeforeUpdate:
    
         Exit Sub
    
     Err_qryFIELD_NAME_BeforeUpdate:
    
         Me.Undo
         If IsNull(Me.qryFIELD_NAME) Or Me.qryFIELD_NAME.Value = "" Then
             MsgBox "You must type in a unique value for this key field." _
             & vbCrLf & "Data is restored."
         Else
             MsgBox Err.Description
             'MsgBox "Duplicate values are not allowed. Choose another value." _
             '& vbCrLf & "Data is restored."
         End If
    
         Resume Exit_qryFIELD_NAME_BeforeUpdate:
    
     End Sub

  8. #8
    Join Date
    Mar 2007
    Posts
    277
    I'm sorry but these two lines:
    Me.Requery
    Me.lstMyListBox.Requery
    ...do *not* validate the control entry and will cause the error you are getting if executed in the BeforeUpdate event.
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

  9. #9
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    With those two lines eliminated from the BeforeUpdate event, the command buttons and navigation buttons are unresponsive, and when I close the form I get the error message "You can't save this record at this time."

    I'm not sure why the AfterUpdate event is not the better way. This is a standalone application (I call it an entertaining novelty with no useful purpose) and the user owns this information that is being edited, so there is not an issue of validating what the default values in the 3-record table have been changed to. This is for a demo that uses the Speech Application Programming Interface (SAPI) for Windows XP. The user can change the three names that appear on the main form (identified by Microsoft as Michael, Michelle and Sam) to something else. The ability to change names allows the user to customize the option labels on the form and sort of make it personal.

    RuralGuy, if you can convince my why it is unwise to use the AfterUpdate in this type of application, and you know what code statements I should use for it to be error free, then please advise me.
    Thanks.
    Jerry

  10. #10
    Join Date
    Mar 2007
    Posts
    277
    I think you should just go ahead the way you had it originally.
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

  11. #11
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    Thanks RuralGuy--your assistance really helped. I believe I have worked out a good solution from your suggestions. The BeforeUpdate event validates the one editable text box and the AfterUpdate event refreshes the List Box if the text is validated. RecordSource for the form is code copied from a query SQL view. I'll restate this maintenance form's purpose again: the form gives the user the ability to change the options labels for any or all of the 3 SAPI (for Windows XP) voice names on the main form of a text-to-speech demo.

    The default names of "Michael", "Michelle" and "Sam" could, for example, be displayed beside the option buttons as "Peter", "Mary" and "Paul".

    This would be overkill if it were a simple demo, but I am counting on the demo's many bells and whistles to justify this optional "personal touch" on the main form.

    The form has 3 text boxes:
    VOICE_NAME (can edit)
    VOICE_CODE (Integer, no edit)
    GENDER (no edit)

    The table has the 3 fields above and only 3 records for the 3 SAPI voices.

    Jerry

    Code:
    Private Sub cmdSave_Click()
    
        'Purpose of this button is to remove focus from VOICE_NAME text box,
        'which triggers the BeforeUpdate event if text has been edited. Pressing
        'the Enter key will also remove the focus.
        'The BeforeUpdate event will then validate the data, and if the data
        'is valid, program flow will move to the AfterUpdate event.
    
    End Sub
    
    Private Sub VOICE_NAME_BeforeUpdate(Cancel As Integer)
    Dim db As Database
    Dim rst As Recordset
    On Error GoTo Err_VOICE_NAME_BeforeUpdate
    
        If Me.VOICE_NAME = "" Or IsNull(Me.VOICE_NAME) Then
            Cancel = True
            Me.Undo
            MsgBox "A unique Name is required for this key field." & _
            vbCrLf & "Data is restored"
            Me.VOICE_NAME.SetFocus
            Me.VOICE_NAME.SelStart = Len(Me.VOICE_NAME) + 1
            Exit Sub
        End If
    
        Set db = CurrentDb()
        Set rst = db.OpenRecordset("Alt SAPI Voice Names", dbOpenDynaset)
    
        strSearch = "VOICE_NAME = '" & Me.[VOICE_NAME] & "'"
    
        With rst
            .FindFirst strSearch
            If Not .NoMatch Then
                If rst![VOICE_CODE] <> Me.[VOICE_CODE] Then
                    'attempted to duplicate key field
                    Me.Undo
                    MsgBox "Duplication not allowed." & vbCrLf & _
                    "Data is restored"
                End If
            End If
        End With
    
    Exit_VOICE_NAME_BeforeUpdate:
    
        Set rst = Nothing
        Set db = Nothing
        Exit Sub
    
    Err_VOICE_NAME_BeforeUpdate:
    
        Me.Undo
        MsgBox Err.Description & vbCrLf & "Data is restored."
    
        Resume Exit_VOICE_NAME_BeforeUpdate
    
    End Sub
    
    
    Private Sub VOICE_NAME_AfterUpdate()
    
        Me.Requery
        Me.lstCustomVoiceNames.Requery
    
        Me.VOICE_NAME.SetFocus
        Me.VOICE_NAME.SelStart = Len(Me.VOICE_NAME) + 1
    
    End Sub

  12. #12
    Join Date
    Mar 2007
    Posts
    277
    If you're happy, I'm happy.
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •