Avoiding access to save on close event

[morglum007]

Avoiding access to save on close event [Solved]

Hi there,

Okay, here I am again with another access problem. I have some troubles when saving data in bound forms.

Here is the picture;

I got a bound form with some textbox/combobox/checkbox controls, that appears on blank once form loads in order to make user to fill 'em.
If everything goes ok, at the end user could push a button that will save records into data base with no problems.
I am using the cancel=true statement in each control's afterupdate event to avoid access to save if user does not allow it.
Here is the code of the update data base button, that is working properly:

Private Sub updb_Click()
confirm = MsgBox("Data will be saved, Are you sure?", vbExclamation + vbYesNo, "Confirmation")
Select Case confirm
Case Is = 6
Cancel = False
Me.Dirty = False
Cancel = True
Me.Undo
DoCmd.GoToRecord , , acNewRec
MsgBox "Data saved", vbExclamation, "Operation completed"
Case Is = 7
Me.Undo
DoCmd.GoToRecord , , acNewRec
End Select
End Sub

Till here, no problem.

For a better front-end looking, and assuming users are not going to fill all fields, I added the close command with the red X right up in one corner (the usual one).
This automated command will obviosly close form, but will save data in entered fields, and thats something I want to avoid.

I have tried to clear all controls in the form's on close event, doing the me.undo trick, the cancel=true one, but no luck, data is still being saved.
I cannot also do the delete last record trick when closing because this form is used as edit/delete form with correspond control load, so data could be corrupted if I approach this way when loading in edit/delete mode.

I have read somewhere to load form as unbound, and to save data changing it to bound one. This will prevent the close button to save data if form is unbounded. A good approach, but how do I code it?.
Even more, if there are tons of fields, should this approach make me to change all fields properties in vba by hand?

I think I didnt missed anything.

Thanks for your patience and eventual reply

Morg

 

[gemma-the-husky]

the problem is that access is an "active" sort of application

there are loads of ways to save data - all of the below will save data with a bound form.

running code of various types
moving to a different record
closing the form
clciking the pencil in the record selector bar

with a bound form, the hard part is actually NOT saving the data, and normally you dont actually need a button to save the data.

so there are two ways really

a) in the forms before update event, put loads of validation code to check that the data is exactly validated and formatted correctly - and cancel the update if it isnt. Then if you close the form, your code will either prevent the close, or more likely you just get the "the record canty be saved now" message (I think the latter, offhand)

note that removing the from close button box is unususal - users expect to see a close button, and not having one is disconcerting - sometimes there is no other good way though.

b) use an unbound form - in which case you DO need some button code to save the data.

unbound forms are necessary for some things, but doing things with unbound forms is much harder than with bound forms.

 

[morglum007]

Well, thats the key, how do I code something to avoid data to be saved?

I have obtained sometimes the "Access cannot save this record and bla bla bla...", but this is ramdomly, and I havent controlled it yet.

So, here is the new question: some code to avoid access updating?
Need ideas,

cancel=true in which events?
me.undo in which events?
whats about disabling recorset in each control's afterevent event?
Any ideas?

Thanks

 

[gemma-the-husky]

its very difficult to do this

the trouble is that clicking the close button, saves the record, then closes the form. if the record cant be saved (because of validation issues etc) the form still closes, but you get the "cant save at this time" message. if the record can be saved it just saves without informing you. you could intercept the "cant save message" and replace it with one of your own.

you can stop the form unloading, in the on unload event, but this occurs AFTER the record save, so it doesnt help

i guess if its really a problem then you will HAVE to use an unbound form, or remove the form close button, and provide them with your own Save/Cancel/close buttons that tidy everything up as you want.

The other thing is to make users aware that if someone changes something it WILL automatically BE saved, unless THEY undo the edit before closing the form. Its just the way access works.

 

[morglum007]

Okay, then, assuming I can do something to make access to show the "Cant save data...:", how can I catch that message, and modify it?

Thanks in advance

 

[Galaxiom]

Probably not what you are after but one extreme way to avoid saving is to load the records being edited or added into a buffer table.

