SetFocus / DoCmd.GoToControl Problem

[CCSC0208]

Hello,

I use the following function to compare a value(FieldDouble) in B Database to aother value(FieldOrig) in a linked table from A Database. If the FieldDouble is entered differently from FieldOrig, I want the focus stays in FieldDouble.
Neither FieldDouble.SetFocus nor DoCmd.GoToControl VarDouble works. The cursor still goes to the next field without error message. SendKeys "{TAB}", False takes the cursor to the second field after FieldDouble. Can somebody help me fix this problem?

Public Function Compare(VarDouble As String, FieldOrig As TextBox, FieldDouble As TextBox)

Dim Result As Integer
Dim Result2 As Integer

If FieldOrig = FieldDouble Or (IsNull(FieldOrig) And IsNull(FieldDouble)) Then
Else

Result = MsgBox("DATA ENTRY ERROR!" & Chr(13) & Chr(13) & "The ORIGINAL value entered for this field was ( " & FieldOrig & " )" & Chr(13) & Chr(13) & "Your CURRENT entry is ( " & FieldDouble & " )" & Chr(13) & Chr(13) & "Is ( " & FieldDouble & " )" & " the correct entry for this field?", vbYesNo + vbDefaultButton2)


If Result = 6 Then
Result2 = MsgBox("CONFIRM CHANGE!" & Chr(13) & Chr(13) & "Are you sure ( " & FieldDouble & " )" & " is the correct value for this field?" & Chr(13) & "The ORIGINAL value will be changed to ( " & FieldDouble & " )", vbYesNo + vbDefaultButton2)

If Result2 = 6 Then
FieldOrig = FieldDouble
Else
MsgBox "Please enter the correct value for this field"
'SendKeys "{TAB}", False
'DoCmd.GoToControl VarDouble
FieldDouble.SetFocus
End If

Else
MsgBox "Please enter the correct value for this field"
'SendKeys "{TAB}", False
'DoCmd.GoToControl VarDouble
FieldDouble.SetFocus

End If
End If
End Function

 

[CCSC0208]

I am using this function "on Exit."
Thank you for any input!

 

[RickSpr]

What you're doing here is a validation of entered data. Validations should always be done in the BeforeUpdate event. BeforeUpdate has a Cancel parameter that, if you set it to True, automatically keeps the cursor from leaving the control, and prevents the current value being stored in the record. (By the time the Exit event occurs, the value has already been stored in the record--though the record hasn't been saved yet.)

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[CCSC0208]

Hey, Rick!

Thank you for your reply.

I tried to use the same code on the BeforeUpdate event, but I got Run-time error 2108 and the "FieldDouble.SetFocus" statement was highlighted. "DoCmd.GoToControl VarDouble" had the same message. Do I need to change anythig in these codes to fit the BeforeUpdate event?

I took these codes from another person's database. This function was used in the OnExit event, and it worked fine. Do you think there in any other reason that this function doesn't work in my database?

Thank you for your time,
CC

 

[RickSpr]

With the code in the BeforeUpdate event procedure, use the statement:

    Cancel = True

instead of trying to do either SetFocus or GoToControl. You don't need either of the latter statements because setting Cancel = True cancels the cursor movement as well as the update.

Trying to make this code work in the Exit event is not advisable. It may appear to work in the limited circumstances under which you're testing it, but there are failure scenarios. Specifically, try this:
1. Open the form and move the cursor to a field that is validated in the Exit event.
2. Change it in a way that will cause a validation error, but instead of tabbing or clicking out of the control, navigate to the next record.
3. Return to the previous record. Note that the data was updated, but that you did not receive an error. The reason is that the record was saved without the cursor ever leaving the control--which is when the Exit event occurs.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[CCSC0208]

Rick,

Public Function Compare(VarDouble As String, FieldOrig As TextBox, FieldDouble As TextBox)

Dim Result As Integer
Dim Result2 As Integer
Dim Cancel As String

If FieldOrig = FieldDouble Or (IsNull(FieldOrig) And IsNull(FieldDouble)) Then
Else

Result = MsgBox("DATA ENTRY ERROR!" & Chr(13) & Chr(13) & "The ORIGINAL value entered for this field was ( " & FieldOrig & " )" & Chr(13) & Chr(13) & "Your CURRENT entry is ( " & FieldDouble & " )" & Chr(13) & Chr(13) & "Is ( " & FieldDouble & " )" & " the correct entry for this field?", vbYesNo + vbDefaultButton2)


If Result = 6 Then
Result2 = MsgBox("CONFIRM CHANGE!" & Chr(13) & Chr(13) & "Are you sure ( " & FieldDouble & " )" & " is the correct value for this field?" & Chr(13) & "The ORIGINAL value will be changed to ( " & FieldDouble & " )", vbYesNo + vbDefaultButton2)

If Result2 = 6 Then
FieldOrig = FieldDouble
Else
MsgBox "Please enter the correct value for this field"
Cancel = True
End If

Else
MsgBox "Please enter the correct value for this field"
Cancel = True

End If
End If
End Function


I made the changes in red and set it to BeforeUpdate, but it still didn't work. I tried these in the database where I took the codes from, it didn't work either. Could you help me more in this issue?

The database that I took the codes was designed in Access97, and I converted it to Access2002. After I played with this function so many times, this function with either SetFocus or GoTocontrol doesn't work any more in the converted database. Does the edition of access make any difference?

Thank you,
CC

 

[RickSpr]

I take it you don't actually understand enough about VBA and event procedures to be attempting this, so you're asking me to rewrite it for you. I'm sorry, but Tek-Tips is not a free programming service. The most I will do is outline the process for you.

Set the text box's BeforeUpdate property to "[Event Procedure]", then click the "..." button to the right. This will create an empty BeforeUpdate event procedure.

Take the body of the function (minus the Function header and the End Function statement) and put it in the BeforeUpdate event procedure. This is what I meant by "With the code in the BeforeUpdate event procedure...".

Delete the Dim Cancel statement. Cancel is a parameter of the BeforeUpdate event procedure, not a variable.

Replace "FieldOrig" and "FieldDouble" with the names of your actual text boxes, wherever they occur in the procedure.

With regard to Access 2002, I don't have it, but I'm quite sure there is nothing in this function that would fail to compile. I really have no idea what "[it] doesn't work any more" means, especially since your initial complaint was that it didn't work to begin with.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[CCSC0208]

Rick,

Thank you very much for your patience and willingness to answer my question.

I got this function work by setting focus to another field and then setfocus back to the field I want. I am new in VB and have read other threads about this issue. I just couldn't believe that this sefocus works so "indirectly" in my database because the original codes did work perfectly in another database in Access97.

CC