Make a form command button override required fields focus?

J

JamesWB

Registered User.
Local time
Today, 16:13
Joined
Jul 14, 2014
Messages
70
I am using code like this to give warning messages and pass on focus to boxes only when things are selected.

Private Sub TextBox2_Exit(Cancel As Integer)
If IsNull(Me.TextBox2) Then
MsgBox "TextBox2 cannot be left blank"
Cancel = True
Else
ComboBox1.SetFocus
End If
End Sub

I do this for a series of required fields in order.

However, I also want a command button that is 'Exit without Saving' that should be clickable at any time, but of course when focus is controlled in my required fields sequence, it can't be clicked. How can I separate this button please?
 
BlueIshDan said:
Why is it that you force the user to focus on a specific field?
Click to expand...

There's a group of fields at the start of the setup process for populating a database of items, I need them all to be filled in before saving the item, these key bits of data are used in lots of other menus and queries in the database. It's basically like the form you use at the start to populate the base data into the database.
 
Would it be easier on the user and you, as the developer, to simply validate the fields on submission?
 
Some people have different thoughts on the process of these buttons. Could you describe what you have planned for these buttons to do.
 
BlueIshDan said:
Some people have different thoughts on the process of these buttons. Could you describe what you have planned for these buttons to do.
Click to expand...

Sure, they work with the following code:

Save and Add New Record

Private Sub SaveAdd_Click()
Me.LoopVal = CalcFourLoop(AccDescAVal, AccDescBVal, AccDescCVal, AccDescDVal)
RunCommand acCmdSaveRecord
DoCmd.GoToRecord , , acNext
End Sub

My aim is to save all the form input and open a new record for adding in the same form. The CalcFourLoop function generates a Calculated Field using the values of four other fields in an equation on save by the way.

Save and Exit

Private Sub SaveExit_Click()
Me.LoopVal = CalcFourLoop(AccDescAVal, AccDescBVal, AccDescCVal, AccDescDVal)
RunCommand acCmdSaveRecord
DoCmd.OpenForm "FrontSwitch"
DoCmd.Close acForm, "AddNewEquipment2"
End Sub

As above but simply goes to the main switchboard and closes down the record.


Exit without Saving

Private Sub ExitNoSave_Click()
Me.Undo
DoCmd.OpenForm "FrontSwitch"
DoCmd.Close acForm, "AddNewEquipment2"
End Sub

Get rid of any input, close the form and go to the main switchboard.
 
Code: 
' Create a validation function
Private Function Validation() As Boolean
        ' Checks if the string is null or empty:
        If Len(ctrl1.Value + vbNullString) = 0 Then: Return false
        If Len(ctrl2.Value + vbNullString) = 0 Then: Return false
        ' etc for each validation rule.
End Function
 
BlueIshDan said:
Code: 
' Create a validation function
Private Function Validation() As Boolean
        ' Checks if the string is null or empty:
        If Len(ctrl1.Value + vbNullString) = 0 Then: Return false
        If Len(ctrl2.Value + vbNullString) = 0 Then: Return false
        ' etc for each validation rule.
End Function
Click to expand...

Would I use that function in the form OnClose event? Sorry, I'm a bit of a novice at all this, how do I use that in conjunction with error messages and then send the user back to the form if they haven't filled in something important? Do I just put the focus back to the relevant control?
 
Oh my apologies, a bit groggy from it being Friday morning!

Place that in your save buttons
Code: 
      If Validation Then
 
Oh my apologies, a bit groggy from it being Friday morning!

Place that in your save buttons
Code: 
Private Function Validation() As Boolean
        ' Checks if the string is null or empty:
        If Len(ctrl1.Value + vbNullString) = 0 Then: 
               ctrl1.SetFocus
               MsgBox "Valuable Error Message" 
               Return false
        If Len(ctrl2.Value + vbNullString) = 0 Then: 
               ctrl2.SetFocus
               MsgBox "Valuable Error Message" 
               Return false
        ' etc for each validation rule.
End Function

Private Sub SaveAdd_Click()
       If Validation Then
             Me.LoopVal = CalcFourLoop(AccDescAVal, AccDescBVal, AccDescCVal, AccDescDVal)
             RunCommand acCmdSaveRecord
             DoCmd.GoToRecord , , acNext
       End If
End Sub


Private Sub SaveExit_Click()
       If Validation Then
             Me.LoopVal = CalcFourLoop(AccDescAVal, AccDescBVal, AccDescCVal, AccDescDVal)
             RunCommand acCmdSaveRecord
             DoCmd.OpenForm "FrontSwitch"
             DoCmd.Close acForm, "AddNewEquipment2"
       End If
End Sub
 
BlueIshDan said:
Oh my apologies, a bit groggy from it being Friday morning!

Place that in your save buttons
Code: 
Private Function Validation() As Boolean
        ' Checks if the string is null or empty:
        If Len(ctrl1.Value + vbNullString) = 0 Then: 
               ctrl1.SetFocus
               MsgBox "Valuable Error Message" 
               Return false
        If Len(ctrl2.Value + vbNullString) = 0 Then: 
               ctrl2.SetFocus
               MsgBox "Valuable Error Message" 
               Return false
        ' etc for each validation rule.
End Function

Private Sub SaveAdd_Click()
       If Validation Then
             Me.LoopVal = CalcFourLoop(AccDescAVal, AccDescBVal, AccDescCVal, AccDescDVal)
             RunCommand acCmdSaveRecord
             DoCmd.GoToRecord , , acNext
       End If
End Sub


Private Sub SaveExit_Click()
       If Validation Then
             Me.LoopVal = CalcFourLoop(AccDescAVal, AccDescBVal, AccDescCVal, AccDescDVal)
             RunCommand acCmdSaveRecord
             DoCmd.OpenForm "FrontSwitch"
             DoCmd.Close acForm, "AddNewEquipment2"
       End If
End Sub
Click to expand...

Oh, I see. Brilliant! Thanks. :)
 
BlueIshDan said:
Oh my apologies, a bit groggy from it being Friday morning!

Place that in your save buttons
Code: 
Private Function Validation() As Boolean
        ' Checks if the string is null or empty:
        If Len(ctrl1.Value + vbNullString) = 0 Then: 
               ctrl1.SetFocus
               MsgBox "Valuable Error Message" 
               Return false
        If Len(ctrl2.Value + vbNullString) = 0 Then: 
               ctrl2.SetFocus
               MsgBox "Valuable Error Message" 
               Return false
        ' etc for each validation rule.
End Function

Private Sub SaveAdd_Click()
       If Validation Then
             Me.LoopVal = CalcFourLoop(AccDescAVal, AccDescBVal, AccDescCVal, AccDescDVal)
             RunCommand acCmdSaveRecord
             DoCmd.GoToRecord , , acNext
       End If
End Sub


Private Sub SaveExit_Click()
       If Validation Then
             Me.LoopVal = CalcFourLoop(AccDescAVal, AccDescBVal, AccDescCVal, AccDescDVal)
             RunCommand acCmdSaveRecord
             DoCmd.OpenForm "FrontSwitch"
             DoCmd.Close acForm, "AddNewEquipment2"
       End If
End Sub
Click to expand...

Hi, just a quick additional question about the syntax. I tried using Return false in the function as in your example. That throws an error. So I tried Validation = False as the last line in the function. That works, but it doesn't trap the error state properly, it continues to throw a False even when the user has selected a value.

What syntax should I be using please? The control is a combo box.

thanks.
 
the easiest way to do this, is to leave all the validation until you try to save the record - and use the forms beforeupdate event.

you can't really have it both ways. you either validate each control before exiting it - OR you validate at the end. If you go the first way, then you aren't going to be able to exit that control prematurely - which may not give you the ideal user experience.

you could temporarily exit from a control by permitting a null, or zero in the controls validation test, and test it later.
 
gemma-the-husky said:
the easiest way to do this, is to leave all the validation until you try to save the record - and use the forms beforeupdate event.

you can't really have it both ways. you either validate each control before exiting it - OR you validate at the end. If you go the first way, then you aren't going to be able to exit that control prematurely - which may not give you the ideal user experience.

you could temporarily exit from a control by permitting a null, or zero in the controls validation test, and test it later.
Click to expand...

Yes, I've begun to realise that.

Out of interest, what's the reason for using Form's beforeupdate rather than OnClick please?

I'm using this kind of code now, which seems to work well, but it made me wonder what was wrong with On Click.

Private Sub SaveAdd_Click()
Dim txtMessage As String

' init error message
txtMessage = ""
' Check fields in reverse order to set focus to the first
If Not Len(Nz(Me.ItemCode)) > 0 Then
txtMessage = "You did not select a Device Code" & vbCrLf
Me.ItemCode.BackColor = vbRed
Me.ItemCode.SetFocus
Else
Me.ItemCode.BackColor = vbWhite
End If

' Check error found
If Len(txtMessage) > 0 Then
MsgBox txtMessage
Exit Sub
End If

Me.LoopVal = CalcFourLoop(AccDescAVal, AccDescBVal, AccDescCVal, AccDescDVal)
RunCommand acCmdSaveRecord
DoCmd.GoToRecord , , acNext

End Sub
 
a form's before update event fires whenever the record saves. in your case the instruction

RunCommand acCmdSaveRecord

saves the record, and causes the form's beforeupdate event to fire - although you probably do not have any code in there.

now it is possible that a user can save records in a number of ways, without clicking your button. What if they

a) just close the form
b) click the record selector (triangle/pencil), if they can see one
c) move to another record using the navigation button
d) from the toolbar, press records/save record
e) press shift+enter - which is a key board shortcut.
f) maybe even click on another form

all of these will/may cause the record to save, and make the form's beforeupdate fire - but they won't necessarily run your button click code.

All of this assumes you have a bound form, rather than an unbound one, of course, but I assume your form is bound.

this is not much different to what you do at present - all the code in your save-add button will work in almost exactly the same way as part of the form's beforeupdate event handler.

-----
in general your app interacts with various events. The trick is to try and respond to these asynchronous events.

Also, experienced Access users will expect not to have to click a "save record" button to save a record - so there needs to be a good reason to use a non-standard strategy.

-----
so for instance this last bit only happens if you accept the entry, and allow the update to proceed - so this actually then belongs in the form's afterupdate event (without the savereord statement). )not sure what the calcfourloop function does)

Me.LoopVal = CalcFourLoop(AccDescAVal, AccDescBVal, AccDescCVal, AccDescDVal)
DoCmd.GoToRecord , , acNext

----
hope this helps. good luck
 
Last edited:
gemma-the-husky said:
a form's before update event fires whenever the record saves. in your case the instruction

RunCommand acCmdSaveRecord

saves the record, and causes the form's beforeupdate event to fire - although you probably do not have any code in there.

now it is possible that a user can save records in a number of ways, without clicking your button. What if they

a) just close the form
b) click the record selector (triangle/pencil), if they can see one
c) move to another record using the navigation button
d) from the toolbar, press records/save record
e) press shift+enter - which is a key board shortcut.
f) maybe even click on another form

all of these will/may cause the record to save, and make the form's beforeupdate fire - but they won't necessarily run your button click code.

All of this assumes you have a bound form, rather than an unbound one, of course, but I assume your form is bound.

this is not much different to what you do at present - all the code in your save-add button will work in almost exactly the same way as part of the form's beforeupdate event handler.

-----
in general your app interacts with various events. The trick is to try and respond to these asynchronous events.

Also, experienced Access users will expect not to have to click a "save record" button to save a record - so there needs to be a good reason to use a non-standard strategy.

-----
so for instance this last bit only happens if you accept the entry, and allow the update to proceed - so this actually then belongs in the form's afterupdate event (without the savereord statement). )not sure what the calcfourloop function does)

Me.LoopVal = CalcFourLoop(AccDescAVal, AccDescBVal, AccDescCVal, AccDescDVal)
DoCmd.GoToRecord , , acNext

----
hope this helps. good luck
Click to expand...

Ahh, all very interesting indeed, thanks a lot!

I didn't realise for example that simply closing the form saves the record. Is there a property that stops that happening?

I am interested in what you said about regular Access users expecting not to have to click a 'save' button. I am dealing with an application where we won't allow users to see record navigation or Access menus and I wanted to try to isolate users within a structure of forms. Hence the custom form navigation buttons, but is there something I've missed in that scenario? Can people just dive into Access anyway somehow? I don't really want users to be able to see under the hood as the customer needs this to be a reasonably trustworthy paper tracking system and they don't want someone to be able to go in and edit records outside the forms and password.

:)
James
 
My bad James. You're right about the Validation = False thing. I've been working in a few different programming languages lately and I got it mixed up :D (Sorry for not testing in the access before testing my air code)
 
You must log in or register to reply here.

Similar threads

D
Solved Running a public sub when form gets focus
Replies
2
Views
165
dobseh
D
J
Command button not responding to double click
Replies
9
Views
261
John Sh
J
D
Solved Setting a field to null on Dirty
Replies
10
Views
319
dullster
D
M
Proper focusing of windows in MS Access (natively or by a code)
2
Replies
31
Views
867
MajP
MajP
Papa_Bear1
How do I allow a new record to be created (by button push) on a form, while requiring the user change one value from its default?
Replies
16
Views
909
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom