This is about code that I've put behind several controls on a form to determine if the user wants' an edit to a contact's info detail to stay local to the specific project, or if the related record in the MDb needs to also be updated...
i had the code in the AfterUpdate event, and it undid the user action as intended, but it was not proving an accurate .OldValue for evaluation (which makes sense in hindsight);
However having moved the sub to the BeforeUpdate event, while the .OldValue returns accurate info, .Undo returns and error
a bit lost (any advice / direction will be greatly appreciated in advance),
m.
Code:
Public Sub UpdateContact(strControl As Control, frm As Form)
Dim strClient_Group As String
Dim blnUpdate As Boolean
blnUpdate = vbFalse
If strControl.OldValue <> strControl Then ' check if the entry is an update to the existing entry
' there are multiple similar controls on the form: cboClient_Contact, cboEE_Contact
' all contacts are in the same table (any contact can be either "Client", "EE",...
' extract from the control name which contact is being edited / evaluated in the Mdb table (tblClientContacts)
If InStr(1, CStr(strControl.Name), "Client", vbTextCompare) Then
strClient_Group = "Client"
Else
strClient_Group = "EE"
End If
intstart = InStr(1, strControl.Name, "_", 3)
intLen = Len(strControl.Name)
strField = strClient_Group & "_" & Mid(strControl.Name, intstart + 1, intLen - intstart + 1)
cntrlContactControl = "cbo" & strClient_Group & "_Contact"
strContact_Name = Forms![frmMainMenu].Controls(cntrlContactControl)
'if the initial entry was blank, add the entry to the MDb as default
'else prompt to leave the entry as is for this project (ONLY), or to cancel and put pack the original information
If Len(Nz(strControl.OldValue, "")) = 0 Then
blnUpdate = vbTrue
Else
gsMsgText = "You have just changed a detail of this person's contact information:" _
& vbCrLf _
& strControl.OldValue & " was chaged to be: " & strControl _
& vbCrLf _
& vbCrLf _
& "Do you want to make this change in the MDB?"
gsMsgTitle = "CONTACT INFO UPDATE"""
Response = MsgBox(gsMsgText, vbYesNoCancel + vbQuestion, gsMsgTitle)
Select Case Response
Case Is = vbNo
Cancel = True
'*** this next line is where i'm hung up:
Case Is = vbCancel
'---> none of these seem to be effective:
'frm.strControl.Undo
'frm.strControl.Value = strControl.OldValue
'frm.Dirty = False
Case Is = vbYes
blnUpdate = vbTrue
End Select
End If
If blnUpdate Then
Set gsDbs = CurrentDb
Set gsRst = gsDbs.OpenRecordset("select * " & _
"FROM tblClientContacts " & _
"WHERE [Client_CompanyName] = '" & Forms!frmMainMenu.cboClient_CompanyName & "' " & _
"AND [Client_Contact] = '" & strContactName & "';")
gsRst.Edit
gsRst.Fields(strField) = strControl
gsRst.Update
Set gsDbs = Nothing
Set gsRst = Nothing
End If
End If
End Sub