x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 257

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

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
moralju
• 4
• 3
• 2
1 Solution

MS Access Systems CreatorCommented:
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

Author Commented:
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

MS Access Systems CreatorCommented:
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

Author Commented:
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

Commented:
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

Commented:
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

Author Commented:
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

Commented:
Success with the sample :-)

Nic;o)
0

Author Commented:
its sweet.. there is a lot of stuff I can use
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.