Link to home
Start Free TrialLog in
Avatar of billcute
billcute

asked on

"Select Case" Amendment

There are some fields that needs to be flagged on multiple occassions based on criteria functions of the data entry. I need this flexibility.
'************
Public Function ChkCtl(ByRef mCtl As Control) As Boolean
  ChkCtl = True
        Select Case ExtractTag(mCtl.Tag, 2)
            Case "ne"
                If Len(Nz(mCtl.Value)) = 0 Then ChkCtl = False
            Case "1ne"
                If Len(Screen.ActiveForm.txtPermitNo) > 0 And Len(Nz(mCtl.Value)) = 0 Then ChkCtl = False
            Case "2ne"
                If Len(Screen.ActiveForm.cboSConnType) > 0 And Len(Nz(mCtl.Value)) = 0 Then ChkCtl = False
            Case ">0"
                If Not Nz(mCtl.Value) > 0 Then ChkCtl = False
            Case "tf"
                If Nz(mCtl.Value, 99) <> 0 And Nz(mCtl.Value, 99) <> -1 Then ChkCtl = False
        End Select
  Exit Function
End Function
' *********
The above worked with a Mandatory Field function at this link:
https://www.experts-exchange.com/questions/22861455/Tag-property-question.html

To achieve the new proposition, I have now organized form fields in five categories and set group tags
in each control in their respective groups as shown below.

I currently have a mandatory field check that seems limited sort off,
as such I want to amend it.

Since the tags are set based on their groups, I want to be able to
activate or de-activate group tags based on some criteria by calling the MyTag function
from the cboConnSize and Form's Current Event handlers.

****The example below are an idea...experts can use other methods****
_________
Example of Grouping of Tag properties.

    "ne"                  "1ne"                    "2ne"                 "3ne"                     "4ne"
txtcboName       txtPermitNo          txtPNo                txtCustomer         cboSupplier
txtHouseNo        txtPermitDate       txtPDate            txtCustPhone        txtSPhone
txtStreet                txtIssueDate        txtCertNo           txtCustFax            txtSFax
txtCity                     txtCIDAte             txtCertDate        txtCustCity            txtSCity
' ***************
For example:
I have 3 pages in my data entry sample form:

Page 0 = are form fields that will always be included in all mandatory check - the
                form fields are required at all time

Some of the form fields in Page1 and Page 2 of my data entry form are then added to Page0 to be checked based on the criteria defined below:
 
(a). If cboWConnType = "TA" And _
           cboServiveType = "DM" And _
            cboConnSize <= 2 Then
           
       ' Display (*) for the following:
         (a). Display all required fields in Page 0 in which the Tag properties are set [ne]
         (b), Display all required fields in Page 1 along with item (a) above where Tag fields are set to
                 [1ne]
          (c). Display all required fields in Page1 along with items (a) and (b) above WHERE Tag Fields
                 are set to [2ne].
         (d). Display all required fields in Page2 along with items (a) and (b) above WHERE Tag Fields
                 are set to [4ne].

(b).  (a). If cboWConnType = "WC" And _
           cboServiveType = "SP" And _
            cboConnSize => 3 Then
 
        ' Display (*) for the following:
         (a). Display all required fields in Page 0 in which the Tag properties are set [ne]
         (b), Display all required fields in Page 1 along with item (a) above where Tag fields are set to
                 [2ne]
          (c). Display all required fields in Page2 along with items (a) and (b) above WHERE Tag Fields
                 are set to [3ne].
          (d). Display all required fields in Page2 along with items (a) and (b) above WHERE Tag Fields
                 are set to [4ne].
 
Take note that tags [2ne] and [4ne] are common to the two criteria above.
 
Something like the select case below to work with my Existing "Mandatory Fields" Function would be great -  (See this link for Mandatory Function below).
Avatar of incrediblejohn
incrediblejohn

TMI
Can't understand what you want.

You sure you don't just need to build the Case statement or ElseIf statement, one item at a time and test it?
Avatar of billcute

ASKER

Incrediblejohn:
See the attached sample

