Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2008
    Posts
    54

    Unanswered: Error message when saving record

    Two of fields in my data entry Form are GrpLastNames and SplitCount. SplitCount is hidden and it indicates the no of lastnames in GrpLastNames. I also have an edit button in the form to allow user to edit any fields in the data entry form. User doing a search can open the data entry form to edit any field should the need arises. However , users are not allowed to add or subtract the no of last names in GrpLastnames once the form is saved.

    Eg
    txtGrpLastNames . SplitCount (hidden)
    Jane, Peter, Joe . 3
    Lawerence, April . 2

    I have problem with this code:

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset

    Dim strGrpLastNames As String
    Dim varGrpLastNames As Variant

    Set dbs = CurrentDb

    Set rst = dbs.OpenRecordset("qryTblA", dbOpenDynaset)

    strGrpLastNames = rst! GrpLastnames
    varGrpLastNames = Split(strGrplastNames, ", ")

    ' error message “The macro or function set to the BeforeUpdate or ValidationRule property
    ' for this field is preventing Microsoft Office Access from saving the data in this field”
    ' when I include these 2 statements
    ' DoCmd****nCommand acCmdSaveRecord and
    ' Me.txtGrpLastName.requery
    '
    Debug.Print UBound(varGrpLastNames) + 1

    If IsNull(txtGrpLastNames) Then ‘ name of GrpLastNames field is txt GrpLastNames
    Exit sub
    Else
    If UBound(varGrpLastNames) + 1 <> SplitCount Then
    MsgBox "Changing No of Lastnames is not allowed"
    Me.txtGrpLastNames.Undo
    Exit Sub
    End If
    End if

    Set dbs = Nothing
    Set rst = Nothing

    I tested with the first record with 4 last names . Nothing happens as the field has not yet been saved. Debug.print UBound(varGrpLastNames) + 1 show 3 instead of 4.
    I tried using on dirty event, but the code does not work.

    Thank you
    Last edited by big_mon; 02-20-11 at 03:17.

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    I've read this several times and, to be honest, I really can't make heads or tails out of your scenario, not really understanding your business need here. But I do know that by the time you're in the Form_BeforeUpdate event, the current record is in the middle of being saved, and any attempts to change anything or to save a record already in the process of being saved will throw the error you're receiving, which is why

    acCmdSaveRecord


    and

    Me.txtGrpLastName.requery

    is bombing out. In the Form_BeforeUpdate event you're pretty much limited to letting it finish the saving process or Canceling the Save, such as you would if a validation check failed.

    I'd try moving your code, in toto, to the Form_AfterUpdate event.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Jan 2008
    Posts
    54
    Actually I try and catch the problem before user can add or subtract the no of the last Names to the grpLastNames.

    I have this dilemma. If I were to use the after update event I can’t use Me.txtGrpLastNames.Undo. Earlier I mentioned that Users are not allowed to add or subtract lastnames to GrpLastnames but only allowed to edit.

    What I wanted to do is to check the no of lastnames in GrpLastnames remains the same number as the splitcount before it is saved Without saving, I am not able to compare the no of last names to the splitcount or are there other ways I can find out whether GrpLastNames have been added or subtracted and trigger a response to undo it?

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Don't use a bound control for Me.txtGrpLastName and you will be able to perform all the verifications you want before saving its value or not, the only burden being that you'll have to write some code to fill the textbox on the Current event of the form.
    Have a nice day!

  5. #5
    Join Date
    Jan 2008
    Posts
    54
    Thank you . I shall give it a try and see what happens

  6. #6
    Join Date
    Jan 2008
    Posts
    54
    I have taken your advice and made some changes and I created an unbound text box , txtGrpLastNamesClone and it works!

    Private Sub txtGrpLastNamesClone_BeforeUpdate(Cancel As Integer)

    If Not Me.NewRecord Then
    Dim strGrpLastNames As String
    Dim strGrpLastNamesClone as string
    Dim varGrpLastNames As Variant


    strGrpLastNamesClone =Me.txtGrpLastNamesClone
    varGrpLastNames = Split(strGrplastNamesClone, ", ")

    If UBound(varGrpLastNames) + 1 <> SplitCount Then
    MsgBox "Changing No of Lastnames is not allowed"
    SendKeys "{ESC}"
    Cancel = True
    Exit Sub
    End If
    End if

    End sub

    Private Sub Form_Current()
    Me. txtGrpLastNamesClone = Me.txtGrpLastNames

    End Sub

    Problem is how do I copy back the data keyed in txtGrpLastNamesClone back into the bound field, txtGrpLastNames and save the records. When I click the New button I hope to save the txtGrpLastNames along with other fields and move to a new record for data entry, but the form remains where it was. I had tried doing it in current event and other events, nothing seems to get going.

    If Me.new record Then
    Me.txtGrpLastNames = Me. txtGrpLastNamesClone
    Else
    Me. txtGrpLastNamesClone = Me.txtGrpLastNames
    End if


    Can Sinndho or someone points me to the direction or show me what I should do?

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    When the data entered in txtGrpLastNamesClone has been validated, you can write it back to txtGrpLastNames:
    Code:
    If UBound(varGrpLastNames) + 1 <> SplitCount Then
        MsgBox "Changing No of Lastnames is not allowed"
        SendKeys "{ESC}"
        Cancel = True
        Exit Sub
    Else
        Me.txtGrpLastNames = Me. txtGrpLastNamesClone
    End If
    Have a nice day!

  8. #8
    Join Date
    Jan 2008
    Posts
    54
    Thank you for being patient with me. I tested the code.
    Yes I was able to go to new record, however txtGrpLastNames did not receive the data from txtGrpLastNamesClone. I checked tblA and confirm that a blank txtGrpLastNames.
    I hope to see txtGrpLastNames gets its data from txtGrpLastNamesClone in tblA when I click new.

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Then try:
    Code:
        Me.txtGrpLastNames.Text = Me. txtGrpLastNamesClone.Value
    Using the Text property to input something into a textbox is the same as typing it, then usual events that happen when you type something in a textbox should also be triggered with this method.

    If this still does not work, it means that I don't clearly understand what you're trying to do and I'll need more infos about the form and probably a longer excerpt of it's code.
    Thank you for being patient with me.
    No problem, you're welcome!
    Have a nice day!

  10. #10
    Join Date
    Jan 2008
    Posts
    54
    I believe I have found the problem. The statement If UBound(varGrpLastNames) + 1 <> SplitCount Then
    is always true for new record so that Me.txtGrpLastNames= Me. txtGrpLastNamesClone is never executed as SplitCount is null. This means that I have to recode the if statement.
    Sinndo, you have provided me the right direction. You are indeed a great help.
    Thank you.

Posting Permissions

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