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.txtP ermitNo) > 0 And Len(Nz(mCtl.Value)) = 0 Then ChkCtl = False
Case "2ne"
If Len(Screen.ActiveForm.cboS ConnType) > 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).
'************
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.txtP
Case "2ne"
If Len(Screen.ActiveForm.cboS
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).
ASKER
Incrediblejohn:
See the attached sample
Regards
Bill
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
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"
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.
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"
ASKER
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
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.
ASKER
ok
Thanks for trying.
Bill
Thanks for trying.
Bill
ASKER
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
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
ASKER
incrediblejohn:
Any luck with the code?
Regards
Bill
Any luck with the code?
Regards
Bill
ASKER
incrediblejohn:
I hope you've not forgotten all about me.
Regards
Bill
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)
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)
ASKER
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.txtP ermitNo) > 0 And Len(Nz(mCtl.Value)) = 0 Then ChkCtl = False
Case "2ne"
If Len(Screen.ActiveForm.cboW ConnType) > 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
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.txtP
Case "2ne"
If Len(Screen.ActiveForm.cboW
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
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
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
ASKER
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
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
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
ASKER
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
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.txtP ermitNo) > 0 And Len(Nz(mCtl.Value)) = 0 Then ChkCtl = False
Case "2ne"
If Len(Screen.ActiveForm.cboW ConnType) > 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?
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.txtP
Case "2ne"
If Len(Screen.ActiveForm.cboW
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?
ASKER
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.
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
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
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
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
Call AllTagfields(True, False, False, False, True)
ElseIf Me.cboServiceType.Column(0
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
ASKER
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
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.
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.
ASKER
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
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
ASKER
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
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
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
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
If Len(Nz(mCtl.Value)) = 0 Then chkCtl = False '<<<==== Do I make sense here?
Call chkCtl("ne", boone)
ElseIf Me.cboServiceType.Column(0
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.
ASKER
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
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?
ASKER
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
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
ASKER
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
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
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
ASKER
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.txtH ouseNo) > 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,
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.txtH
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
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
ASKER
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
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
ASKER
Brilliant job.
ASKER
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
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
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?