Link to home
Start Free TrialLog in
Avatar of Jacques Geday
Jacques GedayFlag for Canada

asked on

VB6 Datagrid Do not prompt before update

Hello,

I have in my VB6 program a Form that has an ADODC data control and a datagrid control that is bided to the ADODC by a complex Shape SQL

When I change any cell in the datagrid it updated it automatically to the database and does not go thru any of the beforeupdate event so I can control these update.

So my question is what can I do and type of code and where can I put to prompt the user just before updating the Datagrid something like Ready to Update ? with Yes No ...

Tks/Rgds
gowflow
Avatar of eemit
eemit
Flag of Germany image

Try this:
Private Sub DataGrid1_BeforeColUpdate(ByVal ColIndex As Integer, OldValue As Variant, Cancel As Integer)
  If MsgBox("Are you sure you want to change record ?", vbYesNo + vbDefaultButton2, "Change record") = vbNo Then
      Cancel = 1
  End If
End Sub

Open in new window

Avatar of Jacques Geday

ASKER

Sorry for late reply !!!

I had tried this and was pretty sure it was not calling this routine but I don't know from what mystery when I tried it again it worked !!!

Now I have an other question
My details have several columns in my grid and everytime I get any item changed I get this question Are you sure you want to change the record ?
It is somehow annoying, I need to be asked only when I change the record not when I change the field how is this possible ???

I tried to put the question in the event

Private Sub grdDataGrid_BeforeUpdate(Index As Integer, Cancel As Integer)
If MsgBox("Are you sure you want to change record ?", vbYesNo + vbDefaultButton2, "Change record") = vbNo Then
    Cancel = 1
End If
End Sub

Open in new window


But it is not calling this event any idea ???

Thank you for answering my question as you are the only one who replied to me after 4 days I had given up !!!

TKs again
gowflow
It seems to be a bug in BeforeUpdate event.
Values in Grid are not reset after cancel update, before you clicks twice on esc key.

Private Sub DataGrid1_BeforeUpdate(Cancel As Integer)
  If MsgBox("Are you sure you want to change record ?", vbYesNo + vbDefaultButton2, "Change record") = vbNo Then
      Cancel = 1
  End If
End Sub

Open in new window

Try a different value for Cancel
Private Sub grdDataGrid_BeforeUpdate(Index As Integer, Cancel As Integer)
If MsgBox("Are you sure you want to change record ?", vbYesNo + vbDefaultButton2, "Change record") = vbNo Then
    Cancel = True
End If
End Sub

Open in new window

It seems you didn't understand my request.

I DO NOT need a trap in the event DataGrid1_BeforeColUpdate but instead need a trap in the event grdDataGrid_BeforeUpdate

So to sum it if I change Col 3 and 6 and 8 I do not want any prompt when I change these values instead I need a prompt when I change record and case I say NO or Cancel then I want all the changes that were performed in Col 3, 6 and 8 to be disregarded and to revert to original values.

gowflow
gowflow,
It seems you didn't read my last post (ID: 39253811)
I didn't say anything about DataGrid1_BeforeColUpdate.

1)
If you have not a Control Array of grdDataGrid, BeforeUpdate event should read:
Private Sub grdDataGrid_BeforeUpdate(Cancel As Integer)

Open in new window

not:
Private Sub grdDataGrid_BeforeUpdate(Index As Integer, Cancel As Integer)

Open in new window


2)
As i already said:
It seems to be a bug in BeforeUpdate event.
Values in Grid are not revert to original values after cancel update, until you twice press on esc key.
Sorry forgot to mention I have a control array of grid actually I have 2 index 0 and index 1 and yes

Private Sub grdDataGrid_BeforeUpdate(Index As Integer, Cancel As Integer)

Open in new window


this is the correct layout I have.

2) now how to overcome this bug ?? This is major !!! My whole app reply on this type of grid in this layout

How do I go about that ??? How to practically do 2 escape ??? shall I do sendkeys {ESC} twice ???

