Moving between records

[marcuscoker]

Hi

I was just wondering which event would I use to make something happen when a user uses the navigation buttons to move between the reocrds on a form. I have written something that basically, validates a form, and I have attached it to the close button on the form, but now want it to also kick in when the user navigates through the records

Thanks

Marcus

 

[john471]

If you are validating the form, presumably you only need to do this after the user changes any data, but before it is saved (to prevent saving of invalid data), and not if the user is merely scrolling through (viewing) records.

The correct place to do this is the Form's BeforeUpdate event. If the validation fails, set cancel to True in that procedure, which will force the user to either meet the validation requirements before the data will be saved in the table(s), or escape out of their changes.

The BeforeUdate event is not fired as you move from one record to another unless the form's data has been modified, so this way the validation is not performed if the user is merely scrolling through records without changing them.

HTH

Regards

John

 

[marcuscoker]

Thanks

Hi John

Thanks for that. Gave it a try and it does what I want, but there is one small problem, I was hoping you could help me with.

The code works and highlights the field as being invalid. However, it still procedues to the next record. Can you tell me what is wrong with my code. I suspect it is the docmd.cancelevents at the end of the code, but not sure how to fix it. Thanks Marcus

Dim MyPracticeName, MyPracticeCode, MyPracticeLocation, MyVisionEmis
Dim PracticeNameFalse, PracticeCodeFalse, PracticeLocationFalse, VisionEmisFalse

MyPracticeName = Me.txtPracticeName & ""
MyPracticeCode = Me.txtPracticeCode & ""
MyPracticeLocation = Me.txtaddress1 & ""
MyVisionEmis = Me.cmbvisionemis & ""

Select Case MyPracticeName
Case Is = ""
Me.txtPracticeName.BackColor = "8421631"
PracticeNameFalse = "False"
MsgBox "Please enter a practice name", vbCritical
Case Else
Me.txtPracticeName.BackColor = "8421631"
PracticeNameFalse = "True"
Me.txtPracticeName.BackColor = "16777215"
End Select

Select Case MyPracticeCode
Case Is = ""
Me.txtPracticeCode.BackColor = "8421631"
PracticeCodeFalse = "False"
MsgBox "Please enter a practice code", vbCritical
Case Else
Me.txtPracticeCode.BackColor = "16777215"
PracticeCodeFalse = "true"
End Select

Select Case MyPracticeLocation
Case Is = ""
Me.txtaddress1.BackColor = "8421631"
PracticeLocationFalse = "False"
MsgBox "Please enter the first line of the address for the practice location", vbCritical
Case Else
Me.txtaddress1.BackColor = "16777215"
PracticeLocationFalse = "true"
End Select

Select Case MyVisionEmis
Case Is = ""
Me.cmbvisionemis.BackColor = "8421631"
VisionEmisFalse = "False"
MsgBox "Please select whether or not this is a vision or Emis practice", vbCritical
Case Else
Me.cmbvisionemis.BackColor = "16777215"
VisionEmisFalse = "true"
End Select

If PracticeNameFalse <> "False" And PracticeCodeFalse <> "False" And PracticeLocationFalse <> "False" And _
VisionEmisFalse <> "False" Then
DoCmd.CancelEvent


End If

End Sub

 

[Othello911]

If I may chime in here, I think you are having problems because of your "IF" function at the bottom. You are basically saying that if "ALL" the fields have invalid data, then do the canel event. I think you mean to use OR between your objects. Like my example below.

Private Sub Command11_Click()
Dim x5, x7, x9 As String
Text5.SetFocus
x5 = Text5.Text

Text7.SetFocus
x7 = Text7.Text

Text9.SetFocus
x9 = Text9.Text

If x5 = 1 Or x7 = 1 Or x9 = 1 Then
DoCmd.CancelEvent
End If
End Sub

However there are still some problems with your code, You say at the out set that if the field is empty a message box pops up, but your are not forcing the user to enter something in that field. You are merely ASKING them to. I have found that most users have a talent for ignoring message. I will (if you don't mind) rework some of your code to give you an idea of what I am talking about.

 

[Othello911]

Try this,
Dim MyPracticeName, MyPracticeCode, MyPracticeLocation, MyVisionEmis
Dim PracticeNameFalse, PracticeCodeFalse, PracticeLocationFalse, VisionEmisFalse
dim falseColor, trueColor



MyPracticeName = Me.txtPracticeName & ""
MyPracticeCode = Me.txtPracticeCode & ""
MyPracticeLocation = Me.txtaddress1 & ""
MyVisionEmis = Me.cmbvisionemis & ""

'This will make it eaiser if you deceide to change the color later.
falseColor = "8421631"
trueColor = "16777215"


if MyPracticeName = "" then
Me.txtPracticeName.BackColor = falseColor
PracticeNameFalse = "False"
MsgBox "Please enter a practice name", vbCritical
Me.txtPracticeName.setfocus

'the show is stopped here and the user MUST fill in or get the same message again.
docmd.cancelevent
'no need to check the other options as we donot want them to continue if something is wrong.
exit sub
Else
PracticeNameFalse = "True"
Me.txtPracticeName.BackColor = trueColor
End if

'HTH

Othello

 

[Pat Hartman]

For starters, when you post code, especially large blocks of it, use the code tag so that it retains its indentations. The proper way to cancel a cancellable event is with -
Cancel = True
So, I removed the extraneous code.

Dim MyPracticeName, MyPracticeCode, MyPracticeLocation, MyVisionEmis

MyPracticeName = Me.txtPracticeName & ""
MyPracticeCode = Me.txtPracticeCode & ""
MyPracticeLocation = Me.txtaddress1 & ""
MyVisionEmis = Me.cmbvisionemis & ""

Select Case MyPracticeName
    Case Is = ""
        Me.txtPracticeName.BackColor = "8421631"
        MsgBox "Please enter a practice name", vbCritical
        Cancel = True
    Case Else
        Me.txtPracticeName.BackColor = "16777215"
    End Select

Select Case MyPracticeCode
    Case Is = ""
        Me.txtPracticeCode.BackColor = "8421631"
        MsgBox "Please enter a practice code", vbCritical
        Cancel = True
    Case Else
        Me.txtPracticeCode.BackColor = "16777215"
    End Select

Select Case MyPracticeLocation
    Case Is = ""
        Me.txtaddress1.BackColor = "8421631"
        MsgBox "Please enter the first line of the address for the practice location", vbCritical
        Cancel = True
    Case Else
        Me.txtaddress1.BackColor = "16777215"
    End Select

Select Case MyVisionEmis
    Case Is = ""
        Me.cmbvisionemis.BackColor = "8421631"
        MsgBox "Please select whether or not this is a vision or Emis practice", vbCritical
        Cancel = True
    Case Else
        Me.cmbvisionemis.BackColor = "16777215"
    End Select
End If

End Sub

Othello911,
The If at the bottom was not wrong. When combining "not" conditions you must use AND rather than OR since one of the "not" conditions will always be true.
If X <> a AND X <> b says "return true if BOTH conditions are false.
If X <> a OR X <> b says "return true if EITHER condition is false.

marcuscoker,
Since people have trouble evaluating NOT conditions, especially compound NOT conditions, it is better to always use positive conditions when possible.
So, If X = a OR X = b is easier for most people to understand than If X <> a AND X <> b.

 

[marcuscoker]

Many thanks for that, Will try that now and see how it all works. I really appreciate your help

Thanks

Marcus

 

[marcuscoker]

Hi

That did the trick

I just replaced the if statement at the bottom of the page with

if cancel <>"true" then
docmd.close
docmd.openform "switchboard"
end if

Many thanks for that

Marcus