Combo change warning message

[caferacer]

Hi All,

On the scrounge again.

I have a combo which I am using to look up values from a table.

What I would like to do is once an initial selection has been made using the combo, should a user then go to change the original selection at any time in the future, before committing to the change a yes/no message box will prompt the user to confirm they wish to proceed with the change (or not). If not, then the procedure quits, if yes, the change is made.

I am not sure which event is the best to use and the code.

A basic algorithm would be

1). Make selection via combo box
2). Check if the field was empty
3). If field is empty then proceed with selection, else
4). Ask user to confirm the new value
5). If yes, then commit to new selection, else
6). Retain original selection and quit.

Thanks in advance.

Mark.

 

[pbaldy]

You can use the before update event of the combo (or form). In either case you can use the Value and OldValue properties to check the current and previous values. In both events you can use:

Cancel = True

to stop the update.

 

[KenHigg]

I'm thinking you should look at using the forms before update event - ?

Edit: pb is fast today

 

[pbaldy]

Edit: pb is fast today

Fastest fingers in the west!

 

[caferacer]

Hi All,

Wrt to your recommendations above (I am a complete VB novice), I searched the net and found some code, which to my untrained VB eye seemed to look as though it may work, so dropped it into the form before update, but it comes up with a compile error. Any ideas welcome?

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

With Me.VendorID
If .Value <> .OldValue Then
bWarn = True
strMsg = strMsg & "Changed Vendor 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

 

[caferacer]

Some more detail on the Compile Error. It states -

"Method or data member not found", and highlights .OldValue in the line.

If .Value <> .OldValue Then.

I am using Access 2003??

Any help much appreciated.

Thanks in advance.

Mark.

 

[bob fitz]

The code below may be of use. To be used in the Before Update event of the Combo Box

   If Me.ActiveControl.OldValue > 0 Then
    If MsgBox("Change Vendor" & vbCrLf & vbCrLf & "Are you sure?", vbYesNo, "Confirmation Required") = vbNo Then
      Cancel = True
      Me.ActiveControl.Undo
    End If
  End If

 

[caferacer]

Thanks Bob,

Works a treat

Regards

Mark

 

[bob fitz]

Mark

I'm glad you've had some success with the code supplied.
If you need the message box to display the old and new values this can be done with a little more code involving the use of the DLookup() function. Post back if you would like some in pursuing this.

 

[caferacer]

Hi Bob,

Thanks for the offer – though it’s doing what I need so I'm happy with the functionality.

However, I have just spotted a small glitch, which I think I know what the problem is, but can’t find a way around.

The combo sits on a sub form, which has selection criteria to limit the records to a particular ID on the main form which it sits. After adding your code I also put a requery in the “on change” event, of the combo so when making a change to the vendor, the requery immediately has the result of removing the record from the combo list and updates form - works brilliantly, or so I thought! The problem is I think the requery in the combo “on change” event is causing a problem with a "duplicate record operation button" I also have on the sub form? Each time the duplicate record button is used it throws up paste errors, although it does actually duplicate the record as the record counter increases?? I can only think that in duplicating the record and therefore forcing the combo “on change” requery into action at the same time, it somehow causes a conflict??

The “on change” event in the combo seems to give the beast results of all event procedures so would prefer to keep this.

Is there a way of maybe setting a flag in the “duplicate record” button code and then some corresponding code prior to the requery in the combo to bypass the requery if a duplicate record procedure is being carried out?

Hope this all makes sense??!!

Look forward to your reply

Regards and thanks as always

Mark.

 

[bob fitz]

Mark:

Can you post a copy of your DB in A2003 mdb format.

 

[caferacer]

Hi Bob,

Its Combo25 and Command44.

Thanks

Mark.

 

[bob fitz]

Mark

Take a look at the attached DB. If this now works as required I can then explain the alterations made.

 

[caferacer]

Hi Bob,

Yes on a very quick use, it looks to have resolved the problem. Thanks as ever once again.

On quick review I see you have added a AND statement in Combo25 to look at the g_blnCreatingDup status of Command44 and moved the requery to AfterUpdate.

Using AfterUpdate had the effect of launching the vendor message box when I tried it (??), so was the reason why I plumped for OnChange.

Look forward to your explanation?

Waiting for a VBA book to be delivered so will hopefully be able to start figuring some of this stuff for myself soon.

Thanks and regards

Mark.

 

[bob fitz]

Mark

It seem as though you already understand most of the changes.
I declared the variable g_blnCreatingDup in the Declaration section of the form with:
Public g_blnCreatingDup As Boolean
so it will stay in scope until the form is closed. Its values are set in the On Click event of Command44.

I changed your modules to include the two following lines:
Option Compare Database
Option Explicit

 

[caferacer]

Hi Bob,

Thought I was going nuts, but the EquipmentID record field on the form when using the duplicate record button increments and then for some reason just stops incrementing, although the record counter still increases? The records are being created as they are in the table, but the EquipmentID just seems to stop indiscriminately at any number after about 20+ duplicates?

I did go back and try a database version prior to adding the warning message (prior to changing the vendor), but this seems to increment okay??

Any ideas?

Regards

Mark

 

[bob fitz]

Mark

Can you post a copy of your DB.
I have been unable to duplicate the problem using the DB that I posted.

 

[caferacer]

Hi Bob,

See attached.

All I did to prove this was to download a fresh copy from here just a moment ago and pressed the duplicate record button Command44. As you will hopefully be able to see the EquipmentID is stuck at 361? It started at 306.

I have dowloaded fresh copies but sooner or later they all reach a number and stop incrementing, despite the record counter continuing to increase? Its seems to duplicate for a while okay 25-35 records sometimes more.

Regards

Mark.

 

[bob fitz]

Mark

Sorry for the delay in replying.

Actually, all the records are there, which is why the record counter shows the results that it does. If you step back through the records using the "previous" button, you will find the records that have ID numbers that are >361. If you apply a sort order to the form, I think it will behave in the way that you expect it to.

 

[caferacer]

Hi Bob,

Thanks,

I was thinking of this, but thought it was a bit of a cop out. However, I have put in an Orderby EquipmentID and it seems to behaving now.

Thanks as always

Regards

Mark