Existing records can be shown in a disabled form bound to the main table. Clicking the edit button loads the current record into the buffer table, changes the record source of the form and enables the controls. Or the record source of the form can be a query that presents a combination of the main and buffer records. Pretty much anything is possible with unbound controls but it adds a lot of extra work.

Add an extra Y/N field to the buffer table to indicate the record should be saved to the main table at the end of the session. You can then review the work from the entire session before saving, choosing what is to be save if you want. Saving is an update query or append query (or both) to write the records to the main table.

Of course then the record locking is a problem and multiple users accessing the same record can lead to changes being in conflict. This can be worked around with all users writing to the buffer table. The editing user ID is also recorded in the buffer table and only records edited by that user would be shown.

It can even be configured so conflicting edits can both be recorded in the buffer table and the two versions presented to the user to select the one to save. Users can be allocated a heirarchy to indicate whose record will be saved.

 

[gemma-the-husky]

Okay, then, assuming I can do something to make access to show the "Cant save data...:", how can I catch that message, and modify it?

Thanks in advance

just use the forms error event

see what the accesserror number is, and replace it with your own message.

 

[morglum007]

Okay, finally succeded avoiding access to save data, but in the other way, it wont save in any case, so I had to do some tricks.

In the form's beforeupdate event I set this code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If denyflag = True Then
Cancel = False
Else
Cancel = True
End If
End Sub

which will avoid access to save any data to tables.
As you may see, I set another variable, which will make cancel=true most times, excepting when I launch some code from update data base button. This will prevent access to save any data, and "Access cannot save data..." msgbox will appear.

My problem now is to copy and update data in form with same data, in order to force the "before update event" fire again. Because denyflag variable now had changed, cancel=false, and then data can be saved into data base.

I am trying this with this code in the update data base button:

Private Sub updb_Click()
confirm = MsgBox("Data will be saved, Are you sure?", vbExclamation + vbYesNo, "Confirmation")
Select Case confirm
Case Is = 6
Dim ctls As Object
  For Each ctls In Me.Controls
      denyflag = True
      Me.SetFocus
' (Here it is supposed to insert some code to copy value, and relaunch beforeupdate event)
  Next ctls
  DoCmd.GoToRecord , , acNewRec
MsgBox "Data saved", vbExclamation, "Operation completed"
Case Is = 7
Me.Undo
DoCmd.GoToRecord , , acNewRec
End Select
End Sub

which of course, is incomplete.

I found this approach more successful than others previously discussed, so, any hints?

Thanks again

TIP: It seems I always got Access to the limit.... damn it!

 

[morglum007]

Okay,finally, after tons ofs tries, I did it.
This is how I proceeded.

To avoid Access 2007 to save on close event, just used this code

Private Sub Form_BeforeUpdate(Cancel As Integer)
denyflag = TempVars("denyflag")
If denyflag = True Then
Cancel = False
Else
Cancel = True
End If
End Sub

This will prevent Access to save data if variable denyflag is "false", so lets do it when form gets loaded:

Private Sub Form_Load()
DoCmd.Maximize
Call ReSizeForm(Me)
Call xg_SizePopUpForm(Me, 1)
Dim denyflag As Boolean
denyflag = False
TempVars("denyflag") = denyflag
language = TempVars("language")
Dim lng As Object
  For Each lng In Me.Controls
       Select Case lng.ControlType
       Case acLabel, acCommandButton
       On Error Resume Next
           templng = lng.Name
           Label = "" & DLookup("[language]![" & language & "]", "language", "[language]![label] like '" & templng & "'")
           lng.Caption = Label
       End Select
Next lng
End Sub

As you may see in the correct place, I set the denyflag variable to "false", so Access wont save data in any case with the above's code.

So, here it comes the interesting, lets do access to change denyflag variable with a "Save button".

In such command button there is the next:

Private Sub updb_Click()
confirm = MsgBox("Data will be saved, Are you sure?", vbExclamation + vbYesNo, "Confirmation")
Select Case confirm
Case Is = 6
Dim ctls As Object
  For Each ctls In Me.Controls
  Select Case ctls.ControlType
  Case acTextBox, acComboBox
      denyflag = True
      TempVars("denyflag") = denyflag
      Me.SetFocus
      DoCmd.Save
      End Select
  Next ctls
  denyflag = False