gowflow
Try this:
Private Sub grdDataGrid_BeforeUpdate(Index As Integer, Cancel As Integer)
  If MsgBox("Are you sure you want to change record ?", vbYesNo + vbDefaultButton2, "Change record") = vbNo Then
      Cancel = True
      grdDataGrid(Index).SetFocus
      Call MySendKeys("{ESC}+{ESC}")
      DoEvents
  End If
End Sub

Open in new window


What is MySendKeys?
Download SendInput.zip from Karl E. Peterson here

Add MSendInput.bas to your project.
Thank you for the above information.

Are you sure that the instruction
Call MySendKeys("{ESC}+{ESC}")
should be in
Private Sub grdDataGrid_BeforeUpdate(Index As Integer, Cancel As Integer)


I tried what you suggested and this event does not get called at all so nothing changed. Like if I put a debug F9 on
If MsgBox("Are you sure you want to change record ?", vbYesNo + vbDefaultButton2, "Change record")
I get nothing this event is not being called at all.

Question do I need to keep the previous event as well ?

Private Sub grdDataGrid_BeforeColUpdate(Index As Integer, ByVal ColIndex As Integer, OldValue As Variant, Cancel As Integer)
If MsgBox("Are you sure you want to change record ?", vbYesNo + vbDefaultButton2, "Change record") = vbNo Then
      Cancel = 1
  End If
End Sub

Open in new window


as what I did I commented them out as did not want to be asked this question each and every time the field get changed.

Appreciate your valuable input.
gowflow
>Question do I need to keep the previous event as well ?
No, you don't.

>Are you sure that the instruction
Call MySendKeys("{ESC}+{ESC}")
should be in
Private Sub grdDataGrid_BeforeUpdate(Index As Integer, Cancel As Integer)
Yes!

- Do you use different ADODC controls for each of these two datagrids?

- Please remove all breakpoints in this event, and try again.

- Here works well on Windows XP SP3.
All break points are removed I have win 7 64bits and it is not called !!!!!
please help !!!!

- Do you use different ADODC controls for each of these two datagrids?
Actually I only use 1 as the other is only used to do background take but is not physically used.
gowflow
- Since it is unclear whether you have both Datagrids bound with the same ADODC,
please try with only one Datagrid and only one ADODC control.

- Koment out code in all your ADODC control events as WillChangeField, WillChangeRecord and WillMove.

Works well here, as I have described it, also on Windows 7 64-bit.
Well well well eemit,

You are letting me review my entire code as this is a quite complex and intricate one as have been stumbling on this issue for long time now before I resigned myself to ask a question in here.

Let me show what I have so you can be in the picture and we can maybe sort this thing out.

The below picture shows you the setup of this form

User generated image
This Form contains the following controls:
On the left a treeview
Text boxes that are bound to the ADODC down below
grdDatagrid below the textboxes that is also bound to the ADODC

NOTE
When you pointed out the multiple grddatagrid I looked again at my code and noticed that for testing purposes I had created grddatagrid(0) and grddatagrid(1) that were overlapping but I only had 1 ADODC

As most of the instructions for grddatagrid(1) were commented out but still some were remained active I proceeded to change the whole code and removed all Indexes and deleted completely grddatagrid(1) so all my code ended up with grddatagrid ONLY with no index. I also delete all change/click/keypress/beforeupdate for grddatagrid(Index) and recreate them removing all refrences to indexes for grddatagrid.

Despite all this, when I cange values in the grid as you can see the Column Free Text in the picture has some data sfasfas and other fields as well are registered but when I move to the next record on the ADODC arrows I do not get the question Ready to Save the record.

I have to note that as the code is quite complex and envolves Text boxes that are bound and some that are just plain calculation on the right side I have envents in all of
Private Sub datPrimaryRS_MoveComplete
Private Sub datPrimaryRS_RecordChangeComplete
Private Sub datPrimaryRS_WillChangeRecord

note that my ADODC name is datPrimaryRS
I cannot remove the code in there but still have put F9 in all of these 3 events at the first line and the code does not go thru these events.

One last thing although I already mentioned it in my Question originally the Recordsource is a complex Shape SQL That I can put in here if this would help you troubleshooting

