Results 1 to 4 of 4
  1. #1
    StuartR is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Location
    Scotland
    Posts
    46

    Question Updating fields of one record using VBA

    I have a form with a combo box, and when a certain selection is made on the combo box, then that particular field changes on the current record. This works fine.

    The Sub Status_BeforeUpdate(Cancel as Integer) then writes a few notes to an admin file for the current record, and again this is working fine.

    However, depending on the chosen item in the combo box, I also want to update some of the fields in the current record.

    As an example, if the chosen item in the combo box is "Deceased", then I want to make most of the fields such as the address blank (so that we could never write to the person again)



    I have been trying various things with myForm.Recordset.Update but haven't managed to get anything to work yet.

    Can anyone suggest the correct syntax, or the best way of achieving this.

    My code at present is as follows, albeit I am just experimenting with trying to make one field [Post Code] to " " at present ...

    Code:
    'Change Status to Deceased  
       
    
      If [Status] = "Deceased" Then
    
      Msg = "Are you sure you want to mark this member as Deceased ?"
      Style = vbYesNo + vbCritical + vbDefaultButton1
      Title = "Changing Member Status"
       
      Response = MsgBox(Msg, Style, Title)
      If Response = vbYes Then
      Cancel = False
       
      Set draft_membership = CurrentDb
      Set rstAdminLog = draft_membership.OpenRecordset("Admin_Log")
       
      rstAdminLog.AddNew
      rstAdminLog!Member_IDFK = Me.Member_ID
      rstAdminLog!Note_Date = Date
      rstAdminLog!Added_By = "System"
      rstAdminLog!Note = "Status Changed to Deceased"
      rstAdminLog!Category = "Status"
      rstAdminLog.Update
       
            ' Set remaining fields to Null.
    
    
         Set draft_membership = CurrentDb
         Set rstAdminLog = draft_membership.OpenRecordset("Member_Names")
         my.Form.Recordset.Fields([Post Code]).Value = " "
         myForm.Recordset.Update
       
      Msg = "Member Status Changed"
      Style = vbOKOnly + vbInformation
      Title = "Confirmation"
      Response = MsgBox(Msg, Style, Title)
      
    ElseIf Response = vbNo Then
    
      Cancel = True
      Me!Status.Undo
      
    End If
    
      End If

  2. #2
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    You can set the conditions in a record in the AfterUpdate event of the combobox, so depending what they select, code would be something like:

    If me.YourCombBoxName = "Deceased" then
    Me.Address = null
    End If

    This happens as the record is still in Edit mode so you will see those changes happen, then you can save the record with those changes.

  3. #3
    StuartR is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Location
    Scotland
    Posts
    46
    Wow, that was simple !!!

    I wish I'd asked sooner.

    Thanks for your help

  4. #4
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Glad to Help!!

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

Similar Threads

  1. Replies: 3
    Last Post: 06-25-2015, 12:54 PM
  2. Updating Multiple Record Form Based on Record Count
    By William McKinley in forum Forms
    Replies: 2
    Last Post: 12-31-2014, 12:45 PM
  3. Replies: 4
    Last Post: 10-16-2014, 07:13 AM
  4. Updating Autonumber Fields
    By Rawb in forum Code Repository
    Replies: 4
    Last Post: 02-21-2011, 07:17 AM
  5. Error in updating fields
    By Kookai in forum Access
    Replies: 1
    Last Post: 08-01-2010, 12:07 AM

Tags for this Thread

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