oldvalue, combo box, beforeupdate

[Database Administrator]

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.

[Database Administrator]

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.