Results 1 to 7 of 7
  1. #1
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618

    Field cannot be updated

    1. Please open form f01Qualification, in the field named "Qualification" the phrase "Bachelors Degree" should be seen since it is the last record.
    2. On the form in the top right corner there are 10 buttons. Click the first one on the left.
    3. It opens the form "f01RecordingDiary" and by means of openargs it brings forward the phrase "Bachelors Degree". All fine up to here.
    4. The field "Date captured" displays a date stamp at this stage which is fine. If I click on Yes that date stamp disappears, although if I exit this form and return the date is there. The involved fields are requeried.
    5. The first code on the VBA page is:
    Private Sub txtDiaryEntryA_AfterUpdate()
    Me!CapturedByB = DLookup("UserNameA", "q01LoggedInUser")
    End Sub


    This should capture the logged in user's name to the field named "CapturedByB" after the field "Diary Entry" is updated.
    An error comes up "Field cannot be updated". If this form is closed and then opened again, both fields "Date Captured" and "Captured By" display what it should, against what the error message implied.

    6. My challenges here are.
    a. The date stamp should not disappear after clicking yes.
    b. The field "Captured By" must display the logged in users name after updating "Diary Entry".
    c. The error message must be gone.
    d. If No is selected when the form is opened, the user must select a subject, then the same error occurs.
    Thank you.
    Attached Files Attached Files

  2. #2
    Edgar is offline Competent Performer
    Windows 8 Access 2016
    Join Date
    Dec 2022
    Posts
    271
    The problem is that you are going to a new record every time you load the form. Look at this:
    Code:
    Private Sub Form_Load()
        DoCmd.Maximize
        btnThisBusiness.Caption = DLookup("BusinessNameA", "q01ThisBusiness")
        DoCmd.GoToRecord , , acNewRec
        ReSizeForm Me
        If Not IsNull(Me.OpenArgs) Then
            Me.cboSubjectSuggestedB = Me.OpenArgs
        End If
        
        If MsgBox(Prompt:="Select YES to capture a DIARY ENTRY on the SUBJECT SUGGESTED, otherwise select NO and SELECT A SUBJECT. Then capture a DIARY ENTRY. If no DIARY ENTRY is going to be captured anymore, select NO and CLOSE.", Buttons:=vbYesNo, Title:="From PERFAC") = vbYes Then
            Me!cboUnkcom_IDb = Me!cboSubjectSuggestedB
            '--------------------------------------------------------------------
            If Not IsNull(Me!RecordingsDiaryNoA) Then
                Exit Sub
            Else
                If DCount("RecdiaID", "t01RecordingsDiary") = 0 Then
                    Me!RecordingsDiaryNoA = 1
                Else
                    Me!RecordingsDiaryNoA = DMax("RecordingsDiaryNoA", "t01RecordingsDiary") + 1
                End If
            End If
            '--------------------------------------------------------------------
            MsgBox "Capture a DIARY ENTRY about the SELECTED SUBJECT."
        Else
            MsgBox "Select a subject from the list, then capture a DIARY ENTRY."
        End If
    End Sub
    The reason this is a problem is because your field DateCapturedB in table t01TrnsctnTbls has Now() as default value, since you are going to a new record on load, the record must be commited before saving the output of Now(). If you comment out that line, it all works as you expect.

  3. #3
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Thank you Edgar.
    If I understood correct, you suggested I comment out the line; DoCmd.GoToRecord , , acNewRec. I did that.
    Results.
    1. The form opens on the first record in the table, if the user clicks "Yes" it changes record 1.
    2. I tried to move that line "
    DoCmd.GoToRecord , , acNewRec" after "Me!cboUnkcom_IDb = Me!cboSubjectSuggestedB". Not working, but I continue to try.

    I assume you understand from post no 1 exactly how it should work.

  4. #4
    Edgar is offline Competent Performer
    Windows 8 Access 2016
    Join Date
    Dec 2022
    Posts
    271
    When I commented the line:
    a. the timestamp did not disappear. But that's because it was already stored.
    b. user name appears after updating diary entry
    c. no errors

    But it appears that is not what you needed. Here's an idea, what if you move the messagebox code to the button that opens the form? instead of this message appearing upon loading the form. Since the message will expect YES or NO right after clicking the button. If the user clicks YES, then open the form to a new record, if the user clicks NO then open the form to record 1. Would that work?

  5. #5
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    For this process to work most effectively, what follows below must be the features. I assume it can definitely be done.
    1. More than 100 main forms in my app have the same 10 buttons top right, button 1 does the same on all those forms.
    2. The subject is selected which later benefits reports etc. If the user selects yes, the user doesn't need to type it again which will complicate intended reports. Spelling errors etc.
    2. For the user to choose between Yes or No, the user should be on the next form already (f01RecordingDiary), since seeing the form will influence the choice.
    3. If the selection is YES, the subject must be transferred to the next field Unkcom_IDb, and "CapturedByB" and "Diary Entry" must be updated as well.
    4. If the user selects NO, the user must select any subject after which
    "CapturedByB" and "Diary Entry" must be updated as well.
    5. I believe there is a way that direct after selecting yes, a new record must be open, and the three mentioned fields be updated.

    I first thought this challenge is simply to update
    CapturedByB. As soon as time allows me, I will put in more effort.
    Thanks for your effort.

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    @ Perfac:
    Please review the updated file and see if that's what you want it to do. I have added a couple lines of code to the Load event of the diary form and changed both the form's recordset property and its record source query property to Dynaset - Inconsistent Updates.

    EDIT: usually I like to use a single table as a form's record source; I use combo boxes to display the required info from other tables (such as descriptions for foreign key IDs) instead of bringing those tables in the form's record source. I have not looked into much detail at your form to see if you can simplify the form's source query but I suspect you could and that would solve your problem without the need to change the recordset type.
    Cheers,
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #7
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Thank you, Vlad.
    Logged in 10 minutes ago, haven't checked thoroughly yet, but it looks good for starters.
    If on the first form I click to go to the Diary form it works like I want it. Also, after either YES or NO it looks right.

    The fields "CapturedByB" and "Diary Entry" do not update yet.
    I find it interesting that when the Diary form opens the two fields display the correct info, but if the form is closed and opened the info is not there, neither in the query of course.
    Hans.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 10-02-2018, 09:57 AM
  2. Replies: 5
    Last Post: 11-27-2017, 07:09 AM
  3. Replies: 8
    Last Post: 03-12-2016, 02:06 PM
  4. Replies: 7
    Last Post: 10-29-2015, 04:20 PM
  5. Replies: 4
    Last Post: 04-06-2014, 10:01 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums