Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Directing the cursor. (VBA Access 2003 SP2)

    I am working on a medical database and one of the screens has about 15 test results needing verification. That is, where the data entry falls out of range then a message needs to appear asking for confirmation. A positive response allows the users to move on: a negative response returns the users to the control in question. I am working on a Function to both minimise lines of codes and standardise the response. I have no trouble passing the results and upper and lower limits to the function but the question is "How do I pass the name of the control to the function?"

    Here is the code so far. I tried "PreviousControl" but it takes me back to the control before the one being tested and the value passed to the function is the typed value within the control itself.
    ================================================== ===========
    Private Sub Ctl13resHaemoglobin_AfterUpdate() ' NOTE: There will be another 15 or so Subs like this one.
    LResult = 65
    UResult = 300
    With Me
    If .Ctl13resHaemoglobin < LResult Or .Ctl13resHaemoglobin > UResult Then
    Call LabParm(.Ctl13resHaemoglobin)
    End If
    End With
    End Sub
    -------------------------------------------------------------------------------------------------------------
    Private Function LabParm(ctlPrevious As Control)
    On Error GoTo LabParm_Error
    Dim Temp As Integer
    Temp = MsgBox("Results are either lower then " & LResult & " or higher than " & UResult & ". Is this correct?", vbQuestion + vbYesNo, "Laboratory Results: Out Of Range")
    If Temp = 6 Then
    Exit Function
    Else
    Set ctlPrevious = Screen.PreviousControl
    ctlPrevious.SetFocus
    Set ctlPrevious = Nothing
    End If
    On Error GoTo 0
    Exit Function
    LabParm_Error:
    MsgBox ...
    End Function
    ================================================== ===========

    Thanks
    Nick

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Directing the cursor. (VBA Access 2003 SP2)

    Instead of After Update, use the Before Update event of the controls, and set the Cancel argument to True if the response is out of the range.

    Private Sub Ctl13resHaemoglobin_BeforeUpdate(Cancel As Integer)
    LResult = 65
    UResult = 300
    With Me
    If .Ctl13resHaemoglobin < LResult Or _
    .Ctl13resHaemoglobin > UResult Then
    Call LabParm
    End If
    End With
    End Sub

    Private Function LabParm()
    On Error GoTo LabParm_Error
    If MsgBox("Results are either lower then " & LResult & _
    " or higher than " & UResult & ". Is this correct?", _
    vbQuestion + vbYesNo, _
    "Laboratory Results: Out Of Range") = vbNo Then
    DoCmd.CancelEvent ' sets Cancel to True
    End If
    Exit Function
    LabParm_Error:
    MsgBox Err.Description, vbExclamation
    End Function

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Directing the cursor. (VBA Access 2003 SP2)

    Here is a slightly different version where the check is moved to the common function. The event procedure only sets the value of the Cancel argument to the result of the function (True to cancel, False - default - to continue). You don't need to declare LResult and UResult at the module level in this version, they are passed to the function as arguments.

    Private Sub Ctl13resHaemoglobin_BeforeUpdate(Cancel As Integer)
    Cancel = LabParm(Me.Ctl13resHaemoglobin, 65, 300)
    End Sub

    Private Function LabParm(varVal As Variant, _
    LResult As Double, UResult As Double) As Boolean
    On Error GoTo LabParm_Error
    If varVal < LResult Or varVal > UResult Then
    LabParm = (MsgBox("Results are either lower then " & LResult & _
    " or higher than " & UResult & ". Is this correct?", _
    vbQuestion + vbYesNo, _
    "Laboratory Results: Out Of Range") = vbNo)
    End If
    Exit Function
    LabParm_Error:
    MsgBox Err.Description, vbExclamation
    End Function

  4. #4
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Directing the cursor. (VBA Access 2003 SP2)

    Thanks Hans. They are both excellent and I will use the second one becasue of its compactness.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •