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