Events and SetFocus before reading control

[parry]

Hi, I am wondering whether I have done things properly with a form, and wanted to see if there is a better way.

Theres a form detailing revenue info and has a field where you can enter a rebate amount. If this value is entered then two other fields (AuthBy and Comments) fields must be completed.

Q1: Is the AfterUpdate event on the form the correct event? I want to ensure that saving/moving to another record will kick off the code.

Q2: In order to do the if statements to check the value of a control, I had to keep setting focus to it all the time. Is there another way? I also had problems with the last control being selected always being the FeeCharged field so decided to use the WhereAmI bit in the code.

Any suggestions appreciated.

Private Sub Form_AfterUpdate()
Dim WhereAmI As Control
Set WhereAmI = ActiveControl

'If RebateAmt > 0 then both AuthBy and Comments fields are compulsory
'Note: SetFocus is required before reading value of control.
RebateAmt.SetFocus
If RebateAmt.Text > 0 Then
'check if Authby is blank
    AuthBy.SetFocus
    If AuthBy.Text = "" Then
        MsgBox "Please enter the name of the person authorising the rebate.", vbExclamation, _
        "Authorised By name not entered"
        Exit Sub
    End If
'check if Comments is blank
    Comments.SetFocus
    If Comments.Text = "" Then
        MsgBox "Please enter a reason for the rebate.", vbExclamation, _
        "Comment Required"
        Exit Sub
    End If
End If

'check if Fee Charged is negative
FeeCharged.SetFocus
If FeeCharged.Value < 0 Then
RebateAmt.SetFocus
MsgBox "Are you trying to bankrupt us?" & Chr(10) & "How about reducing the rebate.", 16, _
"Fee Charged cannot be negative!"
End If

'Return focus to the last active control before error.
WhereAmI.SetFocus

End Sub

 

[SydneyGeek]

Hi Parry, you need to use the BeforeUpdate event of the form to prevent records being saved with inappropriate or missing values.

Denis

 

[parry]

Cheers thanks mate. What about reading values in fields on the form? In Excel, I sont need to set focus to the field to read its contents but it appears this is the case in Access. Is this correct?

 

[dk]

Cheers thanks mate. What about reading values in fields on the form? In Excel, I sont need to set focus to the field to read its contents but it appears this is the case in Access. Is this correct?

Parry,

Use the Value property instead. The Text property is only available when the control has focus. From the help file:-



<u>Remarks</u>

You can set the Text property to the text you want to display in the control.
You can also use the Text property to read the text currently in the control.



You can set or read this property only by using a macro or Visual Basic.



<font color="#FF0000">Note To set or return a control's Text property,
the control must have the focus, or an error occurs. To move the focus to a
control, you can use the SetFocus method or GoToControl action.</font>



While the control has the focus, the Text property contains the text data
currently in the control; the Value property contains the last saved data for
the control. When you move the focus to another control, the control's data is
updated, and the Value property is set to this new value. The Text property
setting is then unavailable until the control gets the focus again. If you use
the Save Record command on the Records menu to save the data in the control
without moving the focus, the Text property and Value property settings will be
the same.

 

[bat17]

'check if Fee Charged is negative
FeeCharged.SetFocus
If FeeCharged.Value < 0 Then
RebateAmt.SetFocus
MsgBox "Are you trying to bankrupt us?" & Chr(10) & "How about reducing the rebate.", 16, _
"Fee Charged cannot be negative!"
End If

This could just be done in the validation property of the control without needing any code

HTH

Peter