MsgBox "Data saved", vbExclamation, "Operation completed"
Case Is = 7
Me.Undo
DoCmd.GoToRecord , , acNewRec
End Select
End Sub

So, finally, with this setting, Access will only save if button is pressed, and will reject to update database if button isnt pressed.

Now I must fix the "data could not be saved... bla bla bla.."

So, here again I am looking for any idea. Someone told to catch the warning event.

So, how do I that?

Thank you so much ppl, I am learning a lot because of u all.

(Hope you also could learn something from me)

TIP: in the above's code there is also some code for translations into different languages. Data base is available in multiple languages ^^, translating "on the fly".

 

[dfenton]

This code is awfully convoluted:

Private Sub Form_BeforeUpdate(Cancel As Integer)
  denyflag = TempVars("denyflag")
  If denyflag = True Then
     Cancel = False
  Else
     Cancel = True
  End If
End Sub

This code is exactly equivalent:

Private Sub Form_BeforeUpdate(Cancel As Integer)
  Cancel = TempVars("denyflag")
End Sub

It's not entirely clear to me why you're using TempVars, but then I've not programmed in A2007 enough to understand the purpose of TempVars. I would tend to make a custom property of the form for this and use that. Something like this:

' in form module declarations
Dim bolDenySave As Boolean

' in form module
Private Property Let DenySave(pbolDenySave As Boolean)
  bolDenySave = pbolDenySave
End Property

Private Property Get DenySave() As Boolean
  DenySave = bolDenySave
End Property

Within the code you'd set Me.DenySave to True or False appropriately, and you'd set Cancel to Me.DenySave. To me this makes for much cleaner code.

 

[morglum007]

Thank you so much for the reply dfenton, and thanks for the hints as well.

I am not a programmer, and have learnt vba for access in just few months, so this kind of things is going to happen so often.

In any case, nobody here said to me how to proceed with this, so, at the end I finally reached my objective. This way, mission accomplished, and no matter how I did it.

By the way, obtained some weird behaviour, so, again searching for solutions here.

As I stated before, first, how may I catch the warning messages from Access from VBA. To be exactly, I want to catch the "Access could not save data, so..." and replace it with my own.

------SOLVED In second place, and the weird thing, is that when working in design mode, the above's code is working quite well. Once I run data base in native mode (protected one, with no ribbon,no visual tables, bypass shift key and so on) data seems to be saved, but it does not. The amazing thing is that it works in design mode. Any ideas about why this is happening? -------SOLVED

And finally, as an aside note, is it possible to access to make multiple selector table to build a pivot table?.
To be clearer, I want to make something like the relationship window, in which all tables appears, and with that ones, let the user to build a personal pivot table. As I am concerned at the moment, no way for access. Can someone here point me out this way?

Thank you so muck all for your replies.

Sincerelly, Morg

TIP: I dont really know why Access introduced this tempvars, but as novice assembler programmer (...) I might think Microsoft did it because multiple users getting in at the same time in database. The public string is always present, and you can call it at any moment. With tempvars, variable is not available unless you call it (variable isnt in "active" memory all the way). This is a big advance when working in multi-user mode, making Access safer (and also quite stable).

 

[dfenton]

I dont really know why Access introduced this tempvars, but as novice assembler programmer (...) I might think Microsoft did it because multiple users getting in at the same time in database. The public string is always present, and you can call it at any moment. With tempvars, variable is not available unless you call it (variable isnt in "active" memory all the way). This is a big advance when working in multi-user mode, making Access safer (and also quite stable).

No one with any sense ever has multiple users opening a single front-end application database, so that can't be it.

 

[morglum007]

Okay, I am not going to discuss you this way, but thats my opinion.

As example, what's going on with splitted data base? I think this is a total multi-user interface, loading "front-ends" in guest comps, and retaining "back-end"(tables), "secured" in server.

In any case, thats my experience in other matters, so I am not going to continue this, cause this is not the appropiate forum to do so.

Could someone point me where to catch access warnings?

There is an error event, maybe this is the zone I am looking for?
In this case, could someone tell me the warning key codes access is using, so I could trap 'em?

Regards, Morg