SHAPE
        {select ID,Vessel,Voyage,LOP,DIP,BL,DFR,Clauses,[Load Date],CorrectionHeader,SName,CName,CAdd,Abreviation,NName,[Arr Date],Booking,XTN,ClearingAgent,AdviceDetails,NotificationDate,DO,Invoice,DODelDate,OBL,Addons from [Details mid] WHERE Vessel & str(Voyage) = LCurrentVesselLoaded Order by BL} AS BLHeader

APPEND
        ({select Abreviation,BL,ID,EqType,QTY,[Car Description],Chassis,FreeText,NUMBERKIND1,[WGT KGS],CBM,BAS,BAS_P,OTH_P,BAS_C,OTH_C,CorrectionDetails,Endorsement from [Details mid] WHERE Vessel & str(Voyage) = LCurrentVesselLoaded Order by Abreviation,ID } AS BLDetails

RELATE BL TO BL) AS BLDetails

The Shape command is BLDetails as you can see
the first part get the data in the textboxes BLHeader and the BLDetails get the data in grddatagrid

Any idea would be greatly appreciated.
gowflow
- Remove all breakpoints from the project, and try again.
- Make a small test project with only Datagrid and ADODC to see if BeforeUpdate works.
Well I did what you suggested and removing all break point did not improve it.

2) Created a new project and imported this form to save time and added the requested library and components and got an error when trying to run it (and recall had the same error before but don't recall how I fixed it !!!)

User generated image

PLs advise how to go around this. I tried to remove the code from the MoveComplete event and deleted the header case it was not correctly entered and let the system recreate it then pasted back the code there and still it give me the error.

Any idea ?
gowflow
Please note that the code and message box text are not readable!
ok it is this error

User generated image
at this event

Private Sub datPrimaryRS_MoveComplete(ByVal adReason As ADODB.EventReasonEnum, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)

.... code

End Sub

the line Private Sub ... is highlighted at the error

these are the component and refrences used in my project

User generated image
User generated image
Greatly Appreciate you stiking with me.
gowflow
- Project references and components are good.
- Is your datPrimaryRS ADODC a control array, or you have 0 in Property Index?
Well what I did is to add in the new project all the refrences I had in the old one and I did not get the error but instead I am not able to Refresh my adodc I get this error at the following instruction:

datPrimaryRS.Refresh

First I get this error

User generated image
Then if I press F8 to continue I get this  error

User generated image
I am stuck here and can't get it to move forward.

May I ask you the question in reverse Can you please put a snapshot of the refrences you have in the small testing you did ?? As my project is quite huge it has 25 forms and 3 modules and have been developed and working at client for over 3 years now and only have an issue in this part. I also use in my database System.mdw to log users/passwords so it was really time consuming to make a sample to try isolate the issue as also use the registry to store data so quite complex.

Definitively I want to reach the bottom of this as have been really stuck in this module for over 1 year now and had to buy 3 books on ADO as working VB6 you don't get any support at this time.

So I truly appreciate all your time and hopefully will nail it down.

Tks again
gowflow
ok I got my sample to work (I was opening the database in exclusive mode) reason why I got previous error.) and still same results  no trapping of BeforeUpdate

Shall I remove all code in any of datprimaryRS ??

gowflow
UPDATE

I am able to trap
Private Sub grdDataGrid_AfterUpdate()

but still not able to trap
Private Sub grdDataGrid_BeforeUpdate(Cancel As Integer)

as have put both code in both events and still it only trap AfterUpdate which is too late if we say no.

gowflow
SOLUTION
Avatar of eemit
eemit
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I am missing a library
Microsoft ActiveX Data Object 6.0 BackCompat Library

why am I missing this ?
gowflow
>I am missing a library
>Microsoft ActiveX Data Object 6.0 BackCompat Library

Change it with your
Microsoft ActiveX Data Object 6.1 Library
ok nice

However I have a situation where your code fails and it goes like this:

Input anything in Adodoc1 say for record 3 change year to 1900
THEN while the cursor still on Year record 3 click on anycell in Adodc2 and change any item there
so far you are not prompt to change Record and that's fine as the cursor in Adodc1 is still on record 3 move the arrows left or right on Adodc2 then move the arrows on Adodc1 and you will not be prompt to save changes in adodc1

