Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Forms Programming / July 2005

Tip: Looking for answers? Try searching our database.

Required fields at form level

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Seth - 31 Jul 2005 05:42 GMT
I have a field that can not be set as required at the table level so I have
code at the form level to decide if the field is required, and then force
entry of the required data.

I have placed this code in Form_BeforeUpdate. It works as intended if I use
the record selector buttons to change records.

However, if I close the form using the close button the message box is
displayed but when I click ok the form closes and does not give me a chance
to enter the required data.

How do I stop the form from closing?

Another thing: Why does Access automatically insert ‘(Cancel As Integer)’
after ‘Private Sub Form_BeforeUpdate’?

Thanks,
Seth

Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(Me!cboTestRprtVRangeLow) = False And IsNull(Me!cboTestRprtFreq) =
True Then
   MsgBox "You must enter a frequency rating."
   Me!cboTestRprtFreq.SetFocus

ElseIf MsgBox("Changes have been made to this record." _
   & vbCrLf & vbCrLf & "Do you want to save these changes?" _
   , vbYesNo, "Changes Made...") = vbYes Then
   DoCmd.Save

Else
   DoCmd.RunCommand acCmdUndo
End If

End Sub
Allen Browne - 31 Jul 2005 07:39 GMT
Access gives you the Cancel argument so you can cancel the event, i.e. stop
the record being saved. Just set Cancel to True to kill the save.

You can optionally undo the form as well, which is saver than:
   DoCmd.RunCommand acCmdUndo
because:
a) it works even if the form does not have focus, and
b) it undoes all changes, not just the one field.
So:
   Cancel  = True
   Me.Undo

You do not need:
   DoCmd.Save
That does not save the record anyway (it saves form designs). More
importantly, Form_BeforeUpdate only fires when Access is trying to save the
record, so unless there is a problem or you cancel the event, the save will
happen.

If the user clicks the close button at the right of the form's title bar
when the record cannot be saved, Access (appropriately) gives the user the
choice to go back and fix up the bad entry or lose it. That makes sense.

However, if the form is closed using the Close action in a macro or the
Close method in code, Access *loses* the entry, *without* notifying the user
that the entry failed! From the user's point of view, they have no idea the
data was not saved, or why, and later they may come to believe that Access
is an unreliable program that cannot be trusted to save the data.

To avoid that problem, always explicitly save before using the Close action
on a bound form. In a macro, use the RunCommand with SaveRecord. In code:
   If Me.Dirty Then Me.Dirty = False
   DoCmd.Close acForm, Me.Name

Signature

Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I have a field that can not be set as required at the table level so I have
> code at the form level to decide if the field is required, and then force
[quoted text clipped - 35 lines]
>
> End Sub
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.