Regards
Bill
I have the ideas but need assistance in putting the "Select Case and Function" to call "Tag" Function  together.
' *****Example **********
Suggested Function '<<==== I am only suggesting, not too sure I am doing the right thing.
 
Public Sub Tagfields(boone As Boolean, boo1ne As Boolean, boo2ne As Boolean, 
                                     boo3ne As Boolean, boo4ne As Boolean)
        Call Tagfields("ne", boone)
        Call Tagfields("1ne", boo1ne)
        Call Tagfields("2ne", boo2ne)
        Call Tagfields("3ne", boo3ne)
       Call Tagfields("4ne", boo4ne)
    End Sub
 
 
 Select Case Me.cboWConnType.Column(0)
        Case "TA"
            If Me.cboServiceType.Column(0) = "DM" Or Me.cboTest.Column(0) = "CM" And _
                  Me.cboConnType.Column(1) <= 2 Then
                'This is 1a
                Call allTagfields(True, False, False, False)
            ElseIf Me.cboServiceType.Column(0) = "TA" Or Me.cboTest.Column(0) = "TP" And _ 
                  Me.cboConnType.Column(1) => 3 Then
                'This is 1b
                Call allTagfields(False, True, True, True)  '<<=== tags set for [ne],[1ne], [2ne], [4ne]
            End If
        End Select
Select Case Me.cboWConnType.Column(0)
        Case "WC", "WP"
            If Me.cboServiceType.Column(0) = "DM" Or Me.cboTest.Column(0) = "CM" And _ 
                  Me.cboConnType.Column(1) =>3 Then
                'This is 1a
                Call allTagfields(True, False, False, False)
            ElseIf Me.cboServiceType.Column(0) = "SP" Or Me.cboTest.Column(0) = "SD" And _ 
                  Me.cboConnType.Column(1) => 3 Then
                'This is 1b
                Call allTagfields(False, True, True, True) '<<=== tags set for [ne],[2ne], [3ne], [4ne]
            End If
        End Select

Open in new window

Ok, you have a public sub called Tagfields with 5 paramenters.
Then inside it you the same sub. Not going to work.
Can I assume that your controls have Tags which are "ne", "1ne", etc? And these are hardcoded in the properties of the control?
Can I assume that you are check to see what was picked in the combo box and then hiding or showing the controls based on their Tag property?

So, inside the function (or sub, depending if you want to return a value or not), you take the true boolean and loops through the controls collections and show or hide everything with the corresponding tag? How am I doing so far?

Also, I was thinking why not just pass the combo column value to the sub?
Call Tagfields(Me.cboWConnType.Column(0))

and in Tagfields(BVal strThis as String)
Case "TA"
For each ctl in Controls
'blah blah
Loop
Case "WC", "WP"
For each ctl Controls
If ctl.tag = "xxx"

John,
You seems to have some ideas per your last comment.
I am uploading a sample for you to visualize the problem:
http://www.ee-stuff.com/Expert/Upload/getFile.php?fid=5952 

When user clicks on the AddNew button, the tags in the controls will flag in a msgbox
if the controls are required but contain no records...in this case, when users enter data in the controls, the controls will no longer flag...

Regards
Bill

I'll look at it from work tomorrow.
You aren't dealing with your functions correctly.
ok

Thanks for trying.
Bill
John:
I noted some naming problem in the first sample, please disregard the first uploaded sample dated 12-02-07. Some of the controls were not properly named and the select Statement has now been amended to synchronize with the amended controls.

As a result, I have decided to upload a new sample with all the corrections made in it.
http://www.ee-stuff.com/Expert/Upload/getFile.php?fid=5958 

Regards
Bill
incrediblejohn:
Any luck with the code?

Regards
Bill
incrediblejohn:
I hope you've not forgotten all about me.

Regards
Bill
Bill, I think this sub:

    Public Sub Tagfields(boone As Boolean, boo1ne As Boolean, boo2ne As Boolean, boo3ne As Boolean, boo4ne As Boolean)
        Call Tagfields("ne", boone)
        Call Tagfields("1ne", boo1ne)
        Call Tagfields("2ne", boo2ne)
        Call Tagfields("3ne", boo3ne)
        Call Tagfields("4ne", boo4ne)
    End Sub

...is supposed to be something like this:

    Public Sub Tagfields(boone As Boolean, boo1ne As Boolean, boo2ne As Boolean, boo3ne As Boolean, boo4ne As Boolean)
        Call ChkCtl("ne", boone)
        Call ChkCtl("1ne", boo1ne)
        Call ChkCtl("2ne", boo2ne)
        Call ChkCtl("3ne", boo3ne)
        Call ChkCtl("4ne", boo4ne)
    End Sub

And when you call it:

    Call allTagfields(False, True, True, True)

...you aren't supplying enough arguments. You'll need to add the boo4ne on the end e.g.

    Call allTagfields(False, True, True, True, True)
Angelplay:
Thank you for your assistance.

I now need a "Select Case" to call the "AllTagFields" function .

I modified my original function such that the Tags can easily be called from a "Select Case...."

I am not sure I did it right...Please check me out....since I received a COMPILE ERROR afterwards
' *******************************
ORIGINAL FUNCTION                             ' <<<=== Note this
Public Function ChkCtl(ByRef mCtl As Control) As Boolean
  ChkCtl = True
 Select Case ExtractTag(mCtl.Tag, 2)
    Case "ne"
      If Len(Nz(mCtl.Value)) = 0 Then ChkCtl = False
    Case "1ne"
      If Len(Screen.ActiveForm.txtPermitNo) > 0 And Len(Nz(mCtl.Value)) = 0 Then ChkCtl = False
    Case "2ne"
      If Len(Screen.ActiveForm.cboWConnType) > 0 And Len(Nz(mCtl.Value)) = 0 Then ChkCtl = False
    Case ">0"
      If Not Nz(mCtl.Value) > 0 Then ChkCtl = False
    Case "tf"
      If Nz(mCtl.Value, 99) <> 0 And Nz(mCtl.Value, 99) <> -1 Then ChkCtl = False
    End Select
End Function
' ************

For example I tried code below and I rceived a "Compile Error": "Type mismatch:

.....on....
"ne"
.....from...
Call chkCtl("ne", boone)
' ***************************
Public Sub AllTagfields(boone As Boolean, boo1ne As Boolean, boo2ne As Boolean, boo3ne As Boolean, boo4ne As Boolean)
        Call chkCtl("ne", boone)         '<<=== Debug highlighted "ne" on this line.
        Call chkCtl("1ne", boo1ne)
        Call chkCtl("2ne", boo2ne)
        Call chkCtl("3ne", boo3ne)
        Call chkCtl("4ne", boo4ne)
    End Sub
' *********

MODIFIED FUNCTION                            ' <<<=== Note this
Public Function chkCtl(ByRef mCtl As Control) As Boolean
    chkCtl = True
    Select Case Me.cboWConnType.Column(0)
        Case "WC"
            If Me.cboServiceType.Column(0) = "DM" Or Me.cboServiceType.Column(0) = "CM" And _
                Me.cboConnSize.Column(1) >= 3 Then
                'This is 1a
                If Len(Nz(mCtl.Value)) = 0 Then chkCtl = False        '<<<====
                    Call ExtractTag(mCtl.Tag, 2)                      '<<<====
                    Call AllTagfields(False, True, True, True, True)  '<<<====
                End Select
            ElseIf Me.cboServiceType.Column(0) = "SD" Or Me.cboServiceType.Column(0) = "SP" And _
                Me.cboConnSize.Column(1) >= 3 Then
                'This is 1a
                If Len(Nz(mCtl.Value)) = 0 Then chkCtl = False        '<<<====
                   Call ExtractTag(mCtl.Tag, 2)                       '<<<====
                   Call AllTagfields(False, True, True, True, True)   '<<<====
                    End Select
                End If
            End Select
         ' .....................................
         ' ......................................
    Exit Function
End Function


What am I doing wrong here.

Regards
Bill
Angelplay:

Do you think the two lines below should be part of my modified function....The line code are from my orignal function?...I am not too sure..  What do you think?

 Case ">0"        '<<=== Not too sure what this case is for...
      If Not Nz(mCtl.Value) > 0 Then ChkCtl = False
    Case "tf"
         ' <<<For checkbox type tags>>>
      If Nz(mCtl.Value, 99) <> 0 And Nz(mCtl.Value, 99) <> -1 Then ChkCtl = False
All that code is a bit of a mess and I'm finding it very hard to read.

I obviously guessed wrong about your mistake. incrediblejohn was definately right, you don't want to be calling that same sub from inside itself. ChkCtl takes a single argument of type "control" and you are passing it a "string" and a "boolean". This is why you're getting a compile error.

I think you should consider getting a book on VBA because I don't think you really understand the code you're typing which makes things difficult.

To make things easier for us to help you please could you describe in plain english what functions / subs you have and what they do e.g.

    Function ExtractTag
        Returns the contents of a controls Tag element formatted appropriately

    Sub AllTagFields
        Runs TagFields Sub 5 times with different arguements
AngelPlay.
Not to worry...
I understood what you are trying to say... ...please find below

(1).  Function ExtractTag
        Returns the contents of a controls Tag element formatted appropriately

(2).   Sub AllTagFields
        Runs TagFields Sub 5 times with different arguements

(3). Public Function chkCtl(ByRef mCtl As Control) As Boolean
      Extract tags based on the SELECT CASE and where the controls = 0 ...i.e no data in it.

(4). Function MandatoryFields() As Boolean      
        ' The dependant controls are covered by specific tagvalues:

and here is the function:
Function MandatoryFields() As Boolean      
         Dim myCtl As Control
         Dim myCtlList, myCtlName As String
            MandatoryFields = True

  ' First the basic controls
  For Each myCtl In Me.Controls
    If Len(myCtl.Tag) > 1 Then
       If ChkCtl(myCtl) = False Then
          myCtlList = myCtlList & Me.Controls("l_" & myCtl.Name).Caption & ", "
            On Error Resume Next
          myCtl.BackColor = 16776960
            On Error GoTo 0
       Else
            On Error Resume Next
            myCtl.BackColor = 13434879
            On Error Resume Next
       End If
    End If
  Next

  ' The dependant controls are covered by specific tagvalues:
  ' first the Me.txtPermitNo) >> 1ne
  ' second the Me.cboSConnType) >> 2ne
    If Len(myCtlList) > 0 Then
      MsgBox "Please fill the following field(s) before continuing:" & vbCrLf & myCtlList
        MandatoryFields = False
    End If
    Exit Function
End Function
' *****************

Regards
Bill
Bill - brilliant!!! Makes lots of sense.

I'm a little curious - I think your chkCtl function acts more like this:

    Function chkCtl
        Checks a control's data is allowed based on its tag e.g. not empty, numeric...


Right, no on to your issue! Am I right in saying you previously had 4 different types of data (ne, 1ne, 2ne, 3ne) and now you have 5 (ne, 1ne, 2ne, 3ne, 4ne)? And you want to modify your functions / subs to accomodate the new data checking for 4ne? If this is the case all you need to do is update your select code to look at the 4ne case, possibly something like this:

    SELECT CASE ExtractTag(mCtl.Tag, 2)
        Case "ne"
            If Len(Nz(mCtl.Value)) = 0 Then ChkCtl = False
        Case "1ne"
            If Len(Screen.ActiveForm.txtPermitNo) > 0 And Len(Nz(mCtl.Value)) = 0 Then ChkCtl = False
        Case "2ne"
            If Len(Screen.ActiveForm.cboWConnType) > 0 And Len(Nz(mCtl.Value)) = 0 Then ChkCtl = False
        Case ">0"
            If Not Nz(mCtl.Value) > 0 Then ChkCtl = False
        Case "tf"
            If Nz(mCtl.Value, 99) <> 0 And Nz(mCtl.Value, 99) <> -1 Then ChkCtl = False
        Case "4ne"
            If *FAIL_CONDITION* Then ChkCtl = False
    End Select

