UNIQUE FIELD VALUES

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, I have scores of textboxes, each of which accepts a numeric value, on my
form. Each box can be null or any value within the given range (eg 1 to 80)
but no two boxes can have the same numeric value. How do I code such that I
don't have to repeat the code 80 times?
 
Write one function (not sub), and set that function to execute for all 80
text boxes.

Select all 80 text boxes (lasso them, click on them one at a time while
holding down the shift key, click your mouse in either of the ruler bars to
select everything in that row or column, or a combination of methods). Once
you have the 80 text boxes selected (and nothing else), look at the
properties window. Whatever you put there will apply to all 80 text boxes.
If you put, for example, =MyFunction() for the AfterUpdate event (complete
with equal sign and parentheses), that function will fire up whenever the
AfterUpdate event fires for any of the 80 controls.

In the function, you can refer to whatever control activated the routine
using Me.ActiveControl.
 
Glint said:
Hi, I have scores of textboxes, each of which accepts a numeric value, on my
form. Each box can be null or any value within the given range (eg 1 to 80)
but no two boxes can have the same numeric value. How do I code such that I
don't have to repeat the code 80 times?
Click to expand...


Assuming the text boxes are named something like txt1, ...,
txt80, you can use code along these lines in a function:

Public Function ChkAll()
Dim K As Integer

For K = 1 To 80
If Me.ActiveControl = Me("txt" & K) Then
If Me.ActiveControl.Name <> "txt" & K Then
Beep
Me.ActiveControl.Undo
End If
End If
Next K
End Function

Then set the text boxes BeforeUpdate event **property** to
=ChkAll()
This can be done most easily by dragging a selection box
around all 80 text boxes and entering the above in all the
text boxes at the same time.
 
Thanx Marshal,
But I don't seem to understand your code. Basically what I want to avoid is
double entry between 1 and 80. Any textbox can take any number or null; once
a number is entered into a textbox however, no other textbox should accept
that number for that record. In other words, for every record, the numbers
are unique to fields.

I tried your code and I did not get much result.
 
What is the data structure underlying your "scores of textboxes"? Is it
scores of fields in a single "wide" table, or a normalised structure in
which the value of each textbox is stored in a separate record in a
related table - or something else?
 
To answer your question John, the data is storeed in scores of fields in the
underlying table.
 
Glint said:
Thanx Marshal,
But I don't seem to understand your code. Basically what I want to avoid is
double entry between 1 and 80. Any textbox can take any number or null; once
a number is entered into a textbox however, no other textbox should accept
that number for that record. In other words, for every record, the numbers
are unique to fields.

I tried your code and I did not get much result.
Click to expand...


Gee, Glint, "I did not get much result" really doesn't
provide me with enough information to figure out what you
did or what happend.

So, ok, I went and built an unbound form with your
arrangement and noticed two issues. One, the Undo method
doesn't seem to work for Me.ActiveControl(??) so I removed
that line and, two, I forgot to cancel the event so the bad
text box wouldn't lose the focus. This is the new version
of the function:

Public Function ChkAll()
Dim K As Integer

For K = 1 To 80
If Me.ActiveControl = Me("txt" & K) Then
If Me.ActiveControl.Name <> "txt" & K Then
Beep
DoCmd.CancelEvent
End If
End If
Next K
End Function

The idea behind the function is that every time a value is
entered in any of the text boxes, it checks to see if any
other box has the same value. Once a value is accepted, the
entire set of values up to that point are guaranteed not to
have any duplicates. Since this will be true up to and
including when the 80th box is assigned a value, it should
meet the stated objective.
 
Thanx Marshal, that was very helpful.
--
Glint


Marshall Barton said:
Gee, Glint, "I did not get much result" really doesn't
provide me with enough information to figure out what you
did or what happend.

So, ok, I went and built an unbound form with your
arrangement and noticed two issues. One, the Undo method
doesn't seem to work for Me.ActiveControl(??) so I removed
that line and, two, I forgot to cancel the event so the bad
text box wouldn't lose the focus. This is the new version
of the function:

Public Function ChkAll()
Dim K As Integer

For K = 1 To 80
If Me.ActiveControl = Me("txt" & K) Then
If Me.ActiveControl.Name <> "txt" & K Then
Beep
DoCmd.CancelEvent
End If
End If
Next K
End Function

The idea behind the function is that every time a value is
entered in any of the text boxes, it checks to see if any
other box has the same value. Once a value is accepted, the
entire set of values up to that point are guaranteed not to
have any duplicates. Since this will be true up to and
including when the 80th box is assigned a value, it should
meet the stated objective.
Click to expand...
 
Back
Top