gowflow
Actually I just stumbled on an article from Microsoft that confirms that there is a BUG in the BeforeUpdate event

take a look at the proposed solution then we can discuss what I found and what I am facing maybe you can help me.

http://support.microsoft.com/kb/288346

gowflow
@gowflow

That is a bad situation.  VB6 is an old development platform and the problem description does not give me much hope that you will be able to fix the grid or the data control with a Microsoft patch.

You might restrict the mouse to the (clipped) area of the grid once a cell has been changed.
You will need to prevent the row header from being clicked.
You will need to hide or prevent user interaction with the navigation control.

You might restrict the mouse to the (clipped) area of the grid once a cell has been changed.
You will need to prevent the row header from being clicked.
You will need to hide or prevent user interaction with the navigation control.

aikimark, tks for joining, as much as VB6 is old I can't help but make this pgm work as it is used at client for all their business operation from input/order entry/invoicing/accounting etc..

this being said I would gratly appreciate 2 minutes of your time to translate your proposal into code as when it comes to these type of code I am not too clever at !!! :(

tks again for your input.
gowflow
It is possible to restrict mouse movement on the screen to a 'clip' area.  You would most likely limit the mouse to the data cells area of the grid.
http://www.freevbcode.com/ShowCode.asp?ID=1620

You will need to establish that restriction when any cell is altered.  You will need some keyboard indicator that the user wants to cancel their changes.
@aikimark

Noted your suggestion. In the additional info of the link I posted it list all of:


The BeforeUpdate event does not fire if you edit a row in the grid, and then move off the row by:
• clicking the Record Selector (the gray left margin) of another row in the grid.
• clicking on an unused (gray) portion of the grid background.
• moving off the grid by clicking on another control on the form.

and it is correct I tried each of these and the event is not fiered. Also somewhere in the article it propose a solution to this problem states


If the users of the application cannot be constrained to navigate the DataGrid by using one of the methods for which the BeforeUpdate event reliably fires, then another event or another approach must be used to validate entries or to take other action when the user moves from one row to another or moves off the grid.

What I did is put the following code in the Validate Event of the datagrid

Private Sub grdDataGrid_Validate(Cancel As Boolean)
If grdDataGrid.DataChanged Then
    grdDataGrid_BeforeUpdate False
    DoEvents
End If
End Sub

Open in new window


Now what it is doing is that when you click outside the grid it triggers the beforeupdate correctly HOEVER say I click on the arrows of the ADODC to move the record back or front then it would trigger BeforeUpdate and stops there it will not execute the move so the record being updated will still be there.

What code can I put in Validate event to not only trigger BeforeUpdate but also to execute after that whatever the user has started wethere a move or a click somewhere or anything else.

You can refer to the picture I posted in ID: 39258643 and see that if the user click on the Treeview for a new item (it will trigger the beforeupdate as the current record has been modified) but it will not display the new record that the user clicked on
Usually when you click in the tree view it automatically displays the item in the text boxes and their details in the grid.

Hope to get around that !! :)
gowflow
what events does the grid provide?  I was thinking of a cell-level change notification establishing the clip region.
gowflow,
I know for this MS Articcle.
But it stated:
The DataGrid only fires the BeforeUpdate event when the user moves from one row to another...
But not that on cancel in BeforeUpdate event Values in Grid are not revert to original values
as in BeforeColUpdate event.

My workaround with
Call MySendKeys("{ESC}+{ESC}")
is how to overcome this bug.
@eemit
yes but this is not my problem !!!

My problem is that it is not called altogether.

@aikimark

here are the events

User generated image
User generated image
User generated image
gowflow
@eemit
furthermore as you can see in my ID: 39258643 posted above most of my records are 1 row in the grid so most of the time I do not get the beforeupdate event fired in the sample you provided there are multiple rows and easily when you move from 1 row to the other you get this event fired it is only when you hit cancel that you get to have the data staying there.
you can also use SendKeys ("{ESC}+{ESC}") that will do the same job no need for the vb module.

gowflow
use the change event to signal a change in a cell and limit the mouse/cursor movement.
@aikimark
I set a variable MouseFreeze as Boolean to be true in change event if the datachanged is true and false in the other situation.

