unbound combo box undo

[supmktg]

I am building a form to insert data into a table using an insert statement behind a command button. The user selects a customer from a combo box, and then enters other data into the form. The combo box value is one of the fields being inserted via the insert statement.

I am trying to prevent the user from changing the customer until the command button's insert statement has been run.

Here's my latest attempt, which causes a run-time error 2115:

Private Sub cboCustomer_BeforeUpdate(Cancel As Integer)
Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("tblPrepayTmp")
If rst.RecordCount > 0 Then
MsgBox "You have not posted the previous customer's payment." & vbCrLf & "Please click 'Post payment' or 'Reset entry' before choosing a new customer.", vbInformation, "Unposted entries"
Me.cboCustomer.Value = Me.cboCustomer.OldValue
Exit Sub
End If
Call fncReset
End Sub

I have tried this (or similar code) on Click, on Got Focus, on After Update, and nothing works, the msgbox pops up but the customer changes to the new selection.

I have tried me.cboCustomer.undo with no success.

I have also tried to capture the previous customer and then set it back, but I can't figure out how or when to capture the previous customer.

Any help would be appreciated!

Thanks,
Sup

 

[Tim L]

I've just done a quick bit of testing and used the On Change event to record that the value in an unbound control had changed (by setting and unsetting another unbound control, a check box). Unfortunately it would appear that unless a control that is actually bound is changed then Access doesn't care about such trivial(!) things.

So, my suggestions, for what they are worth, are:

1) Bind the control to something (although that would mean otherwise unused data in your records).

2) Prevent the user from moving away from the record in any other manner other than by using your 'post' button (or by pressing your cancel button). This would require removing the navigation buttons and adding your own and modifying the code to check for a 'changed' flag (I'd use a hidden check box), which would be reset by the code for the 'post' button. You'd also need to prevent records from being scrolled to by a mouse wheel (there's definitely been some threads about that in here somewhere but you'll have to search for them).

3) Another idea: You could also ensure that one record and only one record can be viewed by setting the Allow Additions property of the form to No and ensuring that the record source refers to just a single record. If you are using an unbound combo box to 'go to' the desired record then you could have an event that refreshes the form record source query (which references the combo box) in the After Update event of the combo. The combo box would then, as soon as the first field is changed, become disabled, until either the 'post' button or 'cancel' button have been pressed.

I'm not sure how useful these suggestions willl be but I hope they do help.

Tim

 

[supmktg]

Tim,

Thanks for your help. I've used your 2nd suggestion with a twist. On entering the first bit of data, I set the combo to locked. The code behind the Post button sets the combo to unlocked. This accomplishes exactly what I wanted.

Thanks,
Sup