Number checking Code insure user input is honest

[Stafford]

Number checking Code to ensure user input is honest

I have a Form that my users enter data into that essentially tells me how much work they have done.

The Data (PWRA Number) is a 10 Digit number that is always generated from a 3rd Party Program. It is generated with Zeros 1st.
These PWRAs Numbers are unique to the Branch Number that they were generated from. When the User completes a PWRA they then enter the numbers into my form, which performs a simple calculation and gives me a total of the PWRAs that they did in that branch.

I setup my key fields to be linked in this Fashion

Branch Number
1st PWRA Number
Last PWRA Number

So I have 3 Linked. When I had originally conceived of this I thought this would stop duplication.
The Problem is:
If the Branch number =1 the 1st PWRA =1 and the Last PWRA =1 then my unique number is 111. OK
However if the Branch Number =1 the 1st =1 and the last PWRA =2 The Unique Number is 112, but the 1st PWRA got counted again. Which of course is a duplicate and therefore I get inaccuracy.

Well, laziness has set in and the Users have found me out.

My question, finally is this, how can I code it so that the user can easily enter their numbers without fear of duplication. I am thinking that perhaps I should Search the record set for duplicates, Ahhhh.......... I dunno!

Here is the code I have running now.
--------------------------------------------------------------------------------
Private Sub txtTotal_AfterUpdate()

txtTotal = Val(txtLastPWRANumber) - Val(txtFirstPWRANumber) + 1

End Sub

Private Sub txtFirstPWRANumber_AfterUpdate()

txtFirstPWRANumber = Right("0000000000" & txtFirstPWRANumber, 10)

If Len(Me![txtFirstPWRANumber]) <> 10 Then
MsgBox "PWRA Number must be 10 digits in length"
Exit Sub
End If
End Sub

Private Sub txtLastPWRANumber_AfterUpdate()

txtLastPWRANumber = Right("0000000000" & txtLastPWRANumber, 10) ' Adds prefix of zeros to data entry
If txtFirstPWRANumber = " " Or txtLastPWRANumber = " " Then ' Checks to see if both fields are filled
' Do nothing
Else
txtTotal = Val(txtLastPWRANumber) - Val(txtFirstPWRANumber) + 1 ' Changes ASCII to Numeric and calculates
End If

If Len(Me![txtLastPWRANumber]) <> 10 Then
MsgBox "PWRA Number must be 10 digits in length"
Exit Sub
End If

End Sub
--------------------------------------------------------------------------------

 

[Stafford]

A partial solution is two unique indexes. One that includes Branch Number and 1st PWRA Number and a second that includes Branch Number and last PWRA Number. A more complete solution is a query that makes sure that there is no overlap in the ranges. Use a DCount() with a where clause similar to:

"BranchNumber = " & txtBranchNumber & " AND (FstPWRANum Between " & txtFstPWRANum & " AND " & txtLstPWRANum & " OR LstPWRANum Between " & txtFstPWRANum & " AND " & txtLstPWRANum & ")"

If the DCount() returns a value > zero, the new range overlaps an existing range. Put the DCount() in the Form's BeforeUpdate event and cancel the event if an error is founc.

I'm sorry Pat, but your solution is a little over my head. I've copied and modifed your code into my form, to no avail. I don't understand enough about DCount (Or VBA for that matter) to really know how to apply it.

I need an example that I can deconstruct, to perhaps understand how DCount works, would you have any links?

It's incredible, I purchased a book titled "Access 97: Unleashed" and the only thing it has unleashed is my frustration. I've had it for months now, and everytime I've gone to it for help it has given me nothing but an inferiority complex. At 900 pages I thought I could use it for something other than an armrest.