Stop Combox value Changing

[abbaddon223]

Hi - I'm hoping someone can help me please? :banghead:

I have a combobox which is bound to a table from which it get's its text values.

When a user changes the value of the combobox, they are promoted with a vbYesNo - do you want to make a change.

If Yes - stuff happens / If No - it doesn't. All is well at this stage

The issue is, if the current value of the combobox is set to

"No Sent" and the use selects "Sent" from the combobox, even if they select No to the VbYesNo prompt, the value of the combo box changes to "Sent" (the reset of the code which executes if Yes doesn't run, but I'm left with a declined value in the combobox"). Code below:

Dim Outcome As String
Outcome = [Current_State].Value

Dim Answer As Integer
Answer = MsgBox("Commit This Data To History?", vbCritical + vbYesNo, "Continue Request?")
If Answer = vbYes Then
DoCmd.SetWarnings False
[Status_URN].Value = [Customer_URN] & [Current_State] & Date
[Current_Status_Date].Value = Date
[Forms]![Frm_1_Q&A_Customer_Details]![Stage].Value = DLookup("Stage", "Lookup_Status", "Status='" & Current_State & "'")
Dim Ref As String
Ref = [Customer_URN]
DoCmd.Close acForm, "Frm_1_Q&A_Customer_Details"
DoCmd.OpenQuery "App_Unmatched_History"
DoCmd.OpenForm "Frm_1_Q&A_Customer_Details"
[Forms]![Frm_1_Q&A_Customer_Details]![Customer_URN].SetFocus
DoCmd.FindRecord Ref
DoCmd.OpenForm "Frm_CRM_History"
MsgBox "Add Notes To New Pipeline Status", vbExclamation, "Action Required"
End If

If [Forms]![Frm_1_Q&A_Customer_Details]![Current_State].Value = "Pack Resent" Then
[Forms]![Frm_1_Q&A_Customer_Details]![Days_From_Created].Value = 0
Exit Sub
End If

If Answer = vbNo Then
MsgBox "Action Cancelled", vbInformation, "Update Recieved"
[Current_State].Value = Outcome
Exit Sub
End If

My variable is after update - can I do a before update then referenece?

 

[pr2-eugin]

Which event does the Code sit in? Also why have you placed If followed by another if? Should it not be If...Else?

 

[abbaddon223]

A before update which is possibly the issue.

The double if is because I need the code to behave differently for each answer in the combo box (this bit works)

 

[pr2-eugin]

Have you canceled the event if they hit no? You will not be able to change the value in the Before update event.. Yes I do understand that you want it to behave differently for each outcome, that will still be achieved by using If..Else Structure..

Could you put the entire code in the BeforeUpdate event?

 

[Solo712]

The issue is, if the current value of the combobox is set to "No Sent" and the use selects "Sent" from the combobox, even if they select No to the VbYesNo prompt, the value of the combo box changes to "Sent" (the reset of the code which executes if Yes doesn't run, but I'm left with a declined value in the combobox"). Code below:

Dim Outcome As String
Outcome = [Current_State].Value

Dim Answer As Integer
Answer = MsgBox("Commit This Data To History?", vbCritical + vbYesNo, "Continue Request?")
If Answer = vbYes Then
DoCmd.SetWarnings False
[Status_URN].Value = [Customer_URN] & [Current_State] & Date
[Current_Status_Date].Value = Date
[Forms]![Frm_1_Q&A_Customer_Details]![Stage].Value = DLookup("Stage", "Lookup_Status", "Status='" & Current_State & "'")
Dim Ref As String
Ref = [Customer_URN]
DoCmd.Close acForm, "Frm_1_Q&A_Customer_Details"
DoCmd.OpenQuery "App_Unmatched_History"
DoCmd.OpenForm "Frm_1_Q&A_Customer_Details"
[Forms]![Frm_1_Q&A_Customer_Details]![Customer_URN].SetFocus
DoCmd.FindRecord Ref
DoCmd.OpenForm "Frm_CRM_History"
MsgBox "Add Notes To New Pipeline Status", vbExclamation, "Action Required"
End If

If [Forms]![Frm_1_Q&A_Customer_Details]![Current_State].Value = "Pack Resent" Then
[Forms]![Frm_1_Q&A_Customer_Details]![Days_From_Created].Value = 0
Exit Sub
End If

If Answer = vbNo Then
MsgBox "Action Cancelled", vbInformation, "Update Recieved"
[Current_State].Value = Outcome
Exit Sub
End If

My variable is after update - can I do a before update then referenece?

The bolded block of code gets executed whatever the user's response. You most likely intended to nest it within the "If Answer = vbYes". This would not be a good way of writing out the code in any case. A piece of code that is to be executed irrespective of the user directive needs to be placed outside of the response resolution. If user says "Yes" x happens ELSE y happens. Any other structure will most likely cause problems.

Best,
Jiri