Form saving changes without hitting save 1

[KentMorand]

I'm trying to force users to hit save in order to save a record but if they scroll or even close the form altogether any info they had typed in is saved. Any ideas why this is happening? Thanks in advance for your help.

 

[RoyVidar]

That's just how Access works.

If you wan't to have users hit a save button, you'l have to build your form with only unbound controls. It's a bit more work and coding...

HTH Roy-Vidar

 

[KentMorand]

Well that sucks... thanks for the info though.

 

[mph1]

Roy-Vidar is correct. Anytime you close a form, go to a new/other recored the current record is saved. There are however several things you can do to help prevent this.

1. Disable PgUp/PgDn keys
2. Disable mouse wheel
3. In the On Exit of the form, use vb yes/no (good reminder)
4. Add an undo statement.
5. If a certain field must be filled in and left blank,
then use code on exit to return to that field.
6. Maybe set up a delete key


Hope some of these ideas are helpfull.

Maurie

An investment in knowledge always pays the best dividends.

by Benjamin Franklin

 

[KentMorand]

If one were to unbind the controls on the form what would the SQL look like to add a record to the table. I just forget how that SQL looks. Thanks again for all of your help.

 

[RoyVidar]

Hi!

You'll have to do some checking of course, to see if the values ar appropriate.

Then, with a table tblTest with the fields ID (Autonumber) Name (text) and Salary (Numeric, single), the form having the controls txtName and txtSalary, I'd probably:

dim sSql as string
ssql="insert into tbltest (Name, Salary) " & _
"Select '" & me("txtName&quot & "' as expr1, " & _
me("txtSalary&quot & " as expr2"
docmd.setwarnings false
docmd.runsql ssql
docmd.setwarning true

in the forms on close event

HTH Roy-Vidar

 

[willir]

KentMorand

There are ways around this... uh, "feature".

One way is to have the user enter the data into unbound fields -- fields not tied to the data table.

Then the user clicks on the "Save" button and the records magically are written to the table.

Alright, thigs dont happen by magic. You have to write some code. And there are various ways around this. Since you seem to be relatively new to Access, a simple approach may be to hide your "bound" fields on the form. When the user clicks "save" the data is transfered from the unbound fields to the bound fields....

I am going to assume the unbound founds are prefixed with the name "unb", and use a simply customer entry form

For the On click event for the command button used for saving / committing the record...

Me.CustomerName = Me.unbCustomerName
Me.Address1 = Me.unbAddress1
Me.PhoneNo = me.unbPhoneNo
etc...
then commit the record

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

Of course, if you use unbound fields to collect the information, for the On Current event, you have to transfer the data from the bound fields to the unbound fields.

This is one of several work arounds if you do not like this "feature".

Richard

 

[LarryDeLaruelle]

Kent:

Another approach, the one I use, is to lock all controls (except record selector combo's) on the form when the form opens. I use an Edit command button that unlocks the controls for editing, an Add command button to add a new record and unlock the controls, a Save command button to commit any changes and an Undo command button to allow the user to undo any changes before saving. In the click event of the Save and Undo command buttons I relock the controls.

This can be a pain if there are a lot of controls on the form but some creative copy/paste and edit/replace can help minimize the typing.

I also hide the default navigation buttons and use custom buttons; these are disabled when in edit mode and re-enabled after a save or undo.

To add to Maurie's list, set the form's cycle property (Other tab) to the current record; this will prevent the user from tabbing into the next record.

I have a sample database that demonstrates this. If you are interested, send me your email address and Access version (I have 97 and 2K) and I'll zip it and send it to you.

Keep in mind, if there is a way to screw things up the user will find it; our job is to stay one step ahead of them.

HTH

Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[RoyVidar]

Hi!

Just a question - doesn't SHIFT+ENTER save current record? Would Undo then undo the changes?

I've seen and tryed some of LarryDeLaruelle's tip earlier on, but had to give in and program with unbound.

My users were one step ahaed of me

Looking forward to trying your code LarryDeLaruelle, expect an email.

Roy-Vidar

 

[danvlas]

Close event is preceded by Unload event.
Use the Dirty property to deny closing the form (Cancel = True) if changes are not saved.

Shift+Enter indeed attempts to save the record if you don't set it inactive through the AutoKeys macro.

Moving to another record (thus saving the record) can be prevented by using the BeforeUpdate event of the form. Just pop a yes/no dialog and Cancel=True if answer is No.

This should be combined with the Save button code-you need a flag to indicate that the button was pushed. This would also solve the Shift+Enter problem, without AutoKeys.

HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[mph1]

Max,


1. Disable PgUp/PgDn keys -

http://www.mvps.org/access/forms/frm0016.htm

2. Disable mouse wheel - search this forum, I know I have seen it, but don't remember off hand

You can also disable the shift key, or any key for that matter.


An investment in knowledge always pays the best dividends.

by Benjamin Franklin

 

[KentMorand]

Wow you guys have been a ton of help. Thanks again for all of this info. Larry I just emailed you so you can send me that sample db, I would love to get it. I have a ton of controls on this form.. is there any code that will lock them all in one go?

 

[LarryDeLaruelle]

Kent:

I thought there was a lock property for the form but I was just looking and could not find it. Anyone know if what Kent is asking is possible -- to lock the entire form?

My solution is not fool proof; I hadn't considered the page up/down keys or the mouse scroll, so I'll be testing those and checking the site mph1 suggested above.

Always one darn thing after another but that's what I love about working with Access -- the challange.

As soon as you let me know which version of Access you use, I'll get a zip file off to you.

Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[willir]

My perferred method is to use a toggle button to shift between edit mode and read only mode.

Here is the gotcha...
If you set the form not to allow edits, then you will not be able to use an unbound combo or list box to say select a specific record, or retrieve by date.

To get around this, I...
- Create a toggle button
-- Down allows edits, text for button displays red "ALLOW EDITS", sometimes even change colour on fields
-- allows queries, but does not allow edits / additions / deletions, text for button displays blue "NO EDIT"
-- Form always opens in NO EDIT mode. User has to click on toggle button to switch to edit mode.
-- "Behind" the toggle button, and the On Load event, calls a sub routine that CAN check for security, and will individually set the fields and subforms in the form to either edit or read mode.

Snippets of code for On Click for Allow_Edits toggle button

Dim boUpdate as Boolean

boUpdate = Me.Allow_Edits 'either true or false
' set to false when form is opened
' set to true when toggle button "down"
' set to false when toggle button "up"
' Pressing "down" changes value from false to true


If boUpdate Then
Me.allow_edits.Caption = "Allow Edits"
Me.allow_edits.ForeColor = 255
Else
Me.allow_edits.Caption = "No Edits"
Me.allow_edits.ForeColor = 16737843
End If

Me.AllowAdditions = boUpdate
Me.AllowDeletions = boUpdate

'okay now switch for field properties
'for Allow additions, want to unlock fields
'for not Allow additions, want to lock fields
boUpdate = Not boUpdate

Me.Field1Name.Locked = boUpdate
Me.Field2Name.Locked = boUpdate
Me.SubForm1.Locked = boUpdate
etc

I realize that some serious coders could think of better ways of doing this...


Unbound fields are not touched.
The form Allow Edits property is not touched
The form Deletions / Additions is toggled
Sometimes will use the DataEntry = true when adding a new record.

I suppose there may be a way to use a loop for the fields and choose only bound fields and subforms to toggle. Like many of us, I am still learning. This would be a cool way since I could then use a public module for any form.

Getting back to the issue raised by KentMorand...
I tried to keep my original post simple since he does seem to be a little new to Access. Have any of us helped you out??

Richard

 

[KentMorand]

Oh yeah this has been a real good lesson. I am fairly new to access as you had guessed. I had some training in it and was half decent at one time but haven't used it in eons and there is definitely some rust... I was able to use the bound/unbound idea on one of my simpler forms and it worked like a charm. The other more complicated form has way too many controls to do bound/unbound so I'm going to use the vb warning box on close of the form and disable all scroll buttons when a user clicks a custom add button. I'm still working on it but I think I have it close. I really appreciate everyones help and hopefully I will be able to repay your kindness to someone else down the line. Thanks again!

 

[mph1]

I really appreciate everyones help and hopefully I will be able to repay your kindness to someone else down the line


IMO, I think the best way is to, as you suggest, help others.


An investment in knowledge always pays the best dividends.

by Benjamin Franklin

 

[mph1]

Max,

To disable the mouse wheel, check this out.

http://support.microsoft.com/default.aspx?scid=kb;en-us;278379

Microsoft Knowledge Base Article - 278379

I knew it was here somewhere.

HTH

An investment in knowledge always pays the best dividends.
by Benjamin Franklin