I don't really know what you're failure condition is supposed to be though. What does data in a 4ne have to be like? More importantly what can't 4ne data be like?
Angel play:
Controls with tags set to "4ne" are:
cboSupplier, txtSPhone, txtSFax and txtSCity respectively.
 
Note:
From my main question above, I had the following controls with tags set as follows:
I want to be able to have several controls flag when they are called.

This is a table of tag groupings in my data entry form.

    "ne"                  "1ne"                    "2ne"                 "3ne"                     "4ne"   '<<<--- Tags set
cboName                txtPermitNo          txtPNo                txtCustomer         cboSupplier
txtHouseNo            txtPermitDate       txtPDate            txtCustPhone        txtSPhone
txtStreet                 txtIssueDate        txtCertNo           txtCustFax            txtSFax
txtCity                     txtCIDAte             txtCertDate        txtCustCity            txtSCity
' ***************
So what controls should flag if my select case from function:

 Public Function chkCtl(ByRef mCtl As Control) As Boolean
Select Case Me.cboWConnType.Column(0)
        Case "WC"
            If Me.cboServiceType.Column(0) = "DM" Or Me.cboServiceType.Column(0) = "CM" And _
                Me.cboConnSize.Column(1) <= 2 Then
                'This is 1a
                If Len(Nz(mCtl.Value)) = 0 Then chkCtl = False        '<<<====
                    Call ExtractTag(mCtl.Tag, 2)                      '<<<====
                    Call AllTagfields(True, False, False, False, True)  '<<<====
                End Select
            ElseIf Me.cboServiceType.Column(0) = "SD" Or Me.cboServiceType.Column(0) = "SP" And _
                Me.cboConnSize.Column(1) >= 3 Then
                'This is 1a
                If Len(Nz(mCtl.Value)) = 0 Then chkCtl = False        '<<<====
                   Call ExtractTag(mCtl.Tag, 2)                       '<<<====
                   Call AllTagfields(False, False, True, False, False)    '<<<====
                    End Select
                End If
            End Select
  '....................
' ......................
End Sub
' ***********
Example A
 If user selects "WC" in cboWConnType, Selects "DM" or "CM" in cboService Type
and cboConnSize <= 2 Then  Call AllTagfields(True, False, True, False, True)    '<<=== See this

 In the case, the only controls expected to flag in a msgbox are: "ne" and "4ne" only :  '<<<====
 which are :
cboName, txtHouseNo, txtStreet, txtCity   '<<=== "ne"
cboSupplier, txtSPhone, txtSFax, and txtSCity ' <<=== "4ne"
 
 Example B
 If user selects "WC" in cboWConnType, Selects "SD" or "SP" in cboService Type
and selects cboConnSize >= 3 Then
and cboConnSize <= 2 Then  Call AllTagfields(False, False, True, False, False)    '<<=== See this

 In the case, the only controls expected to flag in a msgbox are: "ne" and "4ne" only :  '<<<====
 which are :
txtPNo, txtPDate, txtCertNo, txtCertDate '<<=== "2ne"

I hope this is clearer.
Ok, I believe the sub "TagFields" highlights fields with tags set to the arguement it's passed i.e:

    Call TagFields("ne")

...would highlight the fields cboName, txtHouseNo, txtStreet, txtCity.

----------------------------------------------------------------------------------------------
The sub "AllTagFields" is a quick way to do this for all 4 groups ne, 1ne... e.g:

    Call AllTagFields(True, True, True, True, True)

...is the same as:

    Call TagFields("ne")
    Call TagFields("1ne")
    Call TagFields("2ne")
    Call TagFields("3ne")
    Call TagFields("4ne")

----------------------------------------------------------------------------------------------
Sounds like you want to be able to highlight items based on selections in your CBOs (the purpose being to highlight which fields need filling!). Think we need a new function (you still need chkCtl for marking up incorrectly filled fields on submit). Suggest you add something like this:

    Public Sub mrkCtl(ByRef mCtl As Control)
        Select Case Me.cboWConnType.Column(0)
            Case "WC"
                If Me.cboServiceType.Column(0) = "DM" Or Me.cboServiceType.Column(0) = "CM" And Me.cboConnSize.Column(1) <= 2 Then
                    Call AllTagfields(True, False, False, False, True)
                ElseIf Me.cboServiceType.Column(0) = "SD" Or Me.cboServiceType.Column(0) = "SP" And Me.cboConnSize.Column(1) >= 3 Then
                    Call AllTagfields(False, False, True, False, False)
                End If
        End Select
    End Sub

    Public Sub cboWConnType_AfterUpdate()
        mrkCtl
    End Sub

    Public Sub cboServiceType_AfterUpdate()
        mrkCtl
    End Sub

    Public Sub cboConnSize_AfterUpdate()
        mrkCtl
    End Sub
Angelplay:
What happen to your very first post on the subject...?

I thought you changed that to : -

Public Sub AllTagfields(boone As Boolean, boo1ne As Boolean, boo2ne As Boolean, boo3ne As Boolean, boo4ne As Boolean)
        Call ChkCtl("ne", boone)
        Call ChkCtl("1ne", boo1ne)
        Call ChkCtl("2ne", boo2ne)
        Call ChkCtl("3ne", boo3ne)
        Call ChkCtl("4ne", boo4ne)
    End Sub

...and the call is: -

Call AllTagfields(True, False, False, False, True)    

etc....

(2). I have pasted the new function and set the AfterUpdate Event for the following controls:
       
Private Sub cboWConnType_AfterUpdate()
        mrkCtl
    End Sub

    Private Sub cboServiceType_AfterUpdate()
        mrkCtl
    End Sub

    Private Sub cboConnSize_AfterUpdate()
        mrkCtl
    End Sub
 
Now how do we integrate all of the above with the function below which can be called from btnAddNewRecord:

If Not Mandatory Fields Then
      Exit Sub
End If
' *****************

Function MandatoryFields() As Boolean      
         Dim myCtl As Control
         Dim myCtlList, myCtlName As String
            MandatoryFields = True

  ' First the basic controls
  For Each myCtl In Me.Controls
    If Len(myCtl.Tag) > 1 Then
       If ChkCtl(myCtl) = False Then
          myCtlList = myCtlList & Me.Controls("l_" & myCtl.Name).Caption & ", "
            On Error Resume Next
          myCtl.BackColor = 16776960
            On Error GoTo 0
       Else
            On Error Resume Next
            myCtl.BackColor = 13434879
            On Error Resume Next
       End If
    End If
  Next

  ' The dependant controls are covered by specific tagvalues:
  ' first the Me.txtPermitNo) >> 1ne
  ' second the Me.cboSConnType) >> 2ne
    If Len(myCtlList) > 0 Then
      MsgBox "Please fill the following field(s) before continuing:" & vbCrLf & myCtlList
        MandatoryFields = False
    End If
    Exit Function
End Function
' *****************

Regards
Bill
Urgh. I still don't think I'm trying to write code that does what you want. I think I'm only going to get to the bottom of this if I take a look at the database but I don't have access to a copy of MS Access for about 4 weeks (not going to be in the office).

Could you write some "pseudo code" that explains what you want doing? Something like:

    User_clicks_add button
        Check data in controls - base this on user cbo selections and control tag property
        If data passes all checks then do something

Would be quite helpful to see a list of ALL your subs and functions associated with this form since everytime I suggest something we seem to uncover something else.
AngelPlay,
Yes, it's a liitle difficult without you seeing the sample...

However, I am sure that you got the picture based on what you wrote here:......
....User_clicks_add button
        Check data in controls - base this on user cbo selections and control tag property
        If data passes all checks then do something

Further explanation
(a). User fill in the data entry form then clicks the btnAddNew button
(b). Based on the user cbo selection and the control tag property
       ---- Function checks for required fields based the cbo Selection
       ---- If the required controls (with the tags property) are missing
              data or cotained no data then,
       ----  Request user via a msgbox to enter the required fields that are missing data.
      *** If user eters data in those controls and clicks the btnAddNew again
           and after function checks and no data is missing in the controls then
           proceed to save current record to tblMain and present user with a new
           blank screen

Simple as that....

Regards
Bill

I wouldn't loop through and build and "These controls are empty list". I would stop the loop if one is found, pop up a message box and set focus to that contro. That works better in my world, anyway. And it helps the user without leaving the keyboard:

Function MandatoryFields() As Boolean      
         Dim myCtl As Control
         Dim myCtlList, myCtlName As String
            MandatoryFields = True

  ' First the basic controls
  For Each myCtl In Me.Controls
    If Len(myCtl.Tag) > 1 Then
       If ChkCtl(myCtl) = False Then
         msgBox "You need to fill myCtl.Name"
         myCtl.SetFocus
         myCtl.BackColor = whatever
         Exit For
       Else
            myCtl.BackColor = 13434879
       End If
    End If
  Next

  ' The dependant controls are covered by specific tagvalues:
  ' first the Me.txtPermitNo) >> 1ne
  ' second the Me.cboSConnType) >> 2ne
    Exit Function
End Function
Angelplay / incrediblejohn:

okay John,
I have placed the modified MandatoryFileds fucntion in my sample form

Angelplay suggested in addition .....the following:

Public Sub AllTagfields(boone As Boolean, boo1ne As Boolean, boo2ne As Boolean, boo3ne As Boolean, boo4ne As Boolean)
        Call chkCtl("ne", boone)
        Call chkCtl("1ne", boo1ne)
        Call chkCtl("2ne", boo2ne)
        Call chkCtl("3ne", boo3ne)
        Call chkCtl("4ne", boo4ne)
    End Sub
' *****************

How do we now modify the "chkctl" function? I am thinking something like but how do we integrate the line below:

Public Function chkCtl(ByRef mCtl As Control) As Boolean
        Select Case Me.cboWConnType.Column(0)
            Case "WC"
                If Me.cboServiceType.Column(0) = "DM" Or Me.cboServiceType.Column(0) = "CM" And Me.cboConnSize.Column(1) <= 2 Then
                    If Len(Nz(mCtl.Value)) = 0 Then chkCtl = False        '<<<==== Do I make sense here?
                        Call AllTagfields(True, False, False, False, True)
                ElseIf Me.cboServiceType.Column(0) = "SD" Or Me.cboServiceType.Column(0) = "SP" And Me.cboConnSize.Column(1) >= 3 Then
                    If Len(Nz(mCtl.Value)) = 0 Then chkCtl = False    '<<<==== Do I make sense here?
                    Call AllTagfields(False, False, True, False, False)
                End If
        End Select
' .....................................
' .....................................
    End Function

Public Function chkCtl(ByRef mCtl As Control) As Boolean
        Select Case Me.cboWConnType.Column(0)
            Case "WC"
                If Me.cboServiceType.Column(0) = "DM" Or Me.cboServiceType.Column(0) = "CM" And Me.cboConnSize.Column(1) <= 2 Then
                    If Len(Nz(mCtl.Value)) = 0 Then chkCtl = False        '<<<==== Do I make sense here?
                           Call chkCtl("ne", boone)
                ElseIf Me.cboServiceType.Column(0) = "SD" Or Me.cboServiceType.Column(0) = "SP" And Me.cboConnSize.Column(1) >= 3 Then
                    If Len(Nz(mCtl.Value)) = 0 Then chkCtl = False    '<<<==== Do I make sense here?
                    Call chkCtl("1ne", boo1ne)
                End If
        End Select
' .....................................
' .....................................
    End Function

You have too many functions running around creating too much complexity.
You get the values, and do the dirty all in a row. Don't need to get creative and gold plate everything.
Maybe not even use any functions. Just one nice long code where you can build it one item at a time rather than get caught up in your shoestrings.

I am leaving for ski camp tomorrow morning early. I will promis to look at this more but you have GOT to simplify it because once the simple version works, then everything else you add one at a time.

