Solved

Count number of fields and make sure user has entered corresponding # of number

Posted on 2004-03-30
9
223 Views
Last Modified: 2006-11-17
I have a database where the users have to rank 19 fields in order of 1-19.

They have to select a minimum of 8 fields -- so 1 - 8.
The problem is some users seem not to be able to count correctly and enter 1,2,4,5,6,7,8,9  - they omit the 3.
I need to be able to count the fields and make sure the user has entered the corresponding numbers.

So if they rank 12 fields then 12 fields need to be numbered 1 thru 12
0
Comment
Question by:moralju
  • 4
  • 3
  • 2
9 Comments
 
LVL 32

Expert Comment

by:jadedata
Comment Utility
If you are letting users enter this data directly into a table, this is was a mistake waiting to happen.

You need an Edit function that counts these fields and assures that control is exercised WHILE the user is completing the form.
And this data should absolutely be getting entered via a form, because this is the ONLY way you have to exercise ANY amount of control over data entry.

As the user is entering the data you recheck the data in the control to assure that it meets the business rules for the form.

-j-
0
 
LVL 1

Author Comment

by:moralju
Comment Utility
The user enters the data into a form.
I have code that counts a minimum of 8 fields are filled and makes sure fields are not dup.

  Dim ctl As Control
  Dim counter As Integer
      counter = 19        

         For Each ctl In Forms!frmMain.Controls
            If ctl.Tag = "Check" Then
         
          If ctl > 19 Then
       
            MsgBox "Rank # cannot exceed 19."
            ctl.SetFocus
            ctl = Null
          Exit Sub
         end if

          If ctl.Tag = "Check" And IsNull(ctl) Then
        counter = counter - 1
         End If
       End If
      End If
     
    Next ctl
   
               If Not counter >= 8 Then
           MsgBox "Please rank a minimum of 8"
           Exit Sub
       End If
0
 
LVL 32

Expert Comment

by:jadedata
Comment Utility
Now you need additional logic to assure that a number is only selected once,... an array with 19 elements comes to mind.

 For each test pass
 clear the array
 for each number found, toggle the array element to -1 (true)
 if another control is found with that same element the array will show you
0
 
LVL 1

Author Comment

by:moralju
Comment Utility
Well I do have logic to show the number is only selected once.
I'm sure this can be done on an easier scale but this is was I have.

Private Sub txtRank15_AfterUpdate()
  Dim ctl As Control

  Me.txtRank15.Tag = "nocheck"
For Each ctl In Me.Controls
    If ctl.Tag = "Check" Then
          If ctl = Me.txtRank15 Then
            MsgBox "Rank # can only be chosen once."
            txtRank15.SetFocus
            txtRank15 = Null
            Exit Sub
        End If
    End If
Next ctl
 Me.txtRank15.Tag = "check"
End Sub


The problem is lets say somebody enters 1,2,3,5,6,7,8,9

this code will fail b/c it will not find a duplicate.

but the user has made an error. they left out the 4.



0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 54

Expert Comment

by:nico5038
Comment Utility
Try:

Dim intI as integer
Dim intCtl as integer

intI = 0
intCtl = 0
For Each ctl In Me.Controls
    If ctl.Tag = "Check" Then
       intI = IntI + (IntI + 1)
       intCtl = intCtl + ctl.value
    End If
Next ctl

if intI <> intCtl then
   msgbox "Gap in sequence"    
endif

Personally I would probably take another approach.
I would offer two listboxes, one with all 19 options and one where the selected ones can be moved to.
The latter would have an Up and Down button to arrange the sequence.

This way the user won't see the sequencenumber and your program is in full control to make sure no gaps will be introduced.

I have a sample database for moving rows between listboxes and for moving Up and Down, drop me a line when interested.

Nic;o)
(Click my membername for email address)
0
 
LVL 54

Accepted Solution

by:
nico5038 earned 500 total points
Comment Utility
Hmm, I guess the loop is wrong, try:

Dim intH as integer
Dim intI as integer
Dim intCtl as integer

intH = 0
intI = 0
intCtl = 0
For Each ctl In Me.Controls
    If ctl.Tag = "Check" Then
       intH = intH + 1
       intI = IntI + (IntH + 1)
       intCtl = intCtl + ctl.value
    End If
Next ctl

if intI <> intCtl then
   msgbox "Gap in sequence"    
endif

Nic;o)
0
 
LVL 1

Author Comment

by:moralju
Comment Utility
that helped out a lot.. after speaking to you I was able to change my validation code to:

Dim Fform As String
Dim ctl As Control
Fform = Application.CurrentObjectName

Forms(Fform).ActiveControl.Tag = "nocheck"

For Each ctl In Forms(Fform).Controls
    If ctl.Tag = "Check" Then
          If ctl = Forms(Fform).ActiveControl Then
            MsgBox "Rank # can only be chosen once."
            Forms(Fform).ActiveControl.SetFocus
            Forms(Fform).ActiveControl = Null
            Exit Function
        End If
    End If
Next ctl

Forms(Fform).ActiveControl.Tag = "check"



made it a little more universal.

I would be intrested in you sample database..
my email: jmorales6@satx.rr.com
0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
Success with the sample :-)

Nic;o)
0
 
LVL 1

Author Comment

by:moralju
Comment Utility
its sweet.. there is a lot of stuff I can use
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now