Un-highlighting Required Fields - Page 3

Thanks Thanks:  0
Likes Likes:  0
Page 3 of 3 FirstFirst 123
Results 21 to 30 of 30

Thread: Un-highlighting Required Fields

  1. #21
    Board Regular
    Join Date
    Jan 2013
    Posts
    180
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Un-highlighting Required Fields

     
    I disabled Allen's code for required fields and used yours in the Form's Current event.
    I am getting an error message "The expression On Lost Focus you entered as the event property setting produced the following error: The expression you entered has a function name that [database name] can't find."

    I updated the code to:
    Code:
    Dim ctl As Access.Control
        For Each ctl In Me.Controls
            With ctl
                Select Case .ControlType
                    Case acTextBox, acComboBox, acListBox
                        If (.Tag = "*") And (Len(ctl & "") = 0) Then
                            .BackColor = REQUIRED_BACKCOLOR
                            '.OnLostFocus = "=Hilight([" & .Name & "], False)"
                        Else
                            .BackColor = DEFAULT_BACKCOLOR
                        End If
                End Select
            End With
        Next
    and I am no longer seeing that error message.
    However, the required fields control are still staying as red after is has been changed from null (blank) to a value.
    Also, the other issue I am having is that when I add this record and leave a required field blank, there is nothing to trap and the record is being added with a blank required field. I need this required field to be filled. Therefore, I will need a message box.

    I was attempting to tweak a code to include a message box, but it is not working.

    Code:
    Private Sub Form_BeforeUpdate(cancel As Integer)
    Dim ctl As Access.Control
    Dim msg As String, Style As Integer, Title As String
    Dim nl As String
    If Me.NewRecord Then
        For Each ctl In Me.Controls
            With ctl
                Select Case .ControlType
                    Case acTextBox, acComboBox, acListBox
                        If ctl.Tag = "*" And ctl.Name = "" Then
                            msg = "Data Required for '" & ctl.Name & "' field!" & nl & _
                            "You can't save this record until this data is provided!" & nl & _
                            "Enter the data and try again . . . "
                            Style = vbCritical + vbOKOnly
                            Title = "Required Data..."
                            MsgBox msg, Style, Title
                            ctl.SetFocus
                            cancel = True
                        End If
                End Select
            End With
        Next
    End If

  2. #22
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    15,630
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Un-highlighting Required Fields

    I updated the code to:
    [QUOTE]
    when you did this you changed back to Allen's code so it won't do anything new.

    My function is right there - but it has to be in the form. did you put this in the form?

    In your other code where you try to have a message box there is this line:
    If ctl.Tag = "*" And ctl.Name = "" Then
    that looks wrong to me - no control should have a blank name.
    Last edited by xenou; Yesterday at 12:49 PM.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  3. #23
    Board Regular
    Join Date
    Jan 2013
    Posts
    180
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Un-highlighting Required Fields

    I changed the code back to what you originally gave me. I added the function in the form as well. It is under the Form_Current event.
    I am getting the error message I am getting an error message "The expression On Lost Focus you entered as the event property setting produced the following error: The expression you entered has a function name that [database name] can't find.".

    In your Function code, I see
    Code:
    Dim strBackColor As String
    but I don't see it being used in the function itself.


    Also, I thought that ctl.Name was the wrong one to use. What is the correct code to check if the field is blank?
    Last edited by MHamid; Yesterday at 01:08 PM.

  4. #24
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    15,630
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Un-highlighting Required Fields

    this might work:
    Code:
    If ctl.Tag = "*" And len(ctl & "") = 0 Then
    for the lost focus error, you should figure out what control is erroring and what function it is trying to find (that it can't find).
    Last edited by xenou; Yesterday at 01:08 PM.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  5. #25
    Board Regular
    Join Date
    Jan 2013
    Posts
    180
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Un-highlighting Required Fields

    Ok, I see why I was getting that message. Allen's hilight code was in the OnLostFocus and OnGotFocus events.
    And now for some reason your code is doing nothing at all ...

  6. #26
    Board Regular
    Join Date
    Jan 2013
    Posts
    180
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Un-highlighting Required Fields

    I deleted Allen's code completely and am solely using yours now. There was something else in the Tag line besides the asterisk (*). So I deleted any extra notations and just kept it as just the asterisk (*) for the required fields and now your code works perfectly

    I also made the update for the message box and it works!!!

    I tested the message box and after I leave two required fields blank, after the message boxes pop up I get an error message stating 'Error 2105: You can't go to the specified record". Is there a way to trap this error so it won't show up?

  7. #27
    Board Regular
    Join Date
    Jan 2013
    Posts
    180
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Un-highlighting Required Fields

    FYI - This is the code I am using with an error handler.

    Code:
    Private Sub AddRecord_Click()
    On Error GoTo errHandler
    Dim ID As Long
    'blnGood = True
        ID = DMax("RefID", "QAMaster")
    'If Not Me.NewRecord Then
        DoCmd.GoToRecord , , acNewRec
    'End If
        Me.Approved = DLookup("ApprovedBy", "QAMaster", "RefID=" & ID)
        Me.CycleMonth = DLookup("CycleMonth", "QAMaster", "RefID=" & ID)
        Me.DateReviewed = DLookup("DateReviewed", "QAMaster", "RefID=" & ID)
        Me.ReportType = DLookup("ReportType", "QAMaster", "RefID=" & ID)
        Me.MainSection = DLookup("MainSection", "QAMaster", "RefID=" & ID)
        Me.TopicSection = DLookup("TopicSection", "QAMaster", "RefID=" & ID)
        Me.ReviewerType = DLookup("ReviewerType", "QAMaster", "RefID=" & ID)
        Me.Reviewer = DLookup("Reviewer", "QAMaster", "RefID=" & ID)
        Me.ReviewerReportArea = DLookup("ReviewerReportArea", "QAMaster", "RefID=" & ID)
        Me.Individual = DLookup("OwnershipIndividual", "QAMaster", "RefID=" & ID)
        Me.Team = DLookup("OwnershipTeam", "QAMaster", "RefID=" & ID)
        Me.txtHyperlink = DLookup("Hyperlink", "QAMaster", "RefID=" & ID)
        Me.EnteredBy = Environ("USERNAME")
        Me.DateEntered = Format(DateValue(Now()), "Short Date")
        Me.CycleMonth.SetFocus
        Me.DateReviewed.SetFocus
        Me.ReportType.SetFocus
        Me.MainSection.SetFocus
        Me.TopicSection.SetFocus
        Me.ReviewerType.SetFocus
        Me.Reviewer.SetFocus
        Me.ReviewerReportArea.SetFocus
        Me.Individual.SetFocus
        Me.txtHyperlink.SetFocus
        Me.txtCount.SetFocus
    'blnGood = False
        Me.txtOther.Visible = False
        Me.txtOther2.Visible = False
        
        
    exitHere:
    Exit Sub
    errHandler:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume exitHere
    
    End Sub

  8. #28
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    15,630
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Un-highlighting Required Fields

    it looks like you have commented out the if and end if part:
    Code:
    'If Not Me.NewRecord Then
        DoCmd.GoToRecord , , acNewRec
    'End If
    That should be uncommented:
    Code:
    If Not Me.NewRecord Then
        DoCmd.GoToRecord , , acNewRec
    End If

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  9. #29
    Board Regular
    Join Date
    Jan 2013
    Posts
    180
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Un-highlighting Required Fields

    That is commented out on purpose because if I "uncomment" it, then the code will not work when creating a new record. I don't need that if statement.

    I tested it again by leaving 3 required fields blank and I got an error message statsing "run-time error '2110': [Database name] can't move focus to the control ListboxL2.

    That particular control is hidden until an option in L1 has been selected.

    The part of the code that it errors out at is in red below:
    Code:
    Private Sub Form_BeforeUpdate(cancel As Integer)
    'Dim strMsg As String
    Dim ctl As Access.Control
    Dim msg As String, Style As Integer, Title As String
    Dim nl As String
    If Me.NewRecord Then
        For Each ctl In Me.Controls
            With ctl
                Select Case .ControlType
                    Case acTextBox, acComboBox, acListBox
                        If ctl.Tag = "*" And Len(ctl & "") = 0 Then
                            msg = "Data Required for '" & ctl.Name & "' field!" & nl & _
                            "You can't save this record until this data is provided!" & nl & _
                            "Enter the data and try again . . . "
                            Style = vbCritical + vbOKOnly
                            Title = "Required Data..."
                            MsgBox msg, Style, Title
                            ctl.SetFocus
                            cancel = True
                        End If
                End Select
            End With
        Next
    End If
    Last edited by MHamid; Yesterday at 03:19 PM.

  10. #30
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    15,630
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Un-highlighting Required Fields

      
    maybe something like IF Control is not hidden Then set focus otherwise do nothing. I don't think a field that is potentially hidden from the user should be required anyway ... how are they supposed to give it a value if they can't see it?

    as far as the moving to a new record, I thought we saw this before and the error was trying to move to a new record when you are already on a new record. Since that's not possible, you can't run that line of code when you are on a new record.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com