incrediblejohn,
I gave it a trial and I received a compile "error': "Variable not defined"

...on boone

...from
Call chkCtl("ne", boone)

....from your last posted code.

Regards
Bill

Ok, sorry I just cut and pasted that call.
What is "boone"? You aren't sending it but your call is referencing it:

Your function, ckkCtl, has 1 incoming parameter but you are sending it 2:
1 incoming parameter: Public Function chkCtl(ByRef mCtl As Control) As Boolean
2 parameters being sent: Call chkCtl("ne", boone)
Throw away boone and see what happens.
You are stepping through your code, right?

incrediblejohn:
I think we need to re-define this code below (after getting rid of "boone")
Public Sub Tagfields
        Call chkCtl("ne")
        Call chkCtl("1ne")
        Call chkCtl("2ne")
        Call chkCtl("3ne")
        Call chkCtl("4ne",)
    End Sub
...
and the call is;
Call chkCtl("ne")

I tried your last posted code and I am now getting a "Complie Error": Type Mismatch

on ..."ne"

from..

Call chkCtl("ne")

Regards
Bill
incrediblejohn:
It might be a good idea to download my sample db on the subject for your visualization:
http://www.ee-stuff.com/Expert/Upload/getFile.php?fid=5958 

Regards
Bill
ASKER CERTIFIED SOLUTION
Avatar of ldunscombe
ldunscombe
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Leigh,
Thanks for your input....From the way it looks, It appears you understood what I am looking for. I will test your suggested ode in the office today and give you a feedback later.

Regards
Bill
Leigh,
I ran a quick test with your suggested code. It works great and the code is quite flexible to manipulate to my taste.
Anyway, please let me try it out extensibly in the office and will let you know.

Regards
Bill
Leigh,
Everything seems to work fine except one little snag - I am getting a Run-Time Error '2475':
"You entered an expression that requires a form to be the active Window"

..and everytime I get that msgbox, and no matter what controls are being tested, debug always highlight the line below even when data is entered into txtHouseNo....

If Len(Screen.ActiveForm.txtHouseNo) > 0 And Len(Nz(mCtl.Value)) = 0 Then chkCtl = False

I must agree that the error only occur when I tried switching from the form view to the design  / code  view screen,
The reason you are getting the error is because Access is trying to validate all of the data in the form before closing it, by moving the focus to the database window before the form has carried out the before update event you are not allowing the validation checks to occur. It shouldn't pose a problem in normal usage however and if you close the form by clicking the exit button before you open it in design mode you won't get the error.


if you change the syntax of the following select statement to reference the form controls instead of the active screen controls you won't get the error either.

        Select Case CtlTag
            Case "[ss]"
                If Len(Nz(mCtl.Value)) = 0 Then chkCtl = False
            Exit Function
            Case "[tt]"
                If Len(Me.txtHouseNo) > 0 And Len(Nz(mCtl.Value)) = 0 Then chkCtl = False '<<<<<<<<<Note me.txtHouseNo
            Exit Function
            Case "[vv]"
                If Len(Me.cboStreetName) > 0 And Len(Nz(mCtl.Value)) = 0 Then chkCtl = False '<<<<<<<<  Note me.cboStreetName
            Exit Function
            Case ">0"
                If Not Nz(mCtl.Value) > 0 Then chkCtl = False
            Exit Function
           End Select

Leigh
Leigh:
Great. You actually made my day by resolving this post. There is a followup question on this particular question.

Everything here is exactly the same as the one at the link below except that I have added asteriks to be turned on and off at the same time the controls are on and off.

You can download my sample form there as well for your test. If you could assist with that too, it will be great.

https://www.experts-exchange.com/questions/22992167/Display-Asteriks-on-Required-Fields-based-on-criteria.html

Regards
Bill
Brilliant job.
To All Experts:
I have re-listed this question again at the link below. This is because the function is till working eratically -It needs some tweaking. I have attached a downloadable sample db also at the link to provide visual aid.
https://www.experts-exchange.com/questions/23027101/Setting-the-Tag-property-right.html

Regards
Bill