Adding a record

[Guest]

How do you design a form to only edit or add records to the tables after the
record has been saved?

Also, how do you change the function of the scroll button on your mouse to
scroll up and down the form itself rather than through records in the
database?

 

[Guest]

On the first point I assume you want to prevent deletions, in which case
create a form bound to the table or query in the usual way and set its
AllowDeletions property to False (No in the properties sheet).

On the second point there are a couple of ways, which you'll find at the
following links:


http://www.lebans.com/mousewheelonoff.htm


http://www.openaccess.co.za/BlackAndWhiteInc/MouseWheel.htm


Ken Sheridan
Stafford,

 

[Guest]

Regarding your response to my first question, I am not really trying to
prevent deletions as I already have that property set to 'no'. What I am
trying to do is force the user to verify that they have fully completed
editing or adding a new record. Rather than have an annoying pop up box
display when they try to move off of the record, I added a save button to my
form. I want the user to be forced to click the save button before the
additions or changes will take effect. The save button seems to be useless
though since additions and changes to records on the form seem to be
happening almost simultaneously with the records in the table. The scroll
button on the mouse actually made this evident to me while testing because I
accidently hit it while editing a record and when I went back the changes
were had already been saved. Can this be avoided?

 

[Guest]

OIC

Forms have a BeforeUpdate event procedure which includes a Cancel argument,
so you can set the return value of this to True if the Save button has not
been clicked. To flag this toy first need to declare a module level variable
of Boolean data type whose value can be changed when (a) the button is
pressed and (b) when the form's Current event fires, so the code in the
form's module would be like this:

Option Compare Database
Option Explicit

Dim blnSaved As Boolean

Private Sub cmdSave_Click()

blnSaved = True
On Error Resume Next
RunCommand acCmdSaveRecord
If Err <> 0 Then
blnSaved = False
End If

End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)

Cancel = Not blnSaved

End Sub

Private Sub Form_Current()

blnSaved = False

End Sub

If the user tries to navigate to another record before saving the current
one with the button nothing will happen. If they try to close the form after
editing a record but without saving it with the button they'll get a warning
message which allows them to close without saving or to return to the form.

The error handling in the button's Click event procedure above is the bare
minimum necessary. In a fully developed application it would be more
extensive of course.

Ken Sheridan
Stafford, England

 

[Guest]

Does this work on forms that also contain subforms? I think I may have ran
into a problem. The code seems to work, but now my subforms act as if they
are locked. I took the code out and they worked again. Is there some code I
need to put in for the subforms.

Thanks

 

[Guest]

When you move focus from a parent form to a subform the parent form's current
record is updated. This is necessary because a subform commonly is based on
a table which references the parent form's table in a many-to-one
relationship. Consequently if the parent form's record was not updated
referential integrity could be violated by adding a new record in the subform.

What you are asking for is therefore paradoxical, as to enable the subform's
data to be edited while at the same time allowing the parent form to be
updated only via a 'Save Record' button means that the only way the subform's
data can be edited would be to save the parent form's current record with the
button. This is a situation where you can't have your cake and eat it I'm as
far as I can see, apart from disabling the mouse wheel using one of the
methods in the links I gave you.

The only possible solution which springs to mind would be to monitor the
cursor position via regular calls to the Windows API in the parent form's
Timer event procedure, and update the parent form's record if the cursor is
over the subform. I would not recommend this however as it would only need
the user to move the mouse over the subform momentarily to inadvertently save
the parent form's record, which would undermine the benefits of saving only
via the button. Should you wish to try it, however, the following module
includes a GrabCursor procedure which assigns the values of the cursor
coordinates to two public:

Option Compare Database
Option Explicit

Type POINTAPI
X As Long
Y As Long
End Type

Declare Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long

Declare Function SetCursorPos Lib "user32" (ByVal X As Long, ByVal Y As
Long) As Long

Public lngCursorX As Long, lngCursorY As Long

Public Sub GrabCursor()

Dim dl As Long
Dim pt As POINTAPI

dl = GetCursorPos(pt)
lngCursorX = pt.X
lngCursorY = pt.Y

End Sub

Public Sub RestoreCursor()

SetCursorPos lngCursorX, lngCursorY

End Sub

Ken Sheridan
Stafford, England