Now I need to know how to detect when the mouse is outside the border of the datagrid any idea ??
gowflow
use the clipcursor API to limit the mouse movement during an update.

Otherwise, you will need to write a MouseMove event for the code to detect non-grid movement.  However, that doesn't always catch everything.  I've seen mouse moves that were so fast that they went undetected.
you can also use SendKeys ("{ESC}+{ESC}") that will do the same job no need for the vb module.
One should not use SendKeys statement on Vista and above.
That's why you need this SendInput API wrapper.
Ah ic you mean as soon as datagrid is changed then run the Sub Mousefreeze and when update is done unfreeze.

I tried it and no effect whatsoever I debugged and it indeed call for MouseFreeze but the mouse still show normally.

gowflow
what area of the screen is your mouse limited to when you make the API call?
I just copied the link you posted:

'Put this in a module:

Private Declare Function ClipCursor Lib "user32" _
      (lpRect As Any) As Long

Public Sub MouseFreeze()
   ClipCursor Null
End Sub


Public Sub MouseUnFreeze()
'Release the mouse from ClipCursor
   ClipCursor ByVal 0&
End Sub

Open in new window


that it. and in grddatagrid_change event

Private Sub grdDataGrid_Change()
If grdDataGrid.DataChanged Then
    MouseFreeze
End If

End Sub

Open in new window


gowflow
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I think from user point of view, restrict the cursor movement to a certain area is not a solution.
If you have allways only one row then consider not to use a datagrid but maybe an usercontrol.
well it is not ALWAYS it is very seldom and sometimes have 10 rows or more but most of the time 1 row. cannot but use datagrid.

What is usercontrol by any chance ??
gowflow
@aikimark

I think your idea of restricting the screen to the datagrid area is doing the trick. I moved my buttons to be in the header of the grid bringing them front and when I change a field in the datagrid it automatically restrict mouse movements to only in the grid area including the buttons which is fine.

I am stumbling on coding issues now when it comes to update don't know if anyone could help

in my update button I have the following code

Private Sub cmdUpdate_Click()
On Error GoTo UpdateErr

'---> Set Bookmark
bkmark = datPrimaryRS.Recordset.Bookmark

'---> Update DataGrid
If grdDataGrid.DataChanged Then
    grdDataGrid_BeforeUpdate False
    DoEvents
    cmdCursor_Click
End If

'---> Update Record
datPrimaryRS.Recordset.Update

'---> Refresh Record
cmdRefresh_Click

'---> Set Bookmark
datPrimaryRS.Recordset.Bookmark = bkmark
Exit Sub

UpdateErr:
    MsgBox Err.Description
    Resume Next
End Sub

Open in new window


the behavior is when fields are changed and the user click on update it pop up Ready to Update (from the beforeUpdate of the datagrid) if the user press yes what it does is that it does not update the changes but revert to the data the way it was before the update.

Any idea ??
gowflow
It is not a good idea to call:
    grdDataGrid_BeforeUpdate False
You should normaly get MsgBox twice.
At least then when user after first MsgBox clicks on another row at several grid rows.

If you don't have a lot of updates in the grid, think again about using BeforeColUpdate event.
With a message like: "Are you sure you want to change this field?".
BeforeColUpdate event should work also by only one grid row, and dont need 2 <esc>.
@aikimark
@eemit

Thank a lot for your great help to demystify this issue and to realize the bug around BeforeUpdate event triggering which was causing all this trouble.

I was able to solve my issue with a good mixture of both of your suggestions on one hand to restrict mouse move to only the grid (provided by aikimark) which would channel the update thru the button hence control the behavior of update and also when it comes to multiple rows the introduction by eemit of the ESC sequence to reset the grid after update which is also a fault in beforeupdate event.

As far as splitting the points I have to admit that this solution would not be 100% workable if it was not for aikimark input hence the slight advantage I gave to aikimark.

Thank you again for both of you and looking for your help in the future
Regards
gowflow
Here is a new challenge, if you can assist it would be great.

https://www.experts-exchange.com/questions/28164595/ADODC-DataGrid-Copy-1-record-in-a-Recordset.html

Rgds/gowflow