oldvalue, combo box, beforeupdate

My preference is to perform this kind of checking in the BeforeUpdate event
procedure of the form. Reasons:
a. It's less intrusive to pop up one message box after the user has sorted
out their edits, rather than after each control.
b. Form_BeforeUpate is the only place you can check for nulls. (The
controls' events don't fire if nothing was entered.)
c. Form_BeforeUpdate is the only place to use for comparisons.

The sample below is designed so you can add more blocks of code to check for
more controls:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
Dim bWarn As Boolean

With Me.Customer_id
If .Value <> .OldValue Then
bWarn = True
strMsg = strMsg & "Changed customer from " & .OldValue & " to "
& .Value." & vbCrLf
End If
End With

If bWarn And Not Cancel Then
If MsgBox(strMsg, vbOkCancel+vbQuestion, "Confirm change") <> vbOk
Then
Cancel = True
Me.Undo
End If
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"franc sutherland" wrote in message
news:209fb442-6687-4b18-89c7-a26fce86e238@w39g2000prb.googlegroups.com...
> Hello everyone,
>
> I am using Access 2003.
>
> In a sales order form, the customer is selected used a combo box for
> which the bound column is the customer_id field, which is the
> autonumber key in the customers table.
>
> When someone goes back in to the sales order and changes the customer
> for that order, I want to pop up a message box which will ask them to
> confirm the change. If they say no, I want the record to return to
> the initial value. If they say, yes, the customer_id will change and
> the other fields populated with the new information. The lookup is
> carried out in the AfterUpdate event using Dlookup.
>
> In the BeforeUpdate event, I see if the new customer_id is the same as
> the oldvalue customer_id and if it is, that's no change. If there is
> a difference, I then ask them to confirm if they want that change
> using a message box with vbyesno. Probably best to paste in the code
> at this point!
>
> --
>
> Select Case new_id
>
> Case Is = old_id
>
> MsgBox "no change"
>
> Case Else
>
> customer_change = MsgBox("Do you want to change customer?",
> vbYesNo)
>
> Select Case customer_change
>
> Case Is = vbNo
>
> Me.customer_id.Undo
>
> Cancel = True
>
> Case Else
>
> Cancel = False
>
> End Select
>
> End Select
>
> Debug.Print Me.customer_id
>
> End Sub
>
> --
>
> This method works to determine whether they want to change the
> customer or not. The undo reverts the value in the combo box back to
> the oldvalue, however the debug.print shows that the customer_id is
> not changed.
>
> If customer 1 is the original data and I change it to customer 2, then
> I select yes in the message box, it works fine. If I select No from
> the message box, the value shown in the combo box is correct, but the
> me.customer_id value printed by debug gives the customer_id of
> customer 2.
>
> I tried putting in the line of me.customer_id = old_id, but this gave
> the 2115 error.
>
> Thanks in advance,
